Amazon Aurora MySQL を使用する際のベストプラクティス - Amazon Aurora
接続先の DB インスタンスの確認Aurora MySQL のパフォーマンスとスケーリングのためのベストプラクティスAurora MySQL の高可用性のベストプラクティスAurora MySQL に関する推奨事項

Amazon Aurora MySQL を使用する際のベストプラクティス

このトピックには、Amazon Aurora MySQL DB クラスターの使用およびデータ移行のベストプラクティスとオプションに関する情報が含まれます。このトピックの情報は、Amazon Aurora DB クラスターの管理 にあるガイドラインや手順を一部要約し、改めて説明したものです。

目次

接続先の DB インスタンスの確認

Aurora MySQL DB クラスター内のどの DB インスタンスに接続しているかを確認するには、次の例に示すように、innodb_read_only グローバル可変をチェックします。

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

リーダー DB インスタンスに接続している場合、innodb_read_only 可変が ON に設定されます。プロビジョニングされたクラスターのプライマリインスタンスなどのライター DB インスタンスに接続している場合、この設定は OFF です。

この方法は、ワークロードを分散させるロジックや、書き込みオペレーションで適切な接続が使用されているかを確認するロジックを、アプリケーションコードに追加する場合に便利です。

Aurora MySQL のパフォーマンスとスケーリングのためのベストプラクティス

次のベストプラクティスを適用して、Aurora MySQL クラスターのパフォーマンスとスケーラビリティを向上させることができます。

開発やテストのための T インスタンスクラスの使用

db.t2、db.t3、または db.t4g DB インスタンスクラスを使用する Amazon Aurora MySQL インスタンスは、長い時間大量のワークロードをサポートしないアプリケーションに最適です。T インスタンスは、適度なベースラインパフォーマンスを実現したり、ワークロードの必要に応じて非常に高いパフォーマンスまでバーストする機能を実現できるように設計されています。常時または一貫して CPU をフルに使用するわけではないが、バーストが必要なことがあるワークロード向けに用意されています。T DB インスタンスクラスを、開発サーバーおよびテストサーバー、または他の本稼働以外のサーバーにのみ使用することをお勧めします。T インスタンスクラスの詳細については、「バーストパフォーマンスインスタンス」を参照してください。

Aurora クラスターが 40 TB より大きい場合は、T インスタンスクラスを使用しないでください。データベースに大量のデータがある場合、スキーマオブジェクトを管理するためのメモリオーバーヘッドが T インスタンスの容量を超えることがあります。

Amazon Aurora MySQL T インスタンスに対して MySQL パフォーマンススキーマを有効にしないでください。パフォーマンススキーマが有効な場合、インスタンスはメモリ不足になることがあります。

ヒント

データベースがときにはアイドル状態になるが、それ以外では相当なワークロードがある場合は、T インスタンスの代替として Aurora Serverless v2 を使用できます。Aurora Serverless v2 では、容量範囲を定義すると、Aurora は現在のワークロードに応じてデータベースを自動的にスケールアップまたはスケールダウンします。使用方法の詳細については、「Aurora Serverless v2 を使用する」を参照してください。Aurora Serverless v2 で使用できるデータベースエンジンのバージョンについては、「Aurora Serverless v2 の要件と制限」を参照してください。

