MySQLのクエリキャッシュとは
MySQLのクエリキャッシュは、簡単に言うとクエリとクエリの結果を保持しておくキャッシュで、全く同じクエリが発行された場合はクエリキャッシュから結果を取り出すというもので、全く同じクエリが大量に発行される場合は、パフォーマンス面で非常に効果が高いと言われています。
ただし、データの更新が多い場合はキャッシュがクリアされてしまうので、更新が多いシステムではあまり効果がなくむしろオーバーヘッドが増えてしまうために性能が落ちてしまうこともあり、クエリキャッシュは使わないほうがよいともされています。
現在のMySQLとクエリキャッシュ
2010年くらいまでのWebサービスはブログや記事などが多く、人気ブログなどでは確かに全く同じクエリが大量に発行されるケースも多かったです。ただし、現在のWebサービスはゲームなども含め大量にデータが更新されるケースが多く、全く同じものを同時に多くの人が見るということはあまりないのではないでしょうか。
つまり、昔はquery_cacheを入れると効果的な場合が多かったですが、今では設定を入れない方がベターというのが通例のようです。
そもそもmemcachedを利用すればクエリキャッシュを使う必要もないとも言えるでしょう。
ただし、思いもよらない場所で同じクエリが大量に発行されていたり、うまくmemcached化されていない箇所がある場合に助けてくれるという意味では、あったほうがよいケースもあるようです。
どういうケースで利用するのか
クエリキャッシュを利用しない基準としては、クエリキャッシュヒット率が70%以上であれば設定する意味があると言われています。ただし、40%くらいでも効果があったというケースもあるため、各サービスによっては違いがあります。40%以上であれば効果があるというわけではなく、40%程度でも効果があったケースがあるということです。
クエリキャッシュヒット率はデータベース全体としてのヒット率であるため、更新クエリが多いと全体としては低くなりますが、40%程度のヒット率があれば参照系のクエリが改善される可能性もあるのです。
クエリキャッシュを設定してみよう
MySQLのクエリキャッシュ設定は動的にset globalで変えられるので、色々調整してみるのがいいでしょう。きちんと設計されていないシステムであればあるほどクエリキャッシュの設定の意味はあります。
効果があるというのは何をもって効果があるといえるのかはケースバイケースです。クエリのレスポンスは変わらないけれどCPU使用率やI/OWaitが下がったりすることもあります。
クエリキャッシュ関連のパラメータ設定は以下のような形です。(バージョンによっては多少違います)
query_cache_limit キャッシュしたクエリ結果の最大サイズ(1クエリあたり)(デフォルト1MB) query_cache_min_res_unit クエリキャッシュの格納されるブロックサイズ(デフォルト4KB) query_cache_size キャッシュするクエリ結果を格納するメモリ量(デフォルト0：無効) query_cache_type クエリキャッシュの有効・無効(0:OFF 1:ON 2:DEMAND) query_cache_wlock_invalidate ONにした時テーブルの書き込みロック獲得時にそのテーブルのクエリキャッシュを無効にする(デフォルトOFF
query_cache_limit=2Mくらい query_cache_type = 1 query_cache_size = 16Mくらい
一般的なWebサービスだとquerycachesizeを32MB以上にして効果があることはあまりないことが多いようです。Onにして効果があるのは大体4〜32MBくらいですので、初めは大きめの数値に設定して下げていきながら効果を見るのがよいでしょう。## クエリキャッシュヒット率を確認してみよう
次にヒット率を見ていきます。
mysql> show global status like 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 2329 | | Qcache_free_memory | 9209472 | | Qcache_hits | 218608973 | | Qcache_inserts | 262140809 | | Qcache_lowmem_prunes | 839915 | | Qcache_not_cached | 71506151 | | Qcache_queries_in_cache | 5355 | | Qcache_total_blocks | 13096 | +-------------------------+-----------+
ここで表示されるのはMySQLを起動してからの累積値ですので、複数回確認して差分で計算しないと結果はわかりません。
今回はとりあえずもう一度実行して2回分だけで計算してみましょう。
mysql> show global status like 'Qcache%'; 2回目 +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 2986 | | Qcache_free_memory | 11520448 | | Qcache_hits | 218617556 | | Qcache_inserts | 262148859 | | Qcache_lowmem_prunes | 839915 | | Qcache_not_cached | 71508340 | | Qcache_queries_in_cache | 4471 | | Qcache_total_blocks | 11962 | +-------------------------+-----------+
それぞれの数値の意味は以下の通りとなります。
Qcachefreeblocks クエリキャッシュ内の空きメモリブロック数 Qcachefreememory クエリキャッシュの空きメモリ量 Qcachehits キャッシュヒット数 Qcacheinserts キャッシュに追加されたクエリ数 Qcachelowmemprunes メモリ不足のためにキャッシュから削除されたクエリ数 Qcachenotcached キャッシュされていない（キャッシュ不可、または QUERYCACHETYPE により）クエリ数 Qcachequeriesincache キャッシュに登録されているクエリ数 Qcachetotalblocks クエリキャッシュ内の合計ブロック数実際にselectが発行された数がQcacheinserts+Qcachenotcachedとなります。Qcache_hitsはキャッシュから返している数値です。
クエリキャッシュヒット率の計算式は
Qcache_hits/(Qchache_hits+Qchache_inserts+Qchache_not_cached)*100
となるので、上記の場合は
(218617556- 218608973)/((218617556- 218608973)+(262148859-262140809)+(71508340-71506151))*100=45.60089257
となります。
この場合45%なのでそれほど高くもないし、空きメモリも多いのでもう少しクエリキャッシュサイズを下げてみてヒット率やパフォーマンスが上がるか様子を見てみて、あまり変わらないようであればOFFにしたほうがよいかもしれません。
(※実際はクエリの実行時間とサーバの負荷を見ながらどれがいいか検討していくことになります。)
とはいえ、今までやってきた中で大体はクエリキャッシュのチューニングで変化がない、または悪くなる場合が9割です。1割くらいの確率で効果が出ることがあります。