we are running a very large WordPress site on multiple Amazon EC2 instances.
We have a very powerful database server (extra large, high-cpu), and six load balanced web servers.
We have a problem where the database server normally runs at between 1% to 15% CPU, but then jumps to 100% CPU. If left unchecked, the database server comes to halt for a period of time, bringing down the site. We are using WP-Supercache to reduce the load on the database server, and the normal server response time is between 50 and 500 milliseconds).
Using the monYOG tool (which analyzes MySQL's running processes and slow query log), I have found the following three queries which seem to be taking far too long. The problem is that nothing about them seems wrong, except that two of them are using SQL_CALC_FOUND_ROWS.
If I google "wordpress SQL_CALC_FOUND_ROWS", I find plenty of articles indicating that a bug either in MySQL, or WordPress (or both) is responsible for site meltdowns because of queries using SQL_CALC_FOUND_ROWS.
Here is one such article:
Here are the problematic queries:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE ?=? AND wp_posts.post_type = ? AND (wp_posts.post_status = ?) ORDER BY wp_posts.post_date DESC LIMIT ?, ? Avg. time: 41 seconds Max. time: 2:34 seconds Query Occurrence: 36% SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE ?=? AND wp_posts.post_type = ? AND (wp_posts.post_status = ?) ORDER BY wp_posts.ID ASC LIMIT ?, ? Avg. time: 47 seconds Max. time: 2:35 seconds Query Occurrence: 28% SELECT wp_posts.* FROM wp_posts WHERE ?=? AND YEAR(wp_posts.post_date)=? AND MONTH(wp_posts.post_date)=? AND DAYOFMONTH(wp_posts.post_date)=? AND wp_posts.post_name = ? AND wp_posts.post_type = ? ORDER BY wp_posts.post_date DESC Avg. time: 25 seconds Max. time: 1:34 seconds Query Occurrence: 14%
Has anyone else had such problems? What are my options other than hacking the WordPress core to not use SQL_CALC_FOUND_ROWS, increasing the cache time to a much higher level, or switching the site into WP-Supercache's "lock down" mode?
Any help at all would be greatly appreciated! Thank you.