MySQLのレプリケーション形式には、ステートメントベースレプリケーション(SBR)と行ベースレプリケーション(RBR)があります。
SBRはマスターで実行されたDMLをそのままバイナリログに書き込み、スレーブではその記述されたDMLを実行します。RBRはマスターで実行されたDMLによって変更された各行の内容をバイナリ形式でバイナリログに書き込み、スレーブではその変更された行の内容を適用します。今回は行ベースレプリケーションの遅延について紹介いたします。
SBRとRBRについて
まず、SBRとRBRの違いについて説明します。たとえば、以下のようなUPDATE文を実行します。
このUPDATE文は2行のデータが更新されました。このとき、SBRはUPDATE t SET id=4;
の文がそのままバイナリログに保存されてスレーブで実行されます。マスターのバイナリログを見ると、UPDATE文が記述されているのがわかります。
RBRは変更された2行の内容をバイナリログに保存し、各行ごとにスレーブで実行されます。マスターのバイナリログを見ると、変更された行がバイナリ形式(40wtWBMBAAAA…から始まる部分)で記述されています。
また、コメント化されたUPDATE文が記述されているのはmysqlbinlog
コマンドに--verbose
オプションをつけることで表示されるもので、バイナリ形式の内容からSQL文を生成します。生成されたSQL文はどの行に対してどのような変更がされたのをわかりやすく表示するもので、実際に実行された文ではありませんのでご注意ください。
今回は2行の更新があったので、UPDATE文も2つ確認できます。
また、ここでは詳しく説明しませんが、MySQL5.6以降のパラメータbinlog_rows_query_log_events
を使用することで、マスターで発行された元のDMLをバイナリログで確認できる方法もあります。
SBRやRBRはパラメータbinlog_format
で設定が可能です。デフォルトはMySQL5.6とそれ以前ではSTATEMENT
(SBR)、MySQL5.7以降ではROW
(RBR)です。このパラメータは起動中のMySQLに対して動的に変更可能です。
MySQLバージョン |
binlog_formatデフォルト値 |
MySQL5.6とそれ以前 |
STATEMENT |
MySQL5.7以降 |
ROW |
SBRとRBRを混合したMIXED
という設定も可能です。この設定値は基本的にはSBRですが、LIMIT句を使用した更新などの非決定的要素を含む更新があったときにRBRに切り替わります。詳しくは5.2.4.3 混合形式のバイナリロギング形式をご確認ください。
RBRの遅延について
前置きが少し長くなりましたが、このRBRの遅延について紹介します。RBRでは、マスターではさくっと終わったDMLでもスレーブで大幅に遅延が発生することもあります。それが起こる流れを見てみましょう。
レプリケーション遅延の発生
binlog_formatをROW
で運用しているMySQLがあったとします。
このようなテーブルを作成しました。このテーブルはバッチ処理用の仮テーブルのため、プライマリーキーもインデックスも貼っていません。そして、数十万行のデータを投入し、何かしらの作業が終了したあとにデータを削除しました。ロングトランザクションを防ぐために、以下のようなDELETE文を使用しました。
マスターでは1秒以内でこの文は完了し、これを数回繰り返してすべてのデータ削除しました。しかし、この文が原因で、スレーブで大幅な遅延が発生しました。
レプリケーション遅延の原因
今回は以下の要因により遅延が発生しました。
- プライマリキーやインデックスを作成していないため毎行削除のためにテーブルフルスキャン
インデックスを作成していないテーブルなので、スレーブで実行されるときに削除する行を見つけるために、1行削除するたびにテーブルフルスキャンを行うことになってしまい、レプリケーションの遅延が発生しました。
レプリケーション遅延の解決
いくつかの解決方法があります。
- プライマリーキーまたはインデックスを作成する
- パラメータ
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でもいいのでプライマリーキーは付与したほうが良いでしょう。