Summary

instname	5886592150459083519
hostname	pg-rex03
port	5432
pg_version	9.2.4
snap_begin	2013-06-07 14:34:00
snap_end	2013-06-07 16:02:00
duration	01:28:00
total_dbsize	36 GiB
total_commits	481097
total_rollbacks	2122

Alert

timestamp	message
2013-06-07 14:56:00	load average 1min exceeds threshold in snapshot '2013-06-07 14:56:00' --- 44.83 (threshold = 40)
2013-06-07 15:29:00	load average 1min exceeds threshold in snapshot '2013-06-07 15:29:00' --- 46.71 (threshold = 40)

Statistics

Database Statistics

database	MiB	+MiB	commit/s	rollback/s	hit%	gets/s	reads/s	rows/s
postgres	6	0	0.397	0.000	99.700	42.990	0.135	124.872
dbt2	37225	493	90.718	0.402	87.300	19156.911	2428.836	34584.184

Transaction Statistics

Database Size

Recovery Conflicts

database	conflict tblspc	conflict lock	conflict snapshot	conflict bufferpin	conflict deadlock
postgres	0	0	0	0	0
dbt2	0	0	0	0	0

Instance Activity

WAL Statistics

write_total	write_speed
46423.542	8.792

Instance Processes Ratio

idle (%)	idle in xact (%)	waiting (%)	running (%)
29.30	0.83	1.60	68.27

Instance Processes

OS

OS Resource Usage

CPU Usage

Load Average

I/O Usage

device_name	including TableSpaces	total read (MiB)	total write (MiB)	total read time (ms)	total write time (ms)	current I/O queue	total I/O time (ms)
sda2	{pg_default,pg_global,<pg_xlog>,<pg_xlog_archive>}	119154	166238	151752605	235517259	69.483	387266975

Memory Usage

Disk Usage

Disk Usage per Tablespace

tablespace	location	device	used (MiB)	avail (MiB)	remain (%)
<pg_xlog>	/pgdata/pgxlog_dbt2/pg_xlog	8:2	70397	94306	57.258
<pg_xlog_archive>	/pgdata/pgarch/arc_dbt2	8:2	70397	94306	57.258
pg_default	/pgdata/data_dbt2	8:2	70397	94306	57.258
pg_global	/pgdata/data_dbt2	8:2	70397	94306	57.258

Disk Usage per Table

database	schema	table	size (MiB)	table reads	index reads
dbt2	public	stock	12656	4932737	617252
dbt2	public	order_line	10631	2851478	462418
dbt2	public	customer	6745	2520538	614909
dbt2	public	orders	726	214078	312102
dbt2	public	history	871	225870	0
dbt2	public	new_order	139	48208	17869
dbt2	public	item	10	4473	551
dbt2	public	warehouse	0	386	8
dbt2	public	district	1	358	33

SQL

Notable Tables

Heavily Updated Tables

database	schema	table	INSERT	UPDATE	DELETE	total	HOT (%)
dbt2	public	order_line	2224269	1804324	0	4028593	88.400
dbt2	public	stock	0	2224269	0	2224269	99.900
dbt2	public	orders	212147	189860	0	402007	99.900
dbt2	public	new_order	212147	0	189674	401821	0.000
dbt2	public	customer	0	391546	0	391546	99.900
dbt2	public	district	0	218071	0	218071	98.400
dbt2	public	history	201685	0	0	201685	0.000
dbt2	public	warehouse	0	201685	0	201685	99.400
dbt2	public	item	0	0	0	0	0.000

Heavily Accessed Tables

database	schema	table	seq_scan	seq_tup_read	tup_per_seq	hit (%)
dbt2	public	order_line	1	99315562	99315562.000	85.600
dbt2	public	orders	1	10417143	10417143.000	86.000
dbt2	public	history	1	10401739	10401739.000	65.400
dbt2	public	customer	1	10375737	10375737.000	63.400
dbt2	public	new_order	1	3191401	3191401.000	97.700
dbt2	public	item	1	100000	100000.000	99.900
dbt2	public	district	1	3400	3400.000	100.000

Low Density Tables

database	schema	table	tuples	logical_pages	physical_pages	tratio
dbt2	public	history	10401651	107234	111547	96
dbt2	public	item	100000	1235	1281	96
dbt2	public	new_order	3207611	17339	17804	97
dbt2	public	customer	10199984	849999	863392	98
dbt2	public	stock	33999996	1619048	1620003	99
dbt2	public	order_line	99104524	1357597	1360806	99
dbt2	public	orders	10409447	96384	93001	103

Fragmented Tables

database	schema	table	column	correlation
dbt2	public	stock	s_i_id	-0.039
dbt2	public	customer	c_first	-0.015
dbt2	public	customer	c_id	-0.010
dbt2	public	customer	c_id	-0.010
dbt2	public	customer	c_last	0.005
dbt2	public	warehouse	w_id	0.011
dbt2	public	orders	o_c_id	0.014
dbt2	public	orders	o_id	0.018
dbt2	public	order_line	ol_o_id	0.020
dbt2	public	district	d_w_id	0.051
dbt2	public	order_line	ol_d_id	0.055
dbt2	public	district	d_id	0.071
dbt2	public	new_order	no_d_id	0.092
dbt2	public	order_line	ol_number	0.099
dbt2	public	orders	o_d_id	0.101
dbt2	public	orders	o_d_id	0.101
dbt2	public	customer	c_d_id	0.103
dbt2	public	customer	c_d_id	0.103
dbt2	public	new_order	no_o_id	0.147
dbt2	public	new_order	no_w_id	0.904

Query Activity

Functions

database	schema	function	calls	total time (ms)	self time (ms)	time/call (ms)
dbt2	public	pgstattuple	7	170988.685	170988.685	24426.955
postgres	statsinfo	devicestats	88	5436.767	5418.848	61.781
postgres	statsinfo	sample	1051	30.903	30.903	0.029
postgres	statsinfo	cpustats	88	27.722	27.722	0.315
postgres	statsinfo	tablespaces	176	20.877	20.877	0.119
postgres	statsinfo	memory	88	3.279	3.279	0.037
postgres	statsinfo	loadavg	88	2.519	2.519	0.029
postgres	statsinfo	activity	88	0.907	0.907	0.010
postgres	statsinfo	profile	88	0.817	0.817	0.009

