25

InnoDB stores all tables in one big file ibdata1.

After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and re-import the whole database (which has several Hundred GB in total)?

I think the reason is because you are still able to rollback the dropping. In my case I don't need to.

Improve this question

2 Answers 2

Reset to default
30

This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange.

To put it mildly, there is simply no other way to shrink ibdata1. With innodb_file_per_table disabled, every time you run OPTIMIZE TABLE on an InnoDB table, ibdata1 grows rapidly. Data that are dropped using DROP TABLE and DROP DATABASE cannot be rolled back because they are DDL, not DML. I believe Oracle and MSSQL can rollback DDL. MySQL cannot do that.

There are several classes of information that reside in ibdata1

  • Table Data
  • Table Indexes
  • Table MetaData
  • MVCC Control Data
  • Double Write Buffer (Background write to prevent reliance on OS caching)
  • Insert Buffer (Managing changes to non-unique secondary indexes)

Using innodb_file_per_table=1 will allow you to create new tables with table data and table indexes being created outside ibdata1. You could extract any tables still inside ibdata1 using ALTER TABLE ... ENGINE=InnoDB; or OPTIMIZE TABLE but that will leave that big gaping unused space in ibdata1.

Notwithstanding, you must clean up the InnoDB infrastructure. I already wrote StackExchange posts on how and why to do this:

Good News

You only have to dump the data, reload one more time and never revisit this issue again. Running OPTIMIZE TABLE afterwards will indeed shrink the .ibd tablespace file for any InnoDB table.

Improve this answer
3
  • 1
    Minor correction: MSSQL and PostgreSQL can roll back DDL. Oracle cannot. In fact Oracle issues an implicit commit when it sees DDL!
    – Chris Travers
    Commented Oct 1, 2012 at 8:56
  • 1
    @RolandoMySQLDBA since what version of MySQL this "good news" of automatic shrinking works?
    – Gavriel
    Commented Sep 23, 2013 at 20:09
  • @Gavriel -- I think you misread. You still have to dump; remove ibdata1; restart; and reload. No-autoshrink of ibdata1. (Changing iblog* is now simpler.)
    – Rick James
    Commented Mar 18, 2015 at 0:31
2

InnoDB only stores all of your InnoDB tables in ibdata1 if you don't use the following setting in your my.cnf defaults file:

innodb_file_per_table = 1

DROP TABLE (and DROP DATABASE) cannot be rolled back.

That is not the reason you can't shrink ibdata1.

This is an abbreviated explaination, but ibdata1 contains InnoDB internals in addition to your table data. From my understanding, to shrink it would require defragmenting it, which isn't a supported operation.

Improve this answer

Your Answer

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

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