I have a pretty busy wordpress site. >3M pageviews per month. The last couple of weeks we're having MySQL issues. CPU over 500% (8 core, 256GB RAM, dedicated hardware) and very slow performance.
So I found this query dozens of times.
SELECT wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND (wp_postmeta.meta_key = 'tie_views')
AND wp_posts.post_type = 'post'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0
DESC LIMIT 0, 20
It looks like it's waiting to time out and then get killed. Can someone explain what this is ding exactly? And can anyone can tell me what's wrong with this query?
Please advice...
There's nothing inherently wrong with this query. I modified it to work with a database I have and I'm able to grab 10,000 records in 0.0409 seconds (on a db with 21k posts records and 137k postmeta records). That said, I have also modified my database a little bit, including partial indexes on the postmeta table which does speed up queries like these.
One thing you'll notice across WordPress is that larger and larger sites get slower and slower, especially when using a meta_query. (Generally it's worse and more noticeable when you're using multiple JOINs on the same table with OR […] WHERE).
There are a million Articles/SO Questions/Docs on "speed up a slow meta query in WordPress", and unfortunately you'll kind of be on your own to find which one works for you.
If that query is being run dozens of times (per page load??) make sure you're caching the results. Make sure you've got some good Object Caching going, and consider page caching as well - if you haven't already.
You could also consider caching the results of that query with WordPress Transients - which basically set the query results to a temporary option in the wp_options table, for a length of time you determine (30 seconds, 4 hours, 6 weeks, whatever) - and it will pull from there instead of running the query every time, until it expires.
One last thing that may or may not be related, is make sure your wp_options table isn't bloated. Lots of plugins leave lots of bloated information in there to die. And the default storage options set them the autoload: true which means they get loaded into memory on every page load. I've seen "lightweight/small/etc" websites that had a bloated wp_options table with hundreds of megabytes of years old garbage in it causing extreme load times, timeouts, and crashing.
So basically