pg_statsinfo: Details

スナップショット一覧画面へ

Summary

instname	5688733568411365690
hostname	localhost.localdomain
port	20300
pg_version	9.1.2
snap_begin	2011-12-27 07:30:01
snap_end	2011-12-27 17:00:01
duration	09:30:00
total_dbsize	125 MB
total_commits	1424407
total_rollbacks	0

Database Statistics

ID	datname	size	size_incr	xact_commit_tps	xact_rollback_tps	blks_hit_rate	blks_hit_tps	blks_read_tps	tup_fetch_tps
1	postgres	124	102	41.650	0.000	99.800	1403.037	2.808	629.930

Transaction Statistics

refine snapshot

Database Size

Recovery Conflicts

ID	datname	confl_tablespace	confl_lock	confl_snapshot	confl_bufferpin	confl_deadlock
1	postgres	0	0	0	0	0

Instance Activity

Instance Processes ratio

ID	idle (%)	idle in xact (%)	waiting (%)	running (%)
1	38.87	4.61	50.78	5.73

Instance Processes

OS Resource Usage

CPU usage

ID	user (%)	system (%)	idle (%)	iowait (%)
1	9.04	6.97	83.31	0.68

IO usage

total read/write time and I/O queue and I/O time are possibly 0 for tablespaces which is on OS partition's condition.

ID	device_name	Including TabelSpaces	total read(MB)	total write(MB)	total read time (ms)	total write time (ms)	current I/O queue	total I/O time(ms)
1	dm-2	{pg_default,pg_global,<pg_xlog_archive>}	506	28012	171029	232676562	4	232847180

Disk Usage

Disk Usage per Tablespace

ID	tablespace	location	device	used (MB)	avail (MB)	remain%
1	<pg_xlog_archive>	/home/statsinfo/var/archivelog	253:2	17369	31542	64.488
2	pg_default	/home/statsinfo/var/pgdata	253:2	17369	31542	64.488
3	pg_global	/home/statsinfo/var/pgdata	253:2	17369	31542	64.488

Disk Usage per Table

ID	Database	Schema	Table	Size (MB)	table_reads	index_reads
1	postgres	public	pgbench_history	89	86566	0
2	postgres	statsrepo	column_20111227	4	3399	213
3	postgres	statsrepo	index_20111227	1	1351	39
4	postgres	statsrepo	table_20111227	1	926	40
5	postgres	public	pgbench_branches	0	888	0
6	postgres	public	pgbench_accounts	14	437	0
7	postgres	statsrepo	statement	0	153	20
8	postgres	statsrepo	function	0	116	17
9	postgres	statsrepo	autovacuum	0	112	14
10	postgres	statsrepo	autoanalyze	0	96	14

Table Size

Disk Reads

Long Transactions

ID	pid	when to start	duration (sec)	query
1	22878	2011-12-27 13:04:00	9.895	autovacuum: ANALYZE public.pgbench_history
2	32477	2011-12-27 15:44:16	8.652	autovacuum: ANALYZE public.pgbench_history
3	3185	2011-12-27 16:34:22	8.623	autovacuum: ANALYZE public.pgbench_history
4	27287	2011-12-27 14:17:12	8.539	autovacuum: ANALYZE public.pgbench_history
5	29786	2011-12-27 14:59:12	8.282	autovacuum: ANALYZE public.pgbench_history
6	25011	2011-12-27 13:39:12	8.090	autovacuum: ANALYZE public.pgbench_history
7	19419	2011-12-27 12:05:54	5.831	autovacuum: ANALYZE public.pgbench_history
8	17864	2011-12-27 11:39:52	3.421	autovacuum: ANALYZE public.pgbench_history
9	15237	2011-12-27 10:55:47	3.136	autovacuum: ANALYZE public.pgbench_history
10	21027	2011-12-27 12:32:57	3.055	autovacuum: ANALYZE public.pgbench_history
11	7169	2011-12-27 08:43:08	1.975	UPDATE pgbench_branches SET bbalance = bbalance + -1731 WHERE bid = 1;
12	12939	2011-12-27 10:18:43	1.889	autovacuum: ANALYZE public.pgbench_history
13	10749	2011-12-27 09:43:08	1.813	UPDATE pgbench_branches SET bbalance = bbalance + 1836 WHERE bid = 1;
14	3595	2011-12-27 07:43:08	1.739	UPDATE pgbench_branches SET bbalance = bbalance + 4360 WHERE bid = 1;

