Aurora MySQL の MySQL 機能の推奨事項 - Amazon Aurora
Aurora MySQL でのマルチスレッドレプリケーションの使用ネイティブ MySQL 関数を使用した AWS Lambda 関数の呼び出しAmazon Aurora MySQL での XA トランザクションの回避DML ステートメント中に外部キーを有効にしておくログバッファをフラッシュする頻度の設定Aurora MySQL デッドロックの最小化とトラブルシューティング

Aurora MySQL の MySQL 機能の推奨事項

Aurora MySQL では、MySQL との互換性のために次の機能が利用可能です。ただし、Aurora 環境では、パフォーマンス、スケーラビリティ、安定性、または互換性の問題があります。したがって、これらの機能の使用については、特定のガイドラインに従うことをお勧めします。例えば、Aurora の本稼働デプロイには、特定の機能を使用しないことをお勧めします。

Aurora MySQL でのマルチスレッドレプリケーションの使用

マルチスレッドのバイナリログレプリケーションでは、SQL スレッドはリレーログからイベントを読み取り、SQL ワーカースレッドが適用されるようにキューに入れます。SQL ワーカースレッドは、コーディネータスレッドによって管理されます。バイナリログイベントは、可能な場合はパラレルに適用されます。

マルチスレッドレプリケーションは、Aurora MySQL バージョン 3 および Aurora MySQL バージョン 2.12.1 以降でサポートされています。

Aurora MySQL バージョン 3.04 以前では、Aurora MySQL DB クラスターがバイナリログレプリケーションのリードレプリカとして使用されている場合、Aurora はシングルスレッドレプリケーションをデフォルトで使用します。

Aurora MySQL バージョン 2 以前には、MySQL Community Edition から継承したマルチスレッドレプリケーションに関する不具合がいくつかあります。これらのバージョンでは、本番環境でのマルチスレッドレプリケーションの使用はお勧めしません。

マルチスレッドレプリケーションを使用する場合は、完全にテストした上で使用することをお勧めします。

Amazon Aurora におけるレプリケーションの使用の詳細については、「Amazon Aurora でのレプリケーション」を参照してください。Aurora MySQL でのマルチスレッドレプリケーションの詳細については、「マルチスレッドバイナリログレプリケーション」を参照してください。

ネイティブ MySQL 関数を使用した AWS Lambda 関数の呼び出し

ネイティブ MySQL 関数 lambda_sync および lambda_async を使用して、Lambda 関数を呼び出すことをお勧めします。

非推奨の mysql.lambda_async プロシージャを使用している場合は、mysql.lambda_async プロシージャの呼び出しをストアドプロシージャにラップすることをお勧めします。このストアドプロシージャは、トリガーやクライアントコードなどさまざまな出典から呼び出すことができます。この方法により、インピーダンス不整合の問題を回避し、データベースプログラマーが Lambda 関数を簡単に呼び出せるようにすることができます。

Amazon Aurora からの Lambda 関数の呼び出しについて詳細については、「Amazon Aurora MySQL DB クラスターからの Lambda 関数の呼び出し」を参照してください。

Amazon Aurora MySQL での XA トランザクションの回避

Aurora MySQL では eXtended Architecture (XA) トランザクションは使用しないことをお勧めします。これは、XA が PREPARED 状態の場合、復旧時間が長くなる可能性があるためです。Aurora MySQL で XA トランザクションを使用する必要がある場合は、以下のベストプラクティスに従ってください。

  • XA トランザクションを PREPARED 状態で開いたままにしない。

  • XA トランザクションを可能な限り小さくする。

MySQL で XA トランザクションを使用する方法の詳細については、MySQL ドキュメントの「XA トランザクション」を参照してください。

DML ステートメント中に外部キーを有効にしておく

foreign_key_checks 可変が 0 (オフ) に設定されている場合は、データ定義言語 (DDL) ステートメントを実行しないことを強くお勧めします。

外部キーの制約に一時的に違反する行を挿入または更新する必要がある場合は、以下のステップに従います。

  1. foreign_key_checks を 0 に設定します。

  2. データ操作言語 (DML) に変更を加えます。

  3. 完了した変更が外部キーの制約に違反していないことを確認します。

  4. foreign_key_checks を 1 (オン) に設定します。

