2012年10月14日日曜日

MySQLチューニング(tmpdir)

今まであまり意識していなかったのですが、
とある案件でMySQLのサーバーでHDDのパーティションを切って運用しています。
その時に、負荷テストとして大量なレコードをテーブルに作成しようとした際に、
あっという間にHDDが100%になってしまい、SQLが実行できない現象が発生してしまいました(´;ω;`)ウッ…
その時のエラー内容が次のようになります。
#126 - Incorrect key file for table '/tmp/#sql_448f_0.MYI'; try to
MySQLがSQL実行時に一時的な作業ファイルとして作成するファイルみたいですが、
このファイルがあっという間にHDDを圧迫してしまい、SQLが落ちるという現象でした。

以下の手順でHDDの容量が少ない環境などで簡単に試すこと出来ます!
■テストテーブル作成
CREATE TABLE `tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;
■10レコード作成
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
INSERT INTO tmp( NULL, 'text' );
■10レコードのテーブルをベースに10の8乗で1億レコード生成
INSERT INTO tmp(
 SELECT
  tmp.id = NULL,
  tmp.text
 FROM 
  tmp, tmp AS tmp2, tmp AS tmp3, tmp AS tmp4, tmp AS tmp5, tmp AS tmp6, tmp AS tmp7, tmp AS tmp8
);
これを実行すると、mysqlでtmpdirを設定していある場所に、
sql_***_0.MYD、sql_*****_0.MYIみたいなファイルが作成されます。

このファイルがどんどん肥大化してHDDを圧迫します。
※my.cnfなどでtmpdirを設定していない場合にはデフォルト値の/tmpまたは/usr/tmpになるみたいです。
10GBしかない領域でやったらあっという間にHDDが100%になってSQLが落ちましたヘ(゚∀゚ヘ)アヒャ
下はその際の経過時のコマンド結果になります。

実行直後のHDD容量の様子と一時ファイルのサイズ
-rw-rw---- 1 mysql mysql 2626904064 10月 14 14:22 2012 #sql_4e88_0.MYD
-rw-rw---- 1 mysql mysql       1024 10月 14 14:22 2012 #sql_4e88_0.MYI
drwxrwxrwx 2 root  root        4096 10月 14 14:22 2012 .
drwxr-xr-x 8 root  root        4096 10月 10 09:38 2012 ..

Filesystem            Size  Used Avail Use% マウント位置
/dev/vda1             9.9G  2.5G  7.0G  26% /
tmpfs                 3.9G     0  3.9G   0% /dev/shm
/dev/vdb1             493G  143G  325G  31% /data
実行中にファイルサイズが増えなくなった時のHDD容量の様子と一時ファイルのサイズ
drwxrwxrwx 2 root  root         4096 10月 14 14:33 2012 .
-rw-rw---- 1 mysql mysql 77200000000 10月 14 14:33 2012 #sql_4e88_0.MYD
-rw-rw---- 1 mysql mysql        1024 10月 14 14:22 2012 #sql_4e88_0.MYI
drwxr-xr-x 8 root  root         4096 10月 10 09:38 2012 ..

Filesystem            Size  Used Avail Use% マウント位置
/dev/vda1             9.9G  2.5G  7.0G  26% /
tmpfs                 3.9G     0  3.9G   0% /dev/shm
/dev/vdb1             493G  212G  256G  46% /data
77GBもある・・・Σ(´∀`;)
この時にSHWO PROCESSLISTでSQLのプロセスを見てみると、
まだINSERT INTOが実行中でした。
SHOW PROCESSLISTから消えたタイミングでのHDD容量の様子と一時ファイルのサイズ
drwxrwxrwx 2 root root 4096 10月 14 15:05 2012 .
drwxr-xr-x 8 root root 4096 10月 10 09:38 2012 ..

Filesystem            Size  Used Avail Use% マウント位置
/dev/vda1             9.9G  2.5G  7.0G  26% /
tmpfs                 3.9G     0  3.9G   0% /dev/shm
/dev/vdb1             493G  141G  327G  31% /data
このSQLの実行時間はだいたい40分ほどかかっていたかと思います。
SQL実行後にちゃんとMYD、MYIのファイルは消えて、
HDDの容量も元通りにワーイヽ(゚∀゚)メ(゚∀゚)メ(゚∀゚)ノワーイ

肝心なレコード数も、無事に1億出来てしました!
今回は、テーブルのカラムが2つだったので77GBほどでしたが、
テーブルの構造次第ではもっと大きなファイルになりますので、
ご利用には注意をしてください!
ちゃんとしたデータでやったらMYDのファイルが200GB超えとかになってた・・・(; ・`д・´)

MYD、MYIファイルは重い処理を実行したりすると一時作業ファイルとして作られるみたいです。
今回のように1億ものレコードを一度のSQLでやろうとするかなり大きテンポラリファイルが作らてしまい、
HDDの圧迫に繋がっていたのかと思います。

これの厄介なところが、HDDが100%になりトラブルの調査をするころには、
MySQL側が勝手にそのテンポラリファイルを消してしまうので、
原因不明・・・という状況に成りかねないという点です。

また、このテンポラリファイルが作られてしまったSQLは処理が遅くなるそうです。
テンポラリファイルがなるべく作られないようにするために、tmp_table_sizeと
max_heap_table_sizeの設定
を変更する方法もあるみたいです。

参考URL

0 件のコメント:

コメントを投稿