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.
This question is related to
mysql
innodb
delete-row
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 !!
Ten years later and I had the same problem. I solved it in the following way:
That is all :)
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
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.
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.
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.
Source: Stackoverflow.com