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

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

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

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

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

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

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

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

[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