Amazon RDS for MySQL と Aurora MySQL でバイナリログレプリカの遅延増加をトラブルシューティングする方法を教えてください。

簡単な説明

Amazon RDS for MySQL は非同期レプリケーションを使用するため、レプリカがプライマリ DB インスタンスよりも遅延する場合があります。レプリケーション遅延を監視するには、Amazon RDS for MySQL リードレプリカとバイナリログファイルの位置ベースレプリケーションを併用します。

Amazon RDS の ReplicaLag メトリクスを確認するには、Amazon CloudWatch を使用します。ReplicaLag メトリクスは、SHOW SLAVE STATUS コマンドの Seconds_Behind_Master フィールドの値をレポートします。

Aurora の AuroraBinLogReplicaLag メトリクスは、バイナリログを使用する Aurora DB クラスター間のレプリケーション遅延を測定します。

注: MySQL バージョン 8.0.22 以降では、SHOW REPLICA STATUS コマンドにより SHOW SLAVE STATUS コマンドが置き換えられています。詳細については、MySQL のウェブサイトで「SHOW SLAVE | REPLICA STATUS ステートメント」を参照してください。

Seconds_Behind_Master フィールドには、レプリカ DB インスタンスがソースインスタンスよりも遅れている、現在の遅延が秒単位で表示されます。さらに、レプリカ DB インスタンスで処理されたイベントに関する、プライマリ DB インスタンスで記録された本来の値も表示されます。

MySQL レプリケーションでは、バイナリログダンプ、レプリケーション I/O レシーバー、レプリケーション SQL アプライヤースレッドを使用します。スレッドの動作に関する詳細については、MySQL のウェブサイトで「レプリケーションスレッド」を参照してください。レプリケーションに遅延がある場合は、IO_THREAD レプリカと SQL_THREAD レプリカのどちらが遅延の原因であるかを確認します。そうすることで、ラグの根本原因を特定できます。

解決策

遅延しているレプリケーションスレッドを特定する

次の手順を実行します。

1. ソース DB インスタンスまたはプライマリ DB インスタンスがバイナリログを書き込む場所を特定するには、プライマリ DB インスタンスで SHOW MASTER STATUS コマンドを実行します。

   mysql> SHOW MASTER STATUS;

   出力例:

   +----------------------------+----------+--------------+------------------+-------------------+
   | File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +----------------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.066552           |      521 |              |                  |                   |
   +----------------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

   注: 上記の出力例では、ソースまたはプライマリ DB インスタンスがバイナリログを mysql-bin.066552 というファイルに書き込みます。
2. 出力ステータスを確認するには、レプリカ DB インスタンスで SHOW SLAVE STATUS または SHOW REPLICA STATUS コマンドを実行します。

   mysql> SHOW SLAVE STATUS\G;

出力例 1:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066548
Read_Master_Log_Pos: 10050480
Relay_Master_Log_File: mysql-bin.066548
Exec_Master_Log_Pos: 10050300
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

上記の出力例において、Master_Log_File: mysql-bin.066548 は、IO_THREAD レプリカはバイナリログファイル mysql-bin.066548 から読み取っていることを示しています。プライマリ DB インスタンスは、mysql-bin.066552 ファイルにバイナリログを書き込みます。IO_THREAD レプリカは 4 バイナリログ分遅れています。ただし、Relay_Master_Log_File は mysql-bin.066548 なので、SQL_THREAD レプリカは IO_THREAD と同じファイルから読み取っています。SQL_THREAD レプリカでは速度が維持されていますが、IO_THREAD レプリカには遅延があります。

出力例 2:

*************************** 1. row ***************************
Master_Log_File: mysql-bin.066552
Read_Master_Log_Pos: 430
Relay_Master_Log_File: mysql-bin.066530
Exec_Master_Log_Pos: 50360
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

上記の出力例は、プライマリインスタンスのログファイルは mysql-bin.066552 であることを示しています。IO_THREAD はプライマリ DB インスタンスの速度を維持しています。レプリカ出力では、SQL スレッドは Relay_Master_Log_File: mysql-bin.066530 を実行します。その結果、SQL_THREAD が 22 バイナリログ分遅れます。

