Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
data_free has no context in MySQL terms - what is this variable in your calculation referring to? –  thinice Mar 1 '12 at 5:56
    
Have you used innodb_file_per_table from the beginning ??? What is the size of ibdata1 ??? –  RolandoMySQLDBA Mar 2 '12 at 0:31

2 Answers 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.

share|improve this answer
    
From the documentation (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 1 '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 1 '12 at 14:47

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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