Contents
Why does InnoDB not release disk space after deleting data?
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.
How to reduce table size in MySQL InnoDB?
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.
Why does MySQL not release disk space after deleting data?
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.
How to release disk space after deleting data?
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.
How to get rid of InnoDB in MySQL?
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.
How to reclaim disk space after deleting data from table?
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 )
How does the OPTIMIZE TABLE work in InnoDB?
You can use OPTIMIZE TABLE as you mentionned it but you should care about the innodb_file_per_table variable : The OPTIMIZE TABLE with InnoDB tables, locks the table, copies the data in a new clean table (that’s why the result is smaller), drops the original table and renames the new table with the original name.