I have one mysql datatable of type INNODB, it contains about 2M data rows. When I deleted data rows from the table, it did not release alloted disk space.
Is there any way to reclaim disk space from mySQL.
I am in a bad sitution, this application is running at about 50 different locations and now problem of low disk space is appearing at almost all locations.
Mysql doesnt reduce the size of ibata1. 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 a optimize table.
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.