MySQL クエリーのキャッシュヒット率をSQLで求める方法

MySQLのクエリーキャッシュのヒット率を求めるSQLを作成しました。一般的にキャッシュのヒット率が二割以下の場合は、キャッシュを利用しない設定のほうがパフォーマンスを出すことができます。このクエリキャッシュヒット率をSQLで求められるように作成してみました。みなさまのMySQLローカルサーバで直接実行して結果を確認してみてください

以下がクエリーキャッシュのヒット率を求めるSQLです。基本的には、show status で計算することができます。私の場合は、それすらめんどくさいのでSQLで対応しました。クエリーキャッシュのヒット率を以下の計算で求めることができます。

@QCACHE_HITS/(@QCACHE_HITS+@QCACHE_INSERTS+@QCACHE_NOT_CACHED)*100

以下がクエリキャッシュヒット率を求めるSQLです。たぶん合ってます。

SELECT
@QCACHE_FREE_BLOCKS := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_FREE_BLOCKS';

SELECT
@QCACHE_FREE_MEMORY := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_FREE_MEMORY';

SELECT
@QCACHE_HITS := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_HITS';

SELECT
@QCACHE_INSERTS := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_INSERTS';

SELECT
@QCACHE_LOWMEM_PRUNES := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_LOWMEM_PRUNES';

SELECT
@QCACHE_NOT_CACHED := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_NOT_CACHED';

SELECT
@QCACHE_QUERIES_IN_CACHE := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_QUERIES_IN_CACHE';

SELECT
@QCACHE_TOTAL_BLOCKS := VARIABLE_VALUE
FROM
information_schema.`GLOBAL_STATUS`
where
VARIABLE_NAME = 'QCACHE_TOTAL_BLOCKS';

select @QCACHE_HITS/(@QCACHE_HITS+@QCACHE_INSERTS+@QCACHE_NOT_CACHED)*100 as CACHE_HIT_RATE;

動作確認

とあるデータベースのキャッシュヒット率を確認する

mysql> select @QCACHE_HITS/(@QCACHE_HITS+@QCACHE_INSERTS+@QCACHE_NOT_CACHED)*100 as CACHE_HIT_RATE;
+------------------+
| CACHE_HIT_RATE |
+------------------+
| 80.5282490931393 |
+------------------+
1 row in set (0.00 sec)

クエリキャッシュヒット率は80%でした。ストアドファンクションにしてもいいかもです。

matsuuさんより一行SQLで直してもらいました。ありがとうございます

SELECT 
  (SELECT VARIABLE_VALUE 
  FROM 
     INFORMATION_SCHEMA.GLOBAL_STATUS 
WHERE 
  VARIABLE_NAME = 'QCACHE_HITS')/(SELECT SUM(VARIABLE_VALUE) 
FROM 
  INFORMATION_SCHEMA.GLOBAL_STATUS 
WHERE 
  VARIABLE_NAME IN ('QCACHE_HITS','QCACHE_INSERTS','QCACHE_NOT_CACHED'))*100 AS CACHE_HIT_RATE;