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

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 と InnoDB ストレージエンジンのいずれかを選択します。

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_use_mmap の 値 1 を使用します。temptable_max_mmap のデフォルトサイズは、DB インスタンスのメモリサイズに関係なく、ライターインスタンスとリーダーインスタンスの両方で 1 GB です。この値は、ライターインスタンスで調整する方法と同様に調整できます。ただし、リーダーインスタンスの temptable_max_mmap に 0 を指定することはできません。

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

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

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

また、ローカルストレージのサイズと一時テーブル領域の消費量をモニタリングすることも重要です。インスタンスのローカルストレージのモニタリングの詳細については、AWSナレッジセンターの記事「Aurora MySQL 互換のローカルストレージに保存されているものと、ローカルストレージの問題のトラブルシューティング方法を教えてください」を参照してください。

例 1

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

temptable_max_ram2,147,483,648 に、temptable_max_mmap21,474,836,480 に設定します。これらの値はバイト単位です。

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

例 2

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

temptable_max_ram4,294,967,296 に、temptable_max_mmap1,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 Reader DB インスタンスで明示的な一時テーブルを作成する際のエラーを回避するには、CREATE TEMPORARY TABLEリーダー DB インスタンスのステートメントは、次のいずれかの方法で実行されます。

  • ENGINE=InnoDB 句の指定なし

  • SQL モード NO_ENGINE_SUBSTITUTION=OFF の場合

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

受け取るエラーは、プレーン 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 モードの有効/無効にかかわらず、ステートメントは失敗します。MySQL コミュニティエディションは、CREATE TABLE AS SELECT または CREATE TEMPORARY TABLE AS SELECT ステートメントとのストレージエンジンの置換をサポートしていません。これらのステートメントについては、SQL コードから ENGINE=InnoDB 句を削除します。

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> CREATE TEMPORARY TABLE tt3 ENGINE=InnoDB AS SELECT * FROM t1; ERROR 1874 (HY000): InnoDB is in read only mode.

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