さらに、外部キーの制約に関する以下のベストプラクティスに従います。

  • クライアントアプリケーションが foreign_key_checks 可変の一部として 0 可変を init_connect に設定しないことを確認します。

  • mysqldump などの論理的なバックアップからの復元が失敗するか、または不完全な場合は、同じセッションで他のオペレーションをスタートする前に、foreign_key_checks が 1 に設定されていることを確認します。論理的なバックアップのスタート時に foreign_key_checks が 0 に設定されています。

ログバッファをフラッシュする頻度の設定

MySQL Community Edition では、トランザクションを永続的にするには、InnoDB ログバッファを耐久性のあるストレージにフラッシュする必要があります。innodb_flush_log_at_trx_commit パラメータを使用して、ログバッファをディスクにフラッシュする頻度を設定します。

innodb_flush_log_at_trx_commit パラメータをデフォルト値の 1 に設定すると、トランザクションがコミットされるたびにログバッファがフラッシュされます。この設定は、データベースを ACID に準拠させるのに役立ちます。デフォルト設定の 1 を維持することをお勧めします。

innodb_flush_log_at_trx_commit をデフォルト以外の値に変更すると、データ操作言語 (DML) のレイテンシーを短縮できますが、ログレコードの耐久性は損なわれます。この耐久性の欠如により、データベースは ACID に準拠していません。サーバー再起動時にデータが損失するリスクを避けるため、データベースは ACID に準拠させることをお勧めします。このパラメータの詳細については、MySQL ドキュメントの「innodb_flush_log_at_trx_commit」を参照してください。

Aurora MySQL では、REDO ログ処理はストレージレイヤーにオフロードされるため、DB インスタンスではログファイルへのフラッシュは発生しません。書き込みが発行されると、REDO ログはライター DB インスタンスから Aurora クラスターボリュームに直接送信されます。ネットワークを介して行われる唯一の書き込みは REDO ログレコードです。データベース層からページが書き込まれることはありません。

デフォルトでは、トランザクションをコミットする各スレッドは、Aurora クラスターボリュームからの確認を待ちます。この確認は、このレコードとそれ以前のすべての REDO ログレコードが書き込まれ、クォーラムに達したことを示しています。ログレコードを永続化してクォーラムを達成すると、オートコミットでも明示的コミットでも、トランザクションが永続的になります。Aurora ストレージアーキテクチャの詳細については、「Amazon Aurora ストレージのわかりやすい解説」を参照してください。

Aurora MySQL は MySQL コミュニティエディションのようにログをデータファイルにフラッシュしません。ただし、innodb_flush_log_at_trx_commit パラメータを使用すると、REDO ログレコードを Aurora クラスターボリュームに書き込む際の耐久性の制約を緩和できます。

Aurora MySQL バージョン 2 の場合:

  • innodb_flush_log_at_trx_commit = 0 または 2 – データベースは REDO ログレコードが Aurora クラスターボリュームに書き込まれることを確認するまで待ちません。

  • innodb_flush_log_at_trx_commit = 1 – データベースは REDO ログレコードが Aurora クラスターボリュームに書き込まれることを確認するまで待ちます。

Aurora MySQL バージョン 3 の場合:

  • innodb_flush_log_at_trx_commit = 0 – データベースは REDO ログレコードが Aurora クラスターボリュームに書き込まれることを確認するまで待ちません。

  • innodb_flush_log_at_trx_commit = 1 または 2 – データベースは REDO ログレコードが Aurora クラスターボリュームに書き込まれることを確認するまで待ちます。

したがって、Aurora MySQL バージョン 2 で 0 または 2 に設定された値と同じデフォルト以外動作を Aurora MySQL バージョン 3 で取得するには、パラメータを 0 に設定します。

これらの設定はクライアントの DML レイテンシーを低減できますが、フェイルオーバーまたは再起動の際にデータが失われる可能性もあります。したがって、innodb_flush_log_at_trx_commit パラメータはデフォルト値の 1 を維持することをお勧めします。

MySQL Community Edition と Aurora MySQL の両方でデータ損失が発生する可能性がありますが、アーキテクチャが異なるため、動作はデータベースごとに異なります。このようなアーキテクチャの違いにより、さまざまな程度のデータ損失が発生する可能性があります。データベースが ACID に準拠していることを確認するには、必ず innodb_flush_log_at_trx_commit を 1 に設定してください。

