花粉真っ盛り。
もれなく花粉症MAXな日々を過ごしております。
まみーです。

前回のエントリーからの続きになります。
MySQLパフォーマンスチューニング -my.cnfの見直し-

今回は、前回の設定結果の中から、クエリキャッシュの効き具合を測定・検証していきます。

概要

my.cnf に設定した内容のうち、クエリキャッシュについて検証します。
前回の設定変更から1週間経過した時点での値の比較となります。

目的

設定されていなかったクエリキャッシュが適切に効いているのかを検証し、設定が妥当かどうか判断します。

問題点

サービスの運用上、以下の問題点があります。

変更できない テーブル構成
リクエストごとに増え続ける ログレコード
必要な リアルタイム検索機能

状況

クエリキャッシュ設定前の状況は以下でした。

リアルタイム検索を 複数実行するとサーバーが落ちる
1プロセスでも 応答に30秒 かかる時がある
頻繁に タイムアウトする
検索クエリは若干修正したものの フルスキャンは回避できない

分析

検索結果は 1000件程度 である
検索クエリに パターン性 がある
- フルスキャンだが、同一のクエリが実行されることが多い
データとインデックスはある程度 キャッシュに 乗っている

上記を踏まえ、クエリキャッシュの効果を検証していきます。

クエリキャッシュとは

簡単に言いますと、以下であると言う認識です。

SELECTした結果 を
クエリと結果で対にメモリに キャッシュ し
次以降に 同一のクエリ の場合
キャッシュから 結果を返す

他のリクエストであっても、クエリが同一であれば、キャッシュから返す。
ディスクアクセスがないので高速に動作する、と言うことで間違いはないと思います。

my.cnf への設定内容

my.cnf

# #################
# query cache
# #################

# クエリキャッシュ最大サイズ
query_cache_limit=16M

# クエリキャッシュで使用するメモリサイズ
query_cache_size=512M

# クエリキャッシュのタイプ(0:off, 1:ON SELECT SQL_NO_CACHE以外, 2:DEMAND SELECT SQL_CACHEのみ)
query_cache_type=1

検証

query cache の設定状態を確認

mysql で以下のコマンドを実行します。

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 16777216  |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

設定値と解説

以下の設定内容から、my.cnf に記述した query cache の設定がMySQLに反映されていることがわかります。
ただし、この値は mysqld 再起動時に割り当てられる値なので、いつ計測しても値は変わりません。

Variable_name	Value	Comment
have_query_cache	YES	クエリキャッシュを使用可能かどうか
query_cache_limit	16777216	キャッシュするクエリ結果の最大サイズ。16MB
query_cache_min_res_unit	4096	クエリキャッシュのブロックサイズ。1ブロック4096byte
query_cache_size	536870912	クエリキャッシュ領域。512MB
query_cache_type	ON	SELECT SQL_NO_CACHE 以外のクエリをキャッシュする
query_cache_wlock_invalidate	OFF	書込みロック獲得時に、ロックしたテーブルに関するクエリキャッシュを無効にするかどうか

query_cache_limit

1つの query の実行結果として cache する最大のサイズです。
この場合、16MB を超える SELECT結果は cacheされないことになります。

query_cache_size

クエリキャッシュの総領域です。
設定した 512MB を超える場合は、順次キャッシュが入れ替わっていくことになります。
デフォルトは 1MB です。

query_cache_type

どのようなクエリをキャッシュ対象にするか、という設定です。
この場合、query に SQL_NO_CACHE を明示的に書かない限り、原則的に全て cache 対象となります。

値	説明	メモ
0 または OFF	キャッシュへの記録もキャッシュからの取得もしない	デフォルト
1 または ON	クエリに SELECT SQL_NO_CACHE と書いたクエリを除いてキャッシュ	明示的に特定のクエリのキャッシュを避けたい場合
2 または DEMAND	SELECT SQL_CACHE と書いたクエリのみをキャッシュ対象	明示的に特定のクエリをキャッシュさせたい場合

query_cache_wlock_invalidate

キャッシュ対象テーブルが更新ロックされていた場合、ロック解除まで待つかどうかの設定です。
更新中にキャッシュから結果を返したら困る場合に設定します。

【Tips】

ブロックとは
- MySQLサーバのクエリキャッシュでは、サーバの負荷となるメモリ割り当て処理をなるべく減らす手法として、メモリを一定の領域で区切ったブロック単位（query_cache_min_res_unit）で扱います。一定の領域で区切って更新したほうが、全体を更新するより早い、という理解をしています。

session statusを確認

設定変更から1週間後、mysql で以下のコマンドを実行します。

mysql> show session status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 791       |
| Qcache_free_memory      | 533156064 |
| Qcache_hits             | 1442086   |
| Qcache_inserts          | 291072    |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 3599      |
| Qcache_queries_in_cache | 1760      |
| Qcache_total_blocks     | 4361      |
+-------------------------+-----------+

以下の結果から、キャッシュが適切に効いていることが確認できます。

