Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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