IO_THREAD はバイナリログをプライマリインスタンスまたはソースインスタンスのみから読み込むため、通常、IO_THREAD が大規模なレプリケーション遅延を引き起こすことはありません。ただし、ネットワーク接続とネットワーク遅延により、サーバー間の読み取り速度が影響を受ける可能性があります。帯域幅の使用量が多いと、IO_THREAD レプリカのパフォーマンスが遅くなる可能性があります。

SQL_THREAD レプリカがレプリケーションの遅延の原因となっている場合は、次のトラブルシューティング手順を使用して問題を解決してください。

プライマリインスタンスで書き込みクエリが長時間実行している

プライマリ DB インスタンスで長時間実行される書き込みクエリが、レプリカ DB インスタンスで実行するのと同じ時間がかかる場合、seconds_behind_master が増える可能性があります。たとえば、プライマリインスタンスへの変更の実行に 1 時間かかる場合、遅延は 1 時間です。この変更はレプリカにも 1 時間を要するため、合計 2 時間の遅延が起こります。

遅延を最小限に抑えるには、プライマリインスタンスのスロークエリログを監視し、最適化が必要なクエリを特定します。また、実行時間の長いステートメントを小規模のステートメントまたはトランザクションに縮小します。

バイナリログに関連するパフォーマンス問題をトラブルシューティングするうえで、Performance Insights で待機イベントを分析すると詳細情報を取得できます。分離レベルの調整も行ってください。

DB インスタンスクラスのサイズまたはストレージ不足

レプリカ DB インスタンスのクラスまたはストレージ構成がプライマリインスタンスよりも低い場合、リソース不足が原因でレプリカのスロットリングが発生する可能性があります。レプリカは、プライマリインスタンスでの変更の数を維持できなくなります。

この問題を解決するには、レプリカの DB インスタンスタイプがプライマリ DB インスタンスと同じものか、上位のものであることを確認してください。レプリケーションを効果的に機能させるには、各リードレプリカにソース DB インスタンスと同じ数のコンピューティングリソースとストレージリソースが必要です。

プライマリ DB インスタンスで並列クエリを実行している

デフォルトでは、MySQL レプリケーションはシングルスレッドです。プライマリインスタンスでクエリを並行して実行すると、クエリは順番にレプリカにコミットされます。ソースインスタンスへの大量の書き込みが並行して発生する場合、リードレプリカへの書き込みは単一の SQL_THREAD を使用してシリアル化されます。結果的に、ソース DB インスタンスとリードレプリカの間に遅延が生じる可能性があります。

MySQL バージョン 8.0.27 以降では、replica_parallel_workers のデフォルト値は 4 です。この値は、レプリカはデフォルトでマルチスレッド化されることを示します。同様に、Aurora MySQL バージョン 3.04 以降では、レプリケーションはデフォルトでマルチスレッド化されます。replica_parallel_workers の値は 4 に設定されます。このパラメータは、カスタムパラメータグループで変更可能です。

マルチスレッドレプリケーションの詳細については、MySQL のウェブサイトで「バイナリログのオプションと変数」を参照してください。

マルチスレッドレプリケーションにより、レプリケーションに遅延が生じる可能性があります。たとえば、スキップしたトランザクションの特定が困難になります。マルチスレッドレプリケーションを使用する場合は、レプリケーションエラーをスキップしないことをおすすめします。プライマリ DB インスタンスとレプリカ DB インスタンスの間でデータの一貫性にギャップが生じる可能性があります。

バイナリログがレプリカ DB インスタンスのディスクに同期されている

レプリカで自動バックアップを有効にすると、バイナリログをレプリカ上のディスクに同期するためにオーバーヘッドが発生します。sync_binlog パラメータのデフォルト値は 1 に設定されています。値を **0 **に変更した場合、MySQL サーバーはバイナリログをディスクに同期できません。代わりに、オペレーティングシステムが随時バイナリログをディスクにフラッシュするようになります。