Notable Tables

Heavily Updated Tables

ID	Database	Schema	Table	INSERT	UPDATE	DELETE	total	HOT%
1	postgres	public	pgbench_history	1401883	0	0	1401883	0.000
2	postgres	public	pgbench_tellers	0	1401883	0	1401883	100.000
3	postgres	public	pgbench_accounts	0	1401883	0	1401883	99.700
4	postgres	public	pgbench_branches	0	1401883	0	1401883	99.600
5	postgres	statsrepo	column_20111227	34530	0	0	34530	0.000
6	postgres	pg_catalog	pg_statistic	151	5878	0	6029	95.600
7	postgres	statsrepo	index_20111227	5992	0	0	5992	0.000
8	postgres	statsrepo	table_20111227	4700	0	0	4700	0.000
9	postgres	statsrepo	autoanalyze	1405	0	0	1405	0.000
10	postgres	statsrepo	statement	1140	0	0	1140	0.000
11	postgres	statsrepo	autovacuum	1131	0	0	1131	0.000
12	postgres	statsrepo	setting	950	0	0	950	0.000
13	postgres	statsrepo	function	773	0	0	773	0.000
14	postgres	statsrepo	schema	304	0	0	304	0.000
15	postgres	pg_catalog	pg_attribute	134	0	0	134	0.000
16	postgres	statsrepo	tablespace	114	0	0	114	0.000
17	postgres	statsrepo	checkpoint	114	0	0	114	0.000
18	postgres	statsrepo	inherits	108	0	0	108	0.000
19	postgres	pg_catalog	pg_depend	102	0	0	102	0.000
20	postgres	pg_toast	pg_toast_2619	42	0	39	81	0.000

Heavily Updated Tables

Heavily Accessed Tables

ID	Database	Schema	Table	seq_scan	seq_tup_read	tup_per_seq	blks_hit_rate
1	postgres	statsrepo	statement	1	360	360.000	97.100
2	postgres	statsrepo	setting	2	600	300.000	97.700
3	postgres	statsrepo	autovacuum	2	366	183.000	97.200
4	postgres	statsrepo	schema	2989	380332	127.244	99.800
5	postgres	statsrepo	function	9	880	97.778	96.600
6	postgres	statsrepo	snapshot	29958	957269	31.954	100.000
7	postgres	statsrepo	database	9	72	8.000	100.000
8	postgres	public	pgbench_tellers	1136414	7256710	6.386	100.000
9	postgres	statsrepo	replication	1	6	6.000	71.800
10	postgres	statsrepo	instance	3762	4833	1.285	99.900
11	postgres	public	pgbench_branches	2234869	1118372	0.500	100.000

Low Density Tables

ID	Database	Schema	Table	tuples	logical_pages	physical_pages	tratio
1	postgres	public	pgbench_history	1408523	8972	11436	78
2	postgres	public	pgbench_accounts	100000	1640	1918	85

Fragmented Tables

ID	Database	Schema	Table	Column	correlation

Checkpoint Activity

total checkpoints	114
checkpoints by time	114
checkpoints by xlog	0
avg written buffers	2068.877
max written buffers	2869.000
avg duration (sec)	150.014
max duration (sec)	150.346

Autovacuum Activity

ID	Database	Schema	Table	count	avg index scans	avg removed rows	avg remain rows	avg duration (sec)	max duration (sec)
1	postgres	pg_toast	pg_toast_2619	1	1.000	4.000	11.000	0.000	0.000
2	postgres	public	pgbench_branches	557	1.000	142.756	54.797	0.577	8.170
3	postgres	pg_catalog	pg_statistic	3	1.000	98.667	529.333	0.033	0.050
4	postgres	public	pgbench_tellers	569	0.077	627.446	12.638	0.054	4.370

Query Activity

Functions

