How can we free physical disk usage after cleaning up in the MySQL ibdata1 database file?

Question

Our ibdata1 database file has grown to tens of GB, where most of the SQL data is for sure the log-tables in Magento, taking up to 90% of the database's space.

After emptying and cleaning these tables, the ibdata1 file still uses the same amount of disk usage on the server, and after my understanding this will never be "free'd" to the operating system.

Is this correct? Is there not any standard and easy going way to "optimize" the tables in a MySQL database so that the operatingsystem sees the "updated" version of the ibdata1 file, taking less amount of physical disk usage?

PS: After my understanding, when OPTIMIZE'ing the tables, then MySQL will try to re-use the space previously used by the log-data and such.

Answer 1

What you basically will have to do is

• create a full database dump using mysqldump, enable the innodb_file_per_table option
• wipe the data in your mysql data dir (or spin off another mysql instance with a fresh ibdata fileset)
• and re-import your database dump.

This will incur downtime the exact amount of which will depend on the duration of the dump/restore operation (and thus the database size) and your agility in regards to MySQL database handling.

Take a look at this question over at DBA.SE to have it explained in more detail or at this blog post for a complete walk-through for minimized downtime during the operation.

Answer 2

This may not be a direct answer to your question. However, if you have used the server setting innodb_file_per_table before you create the table, a separate file will be used to store the data for the table which the disk space will be freed after running optimize.

Answer 3

It would be worth creating a cron job to regularly clear the log tables? And the index_event table.

You can also set logs to be cleared regularly from the admin area:

System > Configuration > Advanced > System > Log Cleaning

You can configure your store to automatically clean up these logs.

Set "Save Log, Days" to 2 or 3 days. And enable log cleaning. Hope this helps?

Answer 4

If I understand your question correctly you want to free up disk space after cleaning up ibdata1 file. Restart MySql to recover space. If a process in linux has a file open you wont be able to recover disk space with out restarting the process. Files are reference-counted in *nix, and as long as there's an reference (i.e. an open fd), it'll still be there even though there are no links left to the inode.

I hope this helps.