MySQLインスタンス間でテーブルを移行する投げ遣りベンチマーク

よく考えるとレアケースなんですが今月末に作業が控えているのでメモ。
データベース名はdb1, 移行対象のテーブル(2つ)はtable1, table2とする。移行元ではMyISAMエンジンを使っていて、移行先ではInnoDBエンジンに変える。


シンプルなパターン。何も考えずにmysqldumpでばっこ抜く。

$ time mysqldump -S /xxx/mysql.sock -uroot -pxxx --default-character-set=binary --single-transaction db1 table1 table2 > mysqldump.sql

real    0m30.573s
user    0m27.051s
sys     0m3.078s

$ ll -h
合計 2.0G
-rw-rw-r-- 1 mysql mysql 2.0G 11月  6 13:38 2013 mysqldump.sql

合計2GB。scpで転送して20秒ちょっと。コイツをリストア先に流し込む。
まずはMyISAMでリストアしてからInnoDBに変換。

$ time mysql -S /xxx/mysql.sock -uroot -pxxx --default-character-set=binary new_db1 < mysqldump.sql

real    7m53.261s
user    0m57.426s
sys     0m1.956s

mysql> ALTER TABLE table1 Engine= InnoDB;
Query OK, 11730918 rows affected (6 min 14.90 sec)
Records: 11730918  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE table2 Engine= InnoDB;
Query OK, 16171279 rows affected (15 min 44.07 sec)
Records: 16171279  Duplicates: 0  Warnings: 0

合計30分弱。やっぱりmysqldumpはリストアが遅い…。
吸い上げたmysqldumpをs/ENGINE=MyISAM/ENGINE=InnoDB/してみる。

$ time sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/' mysqldump.sql

real    0m25.722s
user    0m3.923s
sys     0m2.994s

$ time mysql -S /xxx/mysql.sock -uroot -pxxx --default-character-set=binary new_db1 < mysqldump.sql

real    23m54.496s
user    0m46.110s
sys     0m2.204s

25分弱。多少速くなった。ALTER TABLEで変換よりはマシ。 気を取り直してmysqldumpを--tabにしたらどうだろうか。

$ time mysqldump -S /xxx/mysql.sock -uroot -pxxx --default-characster-set=binary --single-transaction --tab=./xxx --fields-terminated-by=',' --fields-enclosed-by='"' --fields-escaped-by='\\' --lines-terminated-by='\n' db1 table1 table2
real 0m35.618s
user 0m0.002s
sys 0m0.004s

$ ll -h
合計 1.9G
-rw-rw-r-- 1 mysql mysql 2.0K 11月  6 13:34 2013 table1.sql
-rw-rw-r-- 1 mysql mysql 728M 11月  6 13:34 2013 table1.txt
-rw-rw-r-- 1 mysql mysql 2.1K 11月  6 13:34 2013 table2.sql
-rw-rw-r-- 1 mysql mysql 1.2G 11月  6 13:34 2013 table2.txt

MyISAMのまま入れてALTER TABLEはもういい気がするので、InnoDBにしておいてLOAD DATA INFILEで流し込むパターン。

$ time cat table1.sql table2.sql | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' | mysql -S /xxx/mysql.sock -uroot -pxxx --default-character-set=binary new_db1

real    0m0.017s
user    0m0.005s
sys     0m0.003s

mysql> LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';
Query OK, 11730887 rows affected (6 min 32.29 sec)
Records: 11730887  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/table2.txt' INTO TABLE table2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';
Query OK, 16171279 rows affected (16 min 28.26 sec)
Records: 16171279  Deleted: 0  Skipped: 0  Warnings: 0

25分弱。バルクインサートより速いつもりでいたけどそんなに変わらなかった。。

ここから本命。
SH2さんの電波を受信してINDEXをゴニョゴニョしながらインポートする。
 　⇒ MySQL 5.6における大量データロード時の考慮点 - SH2の日記

$ time cat table1.sql table2.sql | mysql -S /xxx/mysql.sock -uroot -pxxx --default-character-set=binary new_db1

