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

原因 1：読み取り専用インスタンスの仕様不足

現象： レプリケーションは、I/O スレッド (プライマリからバイナリログを取得) と SQL スレッド (ローカルで適用) を使用します。両方のスレッドは、IOPS (1 秒あたりの I/O 処理) を含む I/O リソースをめぐって競合します。読み取り専用インスタンスが必要な IOPS を維持できない場合、遅延が発生します。

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

解決策： 読み取り専用インスタンスを、プライマリインスタンスと同等かそれ以上の仕様にアップグレードしてください。詳細については、「ApsaraDB RDS for MySQL インスタンスの仕様変更」をご参照ください。

原因 2：プライマリインスタンスにおける高い秒間トランザクション数 (TPS)

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

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

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

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

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

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

確認方法： 読み取り専用インスタンスで SHOW SLAVE STATUS \G を実行し、Seconds_Behind_Master が増加し続ける一方で Exec_Master_Log_Pos が変化しないことを確認します。次に 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. ロックを保持しているセッションを終了して、レプリケーションを再開します。詳細については、「Data Management Service (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 値が含まれているかどうかを確認します。

解決策： プライマリキーのないテーブルに明示的なプライマリキーを追加してください。