I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was high CPU usage in the server. All the time CPU load is 15-20. We used litespeed and MySQL 5.1 with CentOS 5.9 in Dual Xeon L5506, 12GB RAM server with Sata HDD.
So I analyzed the database and found there is only 4GB of data and index size of that DB and decided to convert to InnoDB. Once we did, we ended up having 80-150 CPU load and server was about to crash. So we transferred MySQL to another server with same config but to MySQL 5.5.
In New DB server CPU load is 1-2 and web server still on 4-6 constant CPU load.
Here is my my.cnf
[mysqld] innodb_file_per_table=1 local-infile = 0 default-storage-engine = InnoDB max_connections = 1000 innodb_buffer_pool_size = 8G innodb_flush_method = O_DIRECT innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_thread_concurrency = 8 innodb_file_format = Barracuda myisam_sort_buffer_size = 16MB query_cache_type = 1 query_cache_limit = 2M query_cache_size = 256M thread_cache_size = 16K key_buffer_size = 128M max_heap_table_size = 128M tmp_table_size = 128M join_buffer_size = 32M read_buffer_size = 32M read_rnd_buffer_size = 1M sort_buffer_size = 32M table_cache = 4K open_files_limit = 65535 log-slow-queries = /var/log/mysql/slowqueries.log long_query_time = 3
PS : Not all of our DB's are InnoDB, so MyISAM values are placed after good analysis.
Statistics : For 4 hours
Questions since startup: 7,339,471 Documentation ø per hour: 1,704,102 ø per minute: 28,402 ø per second: 473 Traffic ø per hour Received 4.8 GiB 1.1 GiB Sent 248.5 GiB 57.7 GiB Total 253.3 GiB 58.8 Gi