MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない（あるいは、 /var/lib/mysql/#innodb_temp ディレクトリの正体）

TL;DR

• MySQL :: MySQL 8.0 Reference Manual :: 15.6.3.5 Temporary Tablespaces
• MySQL 8.0.13とそれ以降ではテンポラリーテーブルの実データ格納に「セッション単位のテンポラリーテーブルスペース」が使われるようになった
  • セッションが終われば領域が解放されるので、ibtmp1のように「mysqldを再起動しないとDisk Fullから復帰できない」ことがなくなった
  • この「セッション単位のテンポラリーテーブルスペース」の格納ディレクトリが datadir/#innodb_temp ディレクトリ

---

PoC

### ダミーデータを1000万行ほど
$ perl -MDigest::MD5 -E 'for (my $n= 1; $n <= 10000000 ; $n++) { printf("%d\t%s\n", $n, Digest::MD5::md5_hex($n)) }' > /tmp/md5

$ ll -h /tmp/md5
-rw-r--r-- 1 yoku0825 yoku0825 390M Jun 18 22:42 /tmp/md5

$ mysql -h172.17.0.2 --local-infile -uroot
mysql> SET GLOBAL local_infile= 1;
mysql> CREATE TABLE t1 (num serial, val varchar(32));
mysql> LOAD DATA LOCAL INFILE '/tmp/md5' INTO TABLE t1;

### InnoDBに落ちるようにTempTableを使わせない
mysql> SET SESSION internal_tmp_mem_storage_engine = Memory;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT SUBSTR(val, 1, 10) AS v, COUNT(*) AS c FROM t1 LEFT JOIN (SELECT DISTINCT num /2 AS num FROM t1) AS tt1 USING(num) WHERE tt1.num IS NULL GROUP BY v ORDER BY c DESC, v ASC; -- なんでも良いけどとにかくでかそうな暗黙のテンポラリーテーブルを発生させる
..
4999985 rows in set (3 min 0.64 sec)

8.0.12の挙動

$ ll -h /var/lib/mysql/ibtmp1
-rw-r----- 1 mysql mysql 1.1G Jun 19 05:31 /var/lib/mysql/ibtmp1

無事（？）太ってらっしゃる。

8.0.13の挙動

$ ll -h /var/lib/mysql/ibtmp1
-rw-r----- 1 mysql mysql 12M Jun 19 05:33 /var/lib/mysql/ibtmp1

全然太ってない。代わりに、

$ ll -h /var/lib/mysql/#innodb_temp/
total 1.1G
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_1.ibt
-rw-r----- 1 mysql mysql 1.1G Jun 19 05:39 temp_10.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_2.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_3.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_4.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_5.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_6.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_7.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_8.ibt
-rw-r----- 1 mysql mysql  80K Jun 19 05:33 temp_9.ibt

#innodb_temp ディレクトリの.ibtファイルが太っている。
od -c とかで見るとちゃんとデータが入っていて、コイツがテンポラリーテーブルの実体なのだということが見て取れた。

ただしコイツがibtmp1と違うのは、「セッションがクリアされれば.ibtファイルが消える」こと（クエリーが終了すれば、ではない。接続を切らないとダメ）

mysql> quit
Bye

$ ll -h /var/lib/mysql/#innodb_temp/
total 224K
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_1.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:41 temp_10.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_2.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_3.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_4.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_5.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_6.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_7.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_8.ibt
-rw-r----- 1 mysql mysql 80K Jun 19 05:33 temp_9.ibt

やった！ すごい！ これでInnoDBのテンポラリーテーブルがあふれても安心だ！（）

想定Q&A

Q. デカいテンポラリーテーブル作ってたセッションが切れた途端、数ギガバイトがファイルシステムから消されるって怖くない？
A. 暗黙のテンポラリーテーブルがMyISAMの時だってそうだったから、それで問題なければ問題ないんじゃなかろうか

Q. 10個しかないの？ 11スレッド以上で同時に使おうとするとどうなるの？
A. temp_11以降の.ibtファイルがどんどん増える。 ドキュメント 的には40万(400 thousand)まで行けるとか書いてある

Q. コネクションプールを使っているので、セッションがクリアされないんですが
A. ストレージがあふれたらAPサーバーを再起動だ！

Q. 逆に、今までは512MBを超えたらエラーにするとか頭打ちができたけど、今後はサイズによる頭打ちが出来なくなる？
A. 今のところできなさそう。瞬間的に天井まで行かなきゃいけないMyISAM時代に逆戻り…？

​