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.
Best Answer
What you basically will have to do is
mysqldump
, enable theinnodb_file_per_table
optionThis 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.