optimizin mysql 3.23.55

[waren23]

hello to all!

i've got some problem with my webserver's mysql database. we operate 2 portals with PHP-MYSQL content management systems on the apache server with mysql 3.23.55. i know it should be upgraded, but we have no time for that at the moment. thus, we should optimize the current one and i need help with that.

so, we've got two portals with two main databases (lets call them A and B) for each portal. we are

publishing news and operating forums.
------- A
this is a website with news and forums, this causes some problems
- 130 tables, but most of them is used rarely except from news,forum and users
- news table with 10.000 rows, 40 new records inserted each day
- forum table with 36.000 rows, 60 records inserted each day
- users table accounts 8000 rows. mysql selects from this table as a joint query with forum table

- over 5.000 users per day read the news mostly between morning time 7.00-11.00, generating 15.000 page

impressions daily

------- problem
in the morning sometimes we publish an article that is read by 2-3.000 users immediately in just an hour or so, around 500 article-reading in the first 10 minutes. and then, the mysql is exhausted.
checking the process, I see 'Copying to tmp table', 'writing to net' and everything slows down

thats why i need help with mysql settings to optimize for that kind of usage. if anyone could help me, i really appreciate it or need some more statistics, i can post them too.

thanks in advance,
waren


------- hardware
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.40GHz
stepping : 9
cpu MHz : 2394.213
cache size : 512 KB

total: used: free: shared: buffers: cached:
Mem: 1049579520 991408128 58171392 0 9519104 395718656
Swap: 2582654976 289177600 2293477376
MemTotal: 1024980 kB
MemFree: 56808 kB
MemShared: 0 kB
Buffers: 9296 kB
Cached: 368412 kB
SwapCached: 18032 kB
Active: 499760 kB
Inactive: 401012 kB
HighTotal: 122044 kB
HighFree: 1908 kB
LowTotal: 902936 kB
LowFree: 54900 kB
SwapTotal: 2522124 kB
SwapFree: 2239724 kB


free disk space
mysql_log, mysql_tables 633 Mbyte
mysql_tmp 18 Fbyte

------- /etc/my.cnf
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking

set-variable = max_connections=350
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=2M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=15
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
set-variable = thread_concurrency=2
set-variable = tmp_table_size=128000000

log-bin
server-id = 1

# Point the following paths to different dedicated disks
tmpdir = /mnt/sata3/mysql-tmp

# The safe_mysqld script
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
#set-variable = ft_min_word_len=3

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=64M
set-variable = write_buffer=64M

[mysqlhotcopy]
interactive-timeout