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の一時ファイルは確かデータファイルと同じ所にできた記憶なんですが…

折角SSDをおごったのに/tmpがHDDだったのでALTERのパフォーマンスが全然出てない模様

2013-04-24 10:33:33 via YoruFukurou

ALTERはtmpdirを使わずに、もとテーブルの間隣にテンポラリファイルを作った気がします。

2013-04-24 10:34:24 via Janetter

@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

@fujiwara @kazuho rootでアタッチして大丈夫な環境なら、pt-ioprofileで結構簡単に調べられたりします。 URL URL

2013-04-24 10:59:18 via Janetter to @fujiwara

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 さんありがとうございました。