Variable_name	after	Comment
Qcache_free_blocks	791	クエリーキャッシュ内の空きメモリーブロックの数
Qcache_free_memory	533156064	クエリーキャッシュ用の空きメモリーの量
Qcache_hits	1442086	クエリー結果がクエリーキャッシュから返された数
Qcache_inserts	291072	クエリーキャッシュに追加されたクエリーの数
Qcache_lowmem_prunes	0	メモリーが少ないためクエリーキャッシュから削除されたクエリーの数
Qcache_not_cached	3599	非キャッシュクエリーの数 (キャッシュできないか、query_cache_type 設定のためキャッシュされない)
Qcache_queries_in_cache	1760	クエリーキャッシュ内に登録されたクエリーの数
Qcache_total_blocks	4361	クエリーキャッシュ内のブロックの合計数

項目別の解説

Qcache_free_blocks

クエリーキャッシュ内の空きメモリーブロックの数で、 断片化されることにより増加していくブロックの数 です。
増加傾向を見て、数値の見直しや、FLUSH QUERY CACHE の実施を検討する必要があります。
FLUSH QUERY CACHE とは、フリーメモリーブロックのデフラグと考えて間違いはないかと思います。

Qcache_free_memory

クエリーキャッシュ用の空きメモリーの量 です。
もしこの値が full になっていた場合、my.cnf の query_cache_size または物理メモリの増加を検討する必要があります。
Qcache_free_memory の値が小さい場合、プログラムの query が cache できない状態ではないかをチェックする必要も生じます。

Qcache_hits

クエリー結果がクエリーキャッシュから返された数、つまり キャッシュヒット数 です。
キャッシュからクエリーが使われるたびにインクリメントされます。

Com_select の数と比べ、適切に cache から結果を返しているかどうかをチェックしていく必要があります。
Com_select とは、キャッシュミスで実際のテーブルにクエリが実行された数、という認識で間違いないと思います。

Qcache_inserts

クエリーキャッシュに追加されるクエリーの数、つまり キャッシュに挿入された query の数 です。

Qcache_lowmem_prunes

メモリーが少ないため、クエリーキャッシュから削除されたクエリーの数 です。
数が0であるので、問題ないと判断できます。
この値が増加していくようならば、メモリの見直しが必要になってきます。

Qcache_not_cached

非キャッシュクエリーの数 (キャッシュできないか、query_cache_type 設定のためキャッシュされなかった数)です。
つまり、キャッシュしない設定のクエリを除けば、 メモリ不足などでキャッシュされなかったクエリの数 となります。
数値に異常があれば、メモリ割り当てなのか、プログラムで記述された query に問題があるのか切り分けを実施する必要があります。

Qcache_queries_in_cache

クエリーキャッシュ内に登録されたクエリーの数、つまり キャッシュ内のクエリの数 です。

Qcache_total_blocks

クエリーキャッシュ内のブロックの合計数 です。
この値に対し、断片化されたブロック数である「Qcache_free_blocks」が多いと判断できる場合は、注意が必要かもしれません。

【Tips】

ブロックのメモリ領域の動きの例
- MySQLサーバは、ブロック単位で割り当てたメモリの中にクエリ結果を入れ、余った部分は切り詰めて開放します。この際に断片化（フラグメンテーション）が発生します。断片化したメモリ領域は再利用できないので無駄な領域となリます。この断片化したブロックをあらわすのが Qcache_free_blocks で、この値が増加している場合は FLUSH QUERY CACHE コマンドでデフラグする必要が出てきます。

結論

キャッシュhit率

結論、 83% とそこそこ有効な率で hit していることがわかります。
現段階では有効と捉え、運用を続けていく方針です。

計算式
- キャッシュで返されたクエリの数 / 全クエリ * 100

Qcache_hits	Qcache_hits + Qcache_inserts + Qcache_not_cached	hit rate
1442086	1442086 + 291072 + 3599 = 1736757	1442086 / 1736757 * 100 = 83(%)

ちなみに、hit率が 70％を下回る 場合は、クエリキャッシュはオフにしたほうが良いとも言われます。
(諸説あります)

ただし・・・

あくまでもこのキャッシュhit率は「現時点での」数値になります。
今後、以下の観点でhit率を継続的に計測し、チューニングを続けていく必要があります。

データ量の増加
リクエスト数の増加
一回のクエリでのデータ転送量の増加
キャッシュの入れ替わりの検証

課題

以下を課題と捉え、今後もチューニングを進めていきたいと思います。

ログテーブルは更新が多いので、よりキャッシュされやすい構造の考案など
検索はスレーブを作って実行する
memcached など、プログラム側でのキャッシュの検討
ヒット率やその他の数値の定期的な計測バッチの作成

終わりに

クエリキャッシュひとつ取っても、非常に興味深く、また計測・検証し甲斐のある項目でした。
他の設定項目に関しても、別エントリーで詳しく検証していきたいと思います。

間違いなどありましたら、遠慮なくご指摘頂ければ非常に嬉しいです m(_ _)m