ID	Database	Schema	Function	calls	total time (ms)	self time (ms)	time/call (ms)
1	postgres	statsrepo	partition_insert	45222	14057	14057	0.311
2	postgres	statsrepo	get_snap_date	45222	6736	6736	0.149
3	postgres	statsrepo	partition_create	38	666	143	17.526
4	postgres	statsrepo	partition_new	114	523	522	4.588
5	postgres	statsinfo	sample	6840	49	49	0.007
6	postgres	statsrepo	get_disk_usage_table	1	39	39	39.000
7	postgres	statsrepo	get_snapshot_list	1	21	21	21.000
8	postgres	statsrepo	get_low_density_tables	1	15	14	15.000
9	postgres	public	pg_stat_statements	19	14	14	0.737
10	postgres	statsrepo	get_heavily_updated_tables	2	12	9	6.000
11	postgres	statsrepo	get_dbstats	1	10	10	10.000
12	postgres	statsrepo	get_schema_info_indexes	1	10	10	10.000
13	postgres	statsinfo	devicestats	20	10	9	0.500
14	postgres	statsinfo	cpustats	20	7	7	0.350
15	postgres	statsrepo	get_checkpoint_activity	1	7	7	7.000
16	postgres	statsrepo	get_schema_info_tables	1	5	5	5.000
17	postgres	statsrepo	div	329	3	3	0.009
18	postgres	statsrepo	get_query_activity_statements	1	3	3	3.000
19	postgres	statsrepo	get_flagmented_tables	1	3	3	3.000
20	postgres	statsinfo	tablespaces	40	2	2	0.050

Statements

ID	user	database	query	calls	total time (sec)	time/call (sec)
1	postgres	postgres	BEGIN;	1402117	2.286	0.000
2	postgres	postgres	END;	1402117	1.939	0.000
3	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + 1542 WHERE bid = 1;	2	0.059	0.029
4	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + -2704 WHERE tid = 10;	1	0.058	0.058
5	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + 4932 WHERE tid = 3;	1	0.057	0.057
6	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + 345 WHERE bid = 1;	1	0.054	0.054
7	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + -4148 WHERE tid = 10;	1	0.054	0.054
8	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + 3377 WHERE tid = 5;	1	0.054	0.054
9	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + 4588 WHERE tid = 3;	1	0.052	0.052
10	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + -1307 WHERE bid = 1;	1	0.051	0.051
11	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + 3537 WHERE bid = 1;	1	0.050	0.050
12	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + -2109 WHERE tid = 5;	1	0.050	0.050
13	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + 3772 WHERE bid = 1;	1	0.049	0.049
14	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + 2457 WHERE tid = 3;	1	0.048	0.048
15	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + -1892 WHERE bid = 1;	1	0.047	0.047
16	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + 14 WHERE bid = 1;	2	0.046	0.023
17	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + 2827 WHERE tid = 8;	1	0.046	0.046
18	postgres	postgres	UPDATE pgbench_branches SET bbalance = bbalance + -2425 WHERE bid = 1;	2	0.044	0.022
19	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + -3081 WHERE tid = 5;	1	0.043	0.043
20	postgres	postgres	UPDATE pgbench_tellers SET tbalance = tbalance + -4254 WHERE tid = 7;	1	0.042	0.042

Lock Activity

