MySQL InnoDB not releasing disk space after deleting data rows from table

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

User · Answer

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

If you can delete all data truncate command will delete data and reclaim diskspace for you.
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
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)

User · Answer

Year back i also faced same problem on mysql5 7 version and ibdata1 occupied 150 Gb   so i added undo tablespaces   Take Mysqldump backup Stop mysql service  Remove all data from data dir   Add below undo tablespace parameter in current my cnf     undo tablespace   innodb undo directory     var lib mysql    innodb rollback segments   128    innodb undo tablespaces   3   innodb undo logs   128     innodb max undo log size 1G   innodb undo log truncate   ON   Start mysql service   store mysqldump backup   Problem resolved

User · Answer

Ten years later and I had the same problem  I solved it in the following way    I optimized all the databases remained  I restarted my computer and MySQL on services  Windows r --  services msc    That is all

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL InnoDB not releasing disk space after deleting data rows from table

Examples related to mysql

Examples related to innodb

Examples related to delete-row