The 29 GB WordPress database that was mostly nothing

The brief was one line: “the admin is unusably slow, the host says it’s the database, the host can’t say why”. The site was a WooCommerce store, a few thousand products, a healthy order history, nothing exotic. The database was 29 gigabytes. For scale, a store that size should sit somewhere under a gigabyte, and that’s being generous with order history.

Nobody had done anything wrong, exactly. That’s the interesting part. The database had simply been allowed to remember everything, forever, and WordPress is very good at remembering.

Finding out what’s in there

The first query is always the same: which tables are big.

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

The answer came back in a shape I’ve now seen enough times to predict. The top four tables, holding most of the 29 GB, were not posts, not orders, not customers:

• wp_actionscheduler_logs and wp_actionscheduler_actions: the queue WooCommerce and several plugins use for background jobs. Every completed job leaves a log row. Nobody had ever pruned them. Years of “action complete” lines, tens of millions of them.
• wp_options: a table that should hold settings. A plugin had been storing per-visitor session data in it, as transients, and its own cleanup had been broken since an update long ago. The expired rows stayed. Worse, a share of them were marked autoload = yes, which meant a chunk of that table was loaded into memory on every single page view, front end and admin alike.
• wp_postmeta: legitimate, mostly, but carrying orphaned rows for products deleted years earlier, and a per-product “views” counter that a plugin updated on every visit, bloating the table’s index with churn.

The content itself, posts, products, orders, customers, and their metadata, was a small fraction of the total. Everything else was exhaust.

Plain English The site wasn’t big. Its diary was big. WordPress and its plugins had been writing “I did a thing” in the database several times a second for years, and nothing was assigned to throw the diary away.

Why it made the dashboard slow

Three reasons, and they compound. First, the autoloaded options: every request began by loading megabytes of stale session data into memory before doing any work. Second, the logs tables had grown past the point where MySQL could keep their indexes in memory, so every query touching them hit disk. Third, WooCommerce’s own admin screens run status queries against Action Scheduler on load, so the slowest table in the database was consulted on every admin page.

The front end survived because it was page-cached. Visitors got saved HTML. Admins got the real thing.

The fix, in order

I did it in the order that was safe to undo, with a backup at each step.

1. Prune Action Scheduler. WooCommerce keeps completed actions for 30 days by default, but that retention only runs if the cleanup job itself can complete, and it couldn’t on a table that size. Deleted completed and cancelled actions older than 30 days in batches, then the orphaned logs. Two tables went from most of the database to a few hundred megabytes.
2. Fix the transients. Deleted expired transients, then found the plugin responsible and replaced the setting that made it use wp_options for sessions. Set autoload = no on what remained of the big rows. The autoloaded total fell from tens of megabytes to well under one.
3. Clean postmeta. Removed rows whose post_id no longer existed, and moved the view counter out of postmeta into its own small table (a ten-line change in the plugin’s settings, as it turned out, which already offered that).
4. Optimise the tables so MySQL reclaimed the space on disk, and add a real server cron so the retention jobs run every day instead of never.

Final size: a small fraction of the original, in line with what a store that size should be. Dashboard: from eleven seconds to under one. Nothing was lost; every order and customer was exactly where it had been.

What I took from it

The database is the one part of a WordPress site nobody looks at, because it works right up until it doesn’t, and the failure looks like “the host is slow”. Three habits prevent this whole story: a monthly check of the biggest tables (the query above), Action Scheduler retention actually running on a server cron, and never letting a plugin use wp_options as a scratch pad.

The store owner’s question afterwards was the right one: “why didn’t anyone tell us?” Nobody was watching. That’s most of what a care plan is, and it’s why the monthly health report I send has a line for database size. It’s the number that would have caught this two years earlier.