PostgreSQLのバッファへのヒット率を求める

最近は、記事のネタに一貫性が無いな～。

えっと、バッチの性能改善するための方法その１です。データファイルから読み込んだ内容を、PostgreSQL自身でキャッシュして再利用することでクエリーの性能を向上させます。バッファの大きさはshared_buffersっていうパラメータにブロック(1ブロック8KB)単位で大きさを指定します。その妥当性を判断する方法として、統計情報を使います。

PostgeSQLのバージョン

8.1.4です。

統計情報を取得するための設定

 vi $PGDATA/postgresql.conf
 stats_start_collector = true
 stats_command_string = true
 stats_block_level = true
 stats_row_level = true
 stats_reset_on_server_start = true

変更後はPostgreSQLを再起動

実行後に統計情報からヒット率を得る

SELECT relname,
        heap_blks_read  as "ディスクから読み込んだブロック数",
        heap_blks_hit as "キャッシュにヒットしたブロック数",
        case when heap_blks_hit = 0 then 0 else
          heap_blks_hit::real / (heap_blks_read::real + heap_blks_hit::real) end as "ヒット率",
        idx_blks_read as "ディスクから読み込んだブロック数(インデックス)",
        idx_blks_hit as "キャッシュにヒットしたブロック数(インデックス)",
        case when idx_blks_read = 0 then 0 else
          idx_blks_hit::real / (idx_blks_read::real + idx_blks_hit::real) end::real as "インデックスヒット率",
        toast_blks_read as "ディスクから読み込んだブロック数(TOAST)",
        toast_blks_hit as "キャッシュにヒットしたブロック数(TOAST)",
        case when toast_blks_read = 0 then 0 else
          toast_blks_hit::real / ( toast_blks_read::real + toast_blks_hit::real) end::real as "TOASTのヒット率",
        tidx_blks_read as "ディスクから読み込んだブロック数(TOASTのインデックス)",
        tidx_blks_hit as "キャッシュにヒットしたブロック数(TOASTのインデックス)",
        case when tidx_blks_read = 0 then 0 else
          tidx_blks_hit::real / (tidx_blks_read::real + tidx_blks_hit::real) end::real as "TOASTのインデックスヒット率"
   FROM pg_statio_user_tables
   where  heap_blks_read != 0

ヒット率は0.97以上を目指したい。TOASTってのは、１行が１ブロックに収まっていない部分のこと(略し過ぎ)。あと、統計情報はPostgreSQLを停止するリセットされる。適宜、PostgreSQLを再起動。