この記事は PostgreSQL Advent Calendar 2016 の11日目です。

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を作成しました。

buffercache.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の動作の非互換