2

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?

1
  • 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. Feb 11, 2012 at 9:56

2 Answers 2

1

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

1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .