postgres pg_catalog

pg_catalogに属する。postgres固有の情報が格納されているとおぼえておけばよい

設定情報

show文でもとれるけど、pg_settingでも可能。みれるものは同じ

select * from pg_catalog.pg_setting;
-[ RECORD 1 ]-------------------------------------------------------------
name        | allow_system_table_mods
setting     | off
description | Allows modifications of the structure of system tables.
-[ RECORD 2 ]-------------------------------------------------------------
name        | application_name
setting     | psql
description | Sets the application name to be reported in statistics and logs.

DB一覧

pg_database. \lでみれる元になっていると思う。DB Clusterにひとつのみ。という練習問題がよくみられる

select * from pg_catalog.pg_database;
-[ RECORD 1 ]-+------------------------------------
datname       | template1
datdba        | 10
encoding      | 6
datcollate    | C
datctype      | C
datistemplate | t
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12690
datfrozenxid  | 1669
dattablespace | 1663
datacl        | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 2 ]-+------------------------------------
datname       | template0
datdba        | 10
encoding      | 6
datcollate    | C
datctype      | C
datistemplate | t
datallowconn  | f
datconnlimit  | -1
datlastsysoid | 12690
datfrozenxid  | 1669
dattablespace | 1663
datacl        | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 3 ]-+------------------------------------
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | C
datctype      | C
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12690
datfrozenxid  | 1669
dattablespace | 1663
datacl        |

テーブル一覧

select * from pg_tables where schemaname = 'public';
-[ RECORD 1 ]-------
schemaname  | public
tablename   | t1
tableowner  | kurt
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f

ユーザ一覧

pg_catalogに何個かある。superuserじゃないとみれない。たぶん

pg_user

 select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 postgres |       10 | t           | t        | t         | t       | ******** |          |
 kurt     |    16656 | t           | f        | f         | f       | ******** |          |

pg_shadow

 select * from pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |               passwd                | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t         | t       | md508b1405bc56ac19ccb34115a806d4ea6 |          |
 kurt     |    16656 | t           | f        | f         | f       | md55a2e6b5ea18a5c9d0e3db68a3be4e3e5 |          |

md5の値は

SELECT 'md5' || MD5(<PASSWORD> || <DBUSER>);

の値となる

pg_roles

-[ RECORD 1 ]--+---------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcatupdate   | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolconfig      |
oid            | 10
-[ RECORD 2 ]--+---------
rolname        | repuser
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  |
rolconfig      |
oid            | 16384

pg_authid

pg_authidがpg_roles viewとなっている。まああんまり変わらない

select * from pg_authid ;
-[ RECORD 1 ]--+------------------------------------
rolname        | postgres
rolsuper       | t
rolinherit     | t
rolcreaterole  | t
rolcreatedb    | t
rolcatupdate   | t
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    | md508b1405bc56ac19ccb34115a806d4ea6
rolvaliduntil  |
-[ RECORD 2 ]--+------------------------------------
rolname        | repuser
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcatupdate   | f
rolcanlogin    | t
rolreplication | t
rolconnlimit   | -1
rolpassword    |
rolvaliduntil  |

情報収集など

いろいろ情報を収集することが可能。参照するのならsuperuserでみるほうがよい

pg_stat_*

接続中ユーザの表示

9.xからはデフォルトで参照可能？っぽい。pg_stat_activity viewをみる。current_queryはsuperuser or currentuser以外は表示されない

\x
select * from pg_stat_activity;
-[ RECORD 1 ]----+---------------------------------
datid            | 1
datname          | template1
procpid          | 2047
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2011-09-27 00:20:39.138274+09
xact_start       | 2011-09-27 00:29:56.443392+09
query_start      | 2011-09-27 00:29:56.443392+09
waiting          | f
current_query    | select * from pg_stat_activity ;

テーブルの行単位のアクセス情報

pg_stat_all_tables, pg_stat_user_tables, pg_stat_sys_tablesがある。sysはシステムテーブル関連なのでuser_tablesをみればよい

select * from pg_stat_user_tables where schemaname = 'public' and relname = '<TABLE>';
-[ RECORD 1 ]-----+------------------------------
relid             | 16423
schemaname        | public
relname           | t1
seq_scan          | 16
seq_tup_read      | 400035
idx_scan          | 2
idx_tup_fetch     | 1
n_tup_ins         | 299763
n_tup_upd         | 100000
n_tup_del         | 100004
n_tup_hot_upd     | 1
n_live_tup        | 100000
n_dead_tup        | 0
last_vacuum       | 2011-09-27 01:19:47.899726+09
last_autovacuum   | 2011-09-27 01:15:48.105283+09
last_analyze      | 2011-09-27 01:19:48.047052+09
last_autoanalyze  | 2011-09-27 01:21:21.131135+09
vacuum_count      | 1
autovacuum_count  | 2
analyze_count     | 1
autoanalyze_count | 16

