How to Reclaim Data Free in InnoDB Tables?

Question

I came up with an idea on how to shrink ibdata1 in mysql database then i got this solution on stackoverflow

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql schema)

STEP 03) Shutdown mysql

STEP 04) Add the following lines to /etc/my.cnf innodb_file_per_table =1

STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1

STEP 06) Restart mysql

STEP 07) Reload SQLData.sql into mysql

after that steps, i executed SELECT TABLE_NAME, TABLE_SCHEMA, Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql') AND Data_Free >0;

at sqlyog, this is the output:

after that i run:

mysqlcheck -u root -p$pw --debug-info --all-databases --auto-repair

mysqlcheck -u root -p$pw --debug-info --all-databases --optimize

then i query show table status on sqlyog, what i am expecting is under data_free column will be a value of zero, since on my current setup its should release unused space space.

and my question is, is there something wrong in what i did? how am i gonna do it right in reclaiming unused space in innodb tables?

mysql 5.5 on rhel 6.3

Regards,

John

Answer 1

Everything you have done is correct in order to drastically defragment innodb tablespaces (there are other ways, but exporting, deleting files and importing certainly works).

Trying to optimize the tables (which, by the way, the correct way to do it for innodb is running ALTER TABLE mytable ENGINE=InnoDB- OPTIMIZE table just calls this, and REPAIR table does nothing for InnoDB) just after an in-primary-key-order import is useless. The way InnoDB works is optimising for access and write performance, not for data size. You will always have overhead in disk space (even if free space says 0), as innodb reserves spaces in whole extend beyond a certain size. Also, some data types, like blobs and random inserts and deletions can lead to extra fragmentation, but I presume that the size you are showing is less than 10% of the table size, so you should no worry. After a proper import, it is the least fragmented way innodb can work with the default options. Under normal operation, the file size should be constant when inserting new data until no more free space is available.

Of course, you can try to change parameters like extent or page sizes, but if you are concerned about saving disk space over performance I would recommend you to try compression (InnoDB Barracuda file formant has it) or use a different engine. However, please note that fragmentation and having huge files with lots of free space is usually not a concern with innodb_file_per_table = 1 under normal loads.