Created_tmp_disk_tables の意味
MySQL のチューニングする際に指標とするステータスの一つに Created_tmp_disk_tables というものがあります。
詳しくはもっと詳細な記事を見てくれればいいですが、クエリ実行時にメモリ上に収まりきらなかった一時テーブルを、ディスク上に作成した回数を意味します。
そもそも一時テーブルが肥大化するのはテーブルやクエリの設計に問題がある場合もあるので、
まずはクエリ自体を改善して巨大な一時テーブルが作られるのを抑制すべきですが、
大きなデータの集計を定期的に行うなど、要件上割けられない場合もあるのではないでしょうか。
tmp_tables_size と max_heap_table_size
この時、 tmp_table_size と max_heap_table_size の値を変更する事で、ディスク上に作成される事を抑制できる場合があります。
tmp_tables_size は実行時に確保可能な一時テーブル用のメモリサイズを制限しているパラメータで、
max_heap_table_size は一時テーブルに使われている MEMORY ストレージエンジンの最大サイズのパラメータです。
後者は忘れられやすい為、もしチューニングの際に tmp_table_size だけを変更して反映されないと嘆いている場合は、max_heep_table_size を同様の数値にしてみる事で解決する可能性が高いです。
スロークエリログに Created_tmp_disk_tables を増やす原因と思われるクエリがある場合、クエリを何度か実行する事で tmp_table_size の適正値を探る事ができます。
手順は簡単で、まず max_heep_table_size を十分に大きな値にした上で、tmp_table_size を変更し 当該のクエリを実行し、セッション上の Created_tmp_disk_tables を確認していきます。
この時 SET GLOBAL ではなく SET を使う事で、他のセッションに影響を与えずに tmp_table_size の値を変更可能なので、当該セッションだけで実験しましょう。
※利用可能なメモリの最大サイズなどは free コマンドなどを使って確認しておきましょう。
>SET max_heap_table_size = 2048 * 1024 * 1024;
>SET tmp_table_size = 128 * 1024 * 1024;
>SELECT * ...
>SHOW STATUS like "Created%";
SHOW STATUS も GLOBAL を付けない場合はセッション固有の値がカウントされていく為、他で動いているシステムの影響なく、実行したクエリの結果のみを確認できます。
Created_tmp_disk_tables が増加しない tmp_table_size を見付けた場合、それを GLOBAL な値に設定する事で、適正な一時テーブルの最大値を設定する事ができます。
max_heep_table_size も合わせて変更する事をお忘れなく。
SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;
SET GLOBAL tmp_table_size = 256 * 1024 * 1024;
また、MySQL の設定項目は揮発的な為、再起動時に戻ってしまわないように my.conf にもちゃんと設定しておきましょう。
これらはテストを実行した段階での一時テーブルのサイズな為、クエリの形式によってはレコードの増加と共に溢れる可能性があるので
テーブル設計やレコードの増加傾向などを加味して最終的な値を決めるといいと思います。
注意点として、一時テーブル生成時に確保するメモリ量が設定したサイズになるわけですから、
一時テーブルが大量に作成されるような環境では最大値に気をつけてください。
当該のクエリが定期的に cron から実行されるものであれば問題ないですが、
ユーザーのアクセスに左右されるような規模で実行された場合、
「当該クエリを発行するセッション数 × 設定したメモリのサイズ」だけ実メモリが消費される事になる為、
swap が発生したり MySQL や他のアプリケーションがメモリ不足でダウンする可能性があります。
大量に実行されるクエリで Created_tmp_disk_tables が増加する場合は設計を見直しましょう。
Created_tmp_disk_tables の増加が抑えられないクエリ
さてここまで、tmp_table_size を調整するチューニング方法を記載してきましたが、
tmp_table_size をいくら上げても、ディスク上にテーブルが作られるのを抑制できない場合があります。
explain を付けて実行すれば rows カラムより、想定されるレコード数が確認できますが、
数 GBも一時テーブルが消費するとは思えないような規模のクエリであっても、
tmp_table_size の値に関係なくディスク上にテーブルが作成されてしまうケースです。
具体的には MySQL のマニュアルに記載されています。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.4 MySQL が内部一時テーブルを使用する仕組み
状況によっては、インメモリー一時テーブルの使用が妨げられる場合があり、その場合サーバーは代わりにディスク上テーブルを使用します。
- テーブル内の BLOB または TEXT カラムの存在
- GROUP BY または DISTINCT 句内の、バイナリ文字列の場合に 512 バイトまたは非バイナリ文字列の場合に 512 文字より大きい文字列カラムの存在。(MySQL 5.6.15 より前のこの制限は、文字列の型に関係なく 512 バイトです。)
- UNION または UNION ALL が使用された場合に、SELECT リスト内の 512 (バイナリ文字列の場合はバイト数、非バイナリ文字列の場合は文字数) より大きい最大長を持つ文字列カラムの存在。
これらの条件を満たす場合、一時テーブルが強制的にディスク上に作成される場合があるようです。
この制限には明確な理由があり、それは MEMORY ストレージエンジンの特性によるものです。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.3 MEMORY ストレージエンジン
- MEMORY テーブルは固定長の行ストレージフォーマットを使用します。VARCHAR などの可変長型は、固定長を使用して格納されます。
- MEMORY テーブルは BLOB または TEXT カラムを含むことができません。
MEMORY テーブルには可変長を取り扱えない制限がある為、一時テーブルに利用する事ができず、ディスク上のテーブルを利用する事になるようです。
(一時テーブルの文字列の上限が CHAR の上限値である 255 ではなく、512バイトまで利用可能な理由がいまいち分かりませんが。)
BLOG や TEXT、512 バイト以上の VARCHAR などサイズが大きくなる可能性のあるテキストデータを持つ場合は注意しましょう。
また VARCHAR で定義可能なサイズのデータの場合は、こーいった問題を避ける為 安易に TEXT 等を選ばずに最低限のサイズを設定するように心掛けましょう。
この例外を知らずに、延々と tmp_table_size の値を調整していて時間を無駄にしたので記事にしてみました。