注記

Aurora MySQL バージョン 3 では、innodb_flush_log_at_trx_commit を 1 以外の値に変更する前に、まず innodb_trx_commit_allow_data_loss の値を 1 に変更する必要があります。これにより、データ損失のリスクを認識できます。

Aurora MySQL デッドロックの最小化とトラブルシューティング

同じデータページのレコードを同時に変更する場合、一意のセカンダリインデックスや外部キーに対する制約違反が定期的に発生するワークロードを実行していると、デッドロックやロック待機タイムアウトが増加する可能性があります。これらのデッドロックとタイムアウトは、MySQL Community Edition のバグ修正によるものです。

この修正は、MySQL Community Edition バージョン 5.7.26 以降に含まれており、Aurora MySQL バージョン 2.10.3 以降にバックポートされました。この修正は、InnoDB テーブルのレコードに加えられた変更に対して、これらのタイプのデータ操作言語 (DML) オペレーションに追加のロックを実装することで、直列化可能性を強制的に適用するために必要です。この問題は、以前の MySQL Community Edition のバグ修正によって生じたデッドロック問題の調査の一環として発見されました。

この修正により、InnoDB ストレージエンジンでのタプル (行) 更新の部分的なロールバックの内部処理が変更されました。外部キーまたは一意のセカンダリインデックスに制約違反が発生するオペレーションを行うと、部分的にロールバックが発生します。これには、同時 INSERT...ON DUPLICATE KEY UPDATE、REPLACE INTO,、INSERT IGNORE ステートメント (upserts) が含まれますが、これらに限定されません。

ここでいう部分ロールバックとは、アプリケーションレベルのトランザクションのロールバックを指すのではなく、制約違反が発生した場合に、内部 InnoDB が変更をクラスター化されたインデックスにロールバックすることを指します。例えば、upsert オペレーション中に重複するキー値が見つかったとします。

通常の挿入オペレーションでは、InnoDB はインデックスごとにクラスター化されたインデックスエントリとセカンダリインデックスエントリをアトミックに作成します。InnoDB が upsert オペレーション中に一意のセカンダリインデックスで重複値を検出した場合、クラスター化されたインデックスに挿入されたエントリを元に戻し (部分ロールバック)、更新を既存の重複行に適用する必要があります。この内部部分ロールバックステップ中は、InnoDB はオペレーションの一部と見なされる各レコードをロックする必要があります。この修正により、部分ロールバック後に追加のロックを導入することにより、トランザクションの直列化可能性が保証されます。

InnoDB デッドロックの最小化

データベースインスタンスのデッドロックの発生頻度を減らすには、次の方法を使用できます。その他の例は、MySQL のドキュメントにあります。

  1. デッドロックの可能性を削減するために、関連する一連の変更を行った直後にトランザクションをコミットしてください。これを行うには、大きなトランザクション (コミット間の複数行の更新) を小さなトランザクションに分割します。行をバッチ挿入する場合は、特に前述の upsert オペレーションを使用するときは、バッチ挿入のサイズを小さくします。

    部分的なロールバックの頻度を削減するために、次の複数の方法を試行できます。

    1. バッチ挿入オペレーションを、一度に 1 行ずつ挿入するオペレーションに置き換えます。これにより、競合が発生する可能性のあるトランザクションによって、ロックが保持される時間を削減できます。

    2. REPLACE INTO を使用する代わりに、SQL ステートメントを次のような複数ステートメントのトランザクションとして書き換えます。

      BEGIN;
DELETE conflicting rows;
INSERT new rows;
COMMIT;

    3. INSERT...ON DUPLICATE KEY UPDATE を使用する代わりに、SQL ステートメントを次のような複数ステートメントのトランザクションとして書き換えます。

      BEGIN;
