MySQL InnoDB does not free disk space after deleting data rows from table

Question

I have a MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I delete data rows from the table, it does not free the allocated disk space. After running the optimize table command, the size of the ibdata1 file has not been reduced.

Is there any way to reclaim disk space from MySQL?

I'm in a bad situation; the application is running in about 50 different locations and now almost all of them are running out of disk space.

Answer 1

I encountered the same problem myself.

What happens is that even if you delete the database, innodb still does not free up the disk space. I have to export, stop mysql, delete the files manually, start mysql, create database and user, then import. Thank god I only have 200MB of rows but it saved 250GB of innodb file.

Design failed.

Answer 2

MySQL does not reduce the size of ibdata1. once. Even if you use Optimize Table to free up the space used by deleted records, it will reuse it later.

An alternative is to configure the server to use innodb_file_per_table, but this requires backup, deletion of the database, and recovery. On the positive side, the optimized tables.