SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, ( SUM(v.pageviews)/(IF ( DATEDIFF('2018-11-11 18:12:01', DATE_SUB('2018-11-11 18:12:01', INTERVAL 29 DAY)) > 0, DATEDIFF('2018-11-11 18:12:01', DATE_SUB('2018-11-11 18:12:01', INTERVAL 29 DAY)), 1) ) ) AS avg_views FROM `example_popularpostssummary` v LEFT JOIN `example_posts` p ON v.postid = p.ID WHERE 1 = 1 AND p.post_type IN('post') AND v.view_datetime > DATE_SUB('2018-11-11 18:12:01'
This query will literally 100x your CPU utilization, even with MySQL’s query cache, because Hector doesn’t bother to round the dates in the query to the nearest minute. Common sense says, we should not recalculate the popularity of blog posts every second! This wasn’t a difficult problem to solve, however it was there undetected for months wasting a lot of electricity, and disrupted my weekend.
If your blog is crashed, you can quickly bring it back to life with
chmod 0 wp-content/plugins/wordpress-popular-posts
Here’s the tricky thing, I think this plugin comes bundled with “Social Warfare” as a widget. So you won’t see “wordpress-popular-posts” listed on the Plugins panel.
My client doesn’t want to upgrade Social Warfare because the newer version is known to have some issues, so upgrading this plugin (to possibly fix this query problem) was not the best option.
Apparently, you’re supposed to configure this plugin through the widgets panel under Social Warfare: Popular Posts. However, the widget settings panel does not mention any cache option!
How to manually turn on the cache option:
Find this section and change ‘active’ to true.
'cache' => array( 'active' => true, 'interval' => array( 'time' => 'hour', 'value' => 1 ) )