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 🙂