PostgreSQLのキャッシュヒット率計算

どうも、俺です。

PostgreSQLのキャッシュヒット率の計算をググって出てきたのでメモ。

以下のサイトに書いてありました。
稼動統計情報を活用しよう(2)

・テーブルへのキャッシュヒット率の計算

SELECT relname,
   round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2)
   AS cache_hit_ratio FROM pg_statio_user_tables
     WHERE heap_blks_read > 0 ORDER BY cache_hit_ratio;

・インデックスのキャッシュヒット率の計算

SELECT relname, indexrelname,
   round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2)
   AS cache_hit_ratio FROM pg_statio_user_indexes
     WHERE idx_blks_read > 0 ORDER BY cache_hit_ratio;

以上でぇぇぇぇす。


※2015.6.25 追記

postgresql_トラブルシュート
を参考にテーブルキャッシュヒット率とインデックスヒット率を同時に計算できるんじゃないかと考えました。
利用するテーブルはpg_statio_user_tablesです。

SELECT *,
(heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) AS disk_ratio,
(idx_blks_hit*100) / (idx_blks_read+idx_blks_hit) AS idx_ratio
FROM pg_statio_user_tables
WHERE heap_blks_hit >= 1
and schemaname = 'public' ORDER BY idx_ratio;

統計情報を持つテーブルについてはこちら(統計情報コレクタ)を参考に。

ただ、なぜかこのクエリだといくつかのインデックスヒット率は、上述したクエリで算出したものと異なる場合がある。。
なので、修正しないといけない...。


※2015.6.26 追記
キャッシュヒット率の合計平均を出すクエリを作りました。

・テーブルへのキャッシュヒット率平均

SELECT avg (cache_hit_ratio)
FROM
(SELECT relname,
   round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) AS cache_hit_ratio 
FROM pg_statio_user_tables
     WHERE heap_blks_read > 0) AS foo;

・インデックスヒット率の平均

SELECT avg(cache_hit_ratio)
FROM
(SELECT relname, indexrelname,
   round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) AS cache_hit_ratio 
FROM pg_statio_user_indexes
WHERE idx_blks_read > 0) AS foo;

統計情報をリセットする場合は

SELECT pg_stat_reset();

を叩けばリセットされます。

設定を変更して統計を取り直したい場合などに使います。