T インスタンスを Aurora MySQL DB クラスターの DB インスタンスとして使用する場合は、次のことをお勧めします。

  • DB クラスター内のすべてのインスタンスに同じ DB インスタンスクラスを使用します。例えば、ライターインスタンスに db.t2.medium を使用する場合は、リーダーインスタンスにも db.t2.medium を使用することをお勧めします。

  • メモリ関連の構成設定 (innodb_buffer_pool_size など) を調整しないでください。Aurora は、T インスタンスのメモリバッファに、高度に調整された一連のデフォルト値を使用します。これらの特殊なデフォルトは、メモリに制約のあるインスタンスで Aurora を実行するために必要です。T インスタンスでメモリ関連の設定を変更すると、バッファサイズを増やすための変更であっても、メモリ不足状態が発生する可能性が高くなります。

  • CPU クレジットバランス (CPUCreditBalance) をモニタリングして、持続可能なレベルにあることを確認します。つまり、CPU のクレジットは使用されるのと同じレートで累積されています。

    インスタンス用の CPU クレジットが枯渇した場合、利用可能な CPU が急減するため、そのインスタンスに対する読み取りおよび書き込みレイテンシーが長くなります。この状況になると、インスタンス全体のパフォーマンスが大幅に低下します。

    CPU クレジット残高が持続可能なレベルにない場合、サポートされているいずれかの R DB インスタンスクラスを使用するように DB インスタンスを変更すること (コンピューティングのスケーリング) をお勧めします。

    モニタリングメトリクスの詳細については、「Amazon RDS コンソールでのメトリクスの表示」を参照してください。

  • ライターインスタンスとリーダーインスタンスの間のレプリカラグ (AuroraReplicaLag) をモニタリングします。

    ライターインスタンスよりも前にリーダーインスタンスで CPU クレジットが枯渇した場合、結果として生じるラグにより、リーダーインスタンスが頻繁に再起動することがあります。このような結果になるのは一般的に、アプリケーション側で負荷の高い読み取り操作がリーダーインスタンス間に分散されるときに、ライターインスタンス側で書き込み操作の負荷が最小限に抑えられている場合です。

    レプリカラグの増加が持続している場合、DB クラスターのリーダーインスタンスの CPU クレジット残高が枯渇していないことを確認します。

    CPU クレジット残高が持続可能なレベルにない場合は、サポートされているいずれかの R DB インスタンスクラスを使用するように DB インスタンスを変更すること (コンピューティングのスケーリング) をお勧めします。

  • バイナリログが有効な DB クラスターのトランザクションあたりの挿入の数を 100 万以下に維持します。

    DB クラスターの DB クラスターパラメータグループで binlog_format パラメータを OFF 以外の値に設定している場合、DB クラスターに 1,000,000 行以上の挿入を含むトランザクションがあると、DB クラスターでメモリが不足することがあります。解放可能なメモリ (FreeableMemory) メトリクスをモニタリングして、DB クラスターで使用可能なメモリが不足しているかどうかを判断できます。その後、書き込みオペレーション (VolumeWriteIOPS) メトリクスをモニタリングして、書き込みインスタンスで書き込みオペレーションの負荷が高いかどうかを確認します。メモリが不足し、書き込みオペレーションの負荷が高い場合は、トランザクションの挿入数を 100 万未満に制限するようにアプリケーションを更新することをお勧めします。または、サポートされているいずれかの R DB インスタンスクラスを使用するようにインスタンスを変更すること (コンピューティングのスケーリング) もできます。

Asynchronous Key Prefetch を使用した Aurora MySQL インデックス付き結合クエリの最適化

Aurora MySQL は Asynchronous Key Prefetch (AKP) を使用すると、インデックス間でテーブルを結合するクエリのパフォーマンスが向上することがあります。この機能は、JOIN クエリで Batched Key Access (BKA) 結合アルゴリズムと Multi-Range Read (MRR) 最適化機能が必要な場合、クエリの実行に必要な行を予測することで、パフォーマンスを向上させます。BKA と MRR の詳細については、MySQL ドキュメントの「Block Nested-Loop 結合と Batched Key Access 結合」および「Multi-Range Read の最適化」を参照してください。

AKP 機能を利用するには、クエリで BKA と MRR の両方を使用する必要があります。通常、このようなクエリは、クエリの JOIN 句でセカンダリインデックスを使用するが、プライマリインデックスからの一部の列を必要とする場合に発生します。例えば、JOIN 句が小さい外部テーブルと大きい内部テーブル間のインデックス値の等価結合を表し、大きいテーブルに対するインデックスの選択性が高い場合に、AKP を使用できます。AKP は、BKA および MRR と連携し、JOIN 句の評価時にセカンダリからプライマリへのインデックスのルックアップを行います。AKP は、JOIN 句の評価時にクエリの実行に必要な行を特定します。次に、バックグラウンドスレッドを使用して、クエリの実行前に、これらの行を含むページを非同期的にメモリ内にロードします。

AKP は、Aurora MySQL バージョン 2.10 以降、およびバージョン 3 でサポートされています。Aurora MySQL のバージョンの詳細については、「Amazon Aurora MySQL のデータベースエンジンの更新」を参照してください。

