MySQLによるtmpフォルダ肥大化

linux
2023.07.03

また、アラートが出た！こんな毎日。。。

サーバの負荷が異常に高くなって、iowaitがヒドイ。

                CPU     %user     %nice   %system   %iowait    %steal     %idle
22時00分01秒     all      4.97      0.00      3.84     49.70      0.00     41.49
22時01分02秒     all      6.87      0.00      3.97     36.80      0.00     52.37
22時02分01秒     all      5.08      0.00      2.37     46.42      0.00     46.14
22時03分01秒     all      4.73      0.00      2.22     62.96      0.00     30.08

iowaitを高くしてる原因はコレ

$ df -h
ファイルシス                                サイズ  使用  残り 使用% マウント位置
/dev/sda4                                   100G  100G   0G  100% /
/dev/sda3                                   100G   30G  70G   30% /var

は？100%？肥大化しそうなログは/varにして分けてるのに？

数秒後、10%まで下がってることに気づく。90GBも急激に増えてる…

なるほど。一時ファイルか何かと推測し、/tmpの中身をチェック。

$ ls -l  /tmp/
ls: /tmp/#sql_2836_217.MYD にアクセスできません: そのようなファイルやディレクトリはありません
合計 712
?????????? ? ?     ?          ?             ? #sql_2836_217.MYD
-rw-rw---- 1 mysql mysql      0  7月  3 00:46 #sql_2836_218.MYI
-rw-rw---- 1 mysql mysql      0  7月  3 00:46 #sql_2836_219.MYI
-rw-rw---- 1 mysql mysql      0  7月  3 00:46 #sql_2836_222.MYI
-rw-rw---- 1 mysql mysql   1024  7月  3 00:46 #sql_2836_224.MYI
-rw-rw---- 1 mysql mysql   1024  7月  3 00:46 #sql_2836_227.MYI
-rw-rw---- 1 mysql mysql     84  7月  3 00:46 #sql_2836_60.MYD
-rw-rw---- 1 mysql mysql   1024  7月  3 00:46 #sql_2836_60.MYI

ぱっと見表示されてるサイズは小さいように見えるが、これが原因。

MySQLのクエリ実行時、メモリ上に収まらなかった場合、予め指定された（デフォルト値有り /tmp ）ディスク上にテンポラリファイルを作成する。

メモリに余裕があるなら、上限を増やしたらいいので、設定変更を実施。

※急にこのような事態になるのもおかしいので、MySQLのチューニングより、おかしなクエリになって無いかコンテンツ側を調査してもらうべきなんだろうけど、深夜に気づいた。。。

MySQLのチューニング実施

設定は下記二つの上限値を上げる（どちらかに抵触するとディスクを使用してしまう）。
今回は32MBから64MBへ変更。

  • tmp_table_size 
  • max_heap_table_size
mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 33554432 |
+---------------------+----------+
1 row in set (0.00 sec)

mysql> SET tmp_table_size=64*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SET max_heap_table_size=64*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 67108864 |
+---------------------+----------+
1 row in set (0.01 sec)

上記だけだと、MySQLの再起動で消えてしまうので、my.cnfの変更も忘れないように。

$ diff -u my.cnf.old my.cnf
-tmp_table_size          = 32M
-max_heap_table_size     = 32M
+tmp_table_size          = 64M
+max_heap_table_size     = 64M

平均化されて見にくいけど、尖ってるところがディスク使用率100%。対応完了後に、元の状態に戻ってiowaitも無くなった。

おまけ情報

色々文献を見てると、MySQLのバージョンが5.7.5と5.7.6の境で、テンポラリテーブルのストレージエンジンの仕様が変わってる模様。

古いバージョンでは、MyISAMで、新しいバージョンからは、innoDBになった模様。

エンジンが変わるとどうなるかというと、MyISAMの時は、ディスクを使い切ってクエリの処理ができなくなると、一旦テンポラリファイルが消滅（完全に消えてはいなかった）するようで、まだ救われていたが、innoDBになってからは、MySQLを再起動しないと解放されないと。ただ、8.0.13以降のバージョンからはその事象は解消されているとのこと。（未確認）

参考文献

https://yoku0825.blogspot.com/2015/04/mysql-576.html
https://open-groove.net/mysql/huge-myd-myi/
https://open-groove.net/mysql/temp-space/

コメント