フィールドの意味は以下。だいたいわかるところはかかない

項目	意味
seq_scan	seq scan発行数
seq_tup_read	seq scanで読み出した行数
idx_scan	index scan発行数
idx_tup_fetch	index scanで読み出した行数
n_tup_ins	insert回数
n_tup_upd	update回数
n_tup_del	削除回数
n_tup_hot_upd	8.3から追加されたようだ。n_tup_updで示される更新タプル数の内、HOTが機能した更新タプル数。ここ
n_live_tup	有効な回数
n_dead_tup	ガベージコレクション待ちな行数と思う。vacuumが走ると0になるようだ

行ごとのそれぞれの処理集計は

select
  relname,
  coalesce(n_tup_ins, 0) as c,
  (coalesce(seq_tup_read, 0) + coalesce(idx_tup_fetch, 0)) as r,
  coalesce(n_tup_upd, 0) as u,
  coalesce(n_tup_del, 0) as d
from pg_stat_user_tables where schemaname = 'public';

データベースのI/O

pg_stat_database。datidとかdatnameとかは省略

項目	意味
numbackends	接続プロセス数
xact_commit	commit transaction num
xact_rollback	rollabck transaction num
blks_read	ブロック読み出し回数
blks_hit	ブロックをキャッシュから読み出した回数
tup_returned	表スキャンでの読み取り行数。seq scanのことか
tup_fetched	インデックススキャンでの読み取り行数
tup_inserted	insertされた行数
tup_updated	updateされた行数
tup_deleted	deleteされた行数
conflict	何の衝突？
stats_reset	reset日時

indexに関する情報

pg_stat_all(sys/user)_indexes

 select * from pg_stat_all_indexes where schemaname = 'public';
-[ RECORD 1 ]-+--------
relid         | 24897
indexrelid    | 24902
schemaname    | public
relname       | t1
indexrelname  | t1_pkey
idx_scan      | 4
idx_tup_read  | 4
idx_tup_fetch | 4

わかりそうなのは飛ばす

項目	意味
relid	関係するテーブルのoid
indexrelid	index oid
schemaname	publicと思っておけばよい
relname	テーブル名
indexrelname	index名
index_scan	index scan実行回数
index_tup_read	indexの読み取り行数
index_tup_fetch	インデックススキャンで抽出された有効な行数

pg_statio_*

cache hit率を求める

pg_statio_all_tablesから。フィールドの意味は以下。だいたいわかるところはかかない

項目	意味
heap_blks_read	テーブルに対するディスク読込ブロック数
heap_blks_hit	テーブルに対するブロック要求の際、メモリ内に存在したブロック数
idx_blks_read	このテーブルのインデックスに対するディスク読込ブロック数
idx_blks_hit	このテーブルのインデックスに対するブロック要求の際、メモリ内に存在したブロック数
toast_blks_read	このテーブルのTOASTテーブルに対するディスク読込ブロック数
toast_blks_hit	このテーブルのTOASTテーブルに対するブロック要求の際、メモリ内に存在したブロック数
tidx_blks_read	このテーブルのTOASTテーブルのインデックスに対するディスク読込ブロック数
tidx_blks_hit	このテーブルのTOASTテーブルのインデックスに対するブロック要求の際、メモリ内に存在したブロック数

というのを踏まえて以下の計算式で対象テーブルのヒット率を算出できる

select ((heap_blks_hit*100) / (heap_blks_read+heap_blks_hit)) as cache_hit from pg_statio_all_tables where schemaname = 'public' and relname = '<TABLE>';

97%あればキャッシュが有効に活用されている。以下の場合はshared_buffersの設定を見直したりすること。ただしOSのバッファが使われているかもしれないので、vmstatなどをみて、およその値をみてみること。（最低でも80%以上はあること）。vmstat, iostatなどをみて、vmstatの場合だとbiが増えればディスクI/Oが発生しているかもしれない

index statio

pg_statio_all(user/sys)_indexes. あんまり情報がないところをみるとあんまり重要ではないかもしれない

