Aurora MySQL バージョン 3 での新しい一時テーブルの動作 - Amazon Aurora
内部 (黙示的) 一時テーブルのストレージエンジン内部メモリ内一時テーブルのサイズを制限するAurora レプリカの内部一時テーブルのフルネス問題の緩和リーダー DB インスタンスでユーザーが作成した (明示的な) 一時テーブル一時テーブル作成エラーと軽減

Aurora MySQL バージョン 3 での新しい一時テーブルの動作

Aurora MySQL バージョン 3 では、一時テーブルの処理方法は、以前の Aurora MySQL バージョンとは異なります。この新しい動作は MySQL 8.0 コミュニティエディションから継承されています。Aurora MySQL バージョン 3 で作成できる一時テーブルには、次の 2 つのタイプがあります。

  • 内部 (または黙示的) 一時テーブル — 集計の並べ替え、派生テーブル、共通テーブル式 (CTE) などの操作を処理するために Aurora MySQL エンジンによって作成されます。

  • ユーザー作成 (または明示的) 一時テーブル — Aurora MySQL エンジンがCREATE TEMPORARY TABLE表示されます。

Aurora Reader DB インスタンスの内部およびユーザー作成一時テーブルの両方について、その他の考慮事項があります。これらについては、以降のセクションで説明します。

内部 (黙示的) 一時テーブルのストレージエンジン

中間結果セットを生成するとき、Aurora MySQL は最初にメモリ内一時テーブルへの書き込みを試みます。データ型に互換性がないか、制限が設定されていることが原因で、これがうまくいかない可能性があります。その場合、一時テーブルはメモリに保持されるのではなく、ディスク上の一時テーブルに変換されます。これについての詳細は、MySQL ドキュメントの「MySQL での内部一時テーブルの使用」を参照してください。

Aurora MySQL バージョン 3 では、内部一時テーブルの動作方法は、以前の Aurora MySQL バージョンとは異なります。このような一時テーブルの InnoDB ストレージエンジンと MyISAM ストレージエンジンのいずれかを選択する代わりに、現在は TempTable ストレージエンジンと MEMORY ストレージエンジンのいずれかを選択します。

TempTable ストレージエンジンを使用すると、特定のデータの処理方法について追加の選択を行うことができます。影響を受けるデータは、DB インスタンスのすべての内部一時テーブルを保持するメモリプールをオーバーフローします。

これらの選択は、大量の一時データを生成するクエリのパフォーマンスに影響します。例えば、ラージ テーブルの GROUP BY のような集約を実行している場合などです。

ヒント

ワークロードに内部一時テーブルを生成するクエリが含まれている場合は、ベンチマークを実行し、パフォーマンス関連のメトリックをモニタリングして、この変更によるアプリケーションの動作を確認します。

場合によっては、一時データ量は TempTable メモリプールに収まるか、または少量だけメモリプールから溢れます。このような場合は、内部一時テーブルおよびメモリマップファイルの TempTable 設定を使用して、オーバーフローデータを保持することをお勧めします。この設定はデフォルトです。

TempTable ストレージエンジンがデフォルトです。TempTable は、テーブルあたりの最大メモリ制限ではなく、このエンジンを使うすべての一時テーブルの共通メモリプールを使用します。このメモリプールのサイズは、temptable_max_ram パラメータで特定されます。16 GB 以上のメモリを持つ DB インスタンスでは 1 GB、メモリが 16 GB 未満の DB インスタンスでは 16 MB がデフォルトになります。メモリプールのサイズは、セッションレベルのメモリ消費に影響します。

TempTable ストレージエンジンを使用するときに、一時データがメモリプールのサイズを超えることがあります。その場合、Aurora MySQL は二次的なメカニズムを使用してオーバーフローデータを保存します。

temptable_max_mmap パラメータを設定して、メモリマップテ一時ファイルまたはディスク上の InnoDB 内部一時テーブルのどちらにデータがオーバーフローするか、指定することができます。これらのオーバーフローメカニズムの異なるデータ形式とオーバーフロー基準は、クエリのパフォーマンスに影響を与える可能性があります。例えば、ディスクに書き込まれるデータ量や、ディスクストレージのスループットに対する要求に影響します。

