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:

enter image description here

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




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.

  • thanks for your input, i just use our database with less size to evaluate fragmentation, i am concern about the size of datadir before i got 2.6GB, after the new setup its 4.0GB does it suppose to expand its size like that for better performance? i'm just new to this. our main goal in doing so is to reclaimed unused space because we have DB constantly growing in size, so i hope this setup could help. – EnglandYOW Jul 5 '14 at 4:41
  • The main thing that I can think of is that you may have lots of "small" tables, in which fragmentation can be (among others) a problem. There a third way, which is dynamically disable innodb_file_per_table = 0 for the creation of those small tables that may not be very dynamic, so they are still being created in the common tablespace (ibdata1). It is a bit painful to maintain, but it may be necessary in some cases. But first, you should confirm where the extra bytes have gone, by comparing the logical size as seen on SHOW TABLE STATUS and the physical size of the files(ibdata1,log files,.ibd). – jynus Jul 5 '14 at 4:51
  • i see, i want to clarify this i know you could, table 1 engine is innodb with data free xxxx, then altering it to myisam data free value change to zero, what does it mean? – EnglandYOW Jul 5 '14 at 5:07
  • They are 2 different internal formats, with different properties. Even if you were only interested on data size, focus on Data_length + Index_length vs. file size; data_free is in many cases meaningless. – jynus Jul 5 '14 at 5:14
  • Hey Jynus. I looked at your answer and "OMG What's taking everybody so long to vote on this ?" +1 for this very thorough answer. – RolandoMySQLDBA May 8 '15 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.