Asynchronous Key Prefetch の有効化

AKP 機能を有効にするには、MySQL サーバー可変 aurora_use_key_prefetch を on に設定します。デフォルトでは、この値は on に設定されます。ただし、BKA 結合アルゴリズムを有効にして、コストベースの MRR 機能を無効にするまでは、AKP を有効にすることはできません。そのためには、MySQL サーバー可変 optimizer_switch に以下の値を設定する必要があります。

  • batched_key_access を on に設定します。この値は BKA 結合アルゴリズムの使用を制御します。デフォルトでは、この値は off に設定されます。

  • mrr_cost_based を off に設定します。この値は、コストベースの MRR 機能の使用を制御します。デフォルトでは、この値は on に設定されます。

現在、これらの値はセッションレベルでのみ設定できます。次の例は、これらの値を設定し、SET ステートメントを実行して現在のセッションで AKP を有効にする方法を示しています。


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

同様に、SET ステートメントを使用して AKP と BKA 結合アルゴリズムを無効にし、現在のセッションでコストベースの MRR 機能を再度有効にすることができます。次に例を示します。


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

batched_key_access および mrr_cost_based オプティマイザスイッチの詳細については、MySQL ドキュメントの「切り替え可能な最適化の制御」を参照してください。

Asynchronous Key Prefetch のクエリの最適化

クエリで AKP 機能を利用できるかどうかを確認できます。そのためには、EXPLAIN ステートメントを使って、実行する前にクエリをプロファイリングします。EXPLAIN ステートメントは、指定されたクエリで使用する実行プランに関する情報を提供します。

EXPLAIN ステートメントの出力で、Extra 列は実行プランに含まれている追加情報を示します。AKP 機能の適用先がクエリで使用されているテーブルである場合、この列には次のいずれかの値が含まれます。

  • Using Key Prefetching

  • Using join buffer (Batched Key Access with Key Prefetching)

次の例では、EXPLAIN を使用することで、AKP を利用できるクエリの実行プランを表示しています。


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

EXPLAIN 出力形式の詳細については、MySQL ドキュメントの「拡張 EXPLAIN 出力形式」を参照してください。

ハッシュ結合を使用した大規模な Aurora MySQL 結合クエリの最適化

等価結合を使用して大量のデータを結合する必要がある場合は、ハッシュ結合によりクエリのパフォーマンスが向上することがあります。Aurora MySQL に対してハッシュ結合を有効にすることができます。

ハッシュ結合列には任意の複合表現を使用できます。ハッシュ結合列では、以下のようなデータ型間での比較が可能です。

  • int、bigint、numeric、bit などの厳密数値データ型のカテゴリ内で項目を比較できます。

  • float、double などの近似数値データ型のカテゴリ内で項目を比較できます。

  • 文字列型間で文字セットと照合が同じであれば、文字列型間で項目を比較できます。

  • 日付およびタイムスタンプデータ型間で、型が同じあれば、項目を比較できます。

注記

異なるカテゴリのデータ型を比較することはできません。

Aurora MySQL のハッシュ結合には、以下の制限が適用されます。

  • 左右外部結合は、Aurora MySQL バージョン 2 ではサポートされていませんが、バージョン 3 ではサポートされています。

  • サブクエリが初期にマテリアライズされない限り、サブクエリなどの準結合はサポートされていません。

  • 複数テーブルの更新や削除はサポートされていません。

    注記

    単一テーブルの更新や削除はサポートされていません。

  • BLOB および空間データ型の列をハッシュ結合の結合列にすることはできません。

ハッシュ結合を有効にする

ハッシュ結合を有効にするには:

  • Aurora MySQL バージョン 2 - DB パラメータまたは DB クラスターパラメータ aurora_disable_hash_join を 0 に設定します。aurora_disable_hash_join をオフにすると、optimizer_switch の値が hash_join=on に設定されます。

  • Aurora MySQL バージョン 3 — MySQL サーバーパラメータ optimizer_switch を block_nested_loop=on に設定します。

