Splitting a 7GB wp_options table: an autoload field guide

The Loom from the lead developer arrived at 23:41. A WooCommerce site he had inherited from another agency was averaging 2.3 seconds to first byte, the cache was hot, the queries looked fine, and the only thing he could find that smelled off was a wp_options table sitting at 6.8GB on disk. He had checked the slow query log. Nothing in there pointed at a culprit. The autoloaded rows alone were over 400MB.

This is the most common shape of a wp_options problem. It does not show up as a slow query because it is not a slow query, it is one fast query that drags a giant payload back to PHP on every request. Once that payload crosses a few megabytes, the cost moves from the database into maybe_unserialize() and PHP's memory allocator, where almost no profiler will catch it without you pointing at it.

Reading the autoload column properly

The wp_options table has a column called autoload. When WordPress boots, it issues a single query that pulls every row where autoload is on into a serialized array called alloptions, then caches that array for the request. That array is what your PHP process is dragging through memory before WordPress has even decided what page you asked for.

The column behaves like a boolean even though its type is varchar(20). Most rows are yes or no. Some plugins started writing on and off around 2022, and WordPress 6.6 introduced auto-on and auto-off so that core can promote and demote rows automatically. Your query needs to handle all of them or it will lie to you.

The query that surfaces the noise

This is the one to keep in a snippet manager:

SELECT
  option_name,
  LENGTH(option_value) AS size_bytes,
  autoload
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto', 'auto-on')
ORDER BY size_bytes DESC
LIMIT 40;

Run it against a recent dump. The top 40 rows usually explain almost all of the weight. You will see four shapes repeat:

• A plugin that wrote a transient without an expiry, so it never got cleared.
• A logging plugin that flushed entries into a single serialized array.
• An A/B testing or analytics tool that wrote a daily snapshot and never pruned the old ones.
• A bare _transient_timeout_* row that lost its sibling and is now immortal.

If you want the totals instead of the top rows:

SELECT
  autoload,
  COUNT(*) AS rows_,
  ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS mb
FROM wp_options
GROUP BY autoload;

A healthy table is usually under 1MB autoloaded across a few hundred rows. Anything over 3MB autoloaded deserves an afternoon.

Moving rows out without breaking the plugin

The instinct is to flip autoload to no on the offenders and move on. That works, but only if the plugin reads them through get_option(). Some plugins call wp_load_alloptions() directly and assume their value is in the cached blob. Those will hit the database on every read once you flip the row, which can be worse than the original problem.

The safer order:

1. Identify the plugin or theme that owns the row. The option_name prefix usually gives it away (woocommerce_, yoast_, wpseo_, ai1wm_).
2. Read the plugin source to see how it fetches that key. Grep the plugin folder for the literal option name.
3. If it uses get_option(), you can demote with confidence.
4. If it uses wp_load_alloptions(), file an issue with the plugin and demote only after they patch.

The demotion itself is one statement:

UPDATE wp_options
SET autoload = 'no'
WHERE option_name = 'the_offending_key';

Then flush the object cache so the stale blob is rebuilt on the next request:

wp cache flush

For transient junk you can delete outright, the WP-CLI transient docs cover the standard sweeps. The one to run first is wp transient delete --all, which respects external object caches and will not touch the rows you actually care about.

Keeping the table honest

Once you have shrunk the autoloaded set, the table itself is still 6.8GB on disk. MySQL does not return space to the filesystem when rows are deleted from an InnoDB table by default. You have two options.

The blunt one:

OPTIMIZE TABLE wp_options;

This rebuilds the table in place and rewrites the file. On a live site, it holds a metadata lock for the duration. On a 7GB table that can be several minutes during which any request hitting wp_options will wait. It is a window you have to choose deliberately.

The kinder approach is pt-online-schema-change from Percona Toolkit, which copies the table into a new one while writes are mirrored through a trigger:

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=wordpress,t=wp_options \
  --execute

It takes longer in wall-clock time but holds no long locks. The Percona docs cover the edge cases around foreign keys and triggers, neither of which applies to wp_options out of the box.

A weekly check that catches drift

The maintenance pattern is short. Once a week, on each legacy site you are responsible for, run the top-40 query and the totals query and write the numbers down. When the autoloaded MB number drifts past your baseline by more than thirty percent, look at what changed. New plugin, new logging level, a backup that started writing into options instead of a file. Most regressions are caused by something you installed on purpose, so the audit pays for itself the first time it catches one.

When we built Pier we ran into this exact problem on the second beta site, which is why the MySQL editor opens with a pinned "largest autoloaded options" query the first time you connect to a WordPress database. The version history on every UPDATE means demoting a row in production has a one-click undo if a plugin breaks on the next page load.

Open a SQL shell against a staging copy of your slowest WordPress site and run the top-40 query above. The bottom of the screen will tell you which plugin to read tomorrow.