MySQLでデータ領域をシステムと別diskにするならtmpdirも設定した方がいい

某所に300ホスト以上を2年ほど監視していたZabbixのMySQLがありまして、データが100GBぐらいになってメモリ8GBのホストではdisk IOが辛くなってきたので、移行することにしました。普段はそんなにでもないのですが、housekeeperが動作して古いデータを消しに行くとバッファプールに乗っていない部分に読みに行って重いのです。

この際折角なので Intel S3700 (サーバ用のSSD) をおごり、

  • Zabbix-1.8 から 2.0 にアップグレード
  • MySQL-5.0.77 から MySQL-5.6.11 に変更
  • システムは HDD で /dev/sda1
  • データは SSD で /dev/sdb1 を /data にマウント

という構成で移行の検証を行っていたところ…

MySQLのバージョンが大きく上がるので mysqldump を取得して restore 後、patch.sql (1.8→2.0 の migration SQL) を実行中に、妙に HDD への書き込みが多くてボトルネックになっている雰囲気。ALTERの一時ファイルは確かデータファイルと同じ所にできた記憶なんですが…

lsof で mysqld が開いているファイルを見ても、/data 以外には /tmp しかない。
ということで、Percona-Toolkit の pt-ioprofile を教えていただいたので試してみました。

日々の覚書: pt-ioprofileでMySQLのテンポラリテーブルのサイズを測る

結果。ばっちり /tmp を使っていました。

$ sudo pt-ioprofile --cell sizes --run-time 60
2013年  4月 24日 水曜日 11:06:23 JST
Tracing process ID 22049
     total      pread     pwrite filename
1309671424          0 1309671424 /tmp/ibQo90KP
1308622848 1308622848          0 /tmp/ibGwKRu3

/data/tmp を作成し、my.cnf で tmpdir を指定することで、MySQLにそちらを使用させるよう変更し ALTER 再実行したところ、無事 SSDのほうを使ってくれるようになりました。

# mkdir /data/tmp
# chmod 1777 /data/tmp
[mysqld]
tmpdir = /data/tmp

/tmp が十分に高速な場合はデータと別にしておくことでパフォーマンス向上が見込める可能性はあると思いますが、今回は /tmp が低速だったので SSD 上に置いてしまうほうがいいですね。

Twitterで教えていただいた @yoku0825 さん、 @kazuho さんありがとうございました。