datname	postgres	postgres	postgres
nspname
relname
duration	00:00:35	00:00:32	00:00:32
blockee_pid	23437	23435	23436
blocker_pid	23438	23438	23438
blocker_gid
blockee_query	UPDATE pgbench_branches SET bbalance = bbalance + 899 WHERE bid = 1;	UPDATE pgbench_branches SET bbalance = bbalance + -2572 WHERE bid = 1;	UPDATE pgbench_branches SET bbalance = bbalance + 3099 WHERE bid = 1;
blocker_query	UPDATE pgbench_accounts SET abalance = abalance + 4649 WHERE aid = 32503;SELECT abalance FROM pgbench_accounts WHERE aid = 32503;UPDATE pgbench_tellers SET tbalance = tbalance + 4649 WHERE tid = 7;UPDATE pgbench_branches SET bbalance = bbalance + 4649 WHERE bid = 1;INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (7, 1, 32503, 4649, CURRENT_TIM...;	UPDATE pgbench_accounts SET abalance = abalance + 4649 WHERE aid = 32503;SELECT abalance FROM pgbench_accounts WHERE aid = 32503;UPDATE pgbench_tellers SET tbalance = tbalance + 4649 WHERE tid = 7;UPDATE pgbench_branches SET bbalance = bbalance + 4649 WHERE bid = 1;INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (7, 1, 32503, 4649, CURRENT_TIM...;	UPDATE pgbench_accounts SET abalance = abalance + 4649 WHERE aid = 32503;SELECT abalance FROM pgbench_accounts WHERE aid = 32503;UPDATE pgbench_tellers SET tbalance = tbalance + 4649 WHERE tid = 7;UPDATE pgbench_branches SET bbalance = bbalance + 4649 WHERE bid = 1;INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (7, 1, 32503, 4649, CURRENT_TIM...;

Replication Activity

usename	postgres
application_name	xxx
client_addr	127.0.0.1
client_hostname
client_port	44986
backend_start	2011-12-27 07:25:31.759518+09
state	streaming
current_location	4/B6B44C60 (0000000100000004000000B6)
sent_location	4/B6B44C60 (0000000100000004000000B6)
write_location	4/B6B44C60 (0000000100000004000000B6)
flush_location	4/B6B44C60 (0000000100000004000000B6)
replay_location	4/B6B44C20 (0000000100000004000000B6)
sync_priority	1
sync_state	sync

Setting Parameters

ID	name	setting	source
1	archive_command	cp %p /home/statsinfo/var/archivelog/%f	configuration file
2	archive_mode	on	configuration file
3	default_text_search_config	pg_catalog.english	configuration file
4	lc_messages	C	configuration file
5	listen_addresses	*	configuration file
6	log_autovacuum_min_duration	0	configuration file
7	log_checkpoints	on	configuration file
8	log_destination	csvlog	override
9	logging_collector	on	override
10	max_prepared_transactions	10	configuration file
11	max_stack_depth	2048	environment variable
12	max_wal_senders	5	configuration file
13	pg_statsinfo.enable_maintenance	on	configuration file
14	pg_statsinfo.long_lock_threashold	0	configuration file
15	pg_statsinfo.snapshot_interval	1800	configuration file
16	port	20300	command line
17	shared_buffers	4096	configuration file
18	shared_preload_libraries	pg_statsinfo, pg_stat_statements	configuration file
19	synchronous_standby_names	xxx	configuration file
20	track_functions	all	configuration file
21	transaction_isolation	read committed	override
22	wal_buffers	128	override
23	wal_keep_segments	32	configuration file
24	wal_level	hot_standby	configuration file

Schema Information

Tables

ID	Database	Schema	Table	columns	row width	size (MB)	incremental size (MB)	table scans	index scans
1	postgres	public	pgbench_accounts	4	97	14	1	0	2803766
2	postgres	public	pgbench_branches	3	8	0	0	2234869	284081
3	postgres	public	pgbench_history	6	24	89	88	0	0
4	postgres	public	pgbench_tellers	4	12	0	0	1136414	676212

Indexes

ID	Database	Schema	Index	Table	size (MB)	scans	rows/scan	blks_read	blks_hit	keys
1	postgres	public	pgbench_accounts_pkey	pgbench_accounts	2	2803766	1.000	0	5636059	aid
2	postgres	public	pgbench_branches_pkey	pgbench_branches	0	284081	1.000	0	295125	bid
3	postgres	public	pgbench_tellers_pkey	pgbench_tellers	0	676212	0.913	1	680346	tid

Profiles

ID	processing	executes
1	DATA:READ	0.00
2	LWLOCK:LOCK-MGR-LOCK	0.00
3	CPU:REWRITE	0.00
4	CPU:EXECUTE	0.00
5	LWLOCK:BUFFER-MAPPING-LOCK	0.00
6	CPU:PARSE	0.00
7	CPU:PLAN	0.00
8	XLOG:INSERT	1.00
9	UNUSED_OR_IDLE	2.00
10	LOCK:HEAVY-LOCK	2.00
11	CPU	4.00
12	LWLOCK:LELOCK	31.00