real    0m0.017s
user    0m0.005s
sys     0m0.003s

mysql> ALTER TABLE table1 Engine= InnoDB, DEFAULT CHARACTER SET= utf8, DROP KEY xxx, DROP KEY xxx, DROP KEY xxx, AUTO_INCREMENT= 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE table2 Engine= InnoDB, DEFAULT CHARACTER SET= utf8, DROP KEY xxx, DROP KEY xxx, DROP KEY xxx, DROP KEY xxx;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';
Query OK, 11730887 rows affected (2 min 10.14 sec)
Records: 11730887  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/tmp/table2.txt' INTO TABLE table2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';
Query OK, 16171279 rows affected (4 min 52.82 sec)
Records: 16171279  Deleted: 0  Skipped: 0  Warnings: 0

mysql> ALTER TABLE table1 ADD KEY xxx (..), ADD KEY xxx (..), ADD KEY xxx (..);
Query OK, 0 rows affected (2 min 49.87 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE table2 ADD KEY xxx (..), ADD KEY xxx (..), ADD KEY xxx (..), ADD KEY xxx (..);
Query OK, 0 rows affected (4 min 35.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysqldumpを入れ込んで、インデックスを外して、LOAD DATA INFILEしてからインデックスを戻す。
合計15分もかからない。すごいはやいー。
table1でLOAD DATA INFILE, ALTER TABLE, table2でLOAD DATA INFILE, ALTER TABLEの順番にすると、バッファプールの効率がよくなって多少速くなるかもしれない。

今回本番で使うのは↑の手順になるけど、試した中で最速だったのは、
・テキトーに5.6の移動元サーバーのレプリケーションスレーブを作って
・そっちでInnoDB化しておいて
・移動先サーバーも5.6にして
・FLUSH TABLES .. FOR EXPORT
でした。

移動元サーバーで、

mysql> FLUSH TABLES table1, table2 FOR EXPORT;
Query OK, 0 rows affected (0.03 sec)

$ ll -h
合計 4.8G
-rw-rw---- 1 yoku0825 yoku0825   65 11月  6 15:45 2013 db.opt
-rw-rw---- 1 yoku0825 yoku0825 1.3K 11月  6 16:24 2013 table1.cfg
-rw-rw---- 1 yoku0825 yoku0825 8.7K 11月  6 16:14 2013 table1.frm
-rw-rw---- 1 yoku0825 yoku0825 1.5G 11月  6 16:17 2013 table1.ibd
-rw-rw---- 1 yoku0825 yoku0825 1.7K 11月  6 16:24 2013 table2.cfg
-rw-rw---- 1 yoku0825 yoku0825 8.8K 11月  6 16:17 2013 table2.frm
-rw-rw---- 1 yoku0825 yoku0825 3.3G 11月  6 16:21 2013 table2.ibd

.cfgファイルと.ibdファイルをインポート先にコピー(FLUSH TABLES .. FOR EXPORTしたクライアントを閉じちゃうと.cfgファイルが消えてしまうので、クライアントセッションは開いたまま別ターミナルを上げる感じでやる)

ファイルをscpで転送して、移動先のサーバーで

mysql> CREATE TABLE table1 (..) Engine= InnoDB;
Query OK, 0 rows affected (0.35 sec)

mysql> CREATE TABLE table2 (..) Engine= InnoDB;
Query OK, 0 rows affected (0.24 sec)

mysql> ALTER TABLE table1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.66 sec)

mysql> ALTER TABLE table2 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.03 sec)

$ sudo mv table[12].* /xxx/new_db1

mysql> ALTER TABLE table1 IMPORT TABLESPACE;
Query OK, 0 rows affected (28.00 sec)

mysql> ALTER TABLE table2 IMPORT TABLESPACE;
Query OK, 0 rows affected (1 min 2.25 sec)

流石に速い。

知らないと手順がごちゃごちゃする＆使いどころが限られてるけど、上手く使えば5GB/5分くらいで移動できちゃう、という風情。
xtrabackupあたりがバックアップをこの形式で出力できるようにならないかな。。