Aurora MySQL は、データオーバーフローの送信先の選択、およびクエリがライターまたはリーダー DB インスタンスのどちらで実行されるかによって、オーバーフローデータを異なる方法で格納します。

  • ライターインスタンスでは、InnoDB 内部一時テーブルにオーバーフローするデータは Aurora クラスター ボリュームに格納されます。

  • ライターインスタンスでは、メモリマップされた一時ファイルにオーバーフローするデータは、Aurora MySQL バージョン 3 インスタンスのローカルストレージに存在します。

  • リーダーインスタンスでは、オーバーフローデータは常にローカルストレージ上のメモリマップ一時ファイルに存在します。これは、読み取り専用インスタンスでは Aurora クラスターボリュームにデータを保存できないためです。

内部一時テーブルに関連する設定パラメータは、クラスター内のライターインスタンスとリーダーインスタンスに対して異なる方法で適用されます。

  • リーダーインスタンスの場合、Aurora MySQL は常に TempTable ストレージエンジンを使用します。

  • temptable_max_mmap のデフォルトサイズは、DB インスタンスのメモリサイズに関係なく、ライターインスタンスとリーダーインスタンスの両方で 1 GB です。この値はライターインスタンスとリーダーインスタンスの両方で調整できます。

  • temptable_max_mmap を 0 に設定すると、ライターインスタンスでのメモリマップされた一時ファイルの使用がオフになります。

  • リーダーインスタンスでは、temptable_max_mmap を 0 に設定することはできません。

注記

temptable_use_mmap パラメーターの使用はお勧めしません。これは非推奨であり、将来の MySQL リリースでサポートが削除される予定です。

内部メモリ内一時テーブルのサイズを制限する

内部 (黙示的) 一時テーブルのストレージエンジン で説明したように、temptable_max_ram および temptable_max_mmap 設定を使用して、一時テーブルリソースをグローバルに制御できます。

また、tmp_table_size DB パラメータを使用して、個々の内部メモリ内一時テーブルのサイズを制限することもできます。この制限は、個々のクエリがグローバル一時テーブルリソースを大量に消費し、これらのリソースを必要とする同時クエリのパフォーマンスに影響を与えるのを防ぐことを目的としています。

tmp_table_size パラメータは、Aurora MySQL バージョン 3 の MEMORY ストレージエンジンによって作成される一時テーブルの最大サイズを定義します。

Aurora MySQL バージョン 3.04 以降では、tmp_table_size は、aurora_tmptable_enable_per_table_limit DB パラメータが ON に設定されているときに TempTable ストレージエンジンによって作成される一時テーブルの最大サイズも定義します。この動作はデフォルトでは無効になっています (OFF)、これは Aurora MySQL バージョン 3.03 以前のバージョンと同じ動作です。

  • aurora_tmptable_enable_per_table_limit が OFF のとき、tmp_table_size は、TempTable ストレージエンジンによって作成される内部メモリ内一時テーブルでは考慮されません。

    ただし、その場合でも、グローバル TempTable リソース制限は適用されます。Aurora MySQL は、グローバル TempTable リソース制限に達すると、次のように動作します。

    • ライター DB インスタンス — Aurora MySQL は、メモリ内一時テーブルを InnoDB オンディスク一時テーブルに自動的に変換します。

    • リーダー DB インスタンス — クエリはエラーで終了します。

      ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

  • aurora_tmptable_enable_per_table_limit が ON のとき、Aurora MySQL は、tmp_table_size 制限に達すると、次のように動作します。

    • ライター DB インスタンス — Aurora MySQL は、メモリ内一時テーブルを InnoDB オンディスク一時テーブルに自動的に変換します。

    • リーダー DB インスタンス — クエリはエラーで終了します。

      ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlxx_xxx' is full

      この場合、グローバル TempTable リソース制限とテーブルごとの制限の両方が適用されます。

注記

aurora_tmptable_enable_per_table_limit パラメータは、 internal_tmp_mem_storage_engine が MEMORY に設定されたときには、効果がありません。この場合、メモリ内一時テーブルの最大サイズは、tmp_table_size または max_heap_table_size のいずれか小さい方の値によって定義されます。

以下の例は、ライター DB インスタンスとリーダー DB インスタンスについて、aurora_tmptable_enable_per_table_limit パラメータの動作を示しています。

