MySQL では sort_buffer_size 以上にソート領域が必要になった場合、もしくは一部の条件に当てはまるSQLについては、内部一時テーブルを作成し処理を行うという仕組みになっています。
この内部一時テーブルの仕組みに MySQL 8.0 から TempTable ストレージエンジンを利用する事ができるようになりました。
TempTable ストレージエンジンの挙動については、過去の弊社ブログ記事で説明しています。
TempTable ストレージエンジンはMySQL 8.0で導入されたということもあり、初期パッチバージョンに比べて改善が行われています。
今回の記事では、TempTableストレージエンジンの最近追加された機能についてご紹介します。
基本的なソートバッファと内部一時テーブル動作のおさらい
基本的な動作は以下のようになります。
- ソート処理(ORDER BYやGROUP BY)実行
- インデックスが使えればfilesortをスキップ
- インデックスが使えなければ
sort_buffer_size分のスレッドバッファが割り当てられ、クイックソートを行う - sort_buffer_size 以上にソート領域が必要になる場合、
インメモリ内部一時テーブルを作成しソートを実行 - インメモリ内部一時テーブル のサイズ上限以上にソート領域が必要になる場合
ディスク内部一時テーブルを作成しソートを実行
3については密かに改善が加えられており、以前のバージョンでは設定した sort_buffer_size が一度に割り当てられていたため、平均的なソート量を sort_buffer_size が上回る場合無駄なメモリ割り当てが行われていましたが、MySQL 8.0.12 以降では段階的に必要量のバッファが割り当てられるようになっています。
このため、sort_buffer_sizeに大きい値を設定したとしても無駄なメモリの利用を抑えられるようになっています。
MySQL 8.0.12 の時点で、filesort 操作用のメモリーを取得するために、オプティマイザは、MySQL 8.0.12 より前に行われた一定量の sort_buffer_size バイトを割り当てるのではなく、
sort_buffer_size システム変数で指定されたサイズまで必要に応じて増分的にメモリーバッファを割り当てます。
これにより、ユーザーは小さいソートに過剰なメモリー使用を考慮せずに、大きいソートを高速化するために sort_buffer_size を大きい値に設定できます。
(この利点は、マルチスレッド malloc が弱い Windows での複数の同時ソートでは発生しない場合があります。)
前述のブログでも説明していますが、インメモリ内部一時テーブル は internal_tmp_mem_storage_engineパラメータによりMEMORY ストレージエンジン、もしくは TempTable ストレージエンジンを設定できます。
なお、ディスク内部一時テーブル のストレージエンジンも以前は選択できましたが、MySQL 8.0.16 以降、InnoDB に固定されています。
SQLの仕組みよって、上記のソート処理以外でも内部一時テーブルを使用するパターンがあります。
※ 詳しくは、前述のドキュメント をご確認ください。
この内部一時テーブルを作成する条件のうち、BLOB もしくは TEXT 列が含まれる場合に無条件にディスク内部一時テーブルで処理するというものがありましたが、MySQL 8.0.13以降、TempTableストレージエンジンがBLOBをサポートしたことでインメモリで処理する事ができるようになりました。
TempTableストレージエンジンでのメモリ使用量の制限
TempTableストレージエンジンは、MySQL 8.0から導入されたものです。
インメモリ内部一時テーブルにMEMORYストレージエンジンを利用する場合、tmp_table_size(インメモリ内部一時テーブルの最大サイズ) と max_heap_table_size(MEMORYストレージエンジンのテーブルの最大サイズ) のうち小さい方が1つのインメモリ内部一時テーブルの最大サイズとなります。
作成テーブル数については特に制限が無いため、多数のインメモリ内部一時テーブルを作成する処理が発生した場合に、メモリ使用量が逼迫する危険性がありました。
一方で、TempTableストレージエンジンを利用する場合、temptable_max_ramによってインスタンス全体としてのサイズ上限が設けられ、メモリ使用量の上限がより予測しやすくなりました。
しかしながら、主に以下の点からMySQL 8.0で TempTableストレージエンジン を使用している場合でも内部一時テーブルによるメモリ使用量は予測が難しい状況がありました。
temptable_use_mmap によるメモリマップ一時ファイルの存在
使用済みのtemptable_max_ram以上にソートバッファが必要になった場合、前述の通りディスク内部一時テーブル が作成されます。
temptable_use_mmap が有効な場合、ディスク内部一時テーブル はメモリマップ一時ファイルとして作成され、メモリを使用して処理を行います。
temptable_use_mmap はデフォルトで有効ですので、大きなサイズの内部一時テーブルを利用する場合に非常に多くのメモリを消費する可能性があります。
スレッドローカルメモリブロックの確保
8.4.4 MySQL での内部一時テーブルの使用 に以下のNoteがあります。
注記
temptable_max_ram 設定では、TempTable ストレージエンジンを使用する各スレッドに割り当てられたスレッドローカルメモリーブロックは考慮されません。
スレッドローカルメモリーブロックのサイズは、スレッドの最初のメモリー割当てリクエストのサイズによって異なります。
リクエストが 1MB 未満の場合 (ほとんどの場合)、スレッドローカルメモリーブロックサイズは 1MB です。
リクエストが 1MB を超える場合、スレッドローカルメモリーブロックは初期メモリーリクエストとほぼ同じサイズになります。
スレッドローカルメモリーブロックは、スレッドが終了するまでスレッドローカル記憶域に保持されます。
スレッドローカルメモリブロックは、直接ソートバッファとして利用されるものではなく、スレッドごとに一時テーブルの情報を格納するための変数です。
通常スレッドごとに内部一時テーブルを使用する際に1MBが割り当てられますが、この領域はスレッドが終了するまで存続します。
Noteの記載の通りこの領域は temptable_max_ram のように上限値がありません。
スレッドプール等により常時接続が多い環境では、満遍なく一時テーブルを使用していった結果数ギガバイトを消費しているという可能性があります。
また、Bug#99136 の影響により、MySQL 8.0.20以前はスレッドが終了しスレッドキャッシュ に戻った場合も、この一時領域が保持されたままとなります。
https://bugs.mysql.com/bug.php?id=99136
MySQL 8.0 の新しいバージョンでの改善について
MySQL 8.0の新しいパッチバージョンでは以下の改善が行われています。
メモリマップ一時ファイルのサイズ上限の導入と、段階的な廃止
temptable_use_mmap が有効な場合のサイズ上限としてtemptable_max_mmap が 8.0.23 で導入されました。
デフォルト値は1073741824(=1GB)です。
これにより、MMAPされた内部一時テーブルが非常に多くのメモリを消費することを、デフォルトで防止できている状態となりました。
また、temptable_use_mmap は 8.0.26 から非推奨になり、段階的に廃止されることとなりました。
Note
The temptable_use_mmap variable was introduced in MySQL 8.0.16 and deprecated in MySQL 8.0.26; expect support for it to be removed in a future version of MySQL. Setting temptable_max_mmap=0 is equivalent to setting temptable_use_mmap=OFF.
ただし、最新のMySQL 8.0.28でもデフォルトでは有効ですのでご注意ください。
スレッドローカルメモリブロックから、キャッシュ領域への移行
MySQL 8.0.21 より、スレッドローカルメモリブロックに保存されていた情報は、キャッシュ領域へ移行されました。
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html
InnoDB: A segmentation fault occurred in the TempTable storage engine while using the thread pool plugin. TempTable thread-local variables were not compatible with the use of different threads for statements issued by a single client connection. Use of thread local variables also lead to excessive memory consumption due to the memory used by thread-local variables remaining allocated for the life of the thread. To address these issues, thread-local variables were replaced by a caching mechanism. (Bug #30050452, Bug #31116036, Bug #99136)
上限が無いことと、スレッドごとに1MBが確保される事に変わりはありませんが、スレッドキャッシュで該当のメモリを持ち続けることはなくなりました。
個別のインメモリ内部一時テーブルのサイズ上限の設定
以前は TempTable ストレージエンジンは、全体としてのサイズ上限(temptable_max_ram) はありましたが、個々の内部一時テーブルごとのサイズ上限はありませんでした。
これにより一部の大量のソートを行うスレッドの影響で他のスレッドがインメモリ内部一時テーブルを作成できないというケースがありました。
MySQL 8.0.28より、tmp_table_size パラメータの設定値が個別の一時テーブルのサイズ上限になり、より予測可能なメモリ使用量とすることができるようになっています。
まとめ
内部一時テーブルの仕組み自体は歴史が古いですが、MySQL 8.0からTempTable ストレージエンジンが導入されたことにより活発に改善が行われているようです。
MySQL 8.0が出たばかりの頃は、従来の仕組みと新しい仕組みによるパラメータについての理解が難しいと感じましたが、最近になって整理されてきたように思います。
意図せず重いSQLが実行された際にメモリを食い尽くされて驚かないように、事前に一時テーブルの設定をしっかりやりましょう！
