PostgreSQLスキルアップノート（自己啓発のための個人サイト）
統計情報コレクタの標準統計情報ビュー・テーブル関連情報編
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 統計情報コレクタの標準統計情報ビュー・テーブル関連情報編
■■■■
■■■■
■■■■ 2012/11/17
■■■■ 使用環境：PostgreSQL9.1.4 (CentOS6.2)
(C) 2012 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合により実機確認は9.1、マニュアルへのリンクは9.2としています。ご了承下さい。
【マニュアル】
・統計情報コレクタ→●[マニュアル]データベース活動状況の監視−統計情報コレクタ
・実行時統計情報→●[マニュアル]サーバの設定−実行時統計情報
【参考記事】
なし
・統計情報コレクタによって反映された情報
■１■ pg_stat_all_tables ★特に重要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
pg_stat_all_tablesが持っている情報は大きく分けて以下
・テーブルの有効行／無効行数とVACUUM/ANALYZE状況
・テーブルへのアクセス（参照（スキャンのタイプ別）、挿入、更新、削除）
I/Oの情報（ブロック数）などは含まれておらず、pg_statsio_all_tables側で持っている。
すべて重要な項目ばかり。
運用監視などで情報取得する場合は中途半端に取得カラムを絞らずに、全部取得した方がよい。
参照例
１．基本形（全項目表示）
──────────────────────────────
【□】 select * from pg_stat_user_tables;
test1=# select * from pg_stat_user_tables;
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
----------+------------+-----------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
272012 | public | tmp_now_test1db | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 2012-11-16 18:01:39.625944+09 | | 2012-11-16 18:01:39.62597+09 | | 2 | 0 | 2 | 0
262011 | public | t1 | 38 | 212937788 | | | 22096951 | 0 | 0 | 0 | 16777204 | 0 | 2012-11-16 18:01:39.829132+09 | 2012-11-14 03:34:17.011628+09 | 2012-11-16 18:01:40.064317+09 | 2012-11-14 03:34:36.445253+09 | 2 | 1 | 2 | 3
22392840 | public | t10 | 1 | 1 | | | 10 | 0 | 0 | 0 | 1 | 0 | 2012-11-16 18:01:40.118523+09 | | 2012-11-16 18:01:40.118564+09 | | 2 | 0 | 2 | 0
287627 | public | t6 | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 2012-11-16 18:01:39.759954+09 | | 2012-11-16 18:01:39.760186+09 | | 2 | 0 | 2 | 0
287618 | public | t3 | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 2012-11-16 18:01:39.642427+09 | | 2012-11-16 18:01:39.642472+09 | | 2 | 0 | 2 | 0
271819 | public | tttttttt | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 2012-11-16 18:01:39.625643+09 | | 2012-11-16 18:01:39.625876+09 | | 2 | 0 | 2 | 0
287624 | public | t5 | 0 | 0 | | | 0 | 0 | 0 | 0 | 1 | 0 | 2012-11-16 18:01:39.642725+09 | | 2012-11-16 18:01:39.642866+09 | | 2 | 0 | 2 | 0
287621 | public | t4 | 0 | 0 | | | 0 | 0 | 0 | 0 | 0 | 0 | 2012-11-16 18:01:39.64253+09 | | 2012-11-16 18:01:39.642557+09 | | 2 | 0 | 2 | 0
287630 | public | t7 | 0 | 0 | | | 0 | 0 | 0 | 0 | 2 | 0 | 2012-11-16 18:01:40.110272+09 | | 2012-11-16 18:01:40.110426+09 | | 2 | 0 | 2 | 0
287615 | public | t2 | 1 | 3 | | | 0 | 0 | 0 | 0 | 3 | 0 | 2012-11-16 18:01:39.634173+09 | | 2012-11-16 18:01:39.634449+09 | | 2 | 0 | 2 | 0
(10 rows)
２．このテーブルはちゃんとバキューム／アナライズされているか？
──────────────────────────────────
有効無効行数、バキューム／アナライズ日を確認する。
n_live_tup，n_dead_tup 生きている行数と不要になった行数
・・・つまりバキュームが行われた直後はn_dead_tupの値がゼロになる。
バキューム／アナライズはそれぞれ最後の自動実行／手動実行の日時を別々に持っている。（計４カラム）
last_vacuum 最後の手動バキューム
last_autovacuum 最後の自動バキューム
last_analyze 最後の手動アナライズ
last_autoanalyze 最後の自動アナライズ
ここでは手動、自動それぞれの大きい方を取得している。
【□】
select
schemaname,relname,
n_live_tup,
n_dead_tup,
to_char(greatest(last_vacuum,last_autovacuum),'yyyy/mm/dd hh24:mi') as vacuumdate,
to_char(greatest(last_analyze,last_autoanalyze),'yyyy/mm/dd hh24:mi') as analyzedate
from pg_stat_user_tables;
※greatestは片方NULLでも使える・・
schemaname | relname | n_live_tup | n_dead_tup | vacuumdate | analyzedate
------------+-----------------+------------+------------+------------------+------------------
public | tmp_now_test1db | 0 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t1 | 16777216 | 6 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t10 | 1 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t6 | 0 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t3 | 0 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | tttttttt | 0 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t5 | 1 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t4 | 0 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t7 | 2 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
public | t2 | 3 | 0 | 2012/11/17 01:02 | 2012/11/17 01:02
(10 rows)
３．参照系・このテーブル、インデックス使ってアクセスしているか？
──────────────────────────────────
テーブルのスキャン状況（インデックススキャン、テーブルスキャン等）
【□】
select
schemaname ,
relname ,
n_live_tup,
n_dead_tup,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch
from pg_stat_user_tables;
schemaname | relname | n_live_tup | n_dead_tup | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch
------------+-----------------+------------+------------+----------+--------------+----------+---------------
public | tmp_now_test1db | 0 | 0 | 0 | 0 | |
public | t1 | 0 | 0 | 3 | 35024613 | |
public | t10 | 262152 | 0 | 23 | 491521 | 5 | 32
public | t6 | 0 | 0 | 0 | 0 | |
public | t3 | 0 | 0 | 0 | 0 | |
public | tttttttt | 0 | 0 | 0 | 0 | |
public | t5 | 6867647 | 0 | 27 | 13819469 | |
public | t4 | 0 | 0 | 0 | 0 | |
public | t7 | 0 | 0 | 19 | 524286 | 0 | 0
public | t2 | 0 | 3 | 1 | 3 | |
(10 rows)
４．更新系・このテーブル、どのくらい挿入更新削除が発生しているのか？
──────────────────────────────────
テーブルへの挿入、更新、削除状況
挿入行数 n_tup_ins
更新行数 n_tup_upd
削除行数 n_tup_del
HOT更新 n_tup_hot_upd
【見送り】SQLは省略
【見送り】n_tup_updはHOT更新を含むのか除外されているのか（未確認）
【見送り】バキュームされたら挿入行数などの値はどうなるか？
以上、別途きちんと確認必要。
５．pg_stat_all_tablesの定義
──────────────────────────────
test1=# \d+ pg_stat_all_tables
View "pg_catalog.pg_stat_all_tables"
Column | Type | Modifiers | Storage | Description
-------------------+--------------------------+-----------+---------+-------------
relid | oid | | plain |
schemaname | name | | plain |
relname | name | | plain |
seq_scan | bigint | | plain |
seq_tup_read | bigint | | plain |
idx_scan | bigint | | plain |
idx_tup_fetch | bigint | | plain |
n_tup_ins | bigint | | plain |
n_tup_upd | bigint | | plain |
n_tup_del | bigint | | plain |
n_tup_hot_upd | bigint | | plain |
n_live_tup | bigint | | plain |
n_dead_tup | bigint | | plain |
last_vacuum | timestamp with time zone | | plain |
last_autovacuum | timestamp with time zone | | plain |
last_analyze | timestamp with time zone | | plain |
last_autoanalyze | timestamp with time zone | | plain |
vacuum_count | bigint | | plain |
autovacuum_count | bigint | | plain |
analyze_count | bigint | | plain |
autoanalyze_count | bigint | | plain |
View definition:
SELECT
c.oid AS relid,
n.nspname AS schemaname,
c.relname,
pg_stat_get_numscans(c.oid) AS seq_scan,
pg_stat_get_tuples_returned(c.oid) AS seq_tup_read,
sum(pg_stat_get_numscans(i.indexrelid))::bigint AS idx_scan,
sum(pg_stat_get_tuples_fetched(i.indexrelid))::bigint + pg_stat_get_tuples_fetched(c.oid) AS idx_tup_fetch,
pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(c.oid) AS n_tup_del,
pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd,
pg_stat_get_live_tuples(c.oid) AS n_live_tup,
pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,
pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,
pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,
pg_stat_get_last_analyze_time(c.oid) AS last_analyze,
pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,
pg_stat_get_vacuum_count(c.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,
pg_stat_get_analyze_count(c.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
FROM pg_class c
LEFT JOIN pg_index i ON c.oid = i.indrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])
GROUP BY c.oid, n.nspname, c.relname;
■２■ pg_statio_all_tables ★特に重要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】
select
schemaname ,
relname ,
heap_blks_read ,
heap_blks_hit ,
idx_blks_read ,
idx_blks_hit
from pg_statio_user_tables;
blks_read はディスクブロック数という説明があるので、物理read？
blks_hitはバッファブロック数という説明なので論理read？
schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit
------------+-----------------+----------------+---------------+---------------+--------------
public | tttttttt | 2 | 0 | |
public | t3 | 0 | 0 | |
public | t1 | 286234 | 18648848 | |
public | t10 | 1169 | 268000 | 920 | 762085
public | tmp_now_test1db | 0 | 0 | |
public | t5 | 122916 | 14491977 | |
public | t6 | 0 | 0 | |
public | t4 | 0 | 0 | |
public | t7 | 2190 | 541792 | 0 | 0
public | t2 | 6 | 6 | |
(10 rows)
test1=# \d+ pg_statio_all_tables
View "pg_catalog.pg_statio_all_tables"
Column | Type | Modifiers | Storage | Description
-----------------+--------+-----------+---------+-------------
relid | oid | | plain |
schemaname | name | | plain |
relname | name | | plain |
heap_blks_read | bigint | | plain |
heap_blks_hit | bigint | | plain |
idx_blks_read | bigint | | plain |
idx_blks_hit | bigint | | plain |
toast_blks_read | bigint | | plain |
toast_blks_hit | bigint | | plain |
tidx_blks_read | bigint | | plain |
tidx_blks_hit | bigint | | plain |
View definition:
SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, sum(pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))::bigint AS idx_blks_read, sum(pg_stat_get_blocks_hit(i.indexrelid))::bigint AS idx_blks_hit, pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit
FROM pg_class c
LEFT JOIN pg_index i ON c.oid = i.indrelid
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
LEFT JOIN pg_class x ON t.reltoastidxid = x.oid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.oid;
以上
