Handling Recovery of Disk Space in MySQL and MongoDB

Question

So I have worked with MySQL all my life (not as a DBAdmin, but a programmer), and I have some limited knowledge about maintaining MySQL databases.

Recently a portion of our database has been moved to MongoDB and we have outsourced MongoDB administration to another company. They informed us that with MongoDB, when data is deleted disk space is NOT recovered and that specific clean up tasks need to be done for this which require downtime.

What I wanted to ask here is that: They also said the same is applicable to MySQL (where if we delete data, disk space is not recovered and we need to run clean up scripts for that purpose). I was never aware about this regarding MySQL.

Can anyone tell me briefly how this works for MySQL or point me to any good resources that I can understand?

Answer 1

InnoDB

I have mentioned how to shrink InnoDB tables and the System Tablespace (ibdata1) in the past

• Mar 25, 2012 : Why does InnoDB store all databases in one file?
• Apr 01, 2012 : Is innodb_file_per_table advisable?
• Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
• Apr 15, 2013 : MySQL database drop insanely slow
• Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full

MyISAM

You could simply run for mydb.mytable

OPTIMIZE TABLE mydb.mytable;

which will shrunk a MyISAM table and recalculate the index statistics. To shrink all MyISAM tables, see my Jul 24, 2012 answer to MySQL: reducing ibdata file size for MyISAM tables

GIVE IT A TRY !!!