I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours)
In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable = key_buffer=512M set-variable = max_allowed_packet=1M set-variable = table_cache=1024 set-variable = sort_buffer=16M set-variable = join_buffer=8M set-variable = record_buffer=8M set-variable = thread_cache=512 set-variable = max_connections=500 set-variable = tmp_table_size=128M set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=512M set-variable = query_cache_size=6M #set-variable = max_connect_errors=1000 #set-variable = back_log=100 #log-bin skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [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=256M set-variable = sort_buffer=124M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=8M set-variable = write_buffer=4M [mysqlhotcopy] interactive-timeout