MySQL DELETE removed millions of rows, but the .ibd file didn't shrink

We cleaned millions of old rows from a large MySQL table. The data was gone, but the disk space wasn't. OPTIMIZE TABLE returned about 200 GB to the filesystem.

We had a simple problem on one of our MySQL servers: the disk was running out of space.

One large table contained a lot of old data we no longer needed, so we cleaned it up and deleted millions of rows.

DELETE FROM large_table
WHERE ...;

The DELETE completed successfully.

Then we checked the disk.

Almost nothing changed.

Why?

The table was using InnoDB with its own .ibd file.

Deleting rows made the space reusable inside the InnoDB tablespace, but the .ibd file itself did not become significantly smaller on the filesystem.

So MySQL had free space internally while the server was still running low on actual disk space.

What we did

After the cleanup we rebuilt the table:

OPTIMIZE TABLE large_table;

When the operation finished, the unused space was returned to the filesystem.

Result

About 200 GB of disk space was recovered.

Problem solved.

One important warning

Don’t blindly run OPTIMIZE TABLE on a large production table. A rebuild can generate significant I/O, take time, require additional free space and affect the production workload.