MySQL でレプリケーション遅延が発生したときに出来ること
はじめに
MySQL のレプリケーション機能を利用しているアプリケーションの開発をしていると、レプリケーションの遅延が発生することがある。
遅延を解消するために緊急で対応する際は、会社にいる DB の専門家（チーム）の力を借りる（ほぼ任せる）ことが多いので、サービス開発を主とするエンジニアとして対応できることをまとめておく。
漢(オトコ)のコンピュータ道: MySQLにおけるレプリケーション遅延の傾向と対策 の内容がとても参考になったので、このページをもと現時点での僕が出来ることをまとめる。
レプリケーションにかかる時間の内訳
遅延のことを考える前に、まずは「レプリケーションにかかる時間」の内訳を整理する。
レプリケーションにかかる時間は大まかに下記の2つの合算である。
- マスターから転送された更新情報がレプリカに届くまでの時間
- レプリカで更新情報を適用する時間
つまり、レプリケーション遅延は、これらのどちらか（もしくは、両方）が遅延することで発生する。
遅延が発生する原因
ネットワーク遅延
前述の「マスターから転送された更新情報がレプリカに届くまでの時間」が遅延する原因である。
レプリカに届くまでの時間は「ネットワーク上をパケットが流れる時間」なので、マスターから転送された更新情報の量がネットワーク帯域を超える ことで遅延が生じる。
重い更新クエリ（巨大なトランザクション）
前述の「レプリカで更新情報を適用する時間」が遅延する原因である。サービス開発する上でしばしば発生するのがこちら。
マスターのトランザクションがコミットされてから、その内容がレプリカに転送され、レプリカでも同じトランザクションが実行される。 そのため、マスターで1つのトランザクションに膨大な時間がかかってしまった場合、レプリカでも同じように膨大な時間がかかる ことになる。
よって、バッチ処理などで大量のデータ（大量の行）を更新する際は、巨大なトランザクションを実行しないよう、トランザクションを分割し、少しずつコミットするようにロジックを組む。
レプリカの過負荷
前述の「レプリカで更新情報を適用する時間」が遅延する原因である。
高頻度で更新処理が実行され、レプリカが処理出来る量を超えた更新情報がマスターから転送されると、レプリカでは更新が滞ってしまう。
また、更新だけでなく高負荷の参照（SELECT）により CPU 等のリソースが枯渇し、後続の更新処理が実行できない（実行し辛い）状態になることがある。
遅延が発生した際の対応
レプリケーション遅延が発生した際の、基本となる対応をまとめる。
遅延の状況を確認する
レプリカのサーバーにて、mysql クライアントを使用して SHOW SLAVE STATUS を実行することで、遅延の状況を確認することができる。
出力結果の内容をすべて理解することは難しいので、重要なポイントを抑える。
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 13000
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1307
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 1508
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: 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: 1307
Relay_Log_Space: 1858
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: 2
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: 1
Master_UUID: 3e11fa47-71ca-11e1-9e33-c80aa9429562
Master_Info_File: /var/mysqld.2/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 10
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
Auto_Position: 1
1 row in set (0.00 sec)「Seconds_Behind_Master」 フィールドが「どれだけ遅延しているか（単位: 秒）」を示す。
上記の例では 2 と表示されているので、約2秒の遅れが生じていることになる。この値を定期的に確認することで、遅延の状況（継続中なのか、解消しつつあるのか）を知ることができる。
https://dev.mysql.com/doc/refman/5.6/ja/show-slave-status.html
基本的に、このフィールドは、スレーブ SQL スレッドとスレーブ I/O スレッドの間の時間差 (秒単位) を測定します。 マスターとスレーブの間のネットワーク接続が高速である場合は、スレーブ I/O スレッドがマスターに非常に近いため、このフィールドは、スレーブ SQL スレッドがマスターに比べてどれだけ遅延しているかを示す良い近似値になります。 このネットワークが低速である場合、これは良い近似値ではありません。スレーブ SQL スレッドが、読み取りの遅いスレーブ I/O スレッドに非常に頻繁に追い付かれる可能性があるため、I/O スレッドがマスターに比べて遅延している場合でも、Seconds_Behind_Master は多くの場合 0 の値を示します。 つまり、このカラムは高速ネットワークの場合にのみ有効です。
遅延の状況を確認できたら、原因となっているクエリを確認する。
SHOW [FULL] PROCESSLIST で実行中のプロセスを確認することができるので、Time の値を見て実行に時間がかかっているクエリを調査する。
mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave
I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 3112
User: replikator
Host: artemis:2204
db: NULL
Command: Binlog Dump
Time: 2144
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 4. row ***************************
Id: 3113
User: replikator
Host: iconnect2:45781
db: NULL
Command: Binlog Dump
Time: 2086
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 5. row ***************************
Id: 3123
User: stefan
Host: localhost
db: apollon
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
5 rows in set (0.00 sec)遅延している場合の対策
一時的なデータ更新作業やバッチ処理が原因となっている一過性の遅延と判断できた場合、静観する（定期的に SHOW SLAVE STATUS を実行しつつ見守る） 。
解消に時間がかかりそうな場合、原因となっているクエリが新規で発行されない状態にした上で、クエリを kill する。
mysql> kill [Id]おわりに
アプリケーションのコードを実装する際は、レプリケーション遅延に繋がらないよう考慮する。
その上で、レプリケーション遅延等のトラブルが発生した際に迅速に解消のための行動が取れるよう、継続してデータベース層のことも学んでいきたい。