クエリキャッシュを切ったほうがいイカ？ ベンチマークしてみた

mysql

カジュアル！(挨拶)

このエントリは MySQL Casual Advent Calendar 2011 の18日目の記事です。

昔、専ら PostgreSQL を使っていた頃、MySQL のクエリキャッシュって簡単に性能上がるしみたいだし羨ましいなあ、と思っていました。そのため、1年ほど前から業務で MySQL を使うようになっても、クエリキャッシュは当然のごとく有効にしておりました。

ところが先日 DSAS開発者の部屋:クエリキャッシュは切ったほうがいいんじゃなイカ？ というエントリを読みまして、クエリキャッシュはグローバルロックを獲得するとのこと。これはちょっと検証してみなければなるまい、ということでベンチマークをしてみました。

ベンチマーク結果

ざっくりと説明しますと、

  • 平均 260 byte/行、100万行の InnoDB テーブルに対して
  • 主キーによる 1è¡Œ select、セカンダリインデックスによる平均16行の select (light query)
  • 上記に10%の割合で、セカンダリインデックスでの 6万行 count(*) を混ぜる (heavy query)

を実行した場合の qps (query per second) をスコアとします。ちなみに InnoDB buffer pool 2GB で、データは全て buffer pool に乗り切っている状態です。

パラメータは以下です。

  • クエリキャッシュ : 有効 | 無効
  • クライアント数 : 4 | 8 | 16 | 32 | 64
  • 同時にテーブルに更新 : 掛けない | 0.1秒ごとに1è¡Œupdate

考察

結果を見ていきます。

インデックスを使って少数の行を select するだけの場合。(light query)

クエリキャッシュ有効でテーブルに更新がない場合、並列度を上げていっても性能が伸びません。32, 64並列では無効の場合は 10,000 qps 程度、有効だと 5,000 qps に達せず半分以下のスコアです。
有効で並列度を上げたベンチマーク中に SHOW PROCESSLIST を見えると、State が "Waiting for query cache lock" となっている行が大量に存在していました。

ただし、有効の場合でも、0.1秒ごとに update を掛けるとスコアが上がっています。

次に、row examined 60000 程度の重い集約クエリを混ぜた場合。(heavy query)

クエリが重いため、更新がなければキャッシュが有効に作用し、スコアが上がります。ただし、当然ですが更新を同時に掛けるとキャッシュにほとんどヒットしなくなるため、無効の時と同等になってしまいます。

結論

ということで、クエリキャッシュを有効にしているとかえって性能が落ちることがある、という結論です。

もちろんユースケースによって、あまり更新の掛からない DB に重いクエリを投げるような場合には効果的に作用することはあると思います。

さて、この結果を踏まえて、とある実運用中の DB をクエリキャッシュ有効から無効に変更してみました。比較的更新の多い使いかたです。

  • select 1000qps, insert 100qps, update 300qps
  • クエリキャッシュヒット率 50% 程度
  • データは全て InnoDB buffer pool に乗るサイズ

クエリキャッシュを無効にしたところ、

  • CPU (user) 使用率が 2/3 に低下 (3%→2%、程度ですが…)
  • Threads_running が平常時 1〜2程度なのに時折 50程度に跳ね上がる現象が解消

という改善が得られました。

また、slow query log に通常時は数msecで結果の返るクエリが、以下のように 100msec 程度掛かっているという不審な現象が出ていました。これがクエリキャッシュを無効にしたところぱったりと止みました。

Query_time: 0.102920  Lock_time: 0.050687

特定テーブルでもなく、特定クエリでもなく、しかも計ったように Query_time: 0.1+α, Lock_time: 0.05+α という非常に気持ち悪い (上に 100ms も掛かるので性能的に大問題) な現象だったのですが、まさかクエリキャッシュを無効にして直るとは。


明日は @n0ts さんです！