kurt=# select * from pg_statio_all_indexes where schemaname = 'public';
-[ RECORD 1 ]-+--------
relid         | 24897
indexrelid    | 24902
schemaname    | public
relname       | t1
indexrelname  | t1_pkey
idx_blks_read | 8
idx_blks_hit  | 9

わかりそうなのは飛ばす

項目	意味
index_blks_read	インデックスに対するディスク読込ブロック数
index_blks_hit	インデックスに対するブロック要求の際、メモリ内に存在したブロック数

pg_locks

lock状態を見ることができる

select * from pg_locks;
-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 16420
relation           | 24897
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/11
pid                | 1891
mode               | AccessExclusiveLock
granted            | t

見方は以下

項目	意味
locktype	reration/extend/page/tuple/transactionid/virtualxid/object/userlock
database	database oid。pg_database.oid
relation	table oid。pg_class.oid
page	テーブル内のページ番号
tuple	ページ内の列番号
virtualxid	トランザクションの仮想ID
transactionid	transactionid, locktypeがtransactionidの場合
classid	システムカタログのOID
objid	classsid内のOID
virtualtransaction
pid	バックエンドのPID
mode	lock mode
granted	tはlock取得済。fはロックの取得待ち状態を表す

T1でAccessExclusiveLockを実施、T2でAccessExclusiveLockを実施（no waitはもちろん指定しない）した場合

T1

BEGIN;
LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE;

BEGIN;
LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE;

とした場合、対象テーブルのpg_locksの状態をみると

 select * from pg_locks where database = 16420 and relation = 24897;
-[ RECORD 1 ]------+--------------------
locktype           | relation
database           | 16420
relation           | 24897
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/11
pid                | 1891
mode               | AccessExclusiveLock
granted            | t
-[ RECORD 2 ]------+--------------------
locktype           | relation
database           | 16420
relation           | 24897
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 2/107
pid                | 1998
mode               | AccessExclusiveLock
granted            | f

となる

lock一覧SQL

こんなのがあるんだと思う程度でよい

select
  l.pid,
  l.mode,
  l.granted,
  r.relname,
  a.current_query
from
  pg_catalog.pg_locks as l,
  pg_catalog.pg_stat_activity as a,
  pg_catalog.pg_class as r,
  pg_catalog.pg_database as d,
  pg_catalog.pg_namespace as s
where
  a.procpid = l.pid and
  r.oid = l.relation and
  r.relnamespace = s.oid and
  s.nspname = 'public' and
  d.datname = current_database() and
  d.oid = l.database;

言語一覧

pg_language

 select * from pg_catalog.pg_language ;
-[ RECORD 1 ]-+---------
lanname       | internal
lanowner      | 10
lanispl       | f
lanpltrusted  | f
lanplcallfoid | 0
laninline     | 0
lanvalidator  | 2246
lanacl        |
-[ RECORD 2 ]-+---------
lanname       | c
lanowner      | 10
lanispl       | f
lanpltrusted  | f
lanplcallfoid | 0
laninline     | 0
lanvalidator  | 2247
lanacl        |
-[ RECORD 3 ]-+---------
lanname       | sql
lanowner      | 10
lanispl       | f
lanpltrusted  | t
lanplcallfoid | 0
laninline     | 0
lanvalidator  | 2248
lanacl        |
-[ RECORD 4 ]-+---------
lanname       | plpgsql
lanowner      | 10
lanispl       | t
lanpltrusted  | t
lanplcallfoid | 12425
laninline     | 12426
lanvalidator  | 12427
lanacl        |

このページを編集するこのページを元に新規ページを作成

印刷する

カテゴリ：
一般
一般

postgres pg_catalog - holly's wiki 先頭へ

タグ

postgresql

holly's wiki

設定情報

DB一覧

テーブル一覧

ユーザ一覧

pg_user

pg_shadow

pg_roles

pg_authid

情報収集など

pg_stat_*

接続中ユーザの表示

テーブルの行単位のアクセス情報

データベースのI/O

indexに関する情報

pg_statio_*

cache hit率を求める

index statio

pg_locks

lock一覧SQL

言語一覧

Wiki内検索

最近更新したページ

2013-04-21

2012-11-25

2012-11-21

2012-11-19

2012-11-12

2012-11-11

2012-10-29

2012-10-25

2012-10-14

2012-09-25

2012-08-28

2012-08-20

2012-08-16

2012-08-15

2012-07-30

最新コメント

Menu

タグ

アクセス解析中