(Wasn't sure if this was a ServerFault question or StackOverflow, so please forgive cross post)
I have a MySQL 5.1.61 database running behind two load balanced Apache webservers hosting a fairly busy (100K uniques per day) Wordpress sites. I'm caching with Cloudflare, W3TC, and Varnish. Most of the time, the database server handles traffic very well. "show full processlist" shows 20-40 queries at any given time, with most being in the sleep state.
Periodically, though (particularly when traffic spikes or when a large number of comments are cleared), MySQL stops responding. I'll find 1000-1500 queries running, many "sending data", etc. No particular query seems to be straining the database (they're all standard Wordpress queries), but it just seems like the simultaneous volume of requests causes all queries to hang up. I'm (usually) still able to log in, to run "show full processlist", or other queries, but the 1000+ queries already in there just sit. The only solution seems to be to restart mysql (sometimes violently via kill -9 if I can't connect).
All tables are innodb, server has 8 cores, 24GB RAM, plenty of disk space, and the following is my my.cnf:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock port=3306 skip-external-locking skip-name-resolve user=mysql query_cache_type=1 query_cache_limit=16M wait_timeout = 300 query_cache_size=128M key_buffer_size=400M thread_cache_size=50 table_cache=8192 skip-name-resolve max_heap_table_size = 256M tmp_table_size = 256M innodb_file_per_table innodb_buffer_pool_size = 5G innodb_log_file_size=1G #innodb_commit_concurrency = 32 #innodb_thread_concurrency = 32 innodb_flush_log_at_trx_commit = 0 thread_concurrency = 8 join_buffer_size = 256k innodb_log_file_size = 256M #innodb_concurrency_tickets = 220 thread_stack = 256K max_allowed_packet=512M max_connections=2500 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 #2012-11-03 #attempting a ram disk for tmp tables tmpdir = /db/tmpfs01 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Any suggestions how I can potentially improve MySQL config, or other steps to maintain database stability under heavy load?