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
dbt2	37225	493	90.718	0.402	87.300	19156.911	2428.836	34584.184
postgres	6	0	0.397	0.000	99.700	42.990	0.135	124.872

Transaction Statistics

Transaction Statistics Timestamp Transaction per second (xact/s) 0 20 40 60 80 100 120 140 160 180 15:00 15:30 16:00	dbt2 commit_tps dbt2 rollback_tps postgres commit_tps postgres rollback_tps

Database Size

Database Size Timestamp Database Size (Bytes) 0 4G 8G 12G 16G 20G 24G 28G 32G 36G 15:00 15:30 16:00	dbt2 size postgres 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

WAL Statistics Timestamp Size (Bytes) Speed (Bytes/s) 0 512M 1G 1.5G 2G 2.5G 3G 0 15.5M 30.99M 46.49M 61.98M 77.48M 92.98M 15:00 15:30 16:00	write_size (Bytes) write_size_per_sec (Bytes/s)

Instance Processes Ratio

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

Instance Processes

Instance Processes Timestamp Percent (%) 0 20 40 60 80 100 15:00 15:30 16:00	idle idle in xact waiting running

OS

OS Resource Usage

CPU Usage

CPU Usage Timestamp Percent (%) 0 20 40 60 80 100 15:00 15:30 16:00	idle iowait system user

Load Average

Load Average Timestamp Load Average 0 10 20 30 40 50 15:00 15:30 16:00	1min 5min 15min

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

I/O Size Timestamp Data Size (Bytes/s) 0 32M 64M 96M 128M 160M 192M 224M 15:00 15:30 16:00	sda2 read sda2 write

I/O Time Timestamp Average Time (sec) 0 20 40 60 80 100 120 140 15:00 15:30 16:00	sda2 avg read time sda2 avg write time

Memory Usage

Memory Usage (Linear Scale) Timestamp Size (Bytes) 0 2G 4G 6G 8G 10G 12G 14G 16G 18G 15:00 15:30 16:00	memfree buffers cached swap dirty

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	history	871	225870	0
dbt2	public	orders	726	214078	312102
dbt2	public	new_order	139	48208	17869
dbt2	public	item	10	4473	551
dbt2	public	district	1	358	33
dbt2	public	warehouse	0	386	8

Table Size

	dbt2.public.stock
	dbt2.public.order_line
	dbt2.public.customer
	other

40%

33%

21%

Disk Read

	dbt2.public.stock
	dbt2.public.order_line
	dbt2.public.customer
	other

43%

26%

24%

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	order_line	99104524	1357597	1360806	99
dbt2	public	stock	33999996	1619048	1620003	99
dbt2	public	orders	10409447	96384	93001	103
dbt2	public	history	10401651	107234	111547	96
dbt2	public	customer	10199984	849999	863392	98
dbt2	public	new_order	3207611	17339	17804	97
dbt2	public	item	100000	1235	1281	96

Fragmented Tables

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

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	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	sample	1051	30.903	30.903	0.029
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	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
dbt2	public	district	61	0.230	1090.984	3580.721	2.594	23.420	1
dbt2	public	warehouse	61	0.066	1346.459	518.754	0.560	4.110	1

Vacuum Cancels

timestamp	database	schema	table	cause query
2013-06-07 15:37:13	dbt2	public	district	ANALYZE
2013-06-07 15:32:20	dbt2	public	warehouse	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	warehouse	188.607	3.148	0.738	2.549	0.802
dbt2	public	district	368.328	0.098	0.738	0.202	0.347
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	history	1	1.920	0.273	0.316	2013-06-07 15:43:32
dbt2	public	new_order	3	1.300	0.036	0.043	2013-06-07 15:16:39
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

Replication Delays Timestamp Delaying amount (Bytes) 0 128k 256k 384k 512k 640k 768k 896k 15:00 15:30 16:00	172.20.144.31:34691 flush_delay_size 172.20.144.31:34691 replay_delay_size [sync]172.20.144.30:39209 flush_delay_size [sync]172.20.144.30:39209 replay_delay_size

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

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