(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:

wait_timeout = 300
max_heap_table_size = 256M
tmp_table_size = 256M
innodb_buffer_pool_size = 5G
#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
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).

#attempting a ram disk for tmp tables
tmpdir = /db/tmpfs01


Any suggestions how I can potentially improve MySQL config, or other steps to maintain database stability under heavy load?

  • Show us the output of SHOW FULL PROCESSLIST\G at that time? Do you see any lock? – quanta Nov 30 '12 at 8:38
  • There's also dba.SE and wordpress.SE. Anyway, you need more information, such as top output and SHOW FULL PROCESSLIST from mysql. – Michael Hampton Nov 30 '12 at 14:03

I see three(3) dead giveaways as to lack of performance

MySQL Version

You are using MySQL 5.1.61. Most people do not install the InnoDB Plugin that came with MySQL 5.1 starting with MySQL 5.1.38. InnoDB Plugin has new enhancements to enable InnoDB to perform multicore engagement.

Recommendation: Either install the InnoDB Plugin or Upgrade to MySQL 5.5

InnoDB Settings

This is what you have (from the question)

innodb_buffer_pool_size = 5G
#innodb_commit_concurrency = 32
#innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
thread_concurrency = 8
innodb_log_file_size = 256M

If you have InnoDB Plugin installed or upgraded to MySQL 5.5, you must tune InnoDB to activate the multicore enhancements.

Here are my recommendations:

innodb_thread_concurrency = 0 (default in MySQL 5.5)
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000

Please read my DBA StackExchange post from Mar 16, 2012 that discusses this at length.


Your innodb_buffer_pool_size may be too small. Try making it bigger. In fact, I wrote a post on Apr 22, 2011 in the WordPress StackExchange on sizing the buffer pool.

  • Thanks! This is extremely helpful. Do you have a recommendation on using the innodb plugin vs moving to 5.5? I'm on CentOS 6, so 5.1 is all we get (and I'll have to run a work-around to get 5.5). – EvilPluto Dec 1 '12 at 0:17
  • Personally, I would go with MySQL 5.5 because all the MySQL 5.1 bugs have been addressed. Notwithstanding, see my post on installing the InnoDB Plugin if you want to keep MySQL 5.1 : dba.stackexchange.com/a/18240/877 – RolandoMySQLDBA Dec 1 '12 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.