Slow still after WPI Performance Plugin
Mike A. asked 8 years ago
I'm working on a site hosted on Flywheel yet it’s running very slow (I have a lot of sites on Flywheel and none but this one are slow). This site isn’t using WooCommerce but I bought the WPI Performance Plugin to speed it up because it has a lot of custom post type data. You can go to https://www.shoppingbargains.com and login using your email address and see what's going on. I have installed Query Monitor and there are some issues so thank you for taking a look
Dave H. answered 8 years ago
Ok – the main thing I’m seeing is that for some reason you have roughly 350,000 options in your wp_options table with the autoload flag set.
There’s no way you need 350,000 options with autoload – php has to process them on every page – this is probably the cause of your 25 second page load.
The second thing is your ‘stores’ taxonomy has 4771 terms in it. That seems a lot, but maybe it’s correct. Can you confirm/clarify?
Then I installed SQL Executioner plugin so I could check wp_options and I ran the following command:
select * from wp_options where autoload = ‘yes’ limit 500;
What I saw was a bunch of unused options which are still being loaded and fed into PHP, filling up memory, using up CPU etc.
The two biggest culprits were the ewww image optimizer which is a horrible plugin for performance and then the wpseo plugin.
This query returns a count of 320,000 – basically your entire options table:
select count(*) from wp_options where autoload = ‘yes’ and option_name like ‘%wpseo%’;
So – here’s what I’ve done and how to revert it:
I disabled the WP SEO plugin (the one by Yoast). I like this plugin but I’m not sure how you got so many options under autoload into your wp_options table. I’ll raise a query with him because it’s not acceptable.
I ran this query so that you keep your options but they won’t be autoloaded:
update wp_options set autoload = ‘wpi’ where option_name like ‘%wpseo%’ and autoload = ‘yes’;
Hopefully Yoast will get back to us quickly with a solution for this and an explanation as to why these are all set to autoload. I presume it’s because the default on the table is ‘yes’ and he hasn’t set it to no specifically.
To revert, run the following command from SQL Executioner and then reactive the WP SEO plugin:
update wp_options set autoload = ‘yes’ where option_name like ‘%wpseo%’ and autoload = ‘wpi’;
FYI – now the select count statement returns just over 1000 auto_load options and your pages load (logged in) in 11 seconds instead of 27 seconds.
To get that faster, ask your hosts Flywheel to do the following:
1) Upgrade you from PHP 5 to PHP 7
2) Upgrade you from MySQL to PerconaDB or MariaDB
3) Configure your SQL cache properly for InnoDB – you want 500MB cache
4) Upgrade your disks to SSD if they aren’t already
Then you’ll be flying.
Dave H. replied 8 years ago In v2.73 I have added a test to check if wp_options is one of the causes of slow performance. If so, a performance alert is raised in the settings page along with info to fix.
Dave H. replied 8 years ago Also note that adding this filter he's talking about will do nothing to the existing 320,000 options slowing down your site. The point he's missing, that you or I need to get across is that his users can keep the full functionality AND full performance if he just modifies the options to autoload = no
Dave H. replied 8 years ago Can you tell him that it’s fine to have these items in wp_options but he *definitely does not* need to set the auto_load flag to ‘yes’. If he changes this, I won’t be telling the world to avoid using his sitemap functionality if you want a fast site. I’ll go tell him on twitter cos that link you gave doesn’t have comments enabled.
Mike A. replied 8 years ago From Yoast:
The wpseo_sitemap_xxxxxx_cache_validator is related to your sitemap and will appear many times. However, we have a filter to disable this:
add_filter(‘wpseo_enable_xml_sitemap_transient_caching’, ‘__return_false’);
You can learn more about the filter here: Sitemap Does Not Update. If you plan to use the filter please backup your site and database.
Article URL: http://kb.yoast.com/article/252-sitemap-does-not-update
Mike A. replied 8 years ago Wow, thank you. Impressive work!
I’ll reach out to Flywheel and to Yoast. I’m not using Ewww anymore (and haven’t in a long time) because it is horrible as you said (using WP Smush – anything better?). I didn’t realize Ewww was still cluttering the database. Suggestions on cleaning that out?
As for the 4000+ taxonomies, those are my “store” pages. There’s one for each merchant and there are nearly 5K merchants in my database. Not sure how to get around that since the “Clipper” system I’m using set it up that way.
Again thank you!
Dave H. replied 8 years ago In fact – you have premium SEO Yoast account – so can YOU raise a query with Yoast about this awful performance problem please? I can see your backend is flying now – I’m sure you’ll love the responsiveness! I’m pretty sure the last thing you need to figure out, other than your hosting, is the original point I also made about your 4000+ taxonomies – figure out what those are and why so many being loaded every page. It’s a lot.
Bobette K. answered 8 years ago
This is helpful, Dave … I use most of those you mentioned, too. Likely others do, too. Do you have a recommendation for optimizing images?
Dave H. replied 8 years ago Yes – from what I can see, they fixed the issue in terms of it creating MORE items in wp_options with auto_load='yes' but have yet to modify the plugin to clean up old options. That's what the issue they just created is about – so that means an upcoming version will look for old options set to autoload and remove them.
See this tweet from Yoast:
Mike A. replied 8 years ago Also, I was using the latest plugin.
Mike A. replied 8 years ago Another followup from Yoast Support with article referenced found at https://github.com/Yoast/wordpress-seo/pull/4520:
Thanks for following up with us.
The auto_load flag to “yes” no longer happen if you’re using the latest version of the Yoast SEO Premium. Please, refer to our this pull request in GitHub repository to see the fix.
That being said, if you’re not using the latest Yoast SEO v3.4.2, please update your plugin to the latest version.
On the other hand, if you’d like to disable the transient cache, you can do this by using the filter we provided you in the prior message. Once you disable the transient cache, you can remove all the existing transient cache related entries from your wp_options table.
Notice that before you remove the transient cache entries from the wp_options table, please take a backup of your database.
Please let us know if you’ve any questions.
Dave H. replied 8 years ago I doubt it’s related to the ACF analysis plugin – it’s the sitemap functionality and he’s set the options but for some reason he’s set them to autoload when they don’t need to autoload. Autoload options are ones that are loaded on every page and stored in a PHP array for quick reference. They are used by front-end Wordpress. e.g. Comment forms, widgets, stuff you can see, stuff users can interact with. Sitemaps are definitely not in this category. They don’t need to be autoloaded.
Dave H. replied 8 years ago Yeah he’s avoiding the problem – I’ve tweeted to him to see if he can fix it. It’s a single line of code he’ll need to alter to prevent it happening, although he should also add some code to alter all the existing autoload wp_options his plugin has set. 320,000 in your case when you only have 1000 real options for autoload.
Mike A. replied 8 years ago From Yoast again when I asked them about this:
“In regards to the transient cache, while we cannot say whether the transient cache is related to the ACF plugin or not, we do know that the wpseo_sitemap_xxxxxx_cache_validator is related to your sitemap and will appear many times. So, if you’d like you can disable this by using the filter we provided in the prior message.”
Mike A. replied 8 years ago Thinking more about this, I’m wondering if the Yoast SEO issue could have been caused by the Yoast SEO: ACF Analysis plugin?
Thanks. I did so and replied privately.
Hi Mike – no email coming through when i use password-reset feature. Can you set a password and submit the username and password here privately? (to admin and yourself) Make it a temporary user you can delete once I’ve fixed everything up for you.
ok – looking