ApsaraDB RDS:RDS for MySQL 読み取り専用インスタンスにおける同期遅延の原因と解決策

原因 1：読み取り専用インスタンスの仕様が低すぎる

発生する事象。レプリケーションは、I/O スレッド (プライマリからバイナリログをフェッチするため) と SQL スレッド (ローカルで適用するため) を使用します。両方のスレッドは、1 秒あたりの入出力操作数 (IOPS) を含む I/O リソースを競合します。読み取り専用インスタンスが必要な IOPS を維持できない場合、遅延が発生します。

確認方法。ApsaraDB RDS コンソールで、読み取り専用インスタンスのモニタリングとアラートに移動します。CPU 使用率、メモリ使用量、I/O 帯域幅、または IOPS が継続的に上限に達しているかどうかを確認します。また、[設定情報]セクションの[基本情報]ページでインスタンスタイプを確認することもできます。詳細については、「読み取り専用 ApsaraDB RDS for MySQL インスタンスのインスタンスタイプ (x86)」および「読み取り専用 ApsaraDB RDS for MySQL インスタンスのインスタンスタイプ (ARM)」をご参照ください。

解決方法。読み取り専用インスタンスを、プライマリインスタンスと同等以上の仕様にスペックアップします。詳細については、「ApsaraDB RDS for MySQL インスタンスの仕様変更」をご参照ください。

原因 2：プライマリインスタンスでの 1 秒あたりのトランザクション数 (TPS) が高い

発生する事象。単一の SQL スレッドが、読み取り専用インスタンス上のすべてのレプリケートされたトランザクションをシリアルに適用します。プライマリインスタンスが大量の同時書き込みを処理する場合、読み取り専用インスタンスは追いつくことができません。

確認方法。 プライマリまたは読み取り専用インスタンスの [ダッシュボード] ページに移動し、TPS メトリックを確認します。詳細については、「ApsaraDB RDS for MySQL インスタンスでダッシュボード機能を使用する」をご参照ください。

解決方法。プライマリインスタンス上の大規模トランザクションを最適化または分割して、トランザクションあたりの書き込み量を減らし、各トランザクションが SQL スレッドを保持する時間を短縮します。

原因 3：プライマリインスタンスでの大規模トランザクション

発生する事象。大規模なデータ量に対する UPDATE、DELETE、INSERT...SELECT、および REPLACE...SELECT などの操作は、大規模なバイナリログエントリを生成します。読み取り専用インスタンスは、プライマリがトランザクションを実行するのにかかった時間と同じ時間をトランザクションの適用に費やす必要があります。たとえば、プライマリで 80 秒かかる削除は、レプリケートにも 80 秒かかります。

さらに、複数のテーブルでの同時実行トランザクションはサポートされていますが、特定のテーブルでのトランザクションをレプリケートするスレッドは一度に 1 つだけであり、これが遅延をさらに悪化させる可能性があります。

確認方法。読み取り専用インスタンスで SHOW SLAVE STATUS \G を実行し、Exec_Master_Log_Pos が同じままで Seconds_Behind_Master が継続的に増加しているかどうかを監視します。次に、SHOW PROCESSLIST を実行して、特定の SQL スレッドを特定します。バイナリロギングが ROW フォーマットに設定されている場合、バイナリログファイルが max_binlog_size のしきい値を超えているかどうかを確認します。

SHOW BINARY LOGS;

File_size が max_binlog_size を超える場合、大規模トランザクションが原因である可能性が高いです。

また、SHOW SLAVE STATUS \G の出力でロック待機状態を調べて、メタデータロック (MDL) の競合を確認します。

解決方法。大規模トランザクションをより小さなバッチに分割します。たとえば、DELETE ステートメントに WHERE 句と行制限を追加して、各バッチが管理可能な数の行を処理できるようにし、読み取り専用インスタンスがペースを維持できるようにします。

原因 4：プライマリインスタンスでの長時間実行 DDL

発生する事象。レプリケーションは、データ定義言語 (DDL) ステートメントをシリアルに適用します。大規模なテーブルに対する CREATE INDEX、ALTER TABLE ADD COLUMN、または REPAIR TABLE などの DDL 操作は、完了するまですべての後続のレプリケーションをブロックする可能性があります。大量の一時テーブルを生成するスロークエリは、ディスク I/O を消費することで問題をさらに悪化させます。

これとは別に、読み取り専用インスタンスで実行中のクエリやオープンなトランザクションは、プライマリから到着する DDL ステートメントをブロックする MDL 競合を引き起こす可能性があります。

確認方法。

• バイナリログファイルが切り捨てられずに蓄積されているかどうかを確認します。これは DDL バックログを示しています。

• 読み取り専用インスタンスで次のステートメントを実行して、ロック競合を確認します。

  SHOW ENGINE INNODB STATUS \G

• 次のステートメントを実行して、現在使用中のテーブルを特定します。

  SHOW OPEN TABLES;

  in_use = 1 のテーブルはロックされており、レプリケーションをブロックしている可能性があります。

• OPTIMIZE、ALTER、REPAIR、CREATE などの DDL 関連操作についてスロークエリログを確認します。詳細については、「スロークエリログ分析」をご参照ください。

解決方法。

• DDL 操作をオフピーク時間にスケジュールします。

• ストレージの自動拡張機能を有効にするか、手動ストレージ拡張のしきい値を 90% に設定します。詳細については、「ApsaraDB RDS インスタンスをスケーリングするにはどうすればよいですか？」をご参照ください。

• SQL Explorer を有効にして、SQL パフォーマンスを定期的にモニタリングします。詳細については、「ApsaraDB RDS for MySQL インスタンスで SQL Explorer および監査機能を使用する」をご参照ください。

• ディスク使用率アラートを構成するか、インスタンスの仕様をスペックアップします。詳細については、「ApsaraDB RDS for MySQL インスタンスの仕様変更」をご参照ください。

プライマリからの DDL ステートメントが読み取り専用インスタンス上のメタデータロックによってブロックされている場合：

1. 読み取り専用インスタンスで SHOW PROCESSLIST; を実行して、メタデータロックを待機している SQL スレッドを見つけます。

2. ロックを保持しているセッションを終了して、レプリケーションを再開します。詳細については、「DMS を使用してメタデータロックを解除する」をご参照ください。

原因 5：一意なインデックスはあるがプライマリキーがないテーブル

発生する事象。テーブルにプライマリキーがなく、NULL 値を含む一意なインデックスのみがある場合、レプリケーション SQL スレッドは、WHERE 句がより効率的なルックアップを可能にする場合でも、オプティマイザーの推奨プランではなく、一意なインデックスの実行計画を使用します。これにより、レプリケーション中に全表スキャンが強制され、論理読み込みが大幅に増加し、SQL スレッドが遅くなります。

確認方法。プライマリインスタンスで次のクエリを実行して、プライマリキーがないテーブルを特定します。

SELECT tab.table_schema AS database_name, tab.table_name
FROM information_schema.tables tab
LEFT JOIN information_schema.table_constraints tco
  ON tab.table_schema = tco.table_schema
  AND tab.table_name = tco.table_name
  AND tco.constraint_type = 'PRIMARY KEY'
WHERE tco.constraint_type IS NULL
  AND tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND tab.table_type = 'BASE TABLE'
ORDER BY tab.table_schema, tab.table_name;

クエリによって返されたテーブルについては、sys.schema_index_statistics ビューを使用して、一意なインデックスに NULL 値が含まれているかどうかを確認します。

解決方法。プライマリキーがないテーブルには、明示的なプライマリキーを追加します。