40GB wp_options autoload: the night we hit 800ms TTFB

23:41 on a Tuesday

The Loom link came in at 23:41. A lead at an agency we work with in Utrecht had been on a shared screen since just after dinner with their B2B client's IT manager. The client's WooCommerce shop, PHP 8.1, MariaDB 10.6, around 14,000 SKUs, had spent the last fortnight getting steadily slower. That evening the line broke. Homepage TTFB crossed eight seconds. The sales team was forwarding screenshots of the spinning favicon, asking when this would stop. By midnight the slow query log kept pointing at one table: wp_options.

The site itself was familiar work. Six years old, two agency owners deep, the kind of plugin list that grows in archaeological layers. New Relic was pointing at MySQL. The agency had already tried a CDN, a fresh object cache that would not warm, and an emergency PHP-FPM tuning pass. None of it had moved the dial.

The first thing we asked them to run was the table size query.

SELECT
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS size_gb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 10;

wp_options came back at 40.2 GB. The next largest table, wp_postmeta, was 1.1 GB.

What was actually in there

A table that size is not the sort of thing that happens slowly. It is the result of one specific decision somewhere in the codebase that nobody has questioned for a long time. The way to find it is to sort by row size.

SELECT
  option_name,
  LENGTH(option_value) AS bytes,
  autoload
FROM wp_options
ORDER BY LENGTH(option_value) DESC
LIMIT 30;

The top result was a single row, autoload = yes, weighing 4.1 GB. Its name carried a custom integration prefix the previous in-house dev had used three years earlier. It turned out to be a queue table that had been built inside wp_options instead of as its own table. Every failed call to the client's ERP, every payload, every retry, had been appended to a serialized PHP array stored under one option name. The cron job that was supposed to drain the queue had been silently failing since a PHP 8 upgrade.

The other 36 GB was easier to explain. WordPress writes a lot of small things to wp_options without ever cleaning them up. The default transient cache lives there. Expired transients are not removed automatically. A handful of well-known plugins write their session, log, and rate-limit data there too. We have seen this on Yoast indexable rebuilds, on Wordfence attack logs, on WP Rocket cache headers, and on at least two membership plugins that store every login attempt as its own option row.

The damage compounds because of autoload. On every WordPress request, before anything else happens, the function wp_load_alloptions() runs a single query that pulls every row where autoload = 'yes' into PHP memory. On a healthy site that result set is one or two megabytes. On this site it was 6.4 GB. The query ran on every page load, every admin-ajax call, every WP-CLI command. MySQL was serving it from disk, decoding it, and PHP was unserialising it into process memory before it could even decide what to render.

Triage at one in the morning

The plan we wrote on the shared doc had three steps and a rollback for each. Nothing was going to be done without a full mysqldump and a binlog position recorded.

Step one: snapshot

We took a logical backup of wp_options on its own, not the whole database. A full dump of a 40 GB table over the public network was going to take longer than we had patience for, so we used the host's snapshot tooling at the volume level first, then ran a targeted mysqldump of just the table to a second disk.

mysqldump \
  --single-transaction \
  --quick \
  --skip-lock-tables \
  --default-character-set=utf8mb4 \
  --databases shop_prod --tables wp_options \
  | gzip > /backups/wp_options-pre-cleanup.sql.gz

The dump came in at 6.3 GB compressed. That gave us a known-good restore point that did not depend on the hosting provider's snapshot policy.

Step two: stop the bleeding

We did not delete the 4.1 GB queue row first. That was tempting, because it would have produced the largest visible win, but the integration code might have rebuilt the row on the next cron tick. The first move was to disable autoload on it, which removes it from the hot path of every request without touching the data.

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

That single statement, run at 01:14, cut TTFB on the homepage from 8.1 seconds to 1.9 seconds. We watched it on the agency's monitoring dashboard for ten minutes before doing anything else.

The same principle applies to the long tail. A row in this table is only expensive if it autoloads. Plugins that flagged their options for autoload by mistake can be corrected in bulk, with care.

SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload = 'yes'
  AND LENGTH(option_value) > 1048576
ORDER BY bytes DESC;

Anything over a megabyte that is autoloaded is almost certainly a mistake. We reviewed each name with the agency lead. Three more rows came off autoload that night: a Yoast indexable cache, a Wordfence scan log, and an internal "settings backup" array a previous developer had built and then forgotten.

Step three: the transient sweep

WordPress's transient API uses wp_options as its default storage backend when no persistent object cache is configured. Every transient gets two rows, one for the value and one for the timeout. When transients expire, neither row is deleted; the value is just ignored. On a site that uses transients heavily and has no object cache, this accumulates linearly forever.

The safe cleanup is to delete only expired transients, and to do it in batches so replication keeps up.

DELETE a, b
FROM wp_options a
INNER JOIN wp_options b
  ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12))
WHERE a.option_name LIKE '\_transient\_%'
  AND a.option_name NOT LIKE '\_transient\_timeout\_%'
  AND b.option_value < UNIX_TIMESTAMP()
LIMIT 50000;

We ran it in a loop with a small sleep between batches. The transient sweep alone removed 18.7 million rows.

Reclaiming the disk

By 03:00 the autoload set was down to 11 MB. TTFB had settled at 740 ms across ten cold homepage requests. The site was, by any reasonable measure, fixed. But the wp_options.ibd file was still 40 GB on disk, full of holes from all the deleted rows. MySQL would happily reuse that space for new inserts, but it would not give it back to the filesystem on its own.

We ran OPTIMIZE TABLE during the next maintenance window three days later, with the site in read-only mode for the twelve minutes it took.

OPTIMIZE TABLE wp_options;

The final file came in at 92 MB. The MariaDB documentation on OPTIMIZE TABLE is worth reading in full before you run it on anything large in production; it explains exactly which locks it takes and which storage engines support online operation.

What the codebase was doing wrong

The fix above is the kind of work that buys an evening. The harder question is why a database ended up in that state in the first place, and what to change so it does not happen again.

The queue-in-options pattern is the worst offender. WordPress makes it trivially easy to call update_option() with anything, including a megabyte of JSON, and by default the option will autoload. A junior dev under deadline pressure will reach for update_option() rather than write a migration for a new table, every time. The fix is institutional: code review for any new option name, and a hard rule that any data that grows over time gets its own table.

The transient problem is structural. Until you put a persistent object cache in front of WordPress (Redis is the common answer; the Redis Object Cache plugin does the wiring), every transient your themes and plugins set will live in the options table forever. Even with a cache in place, a scheduled job that runs the expired-transient delete weekly is cheap insurance.

The plugin offenders are harder to police because they ship with autoload on by default and most site owners never look. The audit is the same query we started with, sorted by autoloaded bytes, run once a quarter and pasted into the project's monthly health log.

The note we keep finding ourselves writing

This is the third 30 GB-plus options table we have seen since January. The pattern is consistent enough that when we built Pier for editing this kind of legacy site, the MySQL editor ships with a saved query for "autoloaded options over 1 MB" and a one-click toggle on the autoload column. Every option row you change goes through the same version history as a file edit, so when a plugin update writes a giant array back into a row you flipped, you see it happen and revert without restoring the whole table.

The smallest thing to do today

Open the database for the busiest WordPress site you maintain and run the autoloaded-rows-over-1MB query from this post. If anything comes back, you already have an evening's work waiting; if nothing does, add the query to your monthly checklist and move on.