ハッシュ結合は、Aurora MySQL バージョン 3 ではデフォルトで有効であり、Aurora MySQL バージョン 2 ではデフォルトで無効になっています。次の例は、Aurora MySQL バージョン 3 でハッシュ結合を有効にする方法を示しています。ステートメント select @@optimizer_switch をまず発行して、他にどのような設定が SET パラメータ文字列にあるか確認することができます。optimizer_switch パラメータの設定の一つを更新しても、他の設定は消去されたり修正されたりしません。

mysql> SET optimizer_switch='block_nested_loop=on';
注記

Aurora MySQL バージョン 3 では、ハッシュ結合サービスはすべてのマイナーバージョンで利用可能で、デフォルトで有効になっています。

Aurora MySQL バージョン 2 の場合、ハッシュ結合サポートはすべてのマイナーバージョンで利用可能です。Aurora MySQL バージョン 2 の場合、ハッシュ結合機能は常に aurora_disable_hash_join の値によって制御されます。

この設定では、オプティマイザーはコスト、クエリの特徴、リソースの可用性に基づいてハッシュ結合を選択します。コスト見積りが正しくない場合に、オプティマイザーにハッシュ結合を選択させることができます。そのためには、MySQL サーバー可変 hash_join_cost_based を off に設定します。以下の例に示しているのは、オプティマイザーにハッシュ結合を選択させる方法です。


mysql> SET optimizer_switch='hash_join_cost_based=off';
注記

この設定は、コストベースのオプティマイザの決定を上書きします。この設定はテストや開発に役立ちますが、本番環境で使用することは推奨されません。

ハッシュ結合のクエリの最適化

クエリでハッシュ結合を利用できるかどうかを調べるには、初期に EXPLAIN ステートメントを使用してクエリのプロファイリングを行います。EXPLAIN ステートメントは、指定されたクエリで使用する実行プランに関する情報を提供します。

EXPLAIN ステートメントの出力で、Extra 列は実行プランに含まれている追加情報を示します。クエリで使用するテーブルにハッシュ結合が適用される場合、この列には以下のような値が含まれます。

  • Using where; Using join buffer (Hash Join Outer table table1_name)

  • Using where; Using join buffer (Hash Join Inner table table2_name)

以下の例に示しているのは、EXPLAIN を使用してハッシュ結合クエリの実行プランを表示する方法です。


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

出力では、Hash Join Inner table はハッシュテーブルの構築に使用されるテーブルであり、Hash Join Outer table はハッシュテーブルの検証に使用されるテーブルです。

拡張 EXPLAIN 出力形式の詳細については、MySQL 製品ドキュメントの「Extended EXPLAIN Output Format」(拡張 EXPLAIN 出力形式)を参照してください。

Aurora MySQL 2.08 以降では、SQL ヒントを使用して、クエリがハッシュ結合を使用するかどうか、および結合の構築側とプローブ側に使用するテーブルに影響を与えることができます。詳細については、「Aurora MySQL のヒント」を参照してください。

Amazon Aurora を使用した MySQL データベースの読み取りスケーリング

MySQL DB インスタンスで Amazon Aurora を使用することで、Amazon Aurora の読み取りスケーリング機能を活用して MySQL DB インスタンスの読み取りワークロードを拡張できます。Aurora を使用して MySQL DB インスタンスの読み取りを拡張するには、Aurora MySQL DB クラスターを作成し、MySQL DB インスタンスのリードレプリカに指定します。次に、Aurora MySQL クラスターに接続して読み取りクエリを処理します。出典データベースは、RDS for MySQL DB インスタンス、または Amazon RDS の外部で実行されている MySQL データベースです。詳細については、「Amazon Aurora を使用した MySQL データベースの読み取りスケーリング」を参照してください。

タイムスタンプ操作の最適化

システム変数 time_zone の値を SYSTEM に設定すると、タイムゾーン計算を必要とする各 MySQL 関数呼び出しは、システムライブラリ呼び出しを行います。このような TIMESTAMP 値を高い並行性で返したり変更したりする SQL ステートメントを実行すると、レイテンシー、ロック競合、および CPU 使用率が増加する可能性があります。詳細については、MySQL ドキュメントの「time_zone」を参照してください。

この現象を回避するには、time_zone DB クラスターパラメータの値を UTC に変更することをお勧めします。詳細については、「DB クラスターパラメータグループのパラメータの変更」を参照してください。

