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 MySQL DB クラスターの 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 に以下の値を設定する必要があります。

現在、これらの値はセッションレベルでのみ設定できます。次の例は、これらの値を設定し、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 機能の適用先がクエリで使用されているテーブルである場合、この列には次のいずれかの値が含まれます。

次の例では、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 に対してハッシュ結合を有効にすることができます。

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

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

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

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

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

mysql> SET optimizer_switch='block_nested_loop=on';

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

mysql> SET optimizer_switch='hash_join_cost_based=off';

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

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

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

以下の例に示しているのは、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 に変更することをお勧めします。詳細については、「Amazon Aurora の DB クラスターパラメータグループのパラメータの変更」を参照してください。

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

仮想インデックス ID オーバーフローエラー

Aurora MySQL では、仮想インデックス ID の値を 8 ビットに制限することで、MySQL の undo 形式によって発生する問題を防止します。インデックスが仮想インデックス ID 制限を超えると、クラスターが使用できなくなる可能性があります。インデックスが仮想インデックス ID 制限に近づいた場合、または仮想インデックス ID 制限を超えるインデックスを作成しようとした場合、エラーコード 63955 または警告コード 63955 が RDS によってスローされることがあります。仮想インデックス ID 制限エラーに対処するには、論理ダンプと復元を使用してデータベースを再作成することをお勧めします。

Amazon Aurora MySQL の論理ダンプと復元の詳細については、「Migrate very large databases to Amazon Aurora MySQL using MyDumper and MyLoader」を参照してください。Amazon Aurora でのエラーログへのアクセスの詳細については、「Amazon Aurora ログファイルのモニタリング」を参照してください。