SELECT rows that conflict on secondary indexes;
UPDATE conflicting rows;
INSERT new rows;
COMMIT;

  2. アクティブまたはアイドルで長時間稼働するトランザクションは、ロックを保持する可能性があるので避けてください。これには、コミットされていないトランザクションで、長期間開かれている可能性のあるインタラクティブな MySQL クライアントセッションが含まれます。トランザクションサイズまたはバッチサイズを最適化する場合、同時実行性、重複数、テーブル構造などのさまざまな要因で、影響が異なる可能性があります。どのような変更でも、ワークロードに基づいて実装し、テストする必要があります。

  3. 状況によっては、2 つのトランザクションが 1 つまたは複数のテーブル内の同じデータセットに異なる順序でアクセスしようとすると、デッドロックが発生することがあります。これを防止するには、同じ順序でデータにアクセスするようにトランザクションを変更して、アクセスをシリアル化できます。例えば、完了するトランザクションのキューを作成します。このアプローチでは、複数のトランザクションが同時に発生する場合、デッドロックを回避するのに役立ちます。

  4. インデックスを慎重に選択してテーブルに追加することで、選択性が向上し、行にアクセスする必要性が減り、ロックが減少します。

  5. ギャップロックが発生した場合は、セッションまたはトランザクションのトランザクション分離レベルを READ COMMITTED に変更することで、ギャップロックを防止できます。InnoDB 分離レベルとその動作の詳細については、MySQL ドキュメントの「トランザクション分離レベル」を参照してください。

注記

デッドロックが発生する可能性を削減するための予防策を講じることはできますが、デッドロックはデータベースで想定される動作であり、発生する可能性はゼロにはなりません。アプリケーションには、デッドロックが発生した場合の対処に必要なロジックが必要です。例えば、アプリケーションに再試行とバックオフのロジックを実装します。問題の根本原因に対処するのが最善ですが、デッドロックが発生した場合、アプリケーションには待機後に再試行するオプションがあります。

InnoDB デッドロックのモニタリング

MySQL では、アプリケーションのトランザクションがテーブルレベルおよび行レベルのロックを取得しようすると循環待機になり、デッドロックが発生する可能性があります。InnoDB のデッドロックは、InnoDB ストレージエンジンによってすぐに状態を検出し、トランザクションの 1 つを自動的にロールバックするため、ときどき発生するデッドロックは必ずしも問題にはなりません。デッドロックが頻繁に発生する場合は、パフォーマンスの問題を軽減し、デッドロックを回避するために、アプリケーションを見直して修正することをお勧めします。デッドロック検出がオン (デフォルト) の場合、InnoDB はトランザクションのデッドロックを自動的に検出し、1 つまたは複数のトランザクションをロールバックしてデッドロックを解消します。InnoDB は小さなトランザクションを選択してロールバックしようとします。トランザクションのサイズは、挿入、更新、削除された行の数によって決まります。

  • SHOW ENGINE ステートメント — SHOW ENGINE INNODB STATUS \G ステートメントには、前回の再起動以降にデータベースで発生した最新のデッドロックの詳細が含まれます。

  • MySQL エラーログ — SHOW ENGINE ステートメントの出力が不十分なデッドロックが頻繁に発生する場合は、innodb_print_all_deadlocks DB クラスターパラメータを有効にできます。

    このパラメータを有効にすると、InnoDB ユーザートランザクションのすべてのデッドロックに関する情報が Aurora MySQL エラーログに記録されます。

  • Amazon CloudWatch メトリクス — CloudWatch メトリクス Deadlocks を使用して、デッドロックを積極的にモニタリングすることもお勧めします。詳細については、「Amazon Aurora のインスタンスレベルのメトリクス」を参照してください。

  • Amazon CloudWatch Logs — CloudWatch Logs を使用すると、メトリクスの表示、ログデータの分析、アラームのリアルタイム表示を行うことができます。詳細については、「Monitor errors in Amazon Aurora MySQL and Amazon RDS for MySQL using Amazon CloudWatch and send notifications using Amazon SNS」(Amazon CloudWatch を使用して Amazon Aurora MySQL と Amazon RDS for MySQL のエラーをモニタリングし、Amazon SNS を使用して通知を送信する) を参照してください。

    innodb_print_all_deadlocks を有効にした CloudWatch Logs を使用すると、デッドロックの回数が指定したしきい値を超えた場合に通知するようにアラームを設定できます。しきい値を定義するには、傾向を観察して、通常のワークロードに基づいた値を使用することをお勧めします。

  • Performance Insights — Performance Insights を使用すると、innodb_deadlocks および innodb_lock_wait_timeout メトリクスをモニタリングできます。これらのメトリクスの詳細については、「Aurora MySQL の非ネイティブカウンター」を参照してください。