Home / server / Questions / 301490
In Process
ae.
Asked: 2011-08-16 19:32:24 +0800 CST

Whats the best practice for defragmenting a MySQL InnoDB table in production?

  • 772

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?

mysql database innodb

2 Answers

  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

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

    • 1