Re: how to shrink a MySQL database
Posted by: Bill Karwin
Date: October 17, 2006 11:20AM

Correct. This is a limitation of InnoDB's shared tablespace file. It would be a very expensive operation for the MySQL Server to compact the used space in the tablespace file and shrink the file. Imagine if you have a 100GB tablespace file, and you want to perform a rebuild to compact it. Typically, MySQL table rebuilds require both the old and the new storage to exist simultaneously on disk during the operation.

So InnoDB's behavior is to leave the empty space, and use it for subsequent insertion of new row versions.

See:
Bug #1287 http://bugs.mysql.com/bug.php?id=1287
Bug #1341 http://bugs.mysql.com/bug.php?id=1341

One solution to shrinking a shared InnoDB tablespace is:
1. Backup *all* InnoDB tables with mysqldump.
2. Drop all of the InnoDB tables.
3. Physically delete the ibdata1 file at the filesystem interface.
4. Restart MySQL Server, which recreates a new, small tablespace file.
5. Restore all your InnoDB tables, which expands the tablespace file as needed.

Another solution is to use the option to store InnoDB tables in a separate file per table. See http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html If you do that, you should be able to use OPTIMIZE TABLE or ALTER TABLE on each InnoDB table that you want to shrink. This is supposed to rebuild the .ibd file for the individual table.

Regards,
Bill K.

Options: ReplyQuote


Subject
Written By
Posted
October 17, 2006 10:47AM
Re: how to shrink a MySQL database
October 17, 2006 11:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.