スマートスタイル TECH BLOG

データベース&クラウド技術情報

TempTable ストレージエンジンについて

はじめに

今更ですが、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 ストレージエンジンに比べて、さらにメモリーを効率よく使用することができるため、内部一時テーブルの処理のパフォーマンス向上が期待できるようになります。
なお、ユーザーが作成するテーブルとして使用することはできないようです。

そして、temptable_max_ram を指定することでプロセス全体で使用できる TempTable ストレージエンジンテーブルのサイズを決めることが可能であり、その内容は memory/temptable/* の値から確認することができます。

CURRENT_NUMBER_OF_BYTES_USED カラムから、現在割り当てられているサイズを確認することができます。ここで、TempTable ストレージエンジンを使用している場合、従来の tmp_table_size(max_heap_table_size) は内部一時テーブルで参照されないため、tmp_table_size 以上の値がメモリー上にテーブルが作成されていることがわかります。
また、temptable_use_mmap が有効な場合、メモリーマップ一時ファイルとして割り当てられた際には、 memory/temptable/physical_disk の値が増加します。このとき、実際のメモリー使用量はメモリー上の内部一時テーブル使用時と同様に増加してしまう点に注意してください。

InnoDB セッション一時テーブル

temptable_use_mmap を無効にした場合、ディスク上の内部一時テーブルとして従来の InnoDB (セッション)一時テーブルスペースが使用されますが、MySQL 8.0.13 から InnoDB の一時テーブルスペースは、セッション一時テーブルスペースグローバル一時テーブルスペースに分割されています。
従来の innodb_temp_data_file_path(デフォルト名:ibtmp1) はグローバルテーブルスペースとしてユーザーが作成した一時テーブルのロールバックセグメントのみを格納するようになっています。一方、セッション一時テーブルスペースは innodb_temp_tablespaces_dir 内にユーザーが作成した一時テーブルと、ディスク上の内部一時テーブルを格納するようになっています。これによって、ibtmp1 の肥大化によってディスクを圧迫してしまう懸念が解消されました。

セッション一時テーブルスペースは 5 ページ(innodb_page_size(16K) × 5) で 10 個作成され、必要に応じて拡張し、セッションが切断すると該当のテーブルスペースは切り捨てられます。

まとめ

ここまで、MySQL 8.0 で実装された TempTable ストレージエンジンの概要について確認してきました。
TempTable ストレージエンジンはより内部一時テーブルに最適化されたストレージエンジンです。MySQL 5.7 のときとは異なり、temptable_max_ram を指定することでプロセス全体で使用するメモリー量を制御できるのは大きなメリットかと思います。また、メモリー使用量をより厳密に制御したい場合は temptable_use_mmap を無効にすることで、従来の InnoDB 一時テーブルスペースと併用することができるようになります。その際、セッション一時テーブルの導入によって一時テーブルスペースの肥大化を懸念する必要がなくなったのも改良点ですね。
これまでメモリー設計や内部一時テーブルの設定値に頭を悩ませていた方は、是非 MySQL 8.0 を導入して、TempTable ストレージエンジンを試してみてはいかがでしょうか。

MySQL
Return Top