PostgreSQLのロック確認方法

どうも、俺です。

PostgreSQLで発生しているロックの確認の方法です。

PostgreSQL：ロックの確認と解除方法

上記にありますが、

SELECT l.pid, db.datname, c.relname, l.locktype, l.mode
FROM pg_locks l
        LEFT JOIN pg_class c ON l.relation=c.relfilenode
        LEFT JOIN pg_database db ON l.database = db.oid
  WHERE datname='{DATABASE_NAME}'
ORDER BY l.pid;

です。

9.6. ロックとテーブル
にあるように、クエリ実行中は数種類のロックがかかっています。

実際に測ってみると、
SELECT文でも AccessShareLockがかかりますが、
これはAccessExclusiveLockモードとのみ競合するとのことなので、
ALTER TALBE, DROP TABLE, VACUUM FULL, LOCK TABLE
以外のクエリに対しては何ら問題ありません。


以上でぇぇぇぇす。

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();

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

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