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).
For instance
/etc/init.d/mysql stop
cd /var/lib/mysql/DB
cp * /tmp/hold
rm /var/lib/mysql/ib*
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)
stop mysql
rm -f /var/lib/mysql/ib* /var/lib/mysql/DB/*
start mysql
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 :)
2 comments:
I haven't administered a production MySQL box in a few years, but this reminds me of Oracle's ALTER TABLE MOVE TABLESPACE X where X is the tablespace in which it already resides.
From MySQL 5.0 Reference Manual ... Defragmenting a Table:
It can speed up index scans if you periodically perform a “null” ALTER TABLE operation, which causes MySQL to rebuild the table:
ALTER TABLE tbl_name ENGINE=INNODB;
Little scary as you deleting the files directly; you instead instead drop them (or atleast truncate and then drop).. if you just remove, tablespace still has entries for them.
Thats always a problem until InnoDB supports a easy way to purge and reclaim the space without re-creation of the table. Its easy to introduce a command, that can purge this without affecting the online activity on the table.
Normally, I create a new table; and copy over the data and flip at the end... (provided you have a easy way to identify whats new/old records)
Post a Comment