If you are running innodb_file_per_table, you will notice in your database directory
- db.opt - database characteristics file.
- tablename.frm - the table structure.
- tablename.ibd - the actual innodb table space file
Imagine that you have a table with 10 million rows and you delete say 5 million rows in multiple chunks around 400K chunks, because deletes are slow. Next, you notice that the table space file did not shrink. So what do you do? OPTIMIZE tablename, tada all the wasted space is reclaimed, but here is the PROBLEM the ibdata file grew!
ibdata stores all of the UNDO LOGS thus GROWS due to the deletes and space is never reclaimed.
Ok, lets try coping the tablespace file to another directory and re-import the tablespace file after wiping the data dictionary (ibdata).
cp * /tmp/hold
cp /tmp/hold/* /var/lib/mysql/DB
/etc/init.d/mysql start // create the ibdata file
ALTER TABLE tablename IMPORT TABLESPACE
ERROR 1146 (42S02): Table 'DB.tablename' does not exist
Really, there is nothing that you can do to force innodb to purge the undo logs in the ibdata file without a full dump of the data to text and then reimport the data.
So here are the steps to shrink all table spaces and the ibdata file
mysqldump --all-databases (or use mk-parallel-dump)
rm -f /var/lib/mysql/ib* /var/lib/mysql/DB/*
mysqlimport (or use mk-parallel-restore)
Takes a bunch of time but there is no other recourse. If you know of another way please share :)