0

MySQL レプリケーション遅延発生時 の勘所 から解像度をあげてみる。

■MySQL レプリケーション

MySQLのレプリケーションは、安定稼働やバックアップ、負荷分散などの目的に利用できる優れた機能です。

bin-log (バイナリログ) を利用してMaster サーバ⇢Slaveサーバへ更新を伝播させ、
データの複製を行います。

Slaveサーバでは、2 つのスレッドが動作しています。
IO_THREAD – Masterから送られてきたデータを受け取り、relay-log (リレーログ)として書き出す。

SQL_THREAD – relay-logを読み出し、DBを更新する。

スクリーンショット-2015-06-29-16.16.32

■遅延の調べ方
SQL_THREADによる遅延の場合は、SlaveサーバでSHOW SLAVE STATUSコマンドを発行することで確認ができます。

hachim-rep@dbs01> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.49.1   <== Masterのホスト名/IPアドレス
Master_User: replicator     <== Master接続に使用するユーザー名
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.012863 <==現在のMasterから転送された最新の更新ログ
Read_Master_Log_Pos: 205295676 <==現在のMasterから転送された最新の更新ログのポジション
Relay_Log_File: mysqld-relay-bin.026640
Relay_Log_Pos: 75468325
Relay_Master_Log_File: mysql-bin.012863
Slave_IO_Running: Yes ★  共にYesであれば、レプリケーションは正常に動作している。 
Slave_SQL_Running: Yes ★
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 205295676
Relay_Log_Space: 205296082
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 ←*環境によりどんだけまっても0にならない環境もあるかとおもいます。不明な場合はdbaへ相談してみる事をおススメします。
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:100
1 row in set (0.00 sec)

ここに示されている “Seconds_Behind_Master” の値が、
現在SlaveサーバでSQL_THREADが実行しているクエリの実行時刻Slaveサーバが保持しているリレーログの時刻の差となり、遅延を表しています。

IO_THREADによる遅延の場合は、Masterからのバイナリログが受信しきっていないためMaster側でもSHOW MASTER STATUSコマンドを発行し確認します。

hachim-master@dbm> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: mysql-bin.012863
Position: 205295676
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

この結果を基に、file、position項目を見比べ、どれくらいずれているかを判断する必要があります。

具体的には、
SHOW MASTER STATUS
⇢File と Position
SHOW SLAVE STATUS
Master_Log_File と Read_Master_Log_Pos
をそれぞれ比較し、
どの程度転送が遅れているかをチェックします。

“IO_THREAD”に起因した遅延の場合は、サーバの処理というよりはネットワーク帯域の問題である可能性が高いので、
この場合はネットワーク遅延の原因を探ります。

そこで今回は
“SQL_THREAD” による遅延を想定して解像度を上げていきたいとおもいます。
つまり、
マスターの更新頻度が激しすぎて、スレーブ側I/Oが追いついていない時の方法についてのお話です。

多くの場合、商用マスターの更新は簡単に止める選択を決断できない状況かとおもいます。

■Slaveのパフォーマンスを調整する
レプリケーションで伝播するクエリは全て直列化されるため、更新が激しい場合はどうしても遅れてしまうことがあります。
DiskI/Oへの負荷が高いとき “innodb-flush-log-at-trx-commit”の値を変更することで、ディスクへのフラッシュを減らすことができます。
このパラメータではログバッファからログファイルへの書き込み、およびディスクへのフラッシュをコントロールすることができます。

設定値 ログバッファのファイルへの書き込み ディスクへのフラッシュ 備考
0 毎秒 ログファイル上
1 コミット時 ログファイル上 デフォルト値
2 コミット時 毎秒

この設定値によるパフォーマンス向上度合いは、経験的には効果の大きい順に 0 > 2 > 1 の順と思っています。

SELECT @@innodb_flush_log_at_trx_commit;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

この設定値はパフォーマンス向上の代わりに、信頼性を犠牲にします。
プロセスが突然落ちた場合などにディスクにフラッシュされていないデータをロストする可能性がありますので、
状況に応じて(あるいは追いつかせるまでの間だけなど)の使用に抑えることが良いかと思います。

以上です。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です