Skip to main content

MySQL: Disk Usage Can Never Shrink When Using InnoDB

One of the startups I work for is a recommendation engine. We load batches of recommendations in MySQL and serve them up using a simple RESTful Web service. Each batch of recommendations can be quite large--often multiple gigs. We delete batches and even drop databases all the time.

However, we just hit a brick wall. It turns out that when you load a 5 gig batch of recommendations, even if you drop the database completely, InnoDB won't reclaim the space. Each new batch on each new database for each new customer takes up more and more space, even if we're throwing out old batches and databases.

This issue is covered here. It's been around since 2003, and it's marked "Not a Bug". Our staging server just went down because we ran out of disk space. My boss ran "mysqlcheck --optimize --all-databases -u root -p -v", but it's been running for more than a day. It looks like the only viable alternative is to completely dump every single database on the server, delete the InnoDB data file completely, restart the server, and reload every database from scratch.

Comments

sagara said…
There's an innodb option to have a per-table tablespace (http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html), which be useful in your case, since dropping the tables involved will free up the diskspace.