PostgreSQLスキルアップノート（自己啓発のための個人サイト）
バキューム状況、プランナ用統計情報について
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ バキューム状況、プランナ用統計情報について
■■■■
■■■■
■■■■ 2012/11/2
■■■■ 使用環境：PostgreSQL9.1.4 (CentOS6.2)
(C) 2012 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合により実機確認は9.1、マニュアルへのリンクは9.2としています。
・マニュアル
サーバの設定・自動バキューム作業→●[マニュアル]
定常保守作業・自動バキュームデーモン→●[マニュアル]
プランナで使用される統計情報 →●[マニュアル]
pg_statistic(実体）→●[マニュアル]
pg_stats（ビュー）→●[マニュアル]
■１■ 自動バキュームデーモンの実行ログはどこに記録？
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
・デフォルトでは出力されていない。
・設定するとpostgreSQLのログへ出力される。
・設定はpostgresql.conf
パラメータ「log_autovacuum_min_duration」
・-1・・ログなし。(デフォルト）
・0・・すべてのautovacuumの活動記録。
・数字・・指定したミリ秒以上かかったものだけがログに残る。例) 250ms
・出力される内容は以下の通り
・実行ログ
・ロック競合で実行されなかった状況。【重要】
例）
【□】 cd /home/postgres/data/pg_log
【□】 tail postgresql-2012-11-03_161439.log
2012-11-03 16:20:10 JST [](13357)LOG: automatic vacuum of table "test1.public.tttttttt": index scans: 0
pages: 6 removed, 0 remain
tuples: 1315 removed, 0 remain
system usage: CPU 0.00s/0.00u sec elapsed 0.01 sec
2012-11-03 16:20:10 JST [](13357)LOG: automatic analyze of table "test1.public.tttttttt" system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
■２■ テーブル毎のVACUUM／ANALYZEの実行日時はどこに記録？
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
・格納場所 pg_stat_NNN_tables
pg_stat_all_tables すべて
pg_stat_sys_tables システムテーブルのみ
pg_stat_user_tables ユーザテーブルのみ
・実行日時を示す４個のカラム
テーブル毎にバキューム（手動、自動） アナライズ（手動、自動）
last_vacuum
last_autovacuum
last_analyze
last_autoanalyze
例）
【□】 SELECT schemaname,relname,n_live_tup,n_dead_tup,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze FROM pg_stat_user_tables;
schemaname | relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
------------+-----------------+------------+------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
public | tttttttt | 31 | 0 | 2012-11-03 16:30:14.759731+09 | 2012-11-03 16:20:10.059239+09 | 2012-11-03 16:30:14.759972+09 | 2012-11-03 16:20:10.066508+09
↑vacuumすると不要な行が解放されて減る
■３■ プランナ用統計情報はどこに記録？
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
・システムカタログに格納されている。
pg_statstic 実体 データの大元
pg_stats ビュー
・pg_statisticは扱いづらいので解析目的でなければpg_statsがよい。
■４■ 参考・定義内容
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．pg_stat_all_tables等
──────────────────────────────
日時以外にも下記の項目あり（カラムは全部重要）
postgres=# \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_statistic
──────────────────────────────
postgres=# \d+ pg_statistic
Table "pg_catalog.pg_statistic"
Column | Type | Modifiers | Storage | Description
-------------+----------+-----------+----------+-------------
starelid | oid | not null | plain |
staattnum | smallint | not null | plain |
stainherit | boolean | not null | plain |
stanullfrac | real | not null | plain |
stawidth | integer | not null | plain |
stadistinct | real | not null | plain |
stakind1 | smallint | not null | plain |
stakind2 | smallint | not null | plain |
stakind3 | smallint | not null | plain |
stakind4 | smallint | not null | plain |
staop1 | oid | not null | plain |
staop2 | oid | not null | plain |
staop3 | oid | not null | plain |
staop4 | oid | not null | plain |
stanumbers1 | real[] | | extended |
stanumbers2 | real[] | | extended |
stanumbers3 | real[] | | extended |
stanumbers4 | real[] | | extended |
stavalues1 | anyarray | | extended |
stavalues2 | anyarray | | extended |
stavalues3 | anyarray | | extended |
stavalues4 | anyarray | | extended |
Indexes:
"pg_statistic_relid_att_inh_index" UNIQUE, btree (starelid, staattnum, stainherit)
Has OIDs: no
３．pg_stats
──────────────────────────────
postgres=# \d+ pg_stats
View "pg_catalog.pg_stats"
Column | Type | Modifiers | Storage | Description
-------------------+----------+-----------+----------+-------------
schemaname | name | | plain |
tablename | name | | plain |
attname | name | | plain |
inherited | boolean | | plain |
null_frac | real | | plain |
avg_width | integer | | plain |
n_distinct | real | | plain |
most_common_vals | anyarray | | extended |
most_common_freqs | real[] | | extended |
histogram_bounds | anyarray | | extended |
correlation | real | | plain |
View definition:
SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct,
CASE
WHEN s.stakind1 = ANY (ARRAY[1, 4]) THEN s.stavalues1
WHEN s.stakind2 = ANY (ARRAY[1, 4]) THEN s.stavalues2
WHEN s.stakind3 = ANY (ARRAY[1, 4]) THEN s.stavalues3
WHEN s.stakind4 = ANY (ARRAY[1, 4]) THEN s.stavalues4
ELSE NULL::anyarray
END AS most_common_vals,
CASE
WHEN s.stakind1 = ANY (ARRAY[1, 4]) THEN s.stanumbers1
WHEN s.stakind2 = ANY (ARRAY[1, 4]) THEN s.stanumbers2
WHEN s.stakind3 = ANY (ARRAY[1, 4]) THEN s.stanumbers3
WHEN s.stakind4 = ANY (ARRAY[1, 4]) THEN s.stanumbers4
ELSE NULL::real[]
END AS most_common_freqs,
CASE
WHEN s.stakind1 = 2 THEN s.stavalues1
WHEN s.stakind2 = 2 THEN s.stavalues2
WHEN s.stakind3 = 2 THEN s.stavalues3
WHEN s.stakind4 = 2 THEN s.stavalues4
ELSE NULL::anyarray
END AS histogram_bounds,
CASE
WHEN s.stakind1 = 3 THEN s.stanumbers1[1]
WHEN s.stakind2 = 3 THEN s.stanumbers2[1]
WHEN s.stakind3 = 3 THEN s.stanumbers3[1]
WHEN s.stakind4 = 3 THEN s.stanumbers4[1]
ELSE NULL::real
END AS correlation
FROM pg_statistic s
JOIN pg_class c ON c.oid = s.starelid
JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text);
以上
