AurooraMySQL3アップグレード
dehio3
- クエリキャッシュの削除
- デフォルト文字セットは utf8mb4
- innodb_flush_log_at_trx_commitは変更不可（デフォルト：１）
dehio3TempTable
- MySQLのクエリオプティマイザは、複雑なクエリを処理する際に中間データセットを保存するために内部的に一時テーブルを作成する
- MySQL8.0から新しいデフォルトの内部一時テーブルストレージエンジン（TempTable）が導入された
- 通常は内部一時テーブルは最初にメモリで保持される
- 過剰なメモリ割り当てを回避するためにメモリ制限のパラメータがあり、その制限に達するとディスクストレージにオーバーフローする
- TempTableストレージエンジンには独自のディスクオーバーフローメカニズムがある
- メモリマップされた一時ファイルまたはInnoDBのディスク上の内部一時テーブルにオーバーフローされる
- メモリマップされたファイルは、ファイルとメモリ空間の間のマッピングを提供し、ファイルの読み書き操作を高速化する
- メモリー・マップド・テンポラリー・ファイルがローカル・ストレージに作成される
- InnoDBオンディスク内部テンポラリ・テーブルは、共有クラスタ・ボリュームに格納されます
MySQL Community 8.0.28における内部一時テーブル（internal temporary table）の生成と処理のフロー
-
内部一時テーブルの生成
クエリの処理のために内部一時テーブルが生成されます。 -
ストレージエンジンの選択
使用するストレージエンジンはinternal_tmp_mem_storage_engineオプションに基づきます。ここでは、TempTable（デフォルト）またはMEMORYのいずれかを選択します。 -
TempTableストレージエンジンの選択
internal_tmp_mem_storage_engine = TempTableが設定されている場合、TempTableエンジンが選択され、データがメモリに保存されます。以下の条件に応じて処理が続きます：- データが
temptable_max_ramとtmp_table_sizeの制限を超えるかどうかをチェックします。 - 制限を超える場合は、
temptable_max_mmapオプションに基づき、ディスクへのオーバーフローが行われるかどうかが決まります。 -
temptable_max_mmapが0でない場合、メモリマッピングされた一時ファイルが利用されます。 - メモリマッピングされたファイルのサイズが
temptable_max_mmapの制限を超えると、クエリが完了し、内部一時テーブルが削除されます。
- データが
-
MEMORYストレージエンジンの選択
internal_tmp_mem_storage_engine = MEMORYが設定されている場合、MEMORYエンジンが選択され、データがメモリに保存されます。以下の条件に応じて処理が続きます：- データが
tmp_table_sizeまたはmax_heap_table_sizeのうち小さい方の制限を超えるかどうかをチェックします。 - 制限を超えた場合、
temptable_max_mmapが0の場合はInnoDBのオンディスク一時テーブルにオーバーフローします。 - 制限を超えない場合は、クエリが完了し、内部一時テーブルが削除されます。
- データが
-
内部一時テーブルの削除とクエリの完了
最終的に、内部一時テーブルが削除され、クエリの処理が完了します。
このフローにより、MySQLはクエリのメモリ使用量を管理し、必要に応じてディスクへのオーバーフロー処理を行うことでパフォーマンスとリソースのバランスを取っています。
セッション・レベルでメモリー・マップド・テンポラリー・ファイルのサイズを確認するSQLクエリ
SELECT event_name, sum_number_of_bytes_alloc
FROM performance_schema.memory_summary_by_thread_by_event_name
WHERE thread_id=( SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID=90)
ORDER BY sum_number_of_bytes_alloc desc limit 5;
DBインスタンス・レベルでInnoDB内部テンポラリ・テーブルのサイズをチェックするSQLクエリ
SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
- 一時テーブルが必要かどうかを判断するにはEXPLAINのExtraカラムに
Using temporaryが表示されるかどうか - 一部のクエリー条件ではインメモリー一時テーブルを使用できない（ディスクを使う）
- テーブル内にBLOBまたはTEXTカラムが存在（ただし8.0.13のTempTableストレージエンジンはバイナリラージオブジェクト型をサポート）
- 内部一時テーブルはメモリー内に保持され、TempTable または MEMORY ストレージエンジンによって処理されるか、InnoDB ストレージエンジンによってディスクに格納される
temptable_max_ram / デフォルト:1GB
- TempTable ストレージ エンジンで使用できるメモリの最大量
- TempTable ストレージエンジンを使用する各スレッドに割り当てられたスレッドローカルメモリーブロックは考慮されません
- 同等の値を5.7で見る場合は
max_heap_table_size
temptable_max_mmap(MySQL 8.0.23 で導入)/デフォルト:1GB
- TempTable ストレージ エンジンがメモリ マップされた一時ファイルに割り当てることができるディスク ストレージの最大量
- これを 0 に設定すると、メモリ マップされた一時ファイルの使用が無効になり、オーバーフローは InnoDB のディスク上の内部一時テーブルに送られる
temptable_use_mmap (MySQL 8.0.16 で導入) / デフォルト:ON
- temptable_max_ram の制限を超えた場合に、TempTable ストレージエンジンがメモリーマップされたファイルまたは InnoDB ディスク上の内部一時テーブルを使用するかどうかを制御
AuroraにおけるMySQL Community との内部一時テーブル（internal temporary table）の生成と処理のフローの違い
- Aurora DB クラスターの独自のストレージアーキテクチャにより、Aurora レプリカ (リーダー) DB インスタンス上の内部一時テーブルの動作が異なる
- Aurora DB クラスターには、すべての DB インスタンスで共有される
クラスター ボリュームと、各 DB インスタンスに接続されたローカル ストレージの 2 種類のストレージがある - Aurora プライマリ (ライター) DB インスタンスでのオバーフローパス
- メモリマップされた一時ファイルがローカルストレージに作成される
- InnoDBのディスク上の内部一時テーブルは共有クラスタボリュームに保存される
- Aurora レプリカ DB インスタンスには共有クラスターボリュームへの書き込みアクセス権がないため、InnoDB のディスク上の内部一時テーブルを使用できません。
-
内部一時テーブルの生成
クエリの処理に必要な内部一時テーブルが作成されます。 -
TempTableストレージエンジンの使用
データがメモリに保存されるTempTableストレージエンジンが使用されます。 -
aurora_tmptable_enable_per_table_limit設定の確認
このパラメータが1の場合、tmp_table_sizeの制限をチェックします。0の場合は、temptable_max_ramの制限をチェックします。 -
制限超過のチェック
-
aurora_tmptable_enable_per_table_limit = 0のとき、データがtemptable_max_ramの制限を超えた場合、ローカルストレージにメモリマッピングされた一時ファイルを使用します。さらに、temptable_max_mmapの制限もチェックし、制限を超えない限りメモリマッピングファイルにデータが保存されます。temptable_max_mmapを超えた場合、エラー1114（テーブルが満杯）が発生します。 -
aurora_tmptable_enable_per_table_limit = 1のとき、データがtmp_table_sizeを超えると、エラー1114（テーブルが満杯）が発生します。
-
-
内部一時テーブルの削除とクエリの完了
クエリの処理が終了すると、内部一時テーブルが削除されます。
TempTable ストレージエンジンによる内部一時テーブルのオーバーフローメカニズム
- 一時ファイルは、tmpdir 変数で定義されたディレクトリに作成
- 一時ファイルは、作成して開いた直後に削除されるため、tmpdir ディレクトリには表示されません
- RAM と一時ファイル間、RAM 内、または一時ファイル間でデータが移動されることはありません
- temptable_max_ram で定義された制限内に領域が使用可能になると、新しいデータが RAM に格納されます。 それ以外の場合、新しいデータは一時ファイルに格納されます。
- テーブルの一部のデータが一時ファイルに書き込まれた後に RAM で領域が使用可能になった場合、残りのテーブルデータを RAM に格納できます
TempTableにおけるオーバーフローとは、内部一時テーブルでデータがメモリの容量制限に達した際に、クエリを継続して処理するためにデータをメモリ以外の領域に移す動きのことを指します。具体的には以下のような流れでオーバーフローが発生します。
-
メモリ内での一時テーブルの作成
クエリ処理中、TempTableストレージエンジンが内部一時テーブルをメモリ内に作成します。通常は、クエリが処理するデータが小さい場合、メモリ内で完結します。 -
メモリ制限の超過
クエリによって処理するデータ量が増え、メモリの設定された上限（例：temptable_max_ramやtmp_table_size）を超えると、TempTableはメモリ内だけで処理を完了できなくなります。 -
メモリマッピングされた一時ファイルへのオーバーフロー
メモリ制限を超えた場合、デフォルトではTempTableはメモリマッピングされた一時ファイル（ファイルシステム上に作成される仮想的なメモリ領域）にデータをオーバーフローさせます。この一時ファイルはメモリとディスクの中間的な役割を果たし、大量のデータを扱う際にメモリの負荷を軽減します。この際、temptable_max_mmapで指定された上限を超えない範囲で使用されます。 -
InnoDBオンディスク内部一時テーブルへのオーバーフロー
メモリマッピングされた一時ファイルの容量（temptable_max_mmap）にも達した場合、次にInnoDBのオンディスク内部一時テーブルにオーバーフローします。ここでは、データが直接ディスク上のテーブルとして格納され、システムがディスクI/Oを通じてデータを処理します。 -
オーバーフローの管理と調整
TempTableのオーバーフローはシステム設定によって制御できます。例えば、temptable_max_mmapを調整することで、メモリマッピングされた一時ファイルの上限を増やし、InnoDBへのオーバーフローを避けることができます。また、必要に応じてtemptable_max_mmapを0に設定し、InnoDBオンディスク一時テーブルのみにオーバーフローさせることも可能です。 -
クエリの完了後のリソース解放
クエリが完了すると、TempTableに作成された一時テーブルはメモリ、メモリマッピングファイル、InnoDBいずれに保存されていた場合でも解放され、システムリソースが戻されます。
このオーバーフローの仕組みにより、MySQLやAuroraはメモリ内で処理しきれないデータを効率的にディスクに移し、メモリ負荷を抑えながらクエリを継続して処理することができます。ただし、ディスクI/Oが発生するため、パフォーマンスには影響が出る可能性があります。そのため、パフォーマンスを重視する場合は、システムの設定を調整し、なるべく一段階でのオーバーフローが発生するように最適化することが推奨されます。
内部一時テーブルの作成の監視
- 内部一時テーブルがメモリーまたはディスクに作成されると、サーバーは Created_tmp_tables 値を増分します
- 内部一時テーブルがディスク上に作成されると、サーバーは Created_tmp_disk_tables 値を増分します
- memory/temptable/physical_ram および memory/temptable/physical_disk パフォーマンススキーマインストゥルメントを使用すると、DBインスタンス・レベルでTempTableストレージ・エンジンのメモリとストレージ使用量をモニター
- memory/temptable/physical_ram
- TempTableストレージエンジンによって、メモリ内に使用されている一時テーブルのサイズ
- memory/temptable/physical_disk
- TempTableストレージエンジンによってディスク上で使用されている一時テーブルのサイズ
- 0以外の値の場合は
temptable_max_ramの閾値に到達してしまった状態
- memory/temptable/physical_ram
SELECT
EVENT_NAME,
SUM_NUMBER_OF_BYTES_ALLOC / 1073741824 AS SUM_NUMBER_OF_BYTES_ALLOC_GB,
SUM_NUMBER_OF_BYTES_FREE / 1073741824 AS SUM_NUMBER_OF_BYTES_FREE_GB,
CURRENT_NUMBER_OF_BYTES_USED / 1073741824 AS CURRENT_NUMBER_OF_BYTES_USED_GB
FROM
performance_schema.memory_summary_global_by_event_name
WHERE
event_name LIKE 'memory/temptable%';
+--------------------------------+---------------------------+--------------------------+------------------------------+
| EVENT_NAME | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+--------------------------------+---------------------------+--------------------------+------------------------------+
| memory/temptable/physical_disk | 0 | 0 | 0 |
| memory/temptable/physical_ram | 39617379968 | 39607942496 | 9437472 |
+--------------------------------+---------------------------+--------------------------+------------------------------+
2 rows in set (0.00 sec)
- SUM_NUMBER_OF_BYTES_ALLOC（bytes）
- これまでに割り当てられたメモリの総量
- SUM_NUMBER_OF_BYTES_FREE（bytes）
- これまでに解放されたメモリの総量
- CURRENT_NUMBER_OF_BYTES_USED（bytes）
- 現在割り当てられているメモリブロックの合計サイズであり、解放されていないメモリの量
- SUM_NUMBER_OF_BYTES_ALLOC−SUM_NUMBER_OF_BYTES_FREE と同等
- この内部一時テーブルを作成する条件のうち、BLOB もしくは TEXT 列が含まれる場合に無条件にディスク内部一時テーブルで処理するというものがありましたが、MySQL 8.0.13以降、TempTableストレージエンジンがBLOBをサポートしたことでインメモリで処理する事ができるようになりました。
- tmp_table_size and max_heap_table_size
- ｢内部一時テーブルが最初にインメモリーテーブルとして作成されたが、これが大きくなりすぎた場合、MySQL はこれを自動的にディスク上のテーブルに変換します。インメモリー一時テーブルの最大サイズは、tmp_table_size と max_heap_table_size のどちらかの最小値です｣
- デフォルト値はどちらも 16,777,216 バイト
mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Created_tmp_disk_tables | 582 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 188654 |
+-------------------------+--------+
3 rows in set (0.01 sec)
Created_tmp_disk_tables
- ステートメントの実行中にサーバーによって作成される内部ディスク一時テーブルの数
Created_tmp_files
- mysqld が作成した 一時ファイルの数
Created_tmp_tables
- ステートメントの実行中にサーバーによって作成される内部一時テーブルの数
dehio3metadata_locks
- メタデータロックを使用して、データベースオブジェクトへの同時アクセスを管理し、データの一貫性を確保
- メタデータロックには多少のオーバーヘッドが伴い、クエリーボリュームが増加するにつれて増加
- 複数のクエリーが同じオブジェクトにアクセスを試みることが多くなるほど、メタデータの競合が増加