pg_buffercacheモジュール（共有バッファキャッシュ内の確認）

自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ pg_buffercacheモジュール（共有バッファキャッシュ内の確認）
■■■■
■■■■
■■■■ 2013/02/18
■■■■ 使用環境：PostgreSQL9.1.7 (CentOS6.2)
                                                                   (C) 2013 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

pg_buffercacheは共有バッファキャッシュ内の状況を把握するためのモジュール。
別にインストール（CREATE EXTENSION)が必要。
性能トラブル解析やSQLの性能測定の際に使う。


【マニュアル】

追加で提供されるモジュール・pg_buffercache→●[マニュアル]



■１■ インストール
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

インストールはCREATE EXTENSIONするだけでよい。（CREATE EXTENSIONは9.1以降）
測定対象のデータベースへ接続してCREATE EXTENSIONをする。


【□】 CREATE EXTENSION pg_buffercache;





■２■  pg_buffercacheの定義
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━


postgres=# \d pg_buffercache

   ビュー "public.pg_buffercache"
     カラム     |    型    | 修飾語
----------------+----------+--------
 bufferid       | integer  |
 relfilenode    | oid      |
 reltablespace  | oid      |
 reldatabase    | oid      |
 relforknumber  | smallint |
 relblocknumber | bigint   |
 isdirty        | boolean  |
 usagecount     | smallint |



※ bufferid          個々のバッファの番号
   isdirty boolean   ダーティページかどうか 




■３■  pg_buffercacheの参照
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

【注意】

・pg_classに対して結合して使う場合、現在のデータベースのOIDまたは0と等しいreldatabase
を持つ行に限定して結合するようにする。 

・参照すると内部でメモリのロックがかかるためむやみの実行は控える。



１．relnameごとのバッファ数
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

接続したデータベースに関するもの（上位１０個）を表示


【□】 psql psotgres postgres

【□】 

SELECT c.relname, count(*) AS buffers
    FROM pg_buffercache b INNER JOIN pg_class c
    ON b.relfilenode = pg_relation_filenode(c.oid) AND
          b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
             GROUP BY c.relname
             ORDER BY 2 DESC
             LIMIT 10;


            relname             | buffers
--------------------------------+---------
 t1                             |    3241
 pg_proc                        |      61
 pg_proc_proname_args_nsp_index |      23
 pg_attribute                   |      23
 pg_statistic                   |      17
 pg_operator                    |      13
 pg_depend_reference_index      |      12
 pg_class                       |      11
 pg_proc_oid_index              |       9
 pg_depend                      |       8
(10 行)
(10 行)






２．ダーティページの情報のみを表示する
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

ダーティページとは共有バッファ上で更新され、まだディスクに書き出されていないページのこと。


【□】 

SELECT c.relname, count(*) AS buffers
    FROM pg_buffercache b INNER JOIN pg_class c
    ON b.relfilenode = pg_relation_filenode(c.oid) AND
          b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
          AND
          b.isdirty = true
             GROUP BY c.relname
             ORDER BY 2 DESC
             LIMIT 100;




■４■  実施例・チェックポイントによってダーティページがすべて書き出される確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

チェックポイント実施前

【□】  b.isdirty = trueのものだけを抽出

SELECT c.relname, count(*) AS buffers
    FROM pg_buffercache b INNER JOIN pg_class c
    ON b.relfilenode = pg_relation_filenode(c.oid) AND
          b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
          AND
          b.isdirty = true
             GROUP BY c.relname
             ORDER BY 2 DESC
             LIMIT 100;


             relname              | buffers
----------------------------------+---------
 t1                               |    2070
 pg_statistic                     |       4
 pg_class                         |       3
 pg_statistic_relid_att_inh_index |       2
 pg_attribute                     |       1
 pg_index                         |       1
(6 行)



チェックポイントの実行

【□】  checkpoint;



チェックポイント実施前
【□】  b.isdirty = trueのものだけを抽出

SELECT c.relname, count(*) AS buffers
    FROM pg_buffercache b INNER JOIN pg_class c
    ON b.relfilenode = pg_relation_filenode(c.oid) AND
          b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
          AND
          b.isdirty = true
             GROUP BY c.relname
             ORDER BY 2 DESC
             LIMIT 100;


 relname | buffers
---------+---------
(0 行)



チェックポイントにより共有バッファ上の未反映のダーティページはすべて書き出された。



以上