バイナリログの同期を無効にすると、コミットのたびにバイナリログをディスクに同期するために必要なパフォーマンスのオーバーヘッドを減らすことができます。ただし、停電や OS のクラッシュが発生した場合、コミットの一部がバイナリログと同期しない場合があります。非同期により、ポイントインタイムリストア (PITR) 機能が影響を受ける可能性があります。詳細については、MySQL のウェブサイトで sync_binlog を参照してください。

Binlog_format が ROW に設定されている

次のシナリオでは、レプリケーションが発生すると、SQL スレッドはテーブル全体をスキャンします。

• プライマリ DB インスタンスの binlog_format が ROW に設定されている。
• ソーステーブルに主キーがない。

このスキャンは、パラメータ slave_rows_search_algorithms のデフォルト値が TABLE_SCAN,INDEX_SCAN であることが原因で発生します。

この問題を一時的に解決するには、検索アルゴリズムを INDEX_SCAN,HASH_SCAN に変更すると、フルテーブルスキャンのオーバーヘッドを軽減できます。より永続的な解決策として、各テーブルに明示的な主キーを追加することがベストプラクティスです。

slave-rows-search-algorithms パラメータの詳細については、MySQL のウェブサイトで slave_rows_search_algorithms を参照してください。

レプリカ作成の遅延 (RDS MySQL)

Amazon RDS は、MySQL プライマリインスタンスのリードレプリカを作成するために DB スナップショットを取得します。次に、Amazon RDS はスナップショットを復元して新しい DB インスタンスを作成し、2 つのインスタンス間のレプリケーションを確立します。

Amazon RDS がレプリケーションを確立した後、Amazon RDS がプライマリ DB インスタンスのバックアップを作成するときに遅延が発生します。遅延を最小限に抑えるには、レプリカの作成を呼び出す前に手動バックアップを作成します。そうすることで、DB スナップショットは増分バックアップになります。

スナップショットからリードレプリカを復元する際、レプリカはソースからすべてのデータが転送されるまで待機しません。レプリカ DB インスタンスを使用して DB 操作を行うことは可能です。既存の Amazon Elastic Block Store (Amazon EBS) スナップショットにより、バックグラウンドで新しいボリュームが作成されます。

注: Amazon RDS for MySQL のレプリカ (Amazon EBS ベースのボリューム) では、遅延読み込みがレプリケーションのパフォーマンスに影響する場合があるため、最初はレプリカの遅延が大きくなる可能性があります。

フルテーブルスキャンを使用する操作を行うと、新しいリードレプリカのテーブルでの遅延読み込みの影響を減らすことができます。たとえば、特定のテーブルまたはデータベースのリードレプリカで mysqldump を実行すると、Amazon Simple Storage Service (Amazon S3) からバックアップしたすべてのテーブルデータが優先されます。

RDS MySQL では、オンデマンドの InnoDB キャッシュウォーミング機能を使用することもできます。InnoDB キャッシュウォーミング機能は、バッファプールの状態をディスク上の InnoDB データディレクトリ内の ib_buffer_pool というファイルに保存します。リードレプリカを作成する前に、Amazon RDS がプライマリ DB インスタンスバッファプールの現在の状態をダンプするため、パフォーマンスが向上し、負荷が軽減されます。次に、リードレプリカを作成した後にバッファプールをリロードします。

レプリカラグの影響

レプリカインスタンスまたはクラスターでレプリカ遅延が増加すると、ソースにバイナリログが蓄積する可能性があります。潜在的な問題を防ぐには、バイナリログファイルのサイズとディスク容量の使用状況を監視して対処します。RDS では、ソース DB インスタンスの BinLogDiskUsage メトリクスを監視します。

適切なバイナリログ保持期間を設定することで、RDS MySQL インスタンスと Aurora でのポイントタイムリカバリの機能とストレージ使用率のバランスを保ちます。さらに、バイナリログをレプリケーションとリカバリに活用するために、バイナリログファイルの命名規則とローテーション動作を把握しておきます。使用可能なログのリストを取得するには、SHOW BINARY LOGS コマンドを実行します。

関連情報

Amazon RDS での MySQL レプリケーションの操作

MySQL リードレプリカの操作

Aurora MySQL のバイナリログレプリケーションを最適化する