Statements

user	database	query	calls	total time (sec)	time/call (sec)
postgres	dbt2	FETCH ALL IN mycursor	10715	4457.819	0.416
postgres	dbt2	SELECT payment(?, ?, ?, ?, ?, ?, ?)	9940	1494.099	0.150
postgres	dbt2	SELECT * FROM order_status(?, ?, ?, ?)	921	1439.537	1.563
postgres	dbt2	SELECT delivery(?, ?)	938	933.291	0.995
postgres	dbt2	SELECT stock_level(?, ?, ?)	946	669.461	0.708
postgres	postgres	SELECT d.oid AS dbid, d.datname, pg_database_size(d.oid), CASE WHEN pg_is_in_recovery() THEN 0 ELSE age(d.datfrozenxid) END, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_byte	88	282.264	3.208
postgres	dbt2	SELECT c.oid AS relid, c.relnamespace, c.reltablespace, c.relname, c.reltoastrelid, c.reltoastidxid, c.relkind, c.relpages, c.reltuples, c.reloptions, pg_relation_size(c.oid), 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_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_bl	88	198.991	2.261
postgres	dbt2	SELECT EXTRACT(? FROM TRANSACTION_TIMESTAMP()), a.schemaname, a.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, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_all_tables a, pg_stat_all_tables b WHERE a.relid = b.relid ;	87	175.855	2.021
postgres	dbt2	SELECT EXTRACT(? FROM TRANSACTION_TIMESTAMP()), datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database ;	87	174.347	2.004
postgres	dbt2	SELECT EXTRACT(? FROM TRANSACTION_TIMESTAMP()), a.schemaname, a.relname, a.indexrelname, idx_scan, idx_tup_read, idx_tup_fetch, idx_blks_read, idx_blks_hit FROM pg_stat_all_indexes a, pg_statio_all_indexes b WHERE a.indexrelid = b.indexrelid ;	87	172.721	1.985
postgres	postgres	SELECT s.funcid, n.oid AS nspid, s.funcname, pg_get_function_arguments(funcid) AS argtypes, s.calls, s.total_time, s.self_time FROM pg_stat_user_functions s JOIN pg_namespace n ON s.schemaname = n.nspname WHERE n.nspname <> ALL (('{' \|\| $1 \|\| '}')::text[])	88	144.339	1.640
postgres	postgres	SELECT pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start, state, pg_current_xlog_location() \|\| ? \|\| pg_xlogfile_name(pg_current_xlog_location()) \|\| ?, sent_location \|\| ? \|\| pg_xlogfile_name(sent_location) \|\| ?, write_location \|\| ? \|\| pg_xlogfile_name(write_location) \|\| ?, flush_location \|\| ? \|\| pg_xlogfile_name(flush_location) \|\| ?, replay_location \|\| ? \|\| pg_xlogfile_name(replay_location) \|\| ?, sync_priority, sync_state FROM pg_stat_replication	88	4.445	0.051
postgres	postgres	SELECT * FROM statsinfo.cpustats($1)	88	3.438	0.039
postgres	dbt2	SELECT a.attrelid, a.attnum, a.attname, format_type(atttypid, atttypmod) AS type, a.attstattarget, a.attstorage, a.attnotnull, a.attisdropped, s.stawidth as avg_width, s.stadistinct as n_distinct, CASE WHEN s.stakind1 = ? THEN s.stanumbers1[?] WHEN s.stakind2 = ? THEN s.stanumbers2[?] WHEN s.stakind3 = ? THEN s.stanumbers3[?] WHEN s.stakind4 = ? THEN s.stanumbers4[?] ELSE ? END AS correlation FROM pg_attribute a LEFT JOIN pg_class c ON a.attrelid = c.oid LEFT JOIN pg_statistic s ON a.attnum = s.staattnum AND a.attrelid = s.starelid AND NOT s.stainherit LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE a.attnum > ? AND c.relkind IN (?, ?) AND n.nspname <> ALL ((? \|\| $1 \|\| ?)::text[])	88	2.947	0.033
postgres	dbt2	SELECT EXTRACT(? FROM TRANSACTION_TIMESTAMP()), datname, pid, usesysid, usename, application_name, client_addr, client_port, backend_start, xact_start, query_start, waiting, query FROM pg_stat_activity ;	88	2.913	0.033
postgres	postgres	SELECT * FROM statsinfo.devicestats($1)	88	2.689	0.031
postgres	dbt2	SELECT EXTRACT(? FROM TRANSACTION_TIMESTAMP()), relname, pid, mode, granted FROM pg_locks, pg_class WHERE relfilenode = relation ORDER BY relname; ;	88	2.259	0.026
postgres	postgres	SELECT db.datname, nb.nspname, cb.relname, sa.application_name, sa.client_addr, sa.client_hostname, sa.client_port, lb.pid AS blockee_pid, la.pid AS blocker_pid, la.gid AS blocker_gid, (statement_timestamp() - sb.query_start)::interval(0), sb.query, CASE WHEN la.gid IS NOT NULL THEN ? WHEN la.queries IS NULL THEN ? ELSE la.queries END FROM (SELECT DISTINCT l0.pid, l0.relation, transactionid, la.gid, lx.queries FROM pg_locks l0 LEFT JOIN (SELECT l1.virtualtransaction, pp.gid FROM pg_prepared_xacts pp LEFT JOIN pg_locks l1 ON l1.transactionid = pp.transaction) la ON l0.virtualtransaction = la.virtualtransaction LEFT JOIN statsinfo.last_xact_activity() lx ON l0.pid = lx.pid WHERE l0.granted = ? AND (la.gid IS NULL OR l0.relation IS NOT NULL)) la LEFT JOIN pg_stat_activity sa ON la.pid = sa.pid, (SELECT DISTINCT pid, relation, transactionid FROM pg_locks WHERE granted = ?) lb LEFT JOIN pg_stat_activity sb ON lb.pid = sb.pid LEFT JOIN pg_database db ON sb.datid	88	1.466	0.017
postgres	postgres	SELECT * FROM statsinfo.activity()	88	1.126	0.013
postgres	postgres	SELECT name, setting, unit, source FROM pg_settings WHERE source NOT IN (?, ?, ?) AND setting <> boot_val	88	0.960	0.011
postgres	postgres	SELECT s.dbid, s.userid, s.query, s.calls, s.total_time / ?, s.rows, s.shared_blks_hit, s.shared_blks_read, s.shared_blks_dirtied, s.shared_blks_written, s.local_blks_hit, s.local_blks_read, s.local_blks_dirtied, s.local_blks_written, s.temp_blks_read, s.temp_blks_written, s.blk_read_time, s.blk_write_time FROM pg_stat_statements s LEFT JOIN pg_roles r ON r.oid = s.userid WHERE r.rolname <> ALL ((? \|\| $1 \|\| ?)::text[]) ORDER BY s.total_time DESC LIMIT $2	88	0.930	0.011
postgres	postgres	SELECT * FROM statsinfo.loadavg()	88	0.546	0.006
postgres	dbt2	SELECT i.oid AS indexrelid, c.oid AS relid, i.reltablespace, i.relname AS indexrelname, i.relam, i.relpages, i.reltuples, i.reloptions, x.indisunique, x.indisprimary, x.indisclustered, x.indisvalid, x.indkey, pg_get_indexdef(i.oid), pg_relation_size(i.oid), pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch, pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM pg_class c JOIN pg_index x ON c.oid = x.indrelid JOIN pg_class i ON i.oid = x.indexrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (?, ?) AND n.nspname <> ALL ((? \|\| $1 \|\| ?)::text[])	88	0.439	0.005
postgres	dbt2	SELECT s.funcid, n.oid AS nspid, s.funcname, pg_get_function_arguments(funcid) AS argtypes, s.calls, s.total_time, s.self_time FROM pg_stat_user_functions s JOIN pg_namespace n ON s.schemaname = n.nspname WHERE n.nspname <> ALL ((? \|\| $1 \|\| ?)::text[])	88	0.173	0.002
postgres	postgres	SELECT statsinfo.sample()	987	0.125	0.000
postgres	dbt2	CLOSE mycursor	10715	0.108	0.000
postgres	postgres	SELECT a.attrelid, a.attnum, a.attname, format_type(atttypid, atttypmod) AS type, a.attstattarget, a.attstorage, a.attnotnull, a.attisdropped, s.stawidth as avg_width, s.stadistinct as n_distinct, CASE WHEN s.stakind1 = ? THEN s.stanumbers1[?] WHEN s.stakind2 = ? THEN s.stanumbers2[?] WHEN s.stakind3 = ? THEN s.stanumbers3[?] WHEN s.stakind4 = ? THEN s.stanumbers4[?] ELSE ? END AS correlation FROM pg_attribute a LEFT JOIN pg_class c ON a.attrelid = c.oid LEFT JOIN pg_statistic s ON a.attnum = s.staattnum AND a.attrelid = s.starelid AND NOT s.stainherit LEFT JOIN pg_namespace n ON c.relnamespace = n.oid WHERE a.attnum > ? AND c.relkind IN (?, ?) AND n.nspname <> ALL ((? \|\| $1 \|\| ?)::text[])	299	0.096	0.000
postgres	dbt2	BEGIN	23549	0.059	0.000
postgres	dbt2	COMMIT	23434	0.037	0.000
postgres	postgres	SELECT c.oid AS relid, c.relnamespace, c.reltablespace, c.relname, c.reltoastrelid, c.reltoastidxid, c.relkind, c.relpages, c.reltuples, c.reloptions, pg_relation_size(c.oid), 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_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_bl	88	0.035	0.000

