MySQLの「temporary table (一時テーブル)」 と「tmp file(テンポラリファイル)」の違いと「Copying to tmp table」と「copy to tmp table」の違い




投稿日:

※ 間違いもありましたので修正・加筆しました。

突然ですが、以下の2つの違いはご存知でしょうか?

・temporary table (「一時テーブル」と言います。)
・tmp file(「テンポラリファイル」と言います。)

似た名前の両者ですがどちらがどういう意味で何を指しているのでしょうか?私もとても紛らわしいのでおさらいしておこうと思います。「Copying to tmp table」と「copy to tmp table」の件は、後半に記載しています。


■ 値と定義の確認

それぞれの値をコマンドで確認しましょう。

mysql> SHOW STATUS;

・・・略・・・
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 199 |
| Created_tmp_tables | 2 |
・・・略・・・

の項目で確認できます。

それぞれの項目は以下の意味となります。

Created_tmp_disk_tables = ディスクに作成された一時テーブル
Created_tmp_files = 作成されたテンポラリファイル
Created_tmp_tables = 作成された一時テーブル

分かりにくいのでタイトルの対比で見るならば、

・temporary table (一時テーブル) = Created_tmp_tables, Created_tmp_disk_tables
・tmp file (テンポラリファイル) = Created_tmp_files

となります。では本題です。それぞれ詳しく見ましょう。



■ temporary table (一時テーブル) とは?

以下、参照リンクより引用

テンポラリテーブルはその接続でのみ現れ、接続が終了するとドロップされる一時的なテーブルです。テンポラリテーブルはそのサイズがtmp_table_size以下であればMEMORYテーブルとしてメモリ上に作成され、tmp_table_sizeを超えるとISAMテーブルとしてディスクに書き出されます。副問い合わせを含むような複雑なクエリを実行している場合、このパラメータを大きくするとテンポラリテーブルがMEMORYテーブルとして作成されるため、パフォーマンスが向上することがあります。ORDER BY や GROUP BY、JOIN、ALTER TABLE 等で作成されます。

設定

以下の箇所で設定されます。デフォルトは両方とも16Mです。

$ vi /etc/my.cnf

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size = 128M
 
# Maximum allowed size for a single HEAP (in memory) table. This option
# is a protection against the accidential creation of a very large HEAP
# table which could otherwise use up all memory resources.
max_heap_table_size = 128M

デフォルトは両方とも16Mです。
参考サイトからtmp_table_sizeの説明を引用すると以下になります。

tmp_table_size

テンポラリテーブルはその接続でのみ現れ、接続が終了するとドロップされる一時的なテーブルです。テンポラリテーブルはそのサイズがtmp_table_size以下であればMEMORYテーブルとしてメモリ上に作成され、tmp_table_sizeを超えるとISAMテーブルとしてディスクに書き出されます。副問い合わせを含むような複雑なクエリを実行している場合、このパラメータを大きくするとテンポラリテーブルがMEMORYテーブルとして作成されるため、パフォーマンスが向上することがあります。なお、MEMORYテーブルの大きさはmax_heap_table_sizeによっても制約されるため、tmp_table_sizeを大きくする場合は同時にmax_heap_table_sizeも大きくします。

max_heap_table_size

MEMORYテーブル(旧HEAPテーブル)はMEMORYストレージエンジンによってメモリ上に作成されるテーブルです。MEMORYテーブルはmax_heap_table_sizeを超えるテーブルを作成できないようになっています(ただしこの制約はCREATE TABLE文、ALTER TABLE文を実行したときに適用されるため、max_heap_table_sizeを変更しても既存のMEMORYテーブルには影響ありません)。とくに理由がなければtmp_table_sizeと同じ値にしておきます。

クエリが最適化されていない場合は、大きなテンポラリテーブルが作られてしまいます。テンポラリテーブルがtmp_table_sizeやmax_heap_table_sizeに割当のサイズを超える場合は、「Created_tmp_tables」のメモリ上で収まらずに「Created_tmp_disk_tables」としてディスクに書かれてしまいます。

そのため、充てられるメモリが潤沢にある場合は沢山充てて、これらの値を多めに取りたいところです。ISAMテーブルとしてディスクに書き出されにくくなります。



■ tmp file(テンポラリファイル)とは?

以下、参照リンクより引用

