3

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.

  1. Will InnoDB ever release this space?
  2. 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?

Improve this question

1 Answer 1

Reset to default
3

InnoDB tablespace size for individual tables (.ibd) can be reduced with OPTIMIZE and ALTER TABLE ... ENGINE=InnoDB;, but the system tablespace file ibdata1 can be never be shrunk.

The source of ibdata1's growth in in the undo logs.

I have written posts about this since July 2013. I first learned about this phenomenon from Percona's post Reasons for run-away main Innodb Tablespace.

Suggestion #1

If you mysqldump the data into another server running mysql in a newly installed instance, you could setup replication to it, then failover and trash the old master.

Suggestion #2

If you do not have another server, you'll have stop the application, mysqldump the data, drop all DBs, shutdown mysql, delete ibdata1, start mysql (which recreates ibdata1, reload the mysqldump). See my very old post Howto: Clean a mysql InnoDB storage engine?

Improve this answer
4
  • Thanks @RolandoMySQLDBA. Can I get a bit more clarification around the first sentence? I understand that .ibd's can be reduced with OPTIMIZE AND ALTER... But will MySQL ever recover some of the unallocated space within .ibd files? In the question above, the .ibd file grew from ~130GB to ~250GB. And even after removing the majority of excess rows, the .ibd size continues to grow. I'm curious why this is, and if MySQL will ever bring it back in line (without manually running an OPTIMIZE or ALTER).
    – Ryan
    Aug 28, 2017 at 17:28
  • 1
    InnoDB does not reclaim space for tablespace files (.ibd as well as ibdata1) When inserting new data, InnoDB will try to reuse fragmented space for new row data. Any space involved in your deletes is used for rollback purposes of all transaction include the rows being deleted. This is why OPTIMIZE and ALTER TABLE ... ENGINE=InnoDB; are often suggested.
    – RolandoMySQLDBA
    Aug 28, 2017 at 18:28
  • 1
    OPTIMIZE and some variants of ALTER rebuild the .ibd file. That is how the recover the space. However, they need extra disk space during the process.
    – Rick James
    Aug 28, 2017 at 19:04
  • To add to @RickJames comment, for example: if you are rebuilding a 100GB table, there needs to be at least 100GB in disk space free.
    – RolandoMySQLDBA
    Aug 28, 2017 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.