Managing your wp_options table

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
limit 20;

To run SQL you can use the SQL Executioner plugin or mysql from your server command line or phpMyAdmin.

With the query above, you’re looking to find which plugins have 100s or 1000s of entries set to autoload. Once you’ve run the query 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’.

If you don’t find anything significant, there may be large entries in your wp_options table. Some plugins store many MBs of data in wp_options when they really shouldn’t. Even if you use Redis or Memcached, that means when WordPress gets/saves all options that a large amount of data is being read/written.

To discover if you are suffering from this problem, run this query:

select left(option_name,15) option_name, autoload, sum(length(option_value)) totalsize
from wp_options 
group by left(option_name,15), autoload
order by totalsize desc;

With this query, look at the first few entries and see if they are orders of magnitude larger than the next entries. If so, this slows down sites even if Redis is used.

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.