A large MySQL association table with 500M rows recently ballooned into 1B rows due to a bug (now fixed). Disk usage went from ~130GB to 240GB.
Unfortunately there is not room on this server for any of the standard remedies (OPTIMIZE
or ALTER TABLE table ENGINE=InnoDB
, dump & rebuild, build into temporary table & rename & drop old table, etc.). I only have 50GB remaining.
Here's the question:
While I've successfully removed over 100M rows so far (1B down to 850M), disk usage has actually grown by 10GB (to 250GB). Yes, new records are also being added every second, but I'm deleting far faster than they are being added.
- Will InnoDB ever release this space?
- Why does the table disk usage grow, while I'm shrinking the row count?
Any other advice to reclaim unused disk space when the regular solutions don't fit?