fbpx

How do I fix the performance warning of ‘too many options set to autoload’?

If you have installed Scalability Pro, you may receive this warning at the top of the settings page for Scalability Pro:

On every page on the front-end of your website, WordPress will load ‘autoloaded’ options into a PHP object.

Analysing your wp_options performance warning

The way WordPress works, there are options for plugins stored in the wp_options table. There is a column in this table called ‘autoload’ and any options with the value ‘yes’ in the autoload column are loaded into an object on every page load.

The effect this has on your site is to increase the amount of PHP CPU time consumed as you can see from this screenshot – notice that the SQL time is low but the overall time is high. There are 3 things directly affecting page generation speed in WordPress: SQL time, PHP time and API calls.

The first plugin to install is called Query Monitor. Install that, load any of your front-end pages, and look at the new Query Monitor bar.

What you can see with the example above is 0.2815 seconds used for SQL calls. In the absence of API calls, the remaining time is PHP CPU time meaning 3.01 seconds are consumed by PHP CPU time to generate this page. That’s a *lot*.

To confirm you are not using API calls, click the Query Monitor bar and then click the ‘HTTP API Calls’ link on the left in the Query Monitor footer that appears.

Move your transients into your object cache – Install Redis Object Cache

By default, WordPress stores transients in your wp_options table with autoload set to ‘yes’. So, a major boost here is to install Redis object cache on your server and the Redis object cache plugin by Till Krüss. Make sure when installing Redis to configure it to be an in-memory storage system and not to persist items to disk.

You can find a guide for configuring Redis on your stack inside our complete WordPress stack guide.

Once you have Redis installed and the plugin installed, activate the plugin, then visit the settings for the plugin and enable the object cache.

Now, refresh your problem page twice – once to fill the object cache, the second time to see the impact.

Clean up your wp_options table

When you install Redis, your transients are now stored in your object cache but your old transients are still in wp_options. To fix this, run the following SQL:

delete from wp_options where autoload = 'yes' and option_name like 'transient%';

Once you’ve done that, you can recheck if Scalability Pro is still giving you a performance warning. If it is, you need to find your plugin culprit. Run this SQL command:

select left(option_name,15) option_name, count( * ) total
from wp_options where autoload = 'yes'
group by left(option_name,15)
order by total desc;

To run SQL you can use the SQL Executioner plugin.

Once you’ve run the above, it’ll give you an indication of the source of the wp_options that are set to autoload. For example, you might find that the top 20 entries are all from a particular plugin. Each plugin will tend to prefix the option_name with the name of their plugin. Then you know where you can delete them or switch them to autoload = ‘no’.

Now you want to either delete these excessive options or stop them autoloading. There are a few techniques:

  • Deactivate and delete the plugin, then delete the options
  • Alter settings in the plugin, if this is possible, to remove these options from wp_options
  • If viable, change autoload from yes to no

To switch autoload off, you need to know the prefix of the plugin options you are changing. Frequently plugins that only need to run on wp-admin will have autoloaded options which is crazy daft. I’ve seen sitemap plugins, mailing plugins, and other admin plugins with 100s or 1000s of options set to autoload. So – find the prefixes you wish to disable and then you can switch autoload off temporarily by using:

update wp_options set autoload = 'wpi' where option_name like 'badpluginprefix%' and autoload = 'yes';

Note: You can set the autoload value to anything other than ‘yes’ and they will not be autoloaded, but still be available if a plugin really does need them. You can use ‘no’ but if you use ‘wpi’ or some other value it lets you revert your changes if you need to.

This option is safe, because the options will remain in your database and be available for the relevant plugin, they just won’t be autoloaded on every page.

Once you have switched off autoload for your badly behaved plugins, test your front-end pages and confirm you have better speed.

To revert changes back if you need to:

update wp_options set autoload = 'yes' where autoload = 'wpi';

So – basically, run the SQL command at the top to identify which option_names are incorrectly set – you need to use some thinking power to figure out if they’re needed or not. Often you should know based on the plugin name whether it needs options loaded on every single page or if it is only used occasionally.

Then update the autoload value to anything other than ‘yes’ and they will no longer be autoloaded.

Summary

If you have over 1,000 options set to autoload, this typically adds 0.5 seconds to your page load (TTFB). So, if you have 5000 or more then you will have a really slow site on every page. Fixing autoloaded options improves speed for every single page on the front-end of your site.

Did this article answer your question?

2 Comments

  1. S. H.


    July 8, 2019 @
    4:15 pm

    Are there any alternative solutions if Redis is not possible on the server?

    Reply

    • Dave H.


      July 8, 2019 @
      4:44 pm

      Yes – firstly, MemCached is the best alternative.

      If that option isn’t available, hopefully you at least have SSD disks then you can test if you get a speed boost by persisting objects to disk.

      The W3 Total Cache plugin gives you that option. After installing that plugin, use these options:

      1. Enable the object cache from Performance->General->Object Cache section
      2. On Performance->Object Cache page, change ‘Default lifetime of cache objects’ to 1800 instead of 180
      3. On the same page, untick the ‘Store transients in database option’

      Before you do this, you should measure the performance of your slow page using Query Monitor. Then, after you’ve done the above, refresh the slow page TWICE. Once to populate the object cache and then second time to see the page generation speed using your disk-based object cache.

      You should see the total number of queries drop on the 2nd page load after enabling your object cache, and a whole bunch of transients will be moved to RAM instead of the DB. If it’s faster with your particular disks, you should now find EVERY RELATED page is faster.

      e.g. once the object cache for your shop is populated, you’ll find other shop pages faster too.

      You may find initial population of the object cache per page-type is slower than before depending on your disk types.

      Let me know your results.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


No Comments

  1. S. H.
    July 8, 2019 @ 4:15 pm

    Are there any alternative solutions if Redis is not possible on the server?

    Reply

    • Dave H.
      July 8, 2019 @ 4:44 pm

      Yes – firstly, MemCached is the best alternative.

      If that option isn’t available, hopefully you at least have SSD disks then you can test if you get a speed boost by persisting objects to disk.

      The W3 Total Cache plugin gives you that option. After installing that plugin, use these options:

      1. Enable the object cache from Performance->General->Object Cache section
      2. On Performance->Object Cache page, change ‘Default lifetime of cache objects’ to 1800 instead of 180
      3. On the same page, untick the ‘Store transients in database option’

      Before you do this, you should measure the performance of your slow page using Query Monitor. Then, after you’ve done the above, refresh the slow page TWICE. Once to populate the object cache and then second time to see the page generation speed using your disk-based object cache.

      You should see the total number of queries drop on the 2nd page load after enabling your object cache, and a whole bunch of transients will be moved to RAM instead of the DB. If it’s faster with your particular disks, you should now find EVERY RELATED page is faster.

      e.g. once the object cache for your shop is populated, you’ll find other shop pages faster too.

      You may find initial population of the object cache per page-type is slower than before depending on your disk types.

      Let me know your results.

      Reply

Pin It on Pinterest

Share This