この記事は PostgreSQL Advent Calendar 2016 の11日目です。
pg_buffercacheをもうちょっと見やすくする
pg_buffercacheの話です。
これまでにも何度かpg_buffercacheは紹介されているので、使ったことがある人もいるでしょう。
共有バッファにのったキャッシュを覗くアレです。拡張モジュールをロードすると、pg_buffercacheというビューが出来て、キャッシュの状態がわかるようになります。
ただメモリブロック毎にレコードが返されるので、そのままの出力では一目で確認するのは難しいです。
そこで、SQLで集計したりするわけですが、単純にcountするするだけでなく、もうちょっと何とかすることにします。
準備
試した環境はPostgreSQL 9.6です。たぶん9.4以上が必要です。
兎にも角にもpg_buffercacheモジュールが必要です。
# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION
共有バッファにロード出来るpg_prewarmがあると分かりやすいので、これも入れます。
CREATE EXTENSION pg_prewarm;
CREATE EXTENSION
方針
細かくというよりは大まかに見たいと思ったので、パッと一覧で見えるような出力にします。
SQLでやることか...と思わなくもないですが、SQLで出力を頑張ります
SQL
以下のSQLを作成しました。
WITH r AS (
SELECT div(bufferid, :N) AS id,
mode() WITHIN GROUP
(ORDER BY reldatabase) AS reldatabase
, mode() WITHIN GROUP
(ORDER BY relfilenode) AS relfilenode
, mod(mode() WITHIN GROUP (ORDER BY relfilenode)::int
,52) AS singlecode
FROM pg_buffercache
GROUP BY id
), rr AS (
SELECT
r.id
, d.datname
, c.relname
, r.relfilenode
, CASE
WHEN relname IS NULL THEN
'.'
WHEN singlecode >= 26 THEN
chr(ascii('A') + 6 + singlecode)
ELSE chr(ascii('A') + singlecode)
END AS sc
FROM r
LEFT JOIN pg_class AS c
ON (r.relfilenode = c.relfilenode)
LEFT JOIN pg_database AS d
ON (r.reldatabase = d.oid)
ORDER BY id
)
SELECT array_to_string(
array_agg(sc ORDER BY rr.id)
, '') AS buffercache
FROM rr
UNION
SELECT '|'|| sc ||'='||'('||rr.datname||')'||rr.relname
FROM rr
ORDER BY 1;
buffercache.sqlのファイルとしてpsqlで実行してみます。
使い方
使い方は、psqlの変数を使用しているので、psqlのオプションに-v N=20のように渡します。これは、1ブロック1文字だと大量に出過ぎるので、1文字のブロック単位を指定します。
実際の実行は以下のようにします。
$ psql -X -t -Pformat=wrapped -Pcolumns=80 -v N=20 -f buffercache.sql
..........mC.C.................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
.........................................
|C=(btest)pg_amop
|C=(btest)pg_amop_opr_fam_index
"."はNULLを変換していて空いています。起動直後なので、ほとんどキャッシュされていません。
適当なテーブルを作成してみます。
# CREATE TABLE buffercache_test (id int, m int);
# INSERT INTO buffercache_test (id, m)
VALUES (generate_series(1,10000000), (random()*10000)::int % 100);
pg_prewarmを使用してキャッシュにロードしてみます。とりあえず100に制限してロードしてみます。
# SELECT pg_prewarm('buffercache_test','buffer','main',0,100);
pg_prewarm
------------
101
(1 row)
pg_buffercacheのSQLを再度実行してみましょう。
psql -X -t -Pformat=wrapped -Pcolumns=80 -v N=20 -f buffercache.sql
jjnvuioeEBBBBB...BB...djoC.....................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
.........................................
|B=(btest)buffercache_test
|C=(btest)pg_description_o_c_o_index
|E=(btest)pg_statistic
|d=(btest)pg_depend_reference_index
|e=(btest)pg_operator
|i=(btest)pg_operator_oprname_l_r_n_index
buffercache_testがキャッシュにのっているがわかります。
続けてテーブル全部をロードしてみます。
# SELECT pg_prewarm('buffercache_test');
# psql -X -t -Pformat=wrapped -Pcolumns=80 -v N=20 -f buffercache.sql
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBH.uheowBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB.
.BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
|B=(btest)buffercache_test
|H=(btest)pg_amproc_fam_proc_index
|e=(btest)pg_operator
|h=(btest)pg_operator_oid_index
buffercache_test でほぼ埋める結果となりました。
違うテーブルをロードしたりするとbuffercache_testだったところが別のテーブルに置き換わって、追い出されていることも確認出来ます。
※ 一旦共有バッファのキャッシュにのったものをクリアするにはPostgreSQLの再起動しかなさそうです。
まとめ
とりあえず大雑把に確認する用途には使えるかなと思います。
postgresq.confのshared_bufferの値を変えたりして、共有バッファのキャッシュを確認しながらだとパフォーマンスチェックも楽しく出来ます
おまけ
9.6で罠にハマったので、別ページに書いときました。
PostgreSQL 9.6 psqlの動作の非互換