ORDER BY や GROUP BYのソートで使用されます。しかもファイルは、隠しファイルで作成されるので、tmpディレクトリを圧迫するようなファイルが出来ても分かりずらいです。結構厄介です。sort_buffer_sizeに設定されるソートバッファを超えると、テンポラリファイルに書き出されます。

以下の説明も分かりやすいです。

クエリにORDER BYが含まれる場合、MySQLはある程度の大きさまでは全てメモリ内でクイックソートを処理する。ある程度の大きさとはsort_buffer_sizeであり、これはセッションごとに変更可能である。ソートに必要なメモリがsort_buffer_sizeより大きくなると、テンポラリファイル(テンポラリテーブルではない)が作成され、メモリとファイルを併用してクイックソートが実行される。

設定

1, 「sort_buffer_size」は、以下の箇所で設定されます。

$ vi /etc/my.cnf

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.
sort_buffer_size = 8M

こちらも同様のサイトから引用すると以下のように書いてありました。

sort_buffer_size

ソートバッファはファイルソートを実行するときに使うメモリ上の領域です。sort_buffer_sizeを超えた分はテンポラリファイルに書き出され、ソートが実行されます。大きなデータをソートする場合、このパラメータを大きくするとメモリ上でソートが実行されるため、パフォーマンスが向上します。OLTP系では概ねデフォルト(2MB)のままで問題ありません。DWH系で大きなデータをソートする場合、セッションごとに動的に調整すると良いでしょう。

繰り返しになりますが、このsort_buffer_sizeにtemporary tableが収まらない場合に、tmpディレクトリ上にtmp fileとして作成されます。tmpディレクトリは、MySQL側で設定可能です。



結論

こうでしょうか。

■ temporary table (一時テーブル)

 MySQL内のテーブルでメモリ上に作成される。「tmp_table_size」を超える場合は「Created_tmp_disk_tables」としてディスク上に作成される。メモリ上でもディスク上でも「一時テーブル」と分類される。

■ tmp file(テンポラリファイル)

 ファイルソート時に、「sort_buffer_size」に設定のメモリ領域では足りずに、(ディスクの)tmp領域に出力されるファイル。tmp領域は、My.cnfのtmpdirで指定が可能。
  ※ tmp領域には、ディスクもメモリも指定出来るが、スレーブ側にはメモリは指定出来ない。

ちなみに、最初のSHOW STATUS;の結果の以下の値は、

Created_tmp_disk_tables
Created_tmp_files
Created_tmp_tables

どの項目も、「0」である方がパフォーマンスに優れていると言えます。



■「Copying to tmp table」と「copy to tmp table」の違いは?

「temporary table 」と「tmp file」と同様に紛らわしい表記に、「Copying to tmp table」と「copy to tmp table」があります。合わせて違いを見てみます。

「Copying to tmp table」

ORDER BY や GROUP BYで発生します。メモリー内の一時テーブルにデータをコピーしていることを意味します。
設定メモリ(tmp_table_size)の容量を超えると、ディスクへのコピーとなり、「Copying to tmp table on disk」のステータスとなります。
ちなみに、「Copying to tmp table」の場合はメモリにコピーしていますが、それでもかなり遅いです。diskになったら激遅です。

「Copying to tmp table on disk」の状態を改善したい場合は、以下2項目を増やします。

tmp_table_size 
max_heap_table_size 

そもそも「Copying to tmp table」の出現を減らすには、クエリ改善をして一時テーブルを利用しないようにします。


「copy to tmp table」

ALTER TABLE ステートメントを処理しています。新しい一時テーブルが作成されたあとのステータスで、そのテーブルに行がコピーされる前に発生します。

クエリチューニングで「Copying to tmp table 」が頻発する場合には、以下の対策を取りましょう。

 □ テーブルがMyISAMの場合
   myisam_sort_buffer_size (Thread) を大きくする
   key_buffer_size (Global) を大きくする(1/6~1/4)

 □ テーブルがInnoDBの場合
   sort_buffer_size (Thread) を大きくする



以上、紛らわしい表現についてでした。




参考サイト

漢(オトコ)のコンピュータ道: Using filesort

MySQL :: MySQL 4.1 リファレンスマニュアル :: A.4.4 MySQL がテンポラリファイルを格納する場所

MySQL :: MySQL 4.1 リファレンスマニュアル :: 4.6.8.6 SHOW PROCESSLIST

MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 4.5.6.2 一般的なスレッド状態

皆様ありがとうございます。