例 aurora_tmptable_enable_per_table_limit が OFF に設定されたライター DB インスタンス

メモリ内一時テーブルは InnoDB オンディスク一時テーブルに変換されません。

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  0 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (13.99 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)
例 aurora_tmptable_enable_per_table_limit が ON に設定されたライター DB インスタンス

メモリ内一時テーブルは InnoDB オンディスク一時テーブルに変換されます。

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  0 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
+---------+
| max(n)  |
+---------+
| 6000000 |
+---------+
1 row in set (4.10 sec)

mysql> show status like '%created_tmp_disk%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)
例 aurora_tmptable_enable_per_table_limit が OFF に設定されたリーダー DB インスタンス

tmp_table_size は適用されず、グローバル TempTable リソースの上限に達していないため、クエリはエラーなしで終了します。

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 60000000) SELECT max(n) FROM cte;
+----------+
| max(n)   |
+----------+
| 60000000 |
+----------+
1 row in set (14.05 sec)
例 aurora_tmptable_enable_per_table_limit が OFF に設定されたリーダー DB インスタンス

aurora_tmptable_enable_per_table_limit が OFF に設定されている場合、このクエリはグローバル TempTable リソース制限に達します。クエリはリーダーインスタンスのエラーで終了します。

mysql> set aurora_tmptable_enable_per_table_limit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@temptable_max_ram,@@temptable_max_mmap;
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@temptable_max_ram | @@temptable_max_mmap |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
|                  1 | 3.04.0           |                                        0 |          1073741824 |           1073741824 |
+--------------------+------------------+------------------------------------------+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.01 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 120000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
例 aurora_tmptable_enable_per_table_limit が ON に設定されたリーダー DB インスタンス

tmp_table_size 制限に達すると、クエリはエラーで終了します。

mysql> set aurora_tmptable_enable_per_table_limit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_read_only,@@aurora_version,@@aurora_tmptable_enable_per_table_limit,@@tmp_table_size;
+--------------------+------------------+------------------------------------------+------------------+
| @@innodb_read_only | @@aurora_version | @@aurora_tmptable_enable_per_table_limit | @@tmp_table_size |
+--------------------+------------------+------------------------------------------+------------------+
|                  1 | 3.04.0           |                                        1 |         16777216 |
+--------------------+------------------+------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql> set cte_max_recursion_depth=4294967295;
Query OK, 0 rows affected (0.00 sec)

mysql> WITH RECURSIVE cte (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 6000000) SELECT max(n) FROM cte;
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_8_2' is full

Aurora レプリカの内部一時テーブルのフルネス問題の緩和

一時テーブルのサイズ制限の問題を回避するには、temptable_max_ram と temptable_max_mmapパラメータを、ワークロードの要件に適合する値を組み合わせて指定します。

temptable_max_ram パラメータの値を設定するときは注意してください。この値の設定が高すぎると、データベースインスタンスの使用可能なメモリが減少し、メモリ不足状態が発生する可能性があります。DB インスタンスの平均空きメモリ量を監視します。インスタンスには十分な量の空きメモリが残るように temptable_max_ram の適切な値を決定します。詳細については、「Amazon Aurora の解放可能なメモリの問題」を参照してください。

また、ローカルストレージのサイズと一時テーブル領域の消費量をモニタリングすることも重要です。特定の DB インスタンスで使用できる一時ストレージをモニタリングするには、FreeLocalStorage Amazon CloudWatch メトリクスを使用できます。詳細については、「Amazon Aurora の Amazon CloudWatch メトリクス」を参照してください。

注記

この手順は、aurora_tmptable_enable_per_table_limit パラメータが ON に設定されているときには機能しません。詳細については、「内部メモリ内一時テーブルのサイズを制限する」を参照してください。

例 1

一時テーブルの累積サイズが 20 GiB になることがわかっています。インメモリ一時テーブルを 2 GiB に設定し、ディスク上で最大 20 GiB に拡張します。

temptable_max_ram を 2,147,483,648 に、temptable_max_mmap を 21,474,836,480 に設定します。これらの値はバイト単位です。

これらのパラメータ設定により、一時テーブルが累積合計 22 GiB に拡張できます。

例 2

