Turns out MySQL’s InnoDB engine isn’t (by default) very enthusiastic to give back disk space if after mass deletions (say, a DELETE * or a TRUNCATE TABLE).
The fix is a little unsettling, but it’s also simple (and with a modestly size db – fast).
- Back up the db (you should doing this anyway, but now is a nice time to do it again.)
mysqldump -uroot -p --opt --skip-lock-tables -f --quote-names --databases yourDB > yourDB.sql
- Confirm you made a backup. Then drop the databases you just backed up (I told you there was an un-nerving part).
- Shutdown the MySQL server – whatever your preferred method is. I like to click ‘stop’ in Webmin, maybe you like the command line.
- Remove all innodb files (it’s pretty obvious which ones they are)
- Restart the MySQL server
- Reimport your database
mysql -uroot -p < yourDB.sql
While you’re here – hop over to my.cnf and add innodb_file_per_table so next time OPTIMIZE TABLE will actually do what you expect it to do.
