2

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

2
  • Show us the output of SHOW FULL PROCESSLIST\G at that time? Do you see any lock?
    – quanta
    Nov 30, 2012 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. Nov 30, 2012 at 14:03

1 Answer 1

2

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_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
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.

Caching

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.

2
  • 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, 2012 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 Dec 1, 2012 at 18:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .