Checking for Large Autoloaded Data with WP-CLI

WordPress databases comes with an options table, and entries in this table can be marked for autoload. Autoloading means the row of data will be included with each request to WordPress. If the amount of data being autoloaded becomes excessive – for example more than 10MB – it can cause all sorts of performance issues. Kinsta has an awesome post covering the topic and along with methods for dealing with it directly from the database: https://kinsta.com/knowledgebase/wp-options-autoloaded-data/.

Today we’ll be looking at uncovering excessive autoloaded data using WP-CLI.

First let’s check how much data is being autoloaded for this website.

wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024/1024, 2) as 'Autoload MB' FROM $(wp db prefix)options WHERE autoload='yes';"

Anything more then a few MB can most likely be improved. Next let’s see the top 10 largest items.

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

If you see anything with _transient you could try wp transient delete --expired. If that doesn’t resolve the issue then you can run wp transient delete --all however be warned this could affected things like logged in users and shopping carts.

Using tools like grep we can hunt for where these identified large rows are coming from. Searching might look something like this: grep -rnl "string-locator-search" wp-content/.

We can see more info about the theme or plugin using wp theme get <slug> or wp plugin get <slug>.

Figuring out why a theme or plugin is putting so much information in autoload can require some digging. In this instance I discovered String Locator is creating transients that don’t expire. So the fix should be to report this as a bug to the plugin author and manually delete these transients.

transient_to_delete=$(wp transient list --search="*string-locator-search-files*" --fields="name" --format="csv" | tail -n +2)
for transient in ${transient_to_delete[@]}; do
  wp transient delete $transient
done

After improvements are made we can run one of the commands at the start to see the changes it made. For this customer autoloaded data was reduced from 8.34 MB to 0.23MB. That’s a huge win for performance and overall stability of this website.