Long Transactions

pid	client address	when to start	duration (sec)	query
15200	172.20.144.58	2013-06-07 14:38:38	621.675	VACUUM FREEZE VERBOSE ANALYZE;
4562	172.20.144.58	2013-06-07 15:57:43	106.851	SELECT now(),* FROM pgstattuple('customer');
32342		2013-06-07 15:38:26	93.753	autovacuum: ANALYZE public.new_order
4643	172.20.144.58	2013-06-07 16:00:34	85.920	SELECT now(),* FROM pgstattuple('stock');
4619	172.20.144.58	2013-06-07 15:59:36	54.508	SELECT now(),* FROM pgstattuple('order_line');
19966	172.20.144.58	2013-06-07 15:03:34	10.968	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 4 AND d_w_id = 28 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 10 AND ol_o_id BETWEEN 2987 AND 3006
19879	172.20.144.58	2013-06-07 15:01:50	10.091	COMMIT
20256	172.20.144.58	2013-06-07 15:09:30	10.024	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 1 AND d_w_id = 225 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 16 AND ol_o_id BETWEEN 2992 AND 3011
19877	172.20.144.58	2013-06-07 15:05:41	9.457	COMMIT
20251	172.20.144.58	2013-06-07 15:10:01	9.176	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 9 AND d_w_id = 299 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 11 AND ol_o_id BETWEEN 2995 AND 3014
19832	172.20.144.58	2013-06-07 15:16:01	9.017	COMMIT
20208	172.20.144.58	2013-06-07 15:07:07	8.266	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 9 AND d_w_id = 205 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 13 AND ol_o_id BETWEEN 2990 AND 3009
20209	172.20.144.58	2013-06-07 15:45:47	8.234	SELECT o_c_id FROM orders WHERE o_id = 2149 AND o_w_id = 6 AND o_d_id = 10
19830	172.20.144.58	2013-06-07 14:58:57	8.166	COMMIT
20170	172.20.144.58	2013-06-07 14:57:22	8.106	COMMIT
20078	172.20.144.58	2013-06-07 15:19:42	7.839	COMMIT
19921	172.20.144.58	2013-06-07 15:40:32	7.615	COMMIT
19919	172.20.144.58	2013-06-07 14:55:17	7.588	COMMIT
19837	172.20.144.58	2013-06-07 15:25:23	7.494	COMMIT
19962	172.20.144.58	2013-06-07 15:23:38	7.186	COMMIT
19970	172.20.144.58	2013-06-07 14:58:43	7.140	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 1 AND d_w_id = 7 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 11 AND ol_o_id BETWEEN 2985 AND 3004
20014	172.20.144.58	2013-06-07 15:13:18	7.119	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 6 AND d_w_id = 1 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 14 AND ol_o_id BETWEEN 2996 AND 3015
19926	172.20.144.58	2013-06-07 15:48:18	7.055	COMMIT
20014	172.20.144.58	2013-06-07 15:02:38	7.011	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 4 AND d_w_id = 19 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 12 AND ol_o_id BETWEEN 2989 AND 3008
19880	172.20.144.58	2013-06-07 15:42:03	6.971	COMMIT
19917	172.20.144.58	2013-06-07 14:54:38	6.874	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 8 AND d_w_id = 135 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 13 AND ol_o_id BETWEEN 2984 AND 3003
20167	172.20.144.58	2013-06-07 15:15:18	6.849	COMMIT
20057	172.20.144.58	2013-06-07 15:51:13	6.820	COMMIT
19918	172.20.144.58	2013-06-07 15:20:08	6.707	COMMIT
20210	172.20.144.58	2013-06-07 15:32:09	6.539	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 6 AND d_w_id = 85 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 12 AND ol_o_id BETWEEN 3014 AND 3033
20123	172.20.144.58	2013-06-07 14:52:29	6.016	UPDATE orders SET o_carrier_id = 5 WHERE o_id = 2101 AND o_w_id = 102 AND o_d_id= 1
20212	172.20.144.58	2013-06-07 15:33:54	5.996	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 1 AND d_w_id = 36 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 10 AND ol_o_id BETWEEN 3007 AND 3026
20167	172.20.144.58	2013-06-07 15:12:49	5.910	COMMIT
20077	172.20.144.58	2013-06-07 15:36:14	5.904	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 6 AND d_w_id = 112 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 18 AND ol_o_id BETWEEN 3025 AND 3044
20122	172.20.144.58	2013-06-07 15:08:24	5.902	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 2 AND d_w_id = 201 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 17 AND ol_o_id BETWEEN 2993 AND 3012
20126	172.20.144.58	2013-06-07 15:49:09	5.778	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 10 AND d_w_id = 74 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 11 AND ol_o_id BETWEEN 3037 AND 3056
19921	172.20.144.58	2013-06-07 15:28:59	5.707	COMMIT
19921	172.20.144.58	2013-06-07 15:47:45	5.591	COMMIT
20167	172.20.144.58	2013-06-07 15:43:20	5.418	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 8 AND d_w_id = 17 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 17 AND ol_o_id BETWEEN 3019 AND 3038
20009	172.20.144.58	2013-06-07 15:11:55	5.217	COMMIT
19829	172.20.144.58	2013-06-07 15:28:45	5.191	COMMIT
19969	172.20.144.58	2013-06-07 15:27:35	4.969	COMMIT
20164	172.20.144.58	2013-06-07 15:37:40	4.963	COMMIT
20124	172.20.144.58	2013-06-07 15:35:45	4.854	COMMIT
20212	172.20.144.58	2013-06-07 15:41:35	4.821	COMMIT
19879	172.20.144.58	2013-06-07 15:06:40	4.812	COMMIT
20060	172.20.144.58	2013-06-07 15:30:50	4.605	COMMIT
19926	172.20.144.58	2013-06-07 15:52:11	4.368	COMMIT
19961	172.20.144.58	2013-06-07 15:18:21	4.368	COMMIT
19919	172.20.144.58	2013-06-07 14:56:31	4.145	COMMIT
19966	172.20.144.58	2013-06-07 15:46:01	4.050	COMMIT
19917	172.20.144.58	2013-06-07 15:22:11	3.913	COMMIT
20167	172.20.144.58	2013-06-07 14:53:21	3.857	COMMIT
20211	172.20.144.58	2013-06-07 15:34:12	3.565	COMMIT
20080	172.20.144.58	2013-06-07 15:31:07	3.413	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 10 AND d_w_id = 180 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 15 AND ol_o_id BETWEEN 3034 AND 3053
19965	172.20.144.58	2013-06-07 15:26:43	2.587	COMMIT
19967	172.20.144.58	2013-06-07 15:00:18	2.349	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 9 AND d_w_id = 20 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 14 AND ol_o_id BETWEEN 2988 AND 3007
19793	172.20.144.58	2013-06-07 15:21:38	2.297	COMMIT
19965	172.20.144.58	2013-06-07 15:04:03	1.939	COMMIT
20012	172.20.144.58	2013-06-07 15:50:28	1.932	SELECT count(distinct s_i_id) FROM order_line, stock, district WHERE d_id = 6 AND d_w_id = 147 AND d_id = ol_d_id AND d_w_id = ol_w_id AND ol_i_id = s_i_id AND ol_w_id = s_w_id AND s_quantity < 16 AND ol_o_id BETWEEN 3034 AND 3053
20124	172.20.144.58	2013-06-07 15:44:48	1.873	COMMIT
19920	172.20.144.58	2013-06-07 15:24:33	1.819	COMMIT
20207	172.20.144.58	2013-06-07 15:14:44	1.557	COMMIT
20253	172.20.144.58	2013-06-07 15:17:49	1.448	SELECT o_c_id FROM orders WHERE o_id = 2125 AND o_w_id = 71 AND o_d_id = 10

