Suggestion: Add MAX_EXECUTION_TIME hint to queries

Hi @qvasi,

I guess if I configured data sampling, it would spam the database less, but I’m still concerned that the queries seems to run very long. Or do you think it’s just long running because it’s spamming the database too fast?

If I had to guess I’d say it’s the latter but there’s just not enough information to know for sure.

If:

• The site gets quite a bit of traffic every day, chances are that the database server can’t keep up with the constant read-write operations and eventually a deadlock happens – and in this case enabling the Data Sampling feature is the recommended action: the less write operations are being made, the faster things will be overall;
• The INNODB engine is disabled / not available and WPP’s tables are using another engine (like MyISAM). This would also explain why it takes so long writing to the database – enabling INNODB is the recommended course of action here;
• There are other database-intensive plugins installed on the site(s) which in conjunction with WPP are a little bit too much to handle for your database server (which is quite similar to the first IF from above) – you’ll want to take a closer look to the plugins you have installed and see if there’s any room for optimization;
• The combined traffic of all of the sites are a bit too much for the database server to deal with, so same recommendation as in #1;
• A combination of some / all of the above.

We are using mysql v 5.7+ so it would have supported MAX_EXECUTION_TIME hint.
If you added a max execution time hint like this (1 second in the example below), I would feel a bit more comfortable re-activating the plugin (…)

I’d love to give this a try. Problem is I can’t bump minimum MySQL version required to 5.7+ because WordPress itself supports 5.0+ (and, in consequence, so does the plugin). If I did, I might leave quite a few users in the dark because unfortunately not everyone is running the latest and finest version of everything on their sites because they can’t / they don’t want to / they don’t know they should.

Nonetheless, I’ll think about it so thanks for the recommendation.

Ok, I modified a copy of the plugin and tested it with 10000 ms (10 seconds) max exec time, and that worked fine on the almost no traffic alternate language site, but it was not enough at all on the high traffic site that originally caused the issues. Most or all of the queries on the settings page failed to complete within 10 sec (but at least that verified that the execution hint actually works).

I tried using 300 000 ms (5 minutes), but of course nginx that is in front of the wordpress server times out after 60 seconds and gives a 504 Gateway Time-out message when trying to go to /wp-admin/options-general.php?page=wordpress-popular-posts
I tried reducing the max time to 50 seconds, hoping that it were only one slow query and that it would manage to at least render the rest of the page within the remaining 10 seconds, but obviously there are a lot more queries on that page that adds up to a lot more than 60 seconds.

I managed to edit the cache and sample rate stuff by copying the url /wp-admin/options-general.php?page=wordpress-popular-posts&tab=tools from the low-traffic server. And the widget does seem to work. But I wasn’t sure if it just worked because it managed to finish within 5 minutes and that result were still cached. So then I cleared the cache.
And suddenly it was possible to load the frontpage settings. It now appears like it has not been any visitors at all, but there still is hall of fame list with several hundred thousands of views.

Maybe clearing the cache somehow triggered some kind of cleanup?

Maybe clearing the cache somehow triggered some kind of cleanup?

If by “clearing the cache” you mean this, then yes it did. Maybe WPP’s database tables got too large? How long has WPP been running on that site? And how many visitors does it get per day in average?

It’s been running for about a year or so using the plugin. The settings has been left on default (probably a bad idea) until yesterday.

The site belongs to a customer, and I just got access to their google analytics so that I could see the worst case throughput to tune the sampling rate. And it seems something *bad* happened 6. december 2017 when I updated plugins.

Up until then they had a somewhere between 10 thousand and 21 thousand pageviews per day and a bounce rate of about 50%; then the pageviews fell to below 200 pageviews per day and the bounce rate jumped to about 95% percent(!). Clearly they have not been paying much attention to the analytics… (They mentioned that the site had felt slow lately when corresponding with them when we killed the server temporarily yesterday, trying to stop the flow of hour long database queries, but they hadn’t bothered informing us that it was slow before the site actually stopped working, lol)

Most likely something changed between 7. november (which was the previous time i updated plugins) and 6. december that caused the site to be painfully slow. Not sure if it was the update itself or a transient condition during update or something that messed up the database tables. (It seems unlikely to me that the only thing that happened was that the database just went from “not-too-large” to “too-large” by coincidence the same day I updated the plugins.)

Maybe it would be a good idea if the settings were slightly more optimized by default for stupid devs like me that forget to even look at the settings? E.g. keeping the data forever and having no caching, seems to be options suitable only for very small sites or if you really know what you’re doing.

10k-20k views per day should be something any decent server can handle (assuming the server is properly configured, site’s nicely optimized for performance, et cetera.)

Maybe all your site needed was some optimizations (clean up dead transients from database, remove left over data from uninstalled plugins, optimize DB tables, and so on). You’d be amazed by the amount of crap plugins and themes leave behind in WordPress’ database. An alarming amount of them -in my experience- do not clean up after themselves.

Try installing WP-Optimize, it should help keep your database clean & tidy without much hassle.

Maybe it would be a good idea if the settings were slightly more optimized by default for stupid devs like me that forget to even look at the settings? E.g. keeping the data forever and having no caching, seems to be options suitable only for very small sites or if you really know what you’re doing.

Yeah, maybe you’re right. I think I can set WPP to store up to a year of data by default to help ease things up. I’ll think about it, thanks for the suggestion ??