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

A lot of my Innodb tables require 'defragmentaion'. My understanding is that 'OPTIMIZE' may not be the best way to do it. Some people suggest doing a 'ALTER TABLE' to have Mysql rebuild (and therefore defragment) the table.

Does anyone have any suggestions (based on experience) of whats the best way to defragment production Innodb tables?

share|improve this question

migrated from stackoverflow.com Aug 16 '11 at 4:35

This question came from our site for professional and enthusiast programmers.

2  
Since InnoDB physically stores data in a btree fashion and uses a large buffer pool, I dont believe any kind of defragmentation will offer any useful performance benefits. Unless you store lots of very large blob data which uses 'offset' nodes. –  Matthew Ife Feb 11 '12 at 9:56

2 Answers 2

To defragment innodb follow this. Since you say its a production DB you could do this -

  1. Start a master-master sync.
  2. Put your master & slave in the different machines that way they run on different mysql instances.
  3. Once the master-master sync is complete, then start the defragmentation process on the one of them, the other machine will be serving requests till then...
  4. After one is over, do the same for other.

Hope this helps..

share|improve this answer

The first thing I would do is run mysqldump to make sure I had a backup: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Maybe this is obvious but I would make that the first step

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.