ほぼダウンタイムなしでinnodb plugin でのテーブル圧縮

前提条件
- innodb_file_per_tableが有効になってること
- innodb pluginが使えるバージョンのMySQLであること

innodb_file_formatを下記のように変更

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_format_max=Barracuda;

既存のテーブルとほぼ同じ構成の新しいテーブルを作成(ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4を追加しただけ)

CREATE TABLE `new_recipe_log` (
  `type` varchar(200) NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `date` datetime NOT NULL,
  `recipe` mediumtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
 PARTITION BY RANGE (TO_DAYS(date))
(PARTITION p20120530 VALUES LESS THAN (735018) ENGINE = InnoDB,
 PARTITION p20120531 VALUES LESS THAN (735019) ENGINE = InnoDB,
 PARTITION p20120601 VALUES LESS THAN (735020) ENGINE = InnoDB,
 PARTITION p20120602 VALUES LESS THAN (735021) ENGINE = InnoDB,
 PARTITION p20120603 VALUES LESS THAN (735022) ENGINE = InnoDB,
 PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

徐々にINSERT INTO SELECT

INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-05-29 00:00:00' AND '2012-05-29 23:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-05-30 00:00:00' AND '2012-05-30 23:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-05-31 00:00:00' AND '2012-05-31 23:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-06-01 00:00:00' AND '2012-06-01 23:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-06-02 00:00:00' AND '2012-06-02 11:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-06-02 12:00:00' AND '2012-06-02 17:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-06-02 18:00:00' AND '2012-06-02 21:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-06-02 22:00:00' AND '2012-06-02 22:59:59';
INSERT INTO new_recipe_log SELECT * FROM recipe_log WHERE date BETWEEN '2012-06-02 23:00:00' AND '2012-06-02 23:59:59';

適当なタイミングでRENAME TABLE(数秒かかる時もある)

RENAME TABLE recipe_log TO old_recipe_log,
             new_recipe_log TO recipe_log;

動作確認したらDROP TABLE

DROP TABLE old_recipe_log;

データが大きいとかなり時間かかります。