MySQL intermittent when Wordpress is under heavy load

Question

I have a MySQL 5.1.61 database, working on two Apache web servers loaded by weight, hosting quite busy (100 thousand copies per day) Wordpress sites. I cache using Cloudflare, W3TC and Varnish. In most cases, the database server handles traffic very well. "show full processlist" shows 20-40 requests at any given time, with most of them waiting.

Periodically (especially when traffic or when a large number of comments are cleared), MySQL stops responding. I will find 1000-1500 requests, many "send data", etc. No particular query seems to strain the database (they are all standard Wordpress queries), but it seems that the simultaneous volume of queries causes all the queries to hang. I (as a rule) can still log in, run the "show a complete list of processes" or other requests, but there are already 1000 questions that are already sitting there. The only solution seems to be to restart mysql (sometimes violently via kill -9 if I cannot connect).

All tables are innodb, the server has 8 cores, 24 GB of RAM, a large amount of disk space and the following: 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 on how I can improve MySQL configuration or other steps to maintain database stability under heavy load?

Answer 1

As said, think outside the box and don’t be afraid why these requests are slow or hang somehow. An old but good source of problems even for (presumably;) intelligent system engineers is load balancing, which causes problems in web server or database sessions. When all this caching and load balancing continues, are you sure that everything is always interconnected as intended?

Answer 2

I agree with aldit and Bjoern

I am pretty noobish with mysql, but starting mysqltuner may reveal some optimization configurations based on recent DB queries https://github.com/rackerhacker/MySQLTuner-perl

And, if possible, store DB files on a physically separate partition from the OS, the OS can consume IO, which slows down the database. As in the case of problems with the Bjoern logrotate.

Answer 3

First, consider the basic behavior of the system when problems arise. Use both vmstat and iostat if you can find any problems. See if the system starts (pi, po columns in vmstat), and if a lot of I / O occurs. This is the first step in debugging your problem.

Another source of useful information is SHOW INNODB STATUS. See http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ for how to interpret the output.

Perhaps at some point in time, your records are killing read performance because they clear the query cache.