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

• 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 :)

INNODB file_per_table growth based on Data Added

How does INNODB in mysql 5.0 tablespace per file grow?

I can just add data and hope to catch when the table space grows, but I really want to understand the internals of INNODB and build an accurate modal on how big the table space will grow when X rows are added.

Some things that need to be known.

• Data stored in INNODB is stored as pages with a default size of 16KB.
  
• INNODB adds a page for every secondary INDEX


• INNODB has some overhead of about 18 bytes (maybe more) per row

First let's set up a test environment

delimiter //

CREATE PROCEDURE resettest()

 begin  
        DROP TABLE IF EXISTS testvarchar;
        CREATE TABLE `testvarchar` (
                        `id` int(10) unsigned NOT NULL,
                        `data_key` varchar(255) default NULL,
                        `data_nokey` varchar(255) default NULL,
                        PRIMARY KEY  (`id`),
                        KEY `data_key` (`data_key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


 end;
//

delimiter ;

Now set up some inserts to populate the table

delimiter //

CREATE PROCEDURE populate(IN param1 INT)

 begin  

   declare i int; 

   set i = 0;

   start transaction;

   while i < param1 do

     insert into testvarchar (id, data_key, data_nokey)

            values (i, repeat("-", 1), repeat("-", 250));

     set i = i + 1; 

   end while;

   commit;

 end;
//

delimiter ;

Now from this I know that the size of each record is
id - 4 bytes
data_key - 2 bytes (varchar there is a pad of a single byte)
data_nokey - 251 bytes (ditto)
TOTAL: 257 bytes

Since there data_key AND it's not the PRIMARY INDEX-a page is allocated just for it. So let's sum up the data.

id - 4 bytes //PK
data_key - 2 bytes //Secondary Index
Total: 6 bytes

for a total 257 bytes for the table plus 6 bytes for the index for a total of 263 bytes.

Now there is some overhead on INNODB tables, there is a Record Header which is 5 bytes and the secondary index references the PRIMARY KEY (the clustered index), Plus a transaction-id which is 6 bytes and a roll pointer which is 7 bytes for the undo log

So the record size is 263 (INDEX && DATA) + INNODB OVERHEAD (17) = 280 bytes.

Now since the INDEX takes a page and data takes a page I need to take

32768/280 == NUMBER OF RECORDS to make the TABLE SPACE GROW 32 KB
This == 117 rows;

The start of the datafile is 112K which is 96K + Page for INDEX at create time

 ls -lrh
total 132K
-rw-rw----  1 mysql mysql 112K Oct 10 22:00 testvarchar.ibd
-rw-rw----  1 mysql mysql 8.5K Oct 10 22:00 testvarchar.frm
-rw-rw----  1 mysql mysql   65 Oct 10 00:16 db.opt

Adding 117 rows

call resettest();
call populate(117);

 ls -lrh
total 164K
-rw-rw----  1 mysql mysql 144K Oct 10 22:16 testvarchar.ibd
-rw-rw----  1 mysql mysql 8.5K Oct 10 22:16 testvarchar.frm
-rw-rw----  1 mysql mysql   65 Oct 10 00:16 db.opt

call resettest();
call populate(234); // 64K / 280 =~ 234

//GROWS another 32K
ls -lrh
total 196K
-rw-rw----  1 mysql mysql 176K Oct 10 22:17 testvarchar.ibd
-rw-rw----  1 mysql mysql 8.5K Oct 10 22:17 testvarchar.frm
-rw-rw----  1 mysql mysql   65 Oct 10 00:16 db.opt

All though this is may all look right, in fact it's not. This may be a good approximation, but I am not taking into account the additional overhead added by INNODB to force the datafile to grow prior to this calculation at a much earlier row count. I have not tracked down what that is yet. It may be that innodb needs to reserved space based on the 2xheight of the binary tree to make sure there is enough space to undo leaf splitting. I'm sure that INNODB doesn't allocate the full 255 bytes for varchar in indexes or does it? Maybe you can shed some light?