MySQLパフォーマンスチューニング -クエリキャッシュ適用状況の確認-

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

前回のエントリーからの続きになります。
[MySQLパフォーマンスチューニング -my.cnfの見直し-](http://qiita.com/mamy1326/items/9c5eaee3c986cff65a55)

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

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

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

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

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

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

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

# 分析

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

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

# クエリキャッシュとは
簡単に言いますと、以下であると言う認識です。

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

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

# my.cnf への設定内容

```ini: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 で以下のコマンドを実行します。

```sql
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 で以下のコマンドを実行します。

```sql
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