time_zone パラメータは動的 (データベースサーバーの再起動は不要) ですが、新しい値は新しい接続にのみ使用されます。すべての接続が新しい time_zone 値を使用するようにするには、DB クラスターパラメータを更新した後、アプリケーション接続をリサイクルすることをお勧めします。

Aurora MySQL の高可用性のベストプラクティス

次のベストプラクティスを適用して、Aurora MySQL クラスターの可用性を向上させることができます。

Amazon Aurora を使用した MySQL データベースのディザスタリカバリ

MySQL DB インスタンスで Amazon Aurora を使用することで、ディザスタリカバリ用のオフサイトバックアップを作成できます。MySQL DB インスタンスのディザスタリカバリに Aurora を使用するには、Amazon Aurora DB クラスターを作成し、MySQL DB インスタンスのリードレプリカに指定します。これは、RDS for MySQL DB インスタンス、または Amazon RDS の外部で実行されている MySQL データベースに適用されます。

重要

MySQL DB インスタンスと Amazon Aurora MySQL DB クラスターの間でレプリケーションを設定する場合、レプリケーションをモニタリングして、レプリケーションが正常に動作していることを確認し、必要な場合は修復する必要があります。

Amazon Aurora MySQL DB クラスターを作成して MySQL DB インスタンスのリードレプリカに指定するには、「Amazon Aurora を使用した MySQL データベースの読み取りスケーリング」の手順に従ってください。

災害対策モデルの詳細については、「Amazon Aurora MySQL クラスターに最適な災害対策オプションを選択する方法」を参照してください。

ダウンタイムを短縮して MySQL から Amazon Aurora MySQL に移行する

ライブアプリケーションをサポートする MySQL データベースから Amazon Aurora MySQL DB クラスターにデータをインポートするときは、移行中のサービス中断時間を短縮することが必要になる場合があります。これを行うには、『Amazon Relational Database Service ユーザーガイド』の「ダウンタイムを短縮して MySQL または MariaDB DB インスタンスにデータをインポートする」で説明されている手順を使用できます。この手順は、巨大なデータベースを使用する場合に特に役立ちます。この手順を使用すると、ネットワーク経由で AWS に渡されるデータの量を最小限に抑えることで、インポートのコストを削減できます。

このステップでは、データベースのデータのコピーを Amazon EC2 インスタンスに送信し、そのデータを新しい RDS for MySQL DB インスタンスにインポートするステップを示します。Amazon Aurora は MySQL と互換性があるため、ターゲット Amazon RDS MySQL DB インスタンスの代わりに Amazon Aurora DB クラスターを使用することができます。

Aurora MySQL DB インスタンスの低パフォーマンス、自動再起動、フェイルオーバーの回避

負荷の高いワークロードや、DB インスタンスに割り当てられたリソースを超えて急増するワークロードを実行している場合、アプリケーションと Aurora データベースを実行しているリソースを使い果たしてしまう可能性があります。CPU 使用率、メモリ使用量、使用されているデータベース接続数など、データベースインスタンスに関するメトリクスを取得するには、Amazon CloudWatch、パフォーマンスインサイト、および拡張モニタリングが提供するメトリクスを参照できます。DB インスタンスのモニタリングについては、「Amazon Aurora クラスターでのメトリクスのモニタリング」を参照してください。

ワークロードが使用しているリソースを使い果たした場合、DB インスタンスは遅くなったり、再起動したり、別の DB インスタンスにフェイルオーバーしたりする可能性があります。これを避けるには、リソースの使用状況を監視し、DB インスタンスで実行されているワークロードを調べ、必要に応じて最適化を行います。最適化を行ってもインスタンスのメトリクスが改善されず、リソースの枯渇も緩和されない場合は、上限に達する前に DB インスタンスをスケールアップすることを検討してください。利用可能な DB インスタンスクラスとその仕様の詳細については、「Aurora DB インスタンスクラス」を参照してください。

Aurora MySQL に関する推奨事項

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

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

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

Aurora MySQL はマルチスレッドレプリケーションを禁止していませんが、この機能は Aurora MySQL バージョン 3 でのみサポートされています。

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

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

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

ネイティブ 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 の非ネイティブカウンター」を参照してください。