Brian Aker, a brilliant helpful duder, who I learn a lot from. Gives a great talk about what is NoSQL explained in a way for database guys. I warn you, there are some points in this video where you can't hear Brian due to the audience "participation" but you should get the content.
Thursday, February 25, 2010
Thursday, February 04, 2010
innodb_file_per_table, shrinking table spaces and the data dictionary
INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.
If you are running innodb_file_per_table, you will notice in your database directory
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 :)
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 :)
Subscribe to:
Posts (Atom)
