Using WP-CLI doctor Command to Fix Large wp_options autoload Data

Two of my favorite WP-CLI commands are awesome profile and doctor (see my profile tutorial) which help WordPress site administrators keep site secure, clean and fast! The WordPress database has a table called wp_options which is used to store core, theme and plugin settings. Some of these settings are very essential to each component and must be autoloaded to ensure they are available on every single page load. Unfortunately not all plugin and theme developers use autoloaded data frugally.

This tutorial will help you identify large autoloaded values to speed up your WordPress site or WooCommerce store. I highly recommend communicating with the developers of the theme or plugin that has a large amount of autoloaded data so that they can make fixes that will benefit their entire userbase.

This post was inspired by Kinsta's tutorial on cleaning up autoloaded data adjusted to use WP-CLI 🙂

Using WP-CLI doctor Command to Fix Large wp_options autoload Data

Install WP-CLI doctor command with this command

wp package install wp-cli/doctor-command --allow-root

You can list all of the checks the doctor command provides like this

wp doctor list --allow-root

We are going to use the top one: autoload-options-size

+----------------------------+--------------------------------------------------------------------------------+
| name                       | description                                                                    |
+----------------------------+--------------------------------------------------------------------------------+
| autoload-options-size      | Warns when autoloaded options size exceeds threshold of 900 kb.                |
| constant-savequeries-falsy | Confirms expected state of the SAVEQUERIES constant.                           |
| constant-wp-debug-falsy    | Confirms expected state of the WP_DEBUG constant.                              |
| core-update                | Errors when new WordPress minor release is available; warns for major release. |
| core-verify-checksums      | Verifies WordPress files against published checksums; errors on failure.       |
| cron-count                 | Errors when there's an excess of 50 total cron jobs registered.                |
| cron-duplicates            | Errors when there's an excess of 10 duplicate cron jobs registered.            |
| file-eval                  | Checks files on the filesystem for regex pattern `eval\(.*base64_decode\(.*`.  |
| option-blog-public         | Confirms the expected value of the 'blog_public' option.                       |
| plugin-active-count        | Warns when there are greater than 80 plugins activated.                        |
| plugin-deactivated         | Warns when greater than 40% of plugins are deactivated.                        |
| plugin-update              | Warns when there are plugin updates available.                                 |
| theme-update               | Warns when there are theme updates available.                                  |
| cache-flush                | Detects the number of occurrences of the `wp_cache_flush()` function.          |
| php-in-upload              | Warns when a PHP file is present in the Uploads folder.                        |
| language-update            | Warns when there are language updates available.                               |
+----------------------------+--------------------------------------------------------------------------------+

Run the autoload-options-size check

wp doctor check autoload-options-size --allow-root

It looks like this site has excessive amounts of autoloaded data in the WordPress wp_options table, here the  warning shows 2.62mb of autoloaded data!

+-----------------------+---------+------------------------------------------------------------+
| name                  | status  | message                                                    |
+-----------------------+---------+------------------------------------------------------------+
| autoload-options-size | warning | Autoloaded options size (2.62mb) exceeds threshold (900kb) | 
+-----------------------+---------+------------------------------------------------------------+

Let's find out what it is by showing the largest rows with autoloaded data, borrowed from Kinsta's fantastic tutorial.

wp db query "SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM $(wp db prefix --allow-root)options WHERE autoload='yes' UNION SELECT 'autoloaded data count', count(*) FROM $(wp db prefix --allow-root)options WHERE autoload='yes' UNION (SELECT option_name, length(option_value) FROM $(wp db prefix --allow-root)options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)"

Here is the same command just formatted for clarity 🙂

wp db query "SELECT 'autoloaded data in KiB' as name, ROUND(SUM(LENGTH(option_value))/ 1024) as value FROM $(wp db prefix --allow-root)options WHERE autoload='yes' \
UNION \
SELECT 'autoloaded data count', count(*) FROM $(wp db prefix --allow-root)options WHERE autoload='yes' \
UNION \
(SELECT option_name, length(option_value) FROM $(wp db prefix --allow-root)options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10)"

Now we get the output of the largest autoloaded rows, here it is wpmandrill-stats

+---------------------------------------+---------+
| name                                  | value   |
+---------------------------------------+---------+
| autoloaded data in KiB                |    2689 |
| autoloaded data count                 |     937 |
| wpmandrill-stats                      | 2274153 |
| aDBc_options_status                   |  105519 |
| _s_category_pricing_rules             |   31664 |
| rewrite_rules                         |   29788 |
| _wpallexport_session_10_              |   21159 |
| wp_user_roles                         |   12893 |
| woocommerce_tracker_ua                |   12272 |
| 38109fa951c6516a7421d978678f9d3d      |   11397 |
| woocommerce_wf_shipping_usps_settings |   10606 |
| _wpallexport_session_1_               |   10288 |
+---------------------------------------+---------+

I recommend inspection the large option values, using the option get command we grab the contents of the wpmandrill-stats option and place the option_value in the /tmp/mandrill-stats.txt file for inspection.

wp option get wpmandrill-stats --allow-root > /tmp/wpmandrill-stats.txt

If you analyze the option_value for the large option_name and you think it can be logically split into smaller options please pass this on the to the developer. It is very likely that some of the data does need to be autoloaded but the majority do not :).

Once you are feeling confident that the data does not need to be autoloaded, you can change the option_name to not be autoloaded anymore by changing the autoload value from yes to no.

wp db query "UPDATE $(wp db prefix --allow-root)options SET autoload='no' WHERE option_name='wpmandrill-stats'" --allow-root

Now you can run the doctor command again

wp doctor check autoload-options-size --allow-root

There we go success!

+-----------------------+---------+--------------------------------------------------------------------+
| name                  | status  | message                                                            |
+-----------------------+---------+--------------------------------------------------------------------+
| autoload-options-size | success | Autoloaded options size (465.35kb) is less than threshold (900kb). |
+-----------------------+---------+--------------------------------------------------------------------+

I hope this helped you reduce your WordPress databases's autoloaded data size 🙂