MySQL InnoDB not releasing disk space after deleti

Question

I have one MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I deleted data rows from the table, it did not release allocated disk space. Nor did the size of the ibdata1 file reduce after running the optimize table command.

Is there any way to reclaim disk space from MySQL?

I am in a bad situation; this application is running in about 50 different locations and now problem of low disk space is appearing at almost all of them.

Answer 1

Just had the same problem myself.

What happens is, that even if you drop the database, innodb will still not release disk space. I had to export, stop mysql, remove the files manually, start mysql, create database and users, and then import. Thank god I only had 200MB worth of rows, but it spared 250GB of innodb file.

Fail by design.

Answer 2

Other way to solve the problem of space reclaiming is, Create multiple partitions within table - Range based, Value based partitions and just drop/truncate the partition to reclaim the space, which will release the space used by whole data stored in the particular partition.

There will be some changes needed in table schema when you introduce the partitioning for your table like - Unique Keys, Indexes to include partition column etc.

Answer 3

There are several ways to reclaim diskspace after deleting data from table for MySQL Inodb engine

If you don't use innodb_file_per_table from the beginning, dumping all data, delete all file, recreate database and import data again is only way ( check answers of FlipMcF above )

If you are using innodb_file_per_table, you may try

1. If you can delete all data truncate command will delete data and reclaim diskspace for you.
2. Alter table command will drop and recreate table so it can reclaim diskspace. Therefore after delete data, run alter table that change nothing to release hardisk ( ie: table TBL_A has charset uf8, after delete data run ALTER TABLE TBL_A charset utf8 -> this command change nothing from your table but It makes mysql recreate your table and regain diskspace
3. Create TBL_B like TBL_A . Insert select data you want to keep from TBL_A into TBL_B. Drop TBL_A, and rename TBL_B to TBL_A. This way is very effective if TBL_A and data that needed to delete is big (delete command in MySQL innodb is very bad performance)

Answer 4

If you don't use innodb_file_per_table, reclaiming disk space is possible, but quite tedious, and requires a significant amount of downtime.

The How To is pretty in-depth - but I pasted the relevant part below.

Be sure to also retain a copy of your schema in your dump.

Currently, you cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:

Use mysqldump to dump all your InnoDB tables.

Stop the server.

Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.

Remove any .frm files for InnoDB tables.

Configure a new tablespace.

Restart the server.

Import the dump files.

Answer 5

MySQL doesn't reduce the size of ibdata1. Ever. Even if you use optimize table to free the space used from deleted records, it will reuse it later.

An alternative is to configure the server to use innodb_file_per_table, but this will require a backup, drop database and restore. The positive side is that the .ibd file for the table is reduced after an optimize table.