MySQL 5.6のクラッシュセーフなレプリケーションの仕組み

ちょこちょこ思うこと。

MySQL 5.6のnutshellを読むと、`クラッシュセーフなレプリケーションを実現するためにはmaster_info_repositoryとrelay_log_info_repositoryをTABLEに設定しな！ おっと、relay_log_recoveryも1にしておくんだぜ'みたいことが書いてある。

で、このバグレポートを見た時からずっと、sync_master_infoの暗黙のデフォルト10000のまんまじゃクラッシュセーフじゃなくね？ sync_master_info= 1必須？ それはパフォーマンス的に死ねる。という感じでモヤモヤしていたのだが、やっと合点がいったのでメモしときます。

　⇒ MySQL Bugs: #69135: mysql.slave_master_info is not updated

まず、出てくるパラメータの説明。

• master_info_repository
• FILEの場合、従来どおりmaster.infoファイルに書く。
• ファイル名はmaster_info_fileにより可変。
• 更新があるたび(バイナリーログを受信するたび)にファイルにwrite、sync_master_info回イベントを受信するとfdatasync。5.5までの暗黙のデフォルトは0なのでfsyncはしない。5.6.6以降の暗黙のデフォルトは10000。
• TABLEの場合、mysql.slave_master_infoテーブルに書く。
• コイツはmysqlスキーマ初のInnoDBテーブル。
• sync_master_info回イベントを受信するたびにUPDATE。
• sync_master_info= 0にすると、mysql.slave_master_infoは *アップデートされない*
• SHOW SLAVE STATUSはこのテーブルの情報を読んでいる訳ではないので、ちゃんと更新される。
• relay_log_info_repository
• master_info_repositoryのrelay-log.info版っぽいが、TABLEを指定した場合は少し振る舞いが異なる（俺的混乱のもとその1）
• FILEの場合はいっしょなので割愛。
• TABLEの場合、mysql.slave_relay_log_infoテーブルに書く。
• SQLスレッドがリレーログから読み出したステートメントを実行する時に、同じトランザクションの中でmysql.slave_relay_log_infoを更新するようになる。
• sync_relay_log_infoの値に依存せず、毎回テーブルは更新される。
• mysql.slave_relay_log_infoを非トランザクションテーブルにすると、master_info_repository= TABLEの時と同じ動作(sync_relay_log_info回に1回更新)
• 【2013/12/11 11:35】
  mysql.slave_relay_log_infoがトランザクション対応か非対応化は *関係なく* マスター側の更新されたテーブルとスレーブ側で更新が適用されるテーブルが共にトランザクション対応であるかどうか、で更新頻度が変わる。
• Master MyISAM-> Slave MyISAM: depends on sync_relay_log_info
• Master MyISAM-> Slave InnoDB: depends on sync_relay_log_info
• Master InnoDB-> Slave MyISAM: depends on sync_relay_log_info
• Master InnoDB-> Slave InnoDB: updated after each transaction
• http://bugs.mysql.com/bug.php?id=71083
• relay_log_recovery
• 実は5.5からあるオプション。
• 起動した時に、持っているリレーログとrelay-log.infoを比較して、まだ適用していない(Exec_Master_Log_Pos以降の)リレーログを破棄する。

という訳で、relay_log_recovery= ONであれば、「Exec_Master_Log_Posの情報が正しくrelay-log.info(or mysql.slave_relay_log)に反映されている限り、バイナリーログの二重取りや欠損は発生しない(はず)」、かつ、relay_log_info_repository= TABLEであれば、「SQLスレッドが変更を反映するタイミングでmysql.slave_relay_log_infoも同じトランザクションで更新されるため、All or Nothingで片方だけが失われる(不整合が起こる)ことはない(はず)」になり、

relay_log_recovery && relay_log_info_repository= TABLEならクラッシュセーフになる、という理屈らしい。

………master_info_repository関係なくない？ 関係ないよね？

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あたりがバックアップをこの形式で出力できるようにならないかな。。