postgresql_ステータス確認

JITAKU_SVR_Wiki

DBのロック †

ロック確認 †

# psql -U postgres
postgres=# select l.pid, db.datname, c.relname, l.locktype, l.mode
from pg_locks l
left join pg_class c on l.relation=c.relfilenode
left join pg_database db on l.database = db.oid
order by l.pid;

  pid  | datname  | relname  |  locktype  |      mode
-------+----------+----------+------------+-----------------
 16855 | postgres | pg_locks | relation   | AccessShareLock
 16855 |          |          | virtualxid | ExclusiveLock
 16855 |          |          | relation   | AccessShareLock
 16855 | postgres |          | relation   | AccessShareLock
 16855 |          |          | relation   | AccessShareLock
 16855 |          |          | relation   | AccessShareLock
 16855 | postgres |          | relation   | AccessShareLock
 16855 | postgres |          | relation   | AccessShareLock
(8 rows)

現在実施中の処理内容や開始時間を確認する †

postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------
datid            | 12870
datname          | postgres
pid              | 6856
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2013-05-13 19:58:11.570532+09
xact_start       | 2013-05-13 19:59:14.692669+09
query_start      | 2013-05-13 19:59:14.692669+09
state_change     | 2013-05-13 19:59:14.692673+09
waiting          | f
state            | active
query            | SELECT * FROM pg_stat_activity;

TIPS:
mysqlだと"show full processlist"と同じ結果？

統計情報を調べる †

postgres=# select * from pg_stat_database

postgres=# \x
Expanded display is on.
※読みにくいので拡張

postgres=# select * from pg_stat_database;
-[ RECORD 1 ]--+------------------------------
datid          | 12870
datname        | postgres
numbackends    | 1
xact_commit    | 42
xact_rollback  | 7
blks_read      | 142
blks_hit       | 6209
tup_returned   | 5822
tup_fetched    | 3554
tup_inserted   | 0
tup_updated    | 0
tup_deleted    | 0
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2013-02-24 15:22:01.971981+09

参考 †

http://lets.postgresql.jp/documents/technical/statistics/3