DSAS for Social での MySQL のボトルネックと今後の方針
KLab Advent Calendar 2011 「DSAS for Social を支える技術」の5日目です。
@methane による MySQL を骨までしゃぶるチューニングシリーズ (シリーズ名は今考えました)のまとめとして、現在の DSAS for Social の MySQL のリアルな性能値や直面しているボトルネックを赤裸々に公開 してしまいます。
innodb_io_capacity を増やそう
本題に入る前に、まだ紹介してないけど1記事にするほどではなかった パラメータを紹介しておきます。
innodb_io_capacity は、 InnoDB に教えるヒントで、 Disk の IO/sec を指定します。 デフォルトでは、通常のHDDでも使えるように中途半端な値(バージョンによって100か200) になっているのですが、BBU付きバッファがあるRAIDカードを使うなどで IO/sec が 高いマシンでは大き目に設定すると、若干ですが更新クエリ/secの限界値が上がりました。
いくつかの値を試してみたのですが、RAIDカードを使っている場合は 1000〜2000 くらいに設定すると性能が上がり、それ以上あげても性能が変わらなかったので、 この値を参考にしてください。
Gangliaのグラフを大公開
あるゲームでイベントをしているときのグラフを紹介します。 チューニングした結果ちゃんとサービスできてはいるけれど、 もうあちこちが限界です。
(順番に、マスターののCPU使用率、disk io, ネットワーク帯域とパケット数, 秒間クエリ数、SELECT,INSERT,UPDATE,DELETEクエリの内訳、そして スレーブのクエリの内訳になります)
まず、CPU使用率の半分をSystemが占めていますが、これはネットワーク 周りに起因するもので、純粋にクエリ数を減らさないと削減できなさそうです。 しかも、ソフトウェア割り込み処理などあまりコア数に対してスケールできない 部分があるので、2倍どころか1.5倍にも耐えられそうにありません。
Disk 書き込み速度は 50MB/sec にヒットしています。ダンプしたデータを取り込む時など、 ほとんどシーケンシャルな書き込みなら100MB/secを超えるのですが、通常のアクセスでは もう限界でしょう。
ネットワークでは、帯域こそギガビットを使いきっていないものの、 パケット/sec は 80K になっています。NICのハードやドライバにも よりますが、100K〜150K が限界で、限界に到達するとパケットロスが 大量発生してまともにサービスできなくなってしまいます。
秒間クエリ数を見てみると、トータルでは30K、更新クエリの数はマスター側では 見づらいのでスレーブ側を見るとだいたい 6.5K といったところでしょうか。 このグラフの時点ではレプリケーション遅延は時々数秒発生する程度なのですが、 もうすこし負荷が高いときは innodb_flush_log_at_trx_commit=0 にしても どんどんレプリケーションが遅延していくという状況になっていました。 (SELECTクエリがほとんどマスターに行っているのは数十分以上の レプリケーション遅延を前提としているためです)
MySQL チューニングの今後
CPUもネットワークもDisk IOも限界近くて、レプリケーションもどんどん遅延すると なると、もうチューニングでどうにかなる範囲を超えています。 分割するしかありません。
実は、上のグラフはすでに垂直分割をした後のもので、これ以上負荷が増えたら もう一段垂直分割することになるかもしれませんが、バランスよく分割できる 回数には限度があります。 面倒な水平分割から逃げるためにここまでチューニングしてきましたが、 腹をくくって今後のアプリケーションは水平分割を前提に設計していきます。
チューニングは楽しいですが、限界があり、限界を超えるにはアーキテクチャを 変えるしか無いという事実を覚えておく必要がありますね。
