search

Automattic / WP-Job-Manager

Manage job listings from the WordPress admin panel, and allow users to post jobs directly to your site.
https://wpjobmanager.com
GNU General Public License v3.0
901 stars 368 forks source link

Excessive caching in wp_options #1100

Closed tomslominski closed 7 years ago

tomslominski commented 7 years ago

We've been looking at the performance of the server our site is hosted on recently, and to cut a long story short, the MySQL server keeps crashing, and our host has pointed us towards some problematic queries which strain the database, one of which is related to the wp_options table. The Query Manager plugin tells me that the wp_load_alloptions() function is executed on every page load, which takes roughly 0.18 to execute, which is considerably more than other queries.

Upon investigating the wp_options table, I've realised that its 234MB (according to MySQL Workbench), which is quite a lot of data to go through on every page load. 10401/13140 rows in that table belong to WP Job Manager (they begin either _transient_jm_ or _transient_timeout_jm_. I've done some research into this, and the only other issue I've come across is this issue on the WordPress forums, which explains that the transients are created due to caching. Additionally, most of these transients are set to autoload.

What I Expected To Happen

Personally, I think since the plugin seems to have generated around 200MB of cached data (we clean the database using WP Optimize to remove old transients fortnightly), this data should be put into another table to avoid being loaded on every page load by WordPress' wp_load_alloptions() function.

What Happened Instead

The data is cached inside wp_options, which slows down the loading of the site, as around 200MB of data has to be autoloaded on every page load.

Steps to Reproduce the Bug

Run the site as normal for a few weeks and see the transients build up. Personally, we use the Listify theme, with just over 200 listings. We run WP 4.8, with the following plugins related to WPJM: Listing Labels for WP Job Manager, Regions for WP Job Manager. The server runs Apache 2.4.25, PHP, MariaDB 10.1.25-MariaDB, Linux kernel 2.6.32-696.3.2.el6.x86_64 (so presumably RHEL).

I've been looking at this for a few hours now, so I'm hoping I'm not barking up the wrong tree here! I know there's logic behind using default WP transients to cache things, but I'm hoping there's some way this can be optimised. The two main things that worry me is that almost 1800 of these are autoloaded on every page load, as well as the overall size of wp_options thanks to the caching.

jom commented 7 years ago

Hi @tomslominski! Thanks for the report. I think most of those are probably the search caching. The 30 day caching for searches is way too long. I imagine the heftiest of the cached objects are coming from that.

I've started working on this in #1101. I think this is a high priority and hope to get it in by the next point release.

One recent change (#1024) might be the cause of all the autoloaded items. I've added an expiration to those cached items in the new PR so they aren't autoloaded. Once that is implemented, there should only be ~2 WPJM transients autoloaded. Of the WPJM transients that are getting autoloaded, do any of them have serialized/non-integer values?

tomslominski commented 7 years ago

Wonderful, thanks :D

A lot are, yeah. About 1757 WPJM transients, about 1739 are serialised, although a few are empty (78). All of these serialised WPJM transients are called "_transient_jmcats", and are just a list of WP_Terms with some meta attached.

That sounds great. I will keep an eye on updates and let you know if its improved things.

jom commented 7 years ago

@tomslominski Are any of the 1739 serialized transients set to autoload? All autoloaded WPJM transients should be integers (or blank, I suppose). The fix above should also limit those.

tomslominski commented 7 years ago

Yes. Sorry, all of the 1757 transients I talk about are autoloaded, I didn't make that clear.

jom commented 7 years ago

Interesting... in my instance all _transient_jm_cats_* transients are set to not autoload. I'm not sure how they could have autoload set to 'yes' as they are given expirations: https://github.com/Automattic/WP-Job-Manager/blob/master/wp-job-manager-functions.php#L787

tomslominski commented 7 years ago

Huh, not sure what happened there. I've done a search for just "_transient_jmcats%" and "_transient_timeout_jmcats%" and actually the first 1000 or so don't have timeouts set, but the following few thousand do - that might be part of the issue. The following few thousand with timeouts are also set not to overload.

No idea why those don't have a timeout set - perhaps DAY_IN_SECONDS was set to 0 or something ridiculous like that?

Anyway, I suppose I should delete the ones without timeouts manually and let them recache properly?

jom commented 7 years ago

Strange... safe to delete all the transients. DAY_IN_SECONDS comes from WP core... not sure what could change that. Hopefully, this PR will at least make them not pile up.

tomslominski commented 7 years ago

Sure. Thanks for your help, I'll let you know how it goes after the update is released.

tomslominski commented 7 years ago

Just removed those rows and wp_load_alloptions() has gone down from ~0.18 → 0.0059. Massive success. Not sure what happened there with those rows, but that's what seems to have been the issue. Nevertheless, I am glad you're looking at further performance improvements 👍 Thanks!