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.

Crimple

possible duplicate of https://mcmap.net/q/144072/-with-innodb-how-can-i-claim-back-index-space/82114 (but this one was here first) – Psyche

"Nor did the size of the ibdata1 file reduce after running the optimize table command" that is because your innodb_file_per_table is turned off. The good news is that this option is on by default in recent versions of MySQL. – Blate

I run "optimize table xxxx" and got the message "Table does not support optimize, doing recreate + analyze instead." Afterward running "du -h /var/lib/mysql" on a shell, I could see the database had shrunk. – Stampede

Answer 1

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.

Collate

MySQL 5.5 docs about the InnoDB file-per-table mode state "To take advantage of [InnoDB file-per-table] features for an existing table, you can turn on the file-per-table setting and run ALTER TABLE t ENGINE=INNODB on the existing table." This implies that you could turn this feature on, "convert" the existing tables to use a separate InnoDB file with the ALTER TABLE command, then OPTIMIZE the table to shrink it's size. However, once you're done you'd have to figure out how to delete the (huge) source InnoDB file... – Lacedaemonian

I guess this technically answers the question, but I would expect the majority of people searching this topic are looking for the actual process to shrink/reclaim the space, which this answer does not provide. – Levulose

@Levulose The process is "configure the server to use innodb_file_per_table", "backup" the server, "drop the database(s)", stop the mysql, delete .ibd, start the server and restore the database(s). With MySQL 5.5+ you can use what Josh said, and after changing all tables, stop the server, delete the huge .ibd and start it again. – Collate

downvoting this as the scenario has changed with mysql 8.0, optimize tables and restart mysql to free disk space – Lynea

@PradyutBhattacharya, well, then this problem will not happen. So no need to downvote because this answer is the solution for the problem asked. – Collate

@PradyutBhattacharya I deleted 14m records from a table, and 600k from another. Restarting didn't bring the storage size down. Perhaps an issue with my tables not being able to be optimized, not sure. – Selfhelp

@JoshM. Had a similar situation. After removing the records ran optimize and then restarted, cleared 20GB out. In newer version of mysql and mariadb this works a bit better, but also depends on table specifics how much can be freed up. – Huberty

Answer 2

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.

Inartificial

yeah, that's definitely a fail. – Basham

MySql 5.5 has same issue: I ran "optimize table" to reduce disk usage of a 28GB table. The operation probably tried to make an optimized clone of the original one, and doing so used up all the space on the partition. Now "optimize table" failed and I'm left with no space on the partition even after I dropped the entire db... very disappointing. – Cordiality

And 4+ years later I ran into the same problem with MySQL. MS SQL is similar: dba.stackexchange.com/questions/47310/… – Statolith

Answer 3

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.

Psyche

Thanks for including the steps - afaict the 'how to' link doesn't contain this information anymore – Cavanagh

Answer 4

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 :)

Subcontinent

Yes, just "OPTIMIZE TABLE tbl_name;" and mysql restart – Kampmann

I don't know why this is not voted enough. Clearly this should be the top answer. – Vicennial

Unfortunately, this does not work - tried it after seeing this answer, and it didn't release any of the file size. – Bucher

Answer 5

The shortest way I found was:

ALTER TABLE YOURTABLE ENGINE=InnoDB

After truncating or deleting the unvanted records I ran this and the tablespace shrinked.

Here is a nice article by Shlomi Noach, where I found (this and more information about) this.

Credits to Shlomi Noach. Hope it helps. Just in case I'll paste it here as well:

When working with InnoDB, you have two ways for managing the tablespace storage:

Throw everything in one big file (optionally split). Have one file per table. I will discuss the advantages and disadvantages of the two options, and will strive to convince that innodb_file_per_table is preferable.

A single tablespace

Having everything in one big file means all tables and indexes, from all schemes, are ‘mixed’ together in that file.

This allows for the following nice property: free space can be shared between different tables and different schemes. Thus, if I purge many rows from my log table, the now unused space can be occupied by new rows of any other table.

This same nice property also translates to a not so nice one: data can be greatly fragmented across the tablespace.

An annoying property of InnoDB’s tablespaces is that they never shrink. So after purging those rows from the log table, the tablespace file (usually ibdata1) still keeps the same storage. It does not release storage to the file system.

I’ve seen more than once how certain tables are left unwatched, growing until disk space reaches 90% and SMS notifications start beeping all around.

There’s little to do in this case. Well, one can always purge the rows. Sure, the space would be reused by InnoDB. But having a file which consumes some 80-90% of disk space is a performance catastrophe. It means the disk needle needs to move large distances. Overall disk performance runs very low.

The best way to solve this is to setup a new slave (after purging of the rows), and dump the data into that slave.

InnoDB Hot Backup

The funny thing is, the ibbackup utility will copy the tablespace file as it is. If it was 120GB, of which only 30GB are used, you still get a 120GB backed up and restored.

mysqldump, mk-parallel-dump

mysqldump would be your best choice if you only had the original machine to work with. Assuming you’re only using InnoDB, a dump with –single-transaction will do the job. Or you can utilize mk-parallel-dump to speed things up (depending on your dump method and accessibility needs, mind the locking).

