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?

ae.
  • 231
  • 1
  • 7
  • 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 09:56

2 Answers2

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

Srikar Appalaraju
  • 215
  • 3
  • 10
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