Right now I use MySQL mainly for Mifos development. I just noticed that my MySQL data directory was using almost 3 gigabytes even though very little data existed in any of the databases. I figured out why and I thought I would share my findings in case anyone else might find them helpful.
The issue is that Mifos uses many InnoDB-type tables (for ACIDity), and the MySQL InnoDB storage engine, by default on my Ubuntu 7.10 system, uses one big file for all InnoDB tablespace and doesn't automatically reclaim on-disk storage space for dropped InnoDB tables. A while ago I loaded a huge customer database, then dropped it to free up space. But since the InnoDB tablespace is never automatically shrunk, the space was still used up. This issue may be a problem for you if you've dumped and re-loaded large databases several times. If this is the case, I recommend using InnoDB per-table tablespaces. MySQL will delete these when you drop a database. The conversion can safely be performed on existing databases. Relevant manual reference: => http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html A very good step-by-step guide: => http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/ Same issue described by a different author: => http://forums.mysql.com/read.php?35,121880,121886 Related open bugs: => http://bugs.mysql.com/bug.php?id=1341 => http://bugs.mysql.com/bug.php?id=15748 Hope this helps! -- Adam Monsen ------------------------------------------------------------------------- SF.Net email is sponsored by: Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://sourceforge.net/services/buy/index.php
