はじめに
今更ですが、MySQL 8.0 から、UNION 句やソート処理などを実行する際に作成される内部一時テーブルを使用する際の挙動が変更されているのはご存知でしょうか。今回は、このとき使用される TempTable ストレージエンジンの概要と設定について調査をおこないました。
概要
MySQL 5.7 までの挙動について
MySQL 5.7 まで、内部一時テーブルはメモリー内に MEMORY ストレージエンジンとして保持および処理されます。そして、以下の条件に合致する場合、メモリーではなくディスク上にテーブルを作成して処理されます。
- 内部一時テーブルのサイズがインメモリーで保持できる最大サイズ(tmp_table_size または max_heap_table_size の小さい方の値)を超える場合
- テーブルに BLOB または TEXT データ型の列が存在している場合
- UNION または UNION ALL 使用時に、最大長が 512 を超える文字列が存在している場合
また、内部一時テーブルの処理に関連するパラメーターは以下の通りです。
| パラメーター名 | デフォルト値 | 説明 |
|---|---|---|
| internal_tmp_disk_storage_engine | INNODB | ディスク上の内部一時テーブルとして使用するストレージエンジンを定義します。MySQL 5.6 までは MyISAM ストレージエンジンのみが使用できましたが、MySQL 5.7 からは InnoDB も選択できるようになり、デフォルト値になっています。 |
| tmp_table_size | 16777216(16MB) | メモリー上の内部一時テーブルで許可される最大サイズを定義します。正確には、max_heap_table_size の値と比べて小さい方の値が上限値となります。 |
| max_heap_table_size | 16777216(16MB) | MEMORY ストレージエンジンテーブルで許可される最大サイズを定義します。メモリー上の内部一時テーブルの最大サイズを決める際に、tmp_table_size の値と組み合わせて使用されます。 |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend | InnoDB 一時テーブルスペースデータファイルの相対パス、名前、サイズ、および属性を定義します。ユーザーが作成した一時テーブルの他、ディスク上の内部一時テーブルにも使用されます。 |
MySQL 8.0 での挙動について
MySQL 8.0 では、内部一時テーブルはメモリー内にデフォルトで TempTable ストレージエンジンとして保持および処理されます。そして、以下の条件に合致する場合、メモリーではなくディスク上にテーブルを作成して処理されます。
- プロセス全体の内部一時テーブルのサイズがインメモリーで保持できる最大サイズ(temptable_max_ram)を超える場合
- テーブルに BLOB または TEXT データ型の列が存在している場合
- UNION または UNION ALL 使用時に、最大長が 512 を超える文字列が存在している場合
また、内部一時テーブルの処理に関連するパラメーターは以下の通りです。
| パラメーター名 | デフォルト値 | 説明 |
|---|---|---|
| internal_tmp_disk_storage_engine | INNODB | MySQL 8.0.16 からは削除されており、InnoDB で固定になっています。 |
| internal_tmp_mem_storage_engine | TempTable | メモリー上の内部一時テーブルに使用するストレージエンジンを定義します。デフォルトは TempTable ですが、MEMORY にすることで MySQL 5.7 と同様の挙動になります。 |
| temptable_max_ram | 1073741824(1GB) | TempTable ストレージエンジンで使用できるメモリーの最大量を定義します。 |
| temptable_use_mmap | ON | temptable_max_ram を超えた際にメモリーマップ一時ファイルとして割り当てる(mmap()を使用する)かどうかを定義します。この値を無効にすると、従来の InnoDB 一時テーブルスペースが使用されます。 |
| innodb_temp_tablespaces_dir | #innodb_temp | InnoDB セッション一時テーブルスペースを作成する場所を定義します。 |
それでは、MySQL 5.7 との主な違いについて確認をおこなっていきます。
TempTable ストレージエンジン
内部一時テーブル用に、TempTable ストレージエンジンが実装されています。このストレージエンジンを使用することで、MySQL 5.7 における MEMORY ストレージエンジンや(一時テーブル用の) InnoDB ストレージエンジンに比べて、さらにメモリーを効率よく使用することができるため、内部一時テーブルの処理のパフォーマンス向上が期待できるようになります。
なお、ユーザーが作成するテーブルとして使用することはできないようです。
|
1 2 |
mysql> CREATE TABLE test.t1(id int)ENGINE='TempTable'; ERROR 1286 (42000): Unknown storage engine 'TempTable' |
そして、temptable_max_ram を指定することでプロセス全体で使用できる TempTable ストレージエンジンテーブルのサイズを決めることが可能であり、その内容は memory/temptable/* の値から確認することができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
session1> CREATE TABLE <code>test</code>.<code>t1</code> (<code>id</code> int DEFAULT NULL, <code>num</code> int DEFAULT NULL, <code>name</code> varchar(100) DEFAULT NULL); Query OK, 0 rows affected (0.03 sec) session1> INSERT INTO t1 VALUES(CEIL(RAND()*10000000),CEIL(RAND()*10000000),MD5(RAND())); Query OK, 1 row affected (0.01 sec) (...任意の回数実行...) session1> (SELECT * FROM t1) UNION (SELECT * FROM t1); (...結果略...) session2> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/physical%'; +--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+ | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | +--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+ | memory/temptable/physical_disk | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | memory/temptable/physical_ram | 242982 | 242974 | 16305572806656 | 16305438588928 | 0 | 8 | 8 | 0 | 134217728 | 134217728 | +--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+ 2 rows in set (0.00 sec) |
CURRENT_NUMBER_OF_BYTES_USED カラムから、現在割り当てられているサイズを確認することができます。ここで、TempTable ストレージエンジンを使用している場合、従来の tmp_table_size(max_heap_table_size) は内部一時テーブルで参照されないため、tmp_table_size 以上の値がメモリー上にテーブルが作成されていることがわかります。
また、temptable_use_mmap が有効な場合、メモリーマップ一時ファイルとして割り当てられた際には、 memory/temptable/physical_disk の値が増加します。このとき、実際のメモリー使用量はメモリー上の内部一時テーブル使用時と同様に増加してしまう点に注意してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
session1> SET GLOBAL temptable_max_ram=2*1024*1024; Query OK, 0 rows affected (0.00 sec) session1> system cat /proc/<code>pgrep mysqld</code>/status | grep VmRSS VmRSS: 482800 kB session1> (SELECT * FROM t1) UNION (SELECT * FROM t1); (...結果略...) session2> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/physical%'; +--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+ | EVENT_NAME | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | +--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+ | memory/temptable/physical_disk | 662 | 657 | 44155535360 | 44090523648 | 0 | 5 | 6 | 0 | 65011712 | 132120576 | | memory/temptable/physical_ram | 262664 | 262662 | 17626343407616 | 17626341310464 | 0 | 2 | 8 | 0 | 2097152 | 134217728 | +--------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+ 2 rows in set (0.01 sec) session2 system cat /proc/<code>pgrep mysqld</code>/status | grep VmRSS VmRSS: 547296 kB |
InnoDB セッション一時テーブル
temptable_use_mmap を無効にした場合、ディスク上の内部一時テーブルとして従来の InnoDB (セッション)一時テーブルスペースが使用されますが、MySQL 8.0.13 から InnoDB の一時テーブルスペースは、セッション一時テーブルスペースとグローバル一時テーブルスペースに分割されています。
従来の innodb_temp_data_file_path(デフォルト名:ibtmp1) はグローバルテーブルスペースとしてユーザーが作成した一時テーブルのロールバックセグメントのみを格納するようになっています。一方、セッション一時テーブルスペースは innodb_temp_tablespaces_dir 内にユーザーが作成した一時テーブルと、ディスク上の内部一時テーブルを格納するようになっています。これによって、ibtmp1 の肥大化によってディスクを圧迫してしまう懸念が解消されました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# ls -lh /var/lib/mysql/ibtmp1 /var/lib/mysql/#innodb_temp/ -rw-r----- 1 mysql mysql 12M 2月 17 18:56 /var/lib/mysql/ibtmp1 /var/lib/mysql/#innodb_temp/: 合計 272K -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_1.ibt -rw-r----- 1 mysql mysql 96K 2月 17 19:09 temp_10.ibt -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_2.ibt -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_3.ibt -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_4.ibt -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_5.ibt -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_6.ibt -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_7.ibt -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_8.ibt -rw-r----- 1 mysql mysql 80K 2月 17 19:57 temp_9.ibt |
セッション一時テーブルスペースは 5 ページ(innodb_page_size(16K) × 5) で 10 個作成され、必要に応じて拡張し、セッションが切断すると該当のテーブルスペースは切り捨てられます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
session1> SET GLOBAL temptable_max_ram=2*1024*1024; Query OK, 0 rows affected (0.00 sec) session1> SET GLOBAL temptable_use_mmap=OFF; Query OK, 0 rows affected (0.00 sec) session1> (SELECT * FROM t1) UNION (SELECT * FROM t1); (...結果略...) session2> system ls -lh /var/lib/mysql/#innodb_temp/ 合計 41M -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_1.ibt -rw-r----- 1 mysql mysql 96K 2月 17 19:09 temp_10.ibt (...略...) -rw-r----- 1 mysql mysql 40M 2月 17 20:04 temp_9.ibt session1 > quit Bye session2> system ls -lh /var/lib/mysql/#innodb_temp/ 合計 272K -rw-r----- 1 mysql mysql 80K 2月 17 18:56 temp_1.ibt -rw-r----- 1 mysql mysql 96K 2月 17 19:09 temp_10.ibt (...略...) -rw-r----- 1 mysql mysql 80K 2月 17 20:05 temp_9.ibt |
まとめ
ここまで、MySQL 8.0 で実装された TempTable ストレージエンジンの概要について確認してきました。
TempTable ストレージエンジンはより内部一時テーブルに最適化されたストレージエンジンです。MySQL 5.7 のときとは異なり、temptable_max_ram を指定することでプロセス全体で使用するメモリー量を制御できるのは大きなメリットかと思います。また、メモリー使用量をより厳密に制御したい場合は temptable_use_mmap を無効にすることで、従来の InnoDB 一時テーブルスペースと併用することができるようになります。その際、セッション一時テーブルの導入によって一時テーブルスペースの肥大化を懸念する必要がなくなったのも改良点ですね。
これまでメモリー設計や内部一時テーブルの設定値に頭を悩ませていた方は、是非 MySQL 8.0 を導入して、TempTable ストレージエンジンを試してみてはいかがでしょうか。
