Mysql 5.5 Defragmentation not reducing the fragmentation percent

Question

We are running OPTIMIZE TABLE , but not seeing any fragmentation reduction. They are as high as 120% to 200%.

Am calculating the fragmentation % using this: Data_Free / (Data_Length + Index_Length)

My tables are using INNODB engine and "innodb_file_per_table" set to 1.

What am I missing?

Has something change with Mysql 5.5+ onwards?

TIA

data_free has no context in MySQL terms - what is this variable in your calculation referring to? — thinice, Mar 01 '12 at 05:56
Have you used innodb_file_per_table from the beginning ??? What is the size of ibdata1 ??? — RolandoMySQLDBA, Mar 02 '12 at 00:31

thinice · Answer 1 · 2012-03-01T05:56:28.267

2

"OPTIMIZE TABLE" is only for MyISAM tables - InnoDB doesn't suffer from 'fragmentation' like MyISAM tables. Therefore, you don't need to optimize them.

Additionally, your calculation method is incorrect. As there's more going on than just data+index length.

edited Mar 01 '12 at 05:56

answered Mar 01 '12 at 05:50

thinice

4,716
21
38

From the documentation (http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html): For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. AM I MISSING SOMETHING? – Abhishek Ravi Mar 01 '12 at 11:04
1. From the manpage: "Table does not support optimize, doing recreate + analyze instead". 2. InnoDB doesn't suffer from 'fragmentation' _LIKE_ MyISAM tables. I guess I could have been more clear that it -does-, just not at the level of MyISAM. 3. Google "Optimize INNODB table" - you'll see there's a lot of information pointing to how unhelpful 'optimizing' (alter table/index recreation) is in the context of storage. – thinice Mar 01 '12 at 14:47

Fiasco Labs · Answer 2 · 2012-03-01T06:55:56.353

ALTER TABLE table_name ENGINE=InnoDB; can be used on InnoDB tables to rebuild the table, clean up its indexes and in the case of using innodb_file_per_table=1, resize the table file.

Aside from that, innodb takes care of itself and any slowdowns you may now suffer after running this command are because you've rebuilt the table indexed on the primary key and if secondary keys are used more often in db lookups, you've fragmented them instead.

InnoDB tends to understand sparse file storage of data and probably needs to be "fixed" a lot less often than some people inflict upon it.

Mysql 5.5 Defragmentation not reducing the fragmentation percent

2 Answers2