現在のインスタンスサイズは 16xlarge 以上です。必要な一時テーブルの合計サイズが不明です。最大 4 GiB のメモリと、ディスク上の使用可能な最大ストレージサイズまで使用できるようにしたいと思っています。

temptable_max_ram を 4,294,967,296 に、temptable_max_mmap を 1,099,511,627,776 に設定します。これらの値はバイト単位です。

temptable_max_mmap を 1 TiB に設定します。これは、16 倍の大きな Aurora DB インスタンスの最大ローカルストレージである 1.2 TiB 未満です。

小さいインスタンスサイズで、使用可能なローカルストレージがいっぱいにならないように temptable_max_mmap の値を調整します。例えば、2xlarge インスタンスで使用できるローカルストレージは 160 GiB のみです。したがって、値を 160 GiB 未満に設定することをお勧めします。DB インスタンスサイズで使用可能なローカルストレージの詳細については、「Aurora MySQL 用の一時ストレージの制限」を参照してください。

リーダー DB インスタンスでユーザーが作成した (明示的な) 一時テーブル

CREATE TABLE ステートメントの TEMPORARY キーワードを使用して、明示的な一時テーブルを作成できます。Aurora DB クラスター内のライター DB インスタンスでは、明示的な一時テーブルがサポートされています。リーダー DB インスタンスで明示的な一時テーブルを使用することもできますが、テーブルでは InnoDB ストレージエンジンの使用を強制することはできません。

Aurora MySQL Reader DB インスタンスで明示的な一時テーブルを作成する際のエラーを回避するには、次の方法のいずれか、または両方で、すべての CREATE TEMPORARY TABLE ステートメントを実行してください。

  • ENGINE=InnoDB 句を指定しないでください。

  • SQL モードを NO_ENGINE_SUBSTITUTION に設定しないでください。

一時テーブル作成エラーと軽減

受け取るエラーは、プレーン CREATE TEMPORARY TABLE ステートメントまたはバリエーション CREATE TEMPORARY TABLE AS SELECT を使うかどうかによって異なります。次の例では、さまざまなタイプのエラーを示しています。

この一時テーブルの動作は、読み取り専用インスタンスにのみ適用されます。この初期の例では、セッションが接続されているインスタンスの種類を確認します。

mysql> select @@innodb_read_only;
+--------------------+
| @@innodb_read_only |
+--------------------+
|                  1 |
+--------------------+

プレーン CREATE TEMPORARY TABLE ステートメントの場合、NO_ENGINE_SUBSTITUTION SQL モードが有効になっているとステートメントは失敗します。メトリクス NO_ENGINE_SUBSTITUTION がオフ (デフォルト) の場合、適切なエンジン置換が行われ、一時テーブルの作成は成功します。

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql>  CREATE TEMPORARY TABLE tt2 (id int) ENGINE=InnoDB;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> CREATE TEMPORARY TABLE tt4 (id int) ENGINE=InnoDB;

mysql> SHOW CREATE TABLE tt4\G
*************************** 1. row ***************************
       Table: tt4
Create Table: CREATE TEMPORARY TABLE `tt4` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TEMPORARY TABLE AS SELECT ステートメントの場合、NO_ENGINE_SUBSTITUTION SQL モードが有効になっていると、ステートメントは失敗します。メトリクス NO_ENGINE_SUBSTITUTION がオフ (デフォルト) の場合、適切なエンジン置換が行われ、一時テーブルの作成は成功します。

mysql> set sql_mode = 'NO_ENGINE_SUBSTITUTION';

mysql> CREATE TEMPORARY TABLE tt1 ENGINE=InnoDB AS SELECT * FROM t1;
ERROR 3161 (HY000): Storage engine InnoDB is disabled (Table creation is disallowed).

mysql> SET sql_mode = '';

mysql> show create table tt3;
+-------+----------------------------------------------------------+
| Table | Create Table                                             |
+-------+----------------------------------------------------------+
| tt3   | CREATE TEMPORARY TABLE `tt3` (
  `id` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)

Aurora MySQL バージョン 3 での一時テーブルのストレージ側面とパフォーマンスへの影響の詳細については、ブログ記事「Amazon RDS for MySQL および Amazon Aurora MySQL の TempTable ストレージエンジンを使用する」を参照してください。