某所に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の一時ファイルは確かデータファイルと同じ所にできた記憶なんですが…
折角SSDをおごったのに/tmpがHDDだったのでALTERのパフォーマンスが全然出てない模様
ALTERはtmpdirを使わずに、もとテーブルの間隣にテンポラリファイルを作った気がします。
@yoku0825 iostatとiotopでみると、datadirがsdb1にあるのにmysqldがsda1に大量に読み書きしてるのは分かりました、がこれが何故なのかが不明です…(巨大テーブルALTER中です
2013-04-24 10:40:16 via YoruFukurou to @yoku0825
@fujiwara @yoku0825 procfs でmysqldが開いてるファイル確認するとか?
2013-04-24 10:43:09 via Echofon to @fujiwara
@kazuho @yoku0825 /data(sdb)を除くと/tmp(sda)に数ファイル開いてるもののみなのでこれかなあと思っているのです
2013-04-24 10:46:59 via YoruFukurou to @kazuho
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 さんありがとうございました。