MySQL Table Bloat: Reclaiming Disk Space After Big Deletes

The DELETE that never hit the disk

Last month I finally got around to cleaning up an events table on a client's reporting box. Eighty million rows, half of them older than the retention policy says they should be. The DELETE ran in batches over about three hours and finished clean. SELECT COUNT(*) confirmed the table now held half the rows it used to.

Then I ran df -h. The disk hadn't moved. Not one byte.

If you've deleted a big chunk of an InnoDB table and watched the file size sit there, stubbornly unchanged, nothing is broken. That's InnoDB working exactly as designed. Whether you want the space back is a different question, and the answer involves more tradeoffs than most tutorials admit.

Why InnoDB holds onto your space

A DELETE doesn't remove rows. It marks them deleted. The row versions sit in their pages until the purge thread gets around to cleaning them up, and even after purge finishes, the freed space isn't handed back to the operating system. It goes onto free space lists inside the tablespace, where future inserts and updates can reuse it. The .ibd file only ever grows on its own.

This isn't stupidity on InnoDB's part. Growing a file is cheap, shrinking one means rewriting it. InnoDB also fills pages only about 93% full on purpose, leaving headroom so updates don't force page splits right away. Some slack is normal and expected.

One config detail matters a lot here: innodb_file_per_table. It's been the default since 5.6, and it gives each table its own .ibd file, which is the only situation where a rebuild can actually shrink something on disk. If your table still lives in the old shared system tablespace, no amount of optimizing will ever return space to the OS, because everything is packed into one giant ibdata file that reuses its own holes.

Measuring how bloated a table actually is

Before touching anything, look at the numbers:

SELECT table_name,
  ROUND(data_length / 1024 / 1024) AS data_mb,
  ROUND(data_free   / 1024 / 1024) AS free_mb,
  ROUND(data_free / (data_length + index_length + data_free) * 100) AS free_pct
FROM information_schema.tables
WHERE table_schema = 'app' AND table_name = 'events';

DATA_FREE is the number people fixate on, and it's directionally useful, but treat it as an estimate. It counts space marked free inside the tablespace, including rows that are deleted-but-not-yet-purged. If purge has a backlog, your real bloat is understated. I check the purge backlog too:

SHOW ENGINE INNODB STATUS\G
-- look for "History list length" in the TRANSACTIONS section

A history list length stuck in the millions means old row versions are piling up faster than purge can clear them. Common cause: a long-running transaction holding an old read view, which blocks purge entirely. Fix that first, or your measurements lie to you.

As a rough personal rule, if DATA_FREE is under about 20% of the table size, I leave it alone. The free pages will get reused by normal writes anyway.

Running OPTIMIZE TABLE, and that weird note it prints

When the bloat is real, the fix looks innocent enough:

OPTIMIZE TABLE app.events;

Run it on an InnoDB table and MySQL prints something alarming:

Table does not support optimize, doing recreate + analyze instead
status: OK

That note is normal, not an error. InnoDB has no native "optimize" the way MyISAM did, so MySQL maps the statement to ALTER TABLE ... FORCE: it rebuilds the table into a fresh, compact tablespace, indexes included, and refreshes the statistics while it's at it. When it finishes, the free space is gone and the file is as tight as it will ever get.

Three things to know before you run it on anything big. First, the rebuild runs as online DDL, so reads and writes keep flowing, but it takes a brief exclusive lock at the very start and the very end, and on a busy table that final lock can queue behind long-running queries. Second, the rebuild needs free disk space roughly equal to the table size, because old and new copies coexist until the swap. This is the trap that bites people: the moment you most need to reclaim disk is exactly the moment you can least afford the rebuild. Third, OPTIMIZE is written to the binlog by default, so your replicas will run it too, usually right behind the primary. If you want to control that per node, add the LOCAL (or NO_WRITE_TO_BINLOG) keyword.

For a whole database, mysqlcheck --optimize wraps the same logic in a loop. I'd still not point it at everything blindly, which brings me to:

When I skip it

Append-only tables never need it. If rows only go in and never come out, there are no holes to reclaim, and the 7% page headroom is by design. Weekly cron jobs that optimize every table in every database are pure overhead for the same reason.

The habits that prevent bloat in the first place are cheaper than any rebuild. Delete in batches of a few thousand rows instead of one giant DELETE, so purge keeps up and ordinary inserts reuse the freed pages as you go. For time-series data, partition by date and drop old partitions instead of deleting rows. DROP PARTITION just deletes a file. The space is back immediately, no rebuild involved, and nothing to lock beyond the partition itself.

And if you're wondering whether Postgres has the same disease: it does, worse, with dead tuples and autovacuum instead of a purge thread. I wrote up that side of the story in the Postgres bloat post last week. Same lesson at the end of both: measure before you rebuild, and design your deletes so you rarely have to.

The monitoring query above lives in my Snippet Ark now, filed under "run before panicking about disk usage". Honestly that folder has paid for itself a few times over.