Lock Conflicts

database	duration	blockee pid	blocker pid	blockee query	blocker query
dbt2	00:00:02	15537	15527	SELECT delivery(5, 1)	SELECT delivery(5, 7);SELECT no_o_id FROM new_order WHERE no_w_id = $1 AND no_d_id = $2ORDER BY no_o_id LIMIT 1;DELETE FROM new_order WHERE no_o_id = $1 AND no_w_id = $2 AND no_d_id = $3;SELECT o_c_id FROM orders WHERE o_id = $1 AND o_w_id = $2 AND o_d_id = $3;UPDATE orders SET o_carrier_id = $1 WHERE o_id = $2 AND o_w_id = $3 AND o_d_id = $4;UPDATE order_line SET ol_delivery_d = current_timestamp WHERE ol_o_id = $1 AND ol_w_id = $2 ...;SELECT SUM(ol_amount * ol_quantity) FROM order_line WHERE ol_o_id = $1 AND ol_w_id = $2 AND ...;UPDATE customer SET c_delivery_cnt = c_delivery_cnt + 1, c_balance = c_balance + $1 WHERE c_...;SELECT no_o_id FROM new_order WHERE no_w_id = $1 AND no_d_id = $2ORDER BY no_o_id LIMIT 1;DELETE FROM new_order WHERE no_o_id = $1 AND no_w_id = $2 AND no_d_id = $3;SELECT o_c_id FROM orders WHERE o_id = $1 AND o_w_id = $2 AND o_d_id = $3;UPDATE orders SET o_carrier_id = $1 WHERE o_id = $2 AND o_w_id = $3 AND o_d_id = $4;
dbt2	00:00:00	15537	15539	FETCH ALL IN mycursor	DECLARE mycursor CURSOR FOR SELECT new_order(8, 6, 2930, 1, 6, make_new_order_info(81406, 8, 5),...;SELECT w_tax FROM warehouse WHERE w_id = $1;SELECT d_tax, d_next_o_id FROM district WHERE d_w_id = $1 AND d_id = $2 FOR UPDATE;UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = $1 AND d_id = $2;SELECT c_discount, c_last, c_credit FROM customer WHERE c_w_id = $1 AND c_d_id = $2 AND c_id...;INSERT INTO new_order (no_o_id, no_w_id, no_d_id) VALUES ($1, $2, $3);INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_carrier_id, o_ol_cnt, o_all...;
dbt2	00:00:00	15527	15535	FETCH ALL IN mycursor	DECLARE mycursor CURSOR FOR SELECT new_order(2, 3, 511, 1, 8, make_new_order_info(77820, 2, 8), ...;SELECT w_tax FROM warehouse WHERE w_id = $1;SELECT d_tax, d_next_o_id FROM district WHERE d_w_id = $1 AND d_id = $2 FOR UPDATE;UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_w_id = $1 AND d_id = $2;SELECT c_discount, c_last, c_credit FROM customer WHERE c_w_id = $1 AND c_d_id = $2 AND c_id...;INSERT INTO new_order (no_o_id, no_w_id, no_d_id) VALUES ($1, $2, $3);INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_carrier_id, o_ol_cnt, o_all...;SELECT i_price, i_name, i_data FROM item WHERE i_id = $1;SELECT s_quantity, $1, s_data FROM stock WHERE s_i_id = $2 AND s_w_id = $3;UPDATE stock SET s_quantity = s_quantity - $1 WHERE s_i_id = $2 AND s_w_id = $3;INSERT INTO order_line (ol_o_id, ol_d_id, ol_w_id, ol_number, ol_i_id, ol_supply_w_id, ol_del...;SELECT i_price, i_name, i_data FROM item WHERE i_id = $1;SELECT s_quantity, $1, s_data FROM sto...;

Activities

Checkpoint Activity

total checkpoints	10
checkpoints by time	4
checkpoints by xlog	6
avg written buffers	40824.700
max written buffers	135646.000
avg duration (sec)	228.980
max duration (sec)	551.813

Autovacuum Activity

Basic Statistics (Average)

database	schema	table	count	avg index scans	avg removed rows	avg remain rows	avg duration (sec)	max duration (sec)	cancels
dbt2	public	warehouse	61	0.066	1346.459	518.754	0.560	4.110	1
dbt2	public	district	61	0.230	1090.984	3580.721	2.594	23.420	1
dbt2	pg_catalog	pg_statistic	1	1.000	120.000	475.000	0.020	0.020	0
dbt2	pg_toast	pg_toast_2619	1	1.000	69.000	3.000	0.000	0.000	0

Vacuum Cancels

timestamp	database	schema	table	cause query
2013-06-07 15:32:20	dbt2	public	warehouse	ANALYZE
2013-06-07 15:37:13	dbt2	public	district	ANALYZE

I/O Statistics (Average)

database	schema	table	avg page hit	avg page miss	avg page dirty	avg read rate	avg write rate
dbt2	public	district	368.328	0.098	0.738	0.202	0.347
dbt2	public	warehouse	188.607	3.148	0.738	2.549	0.802
dbt2	pg_catalog	pg_statistic	110.000	0.000	29.000	0.000	10.786
dbt2	pg_toast	pg_toast_2619	79.000	0.000	13.000	0.000	336.300

Analyze Statistics

database	schema	table	count	total duration (sec)	avg duration (sec)	max duration (sec)	last analyze time
dbt2	public	stock	2	64.960	4.053	48.160	2013-06-07 15:21:38
dbt2	public	order_line	1	26.900	3.878	36.970	2013-06-07 15:21:36
dbt2	public	customer	1	17.220	2.747	16.380	2013-06-07 15:03:38
dbt2	public	warehouse	130	2.950	0.021	0.050	2013-06-07 15:29:39
dbt2	public	orders	1	2.650	0.961	0.234	2013-06-07 15:46:38
dbt2	public	new_order	3	1.300	0.036	0.043	2013-06-07 15:16:39
dbt2	public	history	1	1.920	0.273	0.316	2013-06-07 15:43:32
dbt2	public	district	139	0.800	0.003	0.005	2013-06-07 15:29:39

Replication Activity

Current Replication Status

user	postgres	postgres
appname	sby	sby
client addr	172.20.144.30	172.20.144.31
client_host
client_port	39209	34691
backend start	2013-06-07 14:34:00	2013-06-07 14:34:00
state	streaming	streaming
current location	110/27BBE000 (000000010000011000000027)	110/27BBE000 (000000010000011000000027)
sent location	110/27BBE000 (000000010000011000000027)	110/27BBE000 (000000010000011000000027)
write location	110/27BBE000 (000000010000011000000027)	110/27BBE000 (000000010000011000000027)
flush location	110/27BBE000 (000000010000011000000027)	110/27BBE000 (000000010000011000000027)
replay location	110/27BAD7E0 (000000010000011000000027)	110/27BAD7E0 (000000010000011000000027)
sync priority	1	1
sync state	sync	potential

Replication Delays

Information

Schema Information

Table

database	schema	table	columns	rows	MiB	+MiB	table scans	index scans
dbt2	public	customer	21	68000	6745	0	1	956265
dbt2	public	district	11	10	1	1	1	980850
dbt2	public	history	8	9000	871	17	1	0
dbt2	public	item	5	100	10	0	1	2226383
dbt2	public	new_order	3	1500	139	9	1	379720
dbt2	public	order_line	10	102000	10631	260	1	417266
dbt2	public	orders	8	8000	726	14	1	398550
dbt2	public	stock	17	130000	12656	0	0	8310679
dbt2	public	warehouse	9	0	0	0	0	615521

Index

database	schema	index	table	MiB	+MiB	scans	rows/scan	reads	hits	keys
dbt2	public	i_customer	customer	591	0	132055	2.418	213787	403871	c_w_id, c_d_id, c_last, c_first, c_id
dbt2	public	i_orders	orders	306	0	18830	1.043	229885	503202	o_w_id, o_d_id, o_c_id
dbt2	public	pk_customer	customer	306	0	824210	1.000	401122	2117999	c_w_id, c_d_id, c_id
dbt2	public	pk_district	district	0	0	980850	0.792	33	1978580	d_w_id, d_id
dbt2	public	pk_item	item	2	0	2226383	0.999	551	4463141	i_id
dbt2	public	pk_new_order	new_order	96	4	379720	0.988	17869	1797056	no_w_id, no_d_id, no_o_id
dbt2	public	pk_order_line	order_line	3062	147	417266	18.336	462418	11402110	ol_w_id, ol_d_id, ol_o_id, ol_number
dbt2	public	pk_orders	orders	333	26	379720	1.000	82217	1746547	o_w_id, o_d_id, o_id
dbt2	public	pk_stock	stock	728	0	8310679	1.000	617252	32768906	s_w_id, s_i_id
dbt2	public	pk_warehouse	warehouse	0	0	615521	1.000	8	1241160	w_id

Setting Parameters

Parameter

name	setting	unit	source
TimeZone	Japan		configuration file
archive_command	/bin/cp %p /pgdata/pgarch/arc_dbt2/%f		configuration file
archive_mode	on		configuration file
autovacuum_freeze_max_age	2000000000		configuration file
autovacuum_max_workers	4		configuration file
autovacuum_vacuum_cost_limit	400		configuration file
checkpoint_completion_target	0.7		configuration file
checkpoint_segments	300		configuration file
checkpoint_timeout	900	s	configuration file
default_statistics_target	10		configuration file
default_text_search_config	pg_catalog.english		configuration file
effective_cache_size	1179648	8kB	configuration file
hot_standby	on		configuration file
hot_standby_feedback	on		configuration file
lc_messages	C		configuration file
listen_addresses	*		configuration file
log_autovacuum_min_duration	0	ms	configuration file
log_checkpoints	on		configuration file
log_destination	csvlog		override
log_error_verbosity	verbose		configuration file
log_line_prefix	%a:%t		configuration file
log_lock_waits	on		configuration file
log_min_duration_statement	10000	ms	configuration file
log_min_messages	log		configuration file
log_rotation_size	0	kB	configuration file
log_timezone	Japan		configuration file
logging_collector	on		override
maintenance_work_mem	65536	kB	configuration file
max_connections	300		configuration file
max_stack_depth	2048	kB	environment variable
max_standby_archive_delay	-1	ms	configuration file
max_standby_streaming_delay	-1	ms	configuration file
max_wal_senders	4		configuration file
pg_statsinfo.enable_maintenance	3		configuration file
pg_statsinfo.maintenance_time	04:00:00		configuration file
pg_statsinfo.long_lock_threashold	0		configuration file
pg_statsinfo.repository_server	host=172.20.144.30 port=5432 dbname=postgres user=postgres		configuration file
pg_statsinfo.snapshot_interval	60	s	configuration file
pg_statsinfo.textlog_line_prefix	%t %p %c-%l %x %q(%u, %d, %r, %a)		configuration file
random_page_cost	2		configuration file
replication_timeout	0	ms	configuration file
restart_after_crash	off		configuration file
shared_buffers	314624	8kB	configuration file
shared_preload_libraries	pg_statsinfo,pg_stat_statements		configuration file
standard_conforming_strings	off		configuration file
superuser_reserved_connections	10		configuration file
track_functions	all		configuration file
transaction_isolation	read committed		override
wal_buffers	2048	8kB	override
wal_keep_segments	64		configuration file
wal_level	hot_standby		configuration file
wal_receiver_status_interval	5	s	configuration file

How to see a Summary table.

instname : database cluster instance id
hostname : host server name
port : port of database cluster instance
pg_version : version of PostgreSQL
snap_begin : start point in this report
snap_end : end point in this report
duration : term in this report
total_dbsize : total database size
total_commits : total commit transactions in this database cluster
total_rollbacks : total rollback transactions in this database cluster

How to see a Database Statistics table.

database : database name
MiB : size of database (MiB)
+MiB : increase size of database (MiB)
commit/s : committed transactions per second
rollback/s : rollback transactions per second
hit% : cache hit's rate (shared_buffers hit's rate)
gets/s : read blocks from disk and disk cache per second
reads/s : read blocks from disk per second
rows/s : number of rows from tables and indexes per second

*The small value is displayed as zero by the truncation of the number in unit conversion

How to see a Transaction Statistics graph.

x axis : time
y axis : transactions per second (xact/s)
[database name] commit_tps : commited transactions of [database name] per second (xact/s)
[database name] rollback_tps : rollback transaction of [database name] per second (xact/s)

How to see a Database Size table.

x axis : time
y axis : size of database (Bytes)
[database name] size : size of [database name] (Bytes)

How to see a Recovery Conflicts table.

database : database name
conflict tblspc : number of query cancels by conflict with deleted table space
conflict lock : number of query cancels by conflict with database locks
conflict snapshot : number of query cancels by conflict with VACUUM in tuples
conflict bufferpin : number of query cancels by conflict with VACUUM in shared_buffers
conflict deadlock : number of query cancels by deadlocks

How to see a WAL Statistics(total) graph.

write_total : total size of WAL(xlog) data in this report (MiB)
write_speed : average size of WAL(xlog) data in this report (MiB)

How to seea WAL Statistics graphs.

x axis : time (snapshot time)
y axis1 (left) : size of WAL(xlog) data per snapshot (Bytes)
y axis2 (right) : size of WAL(xlog) data per second (Bytes/s)
write_size : size of WAL output / snapshot time (Bytes)
write_size_per_sec : size pf WAL output / second (Bytes/s)

How to see a Instance Processes Ratio table.

idle (%) : ratio of idle backend process (%)
idle in xact (%) : ratio of idle backend process in transaction (%)
waiting (%) : ratio of waiting backend process by locks (%)
running (%) : ratio of running backend process in transaction (%)

How to see a Instance Processes graph.

x axis : time
y axis : ratio (%)
idle : ratio of idle backend process (%)
idle in xact : ratio of idle backend process in transaction (%)
waiting : ratio of waiting backend process by locks (%)
running : ratio of running backend process in transaction (%)

How to see a CPU Usage graph.

x axis : time
y axis : ratio(%)
user : ratio of CPU usage in user mode (%)
system : ratio of CPU usage in system mode (%)
iowait : ratio of CPU usage in iowait (%)
idle : ratio of CPU usage in idle (%)

How to see a Load Average graph.

x axis : time
y axis : load average
1min : load average in one minute
5min : load average in five minutes
15min : load average in fifteen minutes

How to see a I/O Usage table/graph.

device_name : device name
Including TableSpaces : size of table space in a device
total read (MiB) : total read size in a device (MiB)
total write (MiB) : total write size in a device (MiB)
total read time (ms) : total read time in a device (ms)
total write time (ms) : total write time in a device (ms)
current I/O queue : number of current I/O queue in a device
total I/O time (ms) : total time of I/O in a device (ms)

*The small value is displayed as zero by the truncation of the number in unit conversion

How to see a I/O Size

x axis : time
y axis : database size (Bytes/s)
[device] read : read I/O size per second in a device (Bytes/s)
[device] write : write I/O size per second in a device (Bytes/s)

How to see a I/O Time

x axis : time
y axis : avarage time (sec)
[device] avg read time : time of read I/O in a device (sec)
[device] avg write time : time of write I/O in a device (sec)

How to see a memory Usage graph table.

x axis : time
y axis : size (Bytes)
memfree : size of free memory (Bytes)
buffers : size of buffers cached (Bytes)
cached : size of file cached(not include buffer cached) (Bytes)
swap : size of swap (Bytes)
dirty : size of dirty pages (Bytes)

How to see a Disk Usage per Tablespace table.

tablespace : name of tablespace
location : directory of tablespace
device : device number of directory of tablespace
used (MiB) : usage data size in table space directory device (MiB)
avail (MiB) : avalable data size in table space directory device (MiB)
remain (%) : remain data size in table space directory device

*The small value is displayed as zero by the truncation of the number in unit conversion

How to see a Disk Usage per Table table/graph.

database : database name
schema : schema name
table : table name
size (MiB) : size of table (MiB)
table reads : table_blocks_reads
index reads : index_blocks_reads
toast reads : TOAST_blocks_reads

*The small value is displayed as zero by the truncation of the number in unit conversion

How to see a Table Size table.

entry : table name [database name].[schema name].[table name])
value : table size ratio

How to see a Disk Read table.

entry : table name ([database name].[schema name].[table name])
value : amount of read all blocks (all tables, indexes, TOAST table and TOAST index)

How to see a Heavily Updated Tables table.

database : database name
schema : schema name
table : table name
INSERT : number of INSERT rows
UPDATE : number of UPDATE rows (include HOT update)
DELETE :number of DELETE rows
total : number of INSERT, UPDATE and DELETE rows
HOT (%) : ratio of HOT update rows per all table rows

How to see a Heavily Accessed Tables table.

database : database name
schema : schema name
table : table name
seq_scan : time of executing sequential scan
seq_tup_read : rows of sequential scan read
tup_per_seq : rows of sequential scan read per once
hit (%) : cache hit ratio

How to see a Low Density Tables table.

database : database name
schema : schama name
table : table name
tuples : tuples in the table
logical_pages : number of logical pages in all records
physical_pages : number of physical pages in all records
tratio : ratio of logical pages per physical pages

How to see a Fragmented Tables table.

database : database name
schema : schema name
table : table name
column : colum name
correlation : correlation of record sort in table

How to see a Functions table.

Database : database name
Schema : schema name
Function : function name
calls : time of function calls
total time (ms) : total execute time (ms)
self time (ms) : self execute time (ms)
time/call (ms) : total execute time per calls (ms)

How to see a Statements table.

User : user name which executed query
Database : database name which was executed query
query : SQL query
calls : number of executing query
total time (sec) : total executing time (sec)
time/call (sec) : average executing time (sec)

How to see a Long Transactions table.

pid : pid of backend process in long transaction
client address : client ip address of backend process in long transaction
when to start : start time of long transaction
duration (sec) : duration time of long transaction (sec)
query : query of long transaction

How to see a Lock Activity table.

database : database name which has lock waiting object
schema : schema name which has lock waiting object
relation : relation name which has lock waiting
duration : duration time which has lock waiting
blockee pid : blockee pid which has lock waiting
blocker pid : blocke pid which has lock
blocker gid : gid of blocker backend
blockee query : executing query which is lock wating in transaction
blocker query : query list which has lock in transaction

How to see a Checkpoint Activity table.

total checkpoints : number of total checkpoint
checkpoints by time : number of checkpoint executed by checkpoint_timeout
checkpoints by xlog : number of checkpoint executed by checkpoint segments
avg written buffers : average written buffer size
max written buffers : max written buffer size
avg duration (sec) : average time of checkpoint's duration (sec)
max duration (sec) : max time of checkpoint's duration (sec)

How to see a Basic Statistics. (Average)

database : database name
schema : schema name
table : table name
count : number of execute autovacuum
avg index scans : number of index scan
avg removed rows : avarage removed rows
avg remain rows : average remain rows
avg duration (sec) : average duration time (sec)
max duration (sec) : max duration time (sec)
cancels : number of cancel autovacuum (use pg_statsinfo 3.0 higher)

How to see a Vacuum Cancels.

timestamp : timestamp
database : database name
schema : schema name
table : table name
cause query : SQL query

How to see a I/O Statistics. (Average)

database : database name
schema : schema name
table : table name
avg page hit : average page hit
avg page miss : average page miss
avg page dirty : average page dirty
avg read rate : average read rate
avg write rate : average write rate

How to see a Analyze Statistics.

database : database name
schema : schema name
table : table name
count : number of execute autoanalyze
total duration (sec) : total duration time (sec)
avg duration (sec) : average duration time (sec)
max duration (sec) : max duration time (sec)
last analyze time : last execute time of autoanalyze (use pg_statsinfo 3.0 higher)

How to see a Current Replication Status table.

user : user name
appname : application name
client addr : IP address in standby
client_host : host name in standby
client_port : port number in standby
backend start : start time of walsender walsender
state : state of walsender
current location : current WAL location
sent location : sent WAL location
write location : write WAL location in standby (LSN or WAL file name)
flush location : flush WAL location in standby (LSN or WAL file name)
replay location : replay WAL location in standby (LSN or WAL file name)
sync priority : priority of synchronous replication
sync state : state of synchronous replication

How to see a Replication Delays

x axis : time
y axis : WAL delay (Bytes)
[address]:[port-number] flush_delay_size : WAL flush-delay between Master and Standby (Bytes)
* Synchronous standby is indicated by [sync]
[address]:[port-number] replay_delay_size : replay-delay between Master and Standby (Bytes)
* Synchronous standby is indicated by [sync]

How to see a Database table.

This information is not supported in this version. We will support in future version.

How to see a Schema table.

This information is not supported in this version. We will support in future version.

How to see a Table table.

database : database name
schema : schema name
table : table name
columns : number of colunms
rows : number of rows (use pg_statsinfo 3.0 higher)
MiB : table size (MiB)
+MiB : increase table size (MiB)
table scans : number of executing sequential scan
index scans : number of executing index scan

*The small value is displayed as zero by the truncation of the number in unit conversion

How to see a Index table.

database : database name
schema : schema name
index : index name
table : table name is refferd to index
MiB : index size (MiB)
+MiB : increase index size (MiB)
scans : number of executing index scans
rows/scan : returned rows which is executing once index scan
reads : read by disk blocks
hits : read by cache blocks
keys : value of index key

*The small value is displayed as zero by the truncation of the number in unit conversion

How to seea View table.

This information is not supported in this version. We will support in future version.

How to see a Sequence table.

This information is not supported in this version. We will support in future version.

How to see a Trigger table.

This information is not supported in this version. We will support in future version.

How to see a Role table.

This information is not supported in this version. We will support in future version.

How to see a Parameter table.

name : parameter name
setting : parameter value
unit : unit
source : setting parameter

How to see a Alert table.

timestamp : timestamp
message : alert message

How to change alert settings.

Update the alert setting table of the repository DB.
Please see the pg_statsinfo manual for details of the alert settings.

e.g. Change the threshold of Rollback per seconds to "3000".

UPDATE statsrepo.alert SET rollback_tps = 3000 WHERE instid = 1;

End of report

repository1

repository2

Summary

Alert

Statistics

Database Statistics

Transaction Statistics

Database Size

Recovery Conflicts

Instance Activity

WAL Statistics

Instance Processes Ratio

Instance Processes

OS

OS Resource Usage

CPU Usage

Load Average

I/O Usage

Memory Usage

Disk Usage

Disk Usage per Tablespace

Disk Usage per Table

SQL

Notable Tables

Heavily Updated Tables

Heavily Accessed Tables

Low Density Tables

Fragmented Tables

Query Activity

Functions

Statements

Long Transactions

Lock Conflicts

Activities

Checkpoint Activity

Autovacuum Activity

Basic Statistics (Average)

Vacuum Cancels

I/O Statistics (Average)

Analyze Statistics

Replication Activity

Current Replication Status

Replication Delays

Information

Schema Information

Table

Index

Setting Parameters

Parameter

How to see a Summary table.

How to see a Database Statistics table.

How to see a Transaction Statistics graph.

How to see a Database Size table.

How to see a Recovery Conflicts table.

How to see a WAL Statistics(total) graph.

How to seea WAL Statistics graphs.

How to see a Instance Processes Ratio table.

How to see a Instance Processes graph.

How to see a CPU Usage graph.

How to see a Load Average graph.

How to see a I/O Usage table/graph.

How to see a I/O Size

How to see a I/O Time

How to see a memory Usage graph table.

How to see a Disk Usage per Tablespace table.

How to see a Disk Usage per Table table/graph.

How to see a Table Size table.

How to see a Disk Read table.

How to see a Heavily Updated Tables table.

How to see a Heavily Accessed Tables table.

How to see a Low Density Tables table.

How to see a Fragmented Tables table.

How to see a Functions table.

How to see a Statements table.

How to see a Long Transactions table.

How to see a Lock Activity table.

How to see a Checkpoint Activity table.

How to see a Basic Statistics. (Average)

How to see a Vacuum Cancels.

How to see a I/O Statistics. (Average)