MySQLのレプリケーション形式には、ステートメントベースレプリケーション（SBR）と行ベースレプリケーション（RBR）があります。

SBRはマスターで実行されたDMLをそのままバイナリログに書き込み、スレーブではその記述されたDMLを実行します。RBRはマスターで実行されたDMLによって変更された各行の内容をバイナリ形式でバイナリログに書き込み、スレーブではその変更された行の内容を適用します。今回は行ベースレプリケーションの遅延について紹介いたします。

SBRとRBRについて

まず、SBRとRBRの違いについて説明します。たとえば、以下のようなUPDATE文を実行します。

mysql> UPDATE t SET id=4 ;

Query OK, 2 row affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

このUPDATE文は2行のデータが更新されました。このとき、SBRはUPDATE t SET id=4;の文がそのままバイナリログに保存されてスレーブで実行されます。マスターのバイナリログを見ると、UPDATE文が記述されているのがわかります。

#161117 14:51:53 server id 1  end_log_pos 282 CRC32 0x15364852  Query   thread_id=3     exec_time=0     error_code=0
use `aa`/*!*/;
SET TIMESTAMP=1479361913/*!*/;
update t set id=4

RBRは変更された2行の内容をバイナリログに保存し、各行ごとにスレーブで実行されます。マスターのバイナリログを見ると、変更された行がバイナリ形式（40wtWBMBAAAA…から始まる部分）で記述されています。

また、コメント化されたUPDATE文が記述されているのはmysqlbinlogコマンドに--verboseオプションをつけることで表示されるもので、バイナリ形式の内容からSQL文を生成します。生成されたSQL文はどの行に対してどのような変更がされたのをわかりやすく表示するもので、実際に実行された文ではありませんのでご注意ください。

今回は2行の更新があったので、UPDATE文も2つ確認できます。

#161117 14:53:07 server id 1  end_log_pos 288 CRC32 0xb7c296bb  Update_rows: table id 71 flags: STMT_END_F

BINLOG '
40wtWBMBAAAAKgAAAOgAAAAAAEcAAAAAAAEAAmFhAAF0AAEDAAHYgKdx
40wtWB8BAAAAOAAAACABAAAAAEcAAAAAAAEAAgAB///+BgAAAP4EAAAA/gYAAAD+BAAAAI57bt8=
'/*!*/;
### UPDATE `aa`.`t`
### WHERE
###   @1=6
### SET
###   @1=4
### UPDATE `aa`.`t`
### WHERE
###   @1=6
### SET
###   @1=4

また、ここでは詳しく説明しませんが、MySQL5.6以降のパラメータbinlog_rows_query_log_eventsを使用することで、マスターで発行された元のDMLをバイナリログで確認できる方法もあります。

SBRやRBRはパラメータbinlog_formatで設定が可能です。デフォルトはMySQL5.6とそれ以前ではSTATEMENT（SBR⁠）⁠、MySQL5.7以降ではROW（RBR）です。このパラメータは起動中のMySQLに対して動的に変更可能です。

SBRとRBRを混合したMIXEDという設定も可能です。この設定値は基本的にはSBRですが、LIMIT句を使用した更新などの非決定的要素を含む更新があったときにRBRに切り替わります。詳しくは5.2.4.3 混合形式のバイナリロギング形式をご確認ください。

RBRの遅延について

前置きが少し長くなりましたが、このRBRの遅延について紹介します。RBRでは、マスターではさくっと終わったDMLでもスレーブで大幅に遅延が発生することもあります。それが起こる流れを見てみましょう。

レプリケーション遅延の発生

binlog_formatをROWで運用しているMySQLがあったとします。

CREATE TABLE temp_table ( tmp_id int);

このようなテーブルを作成しました。このテーブルはバッチ処理用の仮テーブルのため、プライマリーキーもインデックスも貼っていません。そして、数十万行のデータを投入し、何かしらの作業が終了したあとにデータを削除しました。ロングトランザクションを防ぐために、以下のようなDELETE文を使用しました。

DELETE FROM temp_table LIMIT 100000;

Query OK, 100000 row affected (0.82 sec)
Rows matched: 100000  Changed: 100000  Warnings: 0

マスターでは1秒以内でこの文は完了し、これを数回繰り返してすべてのデータ削除しました。しかし、この文が原因で、スレーブで大幅な遅延が発生しました。

レプリケーション遅延の原因

今回は以下の要因により遅延が発生しました。

• プライマリキーやインデックスを作成していないため毎行削除のためにテーブルフルスキャン

インデックスを作成していないテーブルなので、スレーブで実行されるときに削除する行を見つけるために、1行削除するたびにテーブルフルスキャンを行うことになってしまい、レプリケーションの遅延が発生しました。

レプリケーション遅延の解決

いくつかの解決方法があります。

1. プライマリーキーまたはインデックスを作成する
2. パラメータslave_rows_search_algorithmsを変更をする

はじめに、仮テーブルに対してプライマリーキーまたはインデックスを事前に作成しておけば、このような遅延は発生しません。今回のような遅延が発生してしまった場合は、レプリケーションを一旦停止してスレーブのみにインデックスを作成しても、遅延は解消されます。

次に、パラメータslave_rows_search_algorithmsを変更することで、インデックスがないときでもスレーブの更新速度が多少改善されます。これはMySQL5.6以降のパラメータで、RBRにおいて行の一致をどのように検索するかを指定するパラメータです。デフォルトはTABLE_SCAN,INDEX_SCANで、インデックスがあればインデックススキャン、なければテーブルフルスキャンで行を処理するように設定されています。

これをINDEX_SCAN,HASH_SCANに変更することで、インデックスがないときはテーブルハッシュスキャンで処理するため、テーブルフルスキャンよりかは速くなります。しかし、インデックスがあるほうが断然速いため、このパラメータに頼ることは少ないでしょう。このほか、ハッシュスキャンが効果を発揮するのはパラメータbinlog-row-event-max-sizeが大きいときです。このパラメータはRBRにおいてバイナリログに記述する行情報の最大サイズを指定します。そのため、事前にマスターでの設定が必要になります。

前述のとおり、MIXEDでもRBRに切り替わることがあるので同様の事象は発生します。MIXEDは単純なDELETE文であればSBRで処理されますが、今回のような非決定要素のLIMIT句が使用されている場合は行ベースでレプリケーションされます。

まとめ

RBRを行う場合は、不用意なレプリケーション遅延を防ぐために、すべてのテーブルにAUTO INCREMENTでもいいのでプライマリーキーは付与したほうが良いでしょう。