innodb_file_per_table

With this parameter set, a .ibd file is created per table. What we get is this:

Tablespace is not shared among different tables, and certainly not among different schemes. Each file is considered a tablespace of its own. Again, tablespace never reduces in size. It is possible to regain space per tablespace. Wait. The last two seem conflicting, don’t they? Let’s explain.

In our log table example, we purge many rows (up to 90GB of data is removed). The .ibd file does not shrink. But we can do:

ALTER TABLE log ENGINE=InnoDB

What will happen is that a new, temporary file is created, into which the table is rebuilt. Only existing data is added to the new table. Once comlete, the original table is removed, and the new table renamed as the original table.

Sure, this takes a long time, during which the table is completely locked: no writes and no reads allowed. But still – it allows us to regain disk space.

With the new InnoDB plugin, disk space is also regained when execuing a TRUNCATE TABLE log statement.

Fragmentation is not as bad as in a single tablespace: the data is limited within the boundaries of a smaller file.

Monitoring

One other nice thing about innodb_file_per_table is that it is possible to monitor table size on the file system level. You don’t need access to MySQL, to use SHOW TABLE STATUS or to query the INFORMATION_SCHEMA. You can just look up the top 10 largest files under your MySQL data directory (and subdirectories), and monitor their size. You can see which table grows fastest.

Backup

Last, it is not yet possible to backup single InnoDB tables by copying the .ibd files. But hopefully work will be done in this direction.

Behnken

ALTER TABLE YOURTABLE ENGINE=InnoDB this does not seem to affect the table disk space even after deleting rows, running this statement, and rebooting MySQL (8). – Selfhelp

Answer 6

Ran into this problem today (11 years after the question was originally asked) and was able to fix it by dropping the table and creating it again. I did not have to reinstall the DB or dump & restore, modify storage, change tablesapce, etc. - none of that.

I'm using InnoDB but not innodb_file_per_table so even after I deleted 900K rows from the table the DB size did not budge. So I dropped the table and created it again.

In my case, my table was cleaned out to zero rows, so it was easy for me to drop the table, but to retain the structure I ran

create table mynewtable as select * from myoldtable where 1=2;

Followed by

drop table myoldtable;

This dropped my DB size down from 5G to 400MB

Margarite

Welcome to Stack Overflow. The nature of databases is that data is not released back to the system due to deleted records. From my research, INNODB doesn't have a reclaim resources command or similar, like some databases. In your case, though, I'd have been tempted to instead try TRUNCATE TABLE myoldtable; ... I expect that would have been a simpler solution. – Against

Thanks - had forgotten about TRUNCATE. Have used it extensively in Oracle but didn't think of trying it here. Will file that for future reference :) – Margarite

TRUNCATE TABLE does not release the disk space either in my case. – Hairpin

I tried this, and it still didn't release my data :( – Bucher

The SQL create table mynewtable as select * from myoldtable where 1=2; does not create the exact same table, primary keys etc. go lost. Better do a show create table myoldtable; then create a new table based on what is shown. – Isoprene

Answer 7

For InnoDB. You can try this approach.

1. According to this article.

   ALTER TABLE tbl_name ENGINE=INNODB;

2. Then refresh information about table size and indexes in information_schema.

   ANALYZE TABLE tbl_name;

Hornblende

On my MySQL 8.0.33 it just happend that only ANALYZE TABLE tbl_name; did the job well, without the ALTER TABLE... . – Kettledrummer

Answer 8

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.

Blindstory

Answer 9

If the OPTIMIZE doesn't solve your problem, try:

ALTER TABLE tbl_name ENGINE=INNODB, ALGORITHM=INPLACE, LOCK=NONE;

or

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

Manana

Answer 10

I have same issue. I deleted all the data from logging_event table. But, the logging_event.ipd still consumes more than 15GB of data.
So, I have solved it in the following steps.

1. Create new table from existing table.
   CREATE TABLE logging_event_new like logging_event;

2. Drop the actual table.
   DROP TABLE logging_event;

3. Rename New table to old
   RENAME TABLE logging_event_new to logging_event;

And, it worked for me.
Now, I have more than 15GB space available.

Spode

Answer 11

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 !!

Rainfall

Answer 12

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)

Emeric

Please note, option 3 will drop views/indexes built over TBL_A, harming perforance and potentially breaking applications. Also, if TBL_A is a foreign key source, you won’t be able to drop it. Option 2, likewise, should be exercised with caution, as under the covers. it does the same as option 3. Not sure whether the indexes/views will be rebuit after ALTER (doubt it). In general, aside from TRUNCATE, the above are not business-quality practices, and at a minimum they should be performed only during prescribed maintenance times, and with solid attention to (super- and sub-) dependencies. – Against

Hi @Against option 3 is what I learnt from Percona, in this answer I only talk about idea to do it and in practice need to handle all constraint and business by code logic so It cannot fit all situations. One more thing, this question is about how to delete data and release disk. I think all of my solutions can do this but in each case study we need to choose what is suitable. Sometimes a truncate unused big log table is all you want – Occasion