CODEWITHSUNDEEP
mysqlinnodbdiskspace
Omidreza Bagheri
Omidreza Bagheri

Reputation: 841

How to reclaim disk space in InnoDB after dropping table

I have a big InnoDB table in mysql. The problem is my disk space is almost full. I want to reduce my disk space. If I'll delete some rows from the table and use this command:

optimize TABLE_NAME

then my disk space will be reduced. But I want to drop the table. If I'll drop the table, there is not any table to optimize it! What is the appropriate command to reduce disk space after dropping the InnoDB table?

Upvotes: 4

Views: 11180

Answers (4)

Rick James
Rick James

Reputation: 142298

OPTIMIZE TABLE makes a copy of the table. More specifically it does

  1. Create a new table schema like the existing one.
  2. Copy the rows into the new table.
  3. Rename to swap tables.
  4. Drop the old table.

Note especially that for a brief time you will have two copies of the table occupying space on disk.

  • If you are too tight on disk space, this will fail.
  • If you have innodb_file_per_table = OFF, the ibdata file may expand, but not shrink. Again, this may lead to a failure.

Beginning with 5.6.17, OPTIMIZE TABLE will perform the work in place. However, you must drop and re-add any FULLTEXT indexes.

DROP TABLE works in one of these ways:

  • innodb_file_per_table = OFF: free up space in ibdata. But this space is not released to the OS. Instead, it will be reused for inserts, etc, to any table.
  • innodb_file_per_table = ON: The file taken by the table is promptly released to the OS.

But... When I refer to the setting of innodb_file_per_table, I am referring to the setting when the table was CREATEd or last ALTERed, not the current setting.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You can try to configure your server to use innodb_file_per_table, but do make it sure that you have your backup and then drop and restore it. You can run the optimize table.

You can run to check if the innodb_file_per_table is ON

mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

Upvotes: 5

Zafar Malik
Zafar Malik

Reputation: 6844

drop command will free disk space automatically.

Note: Assuming you are using innodb_file_per_table as you are able to free space by optimize table syntax.

Upvotes: 7

Musa Haidari
Musa Haidari

Reputation: 2267

Actually to reclaim space after dropping tables in MySQL you should enable Per-Table File in the MySQL configuration. Read more here:

https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-tablespaces.html

Upvotes: 1

Related Questions