[repository1]
pg-rex03:5432
begin:
2013-06-07 00:00
end:
2013-06-08 00:00



repository1

pg-rex03:5432

repository2

pg-rex01:5432


Summary

instname5886592150459083519
hostnamepg-rex03
port5432
pg_version9.2.4
snap_begin2013-06-07 14:34:00
snap_end2013-06-07 16:02:00
duration01:28:00
total_dbsize36 GiB
total_commits481097
total_rollbacks2122

Database System

Database Statistics

databaseMiB+MiBcommit/srollback/shit%gets/sreads/srows/s
postgres600.3970.00099.70042.9900.135124.872
dbt23722549390.7180.40287.30019156.9112428.83634584.184

Transaction Statistics

Database Size

Recovery Conflicts

databaseconflict tblspcconflict lockconflict snapshotconflict bufferpinconflict deadlock
postgres00000
dbt200000

Instance Activity

WAL Statistics

write_totalwrite_speed
46423.5428.792

Instance Processes Ratio

idle (%)idle in xact (%)waiting (%)running (%)
29.300.831.6068.27

Instance Processes

Operating System

OS Resource Usage

CPU Usage

Load Average

I/O Usage

device_nameincluding TableSpacestotal read (MiB)total write (MiB)total read time (ms)total write time (ms)current I/O queuetotal I/O time (ms)
sda2{pg_default,pg_global,<pg_xlog>,<pg_xlog_archive>}11915416623815175260523551725969.483387266975

Memory Usage

Disk Usage

Disk Usage per Tablespace

tablespacelocationdeviceused (MiB)avail (MiB)remain (%)
<pg_xlog>/pgdata/pgxlog_dbt2/pg_xlog8:2703979430657.258
<pg_xlog_archive>/pgdata/pgarch/arc_dbt28:2703979430657.258
pg_default/pgdata/data_dbt28:2703979430657.258
pg_global/pgdata/data_dbt28:2703979430657.258

Disk Usage per Table

databaseschematablesize (MiB)table readsindex readstoast reads
dbt2publicstock1265649327376172520
dbt2publicorder_line1063128514784624180
dbt2publiccustomer674525205386149090
dbt2publicorders7262140783121020
dbt2publichistory87122587000
dbt2publicnew_order13948208178690
dbt2publicitem1044735510
dbt2publicwarehouse038680
dbt2publicdistrict1358330

SQL

Notable Tables

Heavily Updated Tables

databaseschematableINSERTUPDATEDELETEtotalHOT (%)
dbt2publicorder_line222426918043240402859388.400
dbt2publicstock022242690222426999.900
dbt2publicorders212147189860040200799.900
dbt2publicnew_order21214701896744018210.000
dbt2publiccustomer0391546039154699.900
dbt2publicdistrict0218071021807198.400
dbt2publichistory201685002016850.000
dbt2publicwarehouse0201685020168599.400
dbt2publicitem00000.000

Heavily Accessed Tables

databaseschematableseq_scanseq_tup_readtup_per_seqhit (%)
dbt2publicorder_line19931556299315562.00085.600
dbt2publicorders11041714310417143.00086.000
dbt2publichistory11040173910401739.00065.400
dbt2publiccustomer11037573710375737.00063.400
dbt2publicnew_order131914013191401.00097.700
dbt2publicitem1100000100000.00099.900
dbt2publicdistrict134003400.000100.000

Low Density Tables

databaseschematabletupleslogical_pagesphysical_pagestratio
dbt2publichistory1040165110723411154796
dbt2publicitem1000001235128196
dbt2publicnew_order3207611173391780497
dbt2publiccustomer1019998484999986339298
dbt2publicstock339999961619048162000399
dbt2publicorder_line991045241357597136080699
dbt2publicorders104094479638493001103

Fragmented Tables

databaseschematablecolumncorrelation
dbt2publicstocks_i_id-0.039
dbt2publiccustomerc_first-0.015
dbt2publiccustomerc_id-0.010
dbt2publiccustomerc_id-0.010
dbt2publiccustomerc_last0.005
dbt2publicwarehousew_id0.011
dbt2publicorderso_c_id0.014
dbt2publicorderso_id0.018
dbt2publicorder_lineol_o_id0.020
dbt2publicdistrictd_w_id0.051
dbt2publicorder_lineol_d_id0.055
dbt2publicdistrictd_id0.071
dbt2publicnew_orderno_d_id0.092
dbt2publicorder_lineol_number0.099
dbt2publicorderso_d_id0.101
dbt2publicorderso_d_id0.101
dbt2publiccustomerc_d_id0.103
dbt2publiccustomerc_d_id0.103
dbt2publicnew_orderno_o_id0.147
dbt2publicnew_orderno_w_id0.904

Query Activity

Functions

databaseschemafunctioncallstotal time (ms)self time (ms)time/call (ms)
dbt2publicpgstattuple7170988.685170988.68524426.955
postgresstatsinfodevicestats885436.7675418.84861.781
postgresstatsinfosample105130.90330.9030.029
postgresstatsinfocpustats8827.72227.7220.315
postgresstatsinfotablespaces17620.87720.8770.119
postgresstatsinfomemory883.2793.2790.037
postgresstatsinfoloadavg882.5192.5190.029
postgresstatsinfoactivity880.9070.9070.010
postgresstatsinfoprofile880.8170.8170.009

Statements

userdatabasequerycallstotal time (sec)time/call (sec)
postgresdbt2FETCH ALL IN mycursor107154457.8190.416
postgresdbt2SELECT payment(?, ?, ?, ?, ?, ?, ?)99401494.0990.150
postgresdbt2SELECT * FROM order_status(?, ?, ?, ?)9211439.5371.563
postgresdbt2SELECT delivery(?, ?)938933.2910.995
postgresdbt2SELECT stock_level(?, ?, ?)946669.4610.708
postgrespostgresSELECT 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_byte88282.2643.208
postgresdbt2SELECT 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_bl88198.9912.261
postgresdbt2 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 ; 87175.8552.021
postgresdbt2 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 ;87174.3472.004
postgresdbt2 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 ; 87172.7211.985
postgrespostgresSELECT 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[])88144.3391.640
postgrespostgresSELECT 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_replication884.4450.051
postgrespostgresSELECT * FROM statsinfo.cpustats($1)883.4380.039
postgresdbt2SELECT 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[])882.9470.033
postgresdbt2 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 ;882.9130.033
postgrespostgresSELECT * FROM statsinfo.devicestats($1)882.6890.031
postgresdbt2 SELECT EXTRACT(? FROM TRANSACTION_TIMESTAMP()), relname, pid, mode, granted FROM pg_locks, pg_class WHERE relfilenode = relation ORDER BY relname; ;882.2590.026
postgrespostgresSELECT 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 881.4660.017
postgrespostgresSELECT * FROM statsinfo.activity()881.1260.013
postgrespostgresSELECT name, setting, unit, source FROM pg_settings WHERE source NOT IN (?, ?, ?) AND setting <> boot_val880.9600.011
postgrespostgresSELECT 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 $2880.9300.011
postgrespostgresSELECT * FROM statsinfo.loadavg()880.5460.006
postgresdbt2SELECT 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[])880.4390.005
postgresdbt2SELECT 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[])880.1730.002
postgrespostgresSELECT statsinfo.sample()9870.1250.000
postgresdbt2CLOSE mycursor107150.1080.000
postgrespostgresSELECT 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[])2990.0960.000
postgresdbt2BEGIN235490.0590.000
postgresdbt2COMMIT234340.0370.000
postgrespostgresSELECT 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_bl880.0350.000
pidclient addresswhen to startduration (sec)query
15200172.20.144.582013-06-07 14:38:38621.675VACUUM FREEZE VERBOSE ANALYZE;
4562172.20.144.582013-06-07 15:57:43106.851SELECT now(),* FROM pgstattuple('customer');
323422013-06-07 15:38:2693.753autovacuum: ANALYZE public.new_order
4643172.20.144.582013-06-07 16:00:3485.920SELECT now(),* FROM pgstattuple('stock');
4619172.20.144.582013-06-07 15:59:3654.508SELECT now(),* FROM pgstattuple('order_line');
19966172.20.144.582013-06-07 15:03:3410.968SELECT 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
19879172.20.144.582013-06-07 15:01:5010.091COMMIT
20256172.20.144.582013-06-07 15:09:3010.024SELECT 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
19877172.20.144.582013-06-07 15:05:419.457COMMIT
20251172.20.144.582013-06-07 15:10:019.176SELECT 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
19832172.20.144.582013-06-07 15:16:019.017COMMIT
20208172.20.144.582013-06-07 15:07:078.266SELECT 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
20209172.20.144.582013-06-07 15:45:478.234SELECT o_c_id FROM orders WHERE o_id = 2149 AND o_w_id = 6 AND o_d_id = 10
19830172.20.144.582013-06-07 14:58:578.166COMMIT
20170172.20.144.582013-06-07 14:57:228.106COMMIT
20078172.20.144.582013-06-07 15:19:427.839COMMIT
19921172.20.144.582013-06-07 15:40:327.615COMMIT
19919172.20.144.582013-06-07 14:55:177.588COMMIT
19837172.20.144.582013-06-07 15:25:237.494COMMIT
19962172.20.144.582013-06-07 15:23:387.186COMMIT
19970172.20.144.582013-06-07 14:58:437.140SELECT 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
20014172.20.144.582013-06-07 15:13:187.119SELECT 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
19926172.20.144.582013-06-07 15:48:187.055COMMIT
20014172.20.144.582013-06-07 15:02:387.011SELECT 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
19880172.20.144.582013-06-07 15:42:036.971COMMIT
19917172.20.144.582013-06-07 14:54:386.874SELECT 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
20167172.20.144.582013-06-07 15:15:186.849COMMIT
20057172.20.144.582013-06-07 15:51:136.820COMMIT
19918172.20.144.582013-06-07 15:20:086.707COMMIT
20210172.20.144.582013-06-07 15:32:096.539SELECT 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
20123172.20.144.582013-06-07 14:52:296.016UPDATE orders SET o_carrier_id = 5 WHERE o_id = 2101 AND o_w_id = 102 AND o_d_id= 1
20212172.20.144.582013-06-07 15:33:545.996SELECT 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
20167172.20.144.582013-06-07 15:12:495.910COMMIT
20077172.20.144.582013-06-07 15:36:145.904SELECT 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
20122172.20.144.582013-06-07 15:08:245.902SELECT 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
20126172.20.144.582013-06-07 15:49:095.778SELECT 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
19921172.20.144.582013-06-07 15:28:595.707COMMIT
19921172.20.144.582013-06-07 15:47:455.591COMMIT
20167172.20.144.582013-06-07 15:43:205.418SELECT 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
20009172.20.144.582013-06-07 15:11:555.217COMMIT
19829172.20.144.582013-06-07 15:28:455.191COMMIT
19969172.20.144.582013-06-07 15:27:354.969COMMIT
20164172.20.144.582013-06-07 15:37:404.963COMMIT
20124172.20.144.582013-06-07 15:35:454.854COMMIT
20212172.20.144.582013-06-07 15:41:354.821COMMIT
19879172.20.144.582013-06-07 15:06:404.812COMMIT
20060172.20.144.582013-06-07 15:30:504.605COMMIT
19926172.20.144.582013-06-07 15:52:114.368COMMIT
19961172.20.144.582013-06-07 15:18:214.368COMMIT
19919172.20.144.582013-06-07 14:56:314.145COMMIT
19966172.20.144.582013-06-07 15:46:014.050COMMIT
19917172.20.144.582013-06-07 15:22:113.913COMMIT
20167172.20.144.582013-06-07 14:53:213.857COMMIT
20211172.20.144.582013-06-07 15:34:123.565COMMIT
20080172.20.144.582013-06-07 15:31:073.413SELECT 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
19965172.20.144.582013-06-07 15:26:432.587COMMIT
19967172.20.144.582013-06-07 15:00:182.349SELECT 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
19793172.20.144.582013-06-07 15:21:382.297COMMIT
19965172.20.144.582013-06-07 15:04:031.939COMMIT
20012172.20.144.582013-06-07 15:50:281.932SELECT 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
20124172.20.144.582013-06-07 15:44:481.873COMMIT
19920172.20.144.582013-06-07 15:24:331.819COMMIT
20207172.20.144.582013-06-07 15:14:441.557COMMIT
20253172.20.144.582013-06-07 15:17:491.448SELECT o_c_id FROM orders WHERE o_id = 2125 AND o_w_id = 71 AND o_d_id = 10

Lock Conflicts

databaseschemarelationdurationblockee pidblocker pidblocker gidblockee queryblocker query
dbt200:00:021553715527SELECT 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;
dbt200:00:001553715539FETCH ALL IN mycursorDECLARE 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...;
dbt200:00:001552715535FETCH ALL IN mycursorDECLARE 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...;

Operation Activity

Checkpoint Activity

total checkpoints10
checkpoints by time4
checkpoints by xlog6
avg written buffers40824.700
max written buffers135646.000
avg duration (sec)228.980
max duration (sec)551.813

Autovacuum Activity

Basic Statistics (Average)

databaseschematablecountavg index scansavg removed rowsavg remain rowsavg duration (sec)max duration (sec)
dbt2publicwarehouse610.0661346.459518.7540.5604.110
dbt2publicdistrict610.2301090.9843580.7212.59423.420
dbt2pg_catalogpg_statistic11.000120.000475.0000.0200.020
dbt2pg_toastpg_toast_261911.00069.0003.0000.0000.000

I/O Statistics (Average)

databaseschematableavg page hitavg page missavg page dirtyavg read rateavg write rate
dbt2publicdistrict368.3280.0980.7380.2020.347
dbt2publicwarehouse188.6073.1480.7382.5490.802
dbt2pg_catalogpg_statistic110.0000.00029.0000.00010.786
dbt2pg_toastpg_toast_261979.0000.00013.0000.000336.300

Replication Activity

Current Replication Status

userpostgrespostgres
appnamesbysby
client addr172.20.144.30172.20.144.31
client_host
client_port3920934691
backend start2013-06-07 14:34:002013-06-07 14:34:00
statestreamingstreaming
current location110/27BBE000 (000000010000011000000027)110/27BBE000 (000000010000011000000027)
sent location110/27BBE000 (000000010000011000000027)110/27BBE000 (000000010000011000000027)
write location110/27BBE000 (000000010000011000000027)110/27BBE000 (000000010000011000000027)
flush location110/27BBE000 (000000010000011000000027)110/27BBE000 (000000010000011000000027)
replay location110/27BAD7E0 (000000010000011000000027)110/27BAD7E0 (000000010000011000000027)
sync priority11
sync statesyncpotential

Replication Delays

Information

Schema Information

Table

databaseschematablecolumnsrow widthMiB+MiBtable scansindex scans
dbt2publiccustomer21557674501956265
dbt2publicdistrict1189111980850
dbt2publichistory8508711710
dbt2publicitem57010012226383
dbt2publicnew_order31213991379720
dbt2publicorder_line1065106312601417266
dbt2publicorders836726141398550
dbt2publicstock1731212656008310679
dbt2publicwarehouse985000615521

Index

databaseschemaindextableMiB+MiBscansrows/scanreadshitskeys
dbt2publici_customercustomer59101320552.418213787403871c_w_id, c_d_id, c_last, c_first, c_id
dbt2publici_ordersorders3060188301.043229885503202o_w_id, o_d_id, o_c_id
dbt2publicpk_customercustomer30608242101.0004011222117999c_w_id, c_d_id, c_id
dbt2publicpk_districtdistrict009808500.792331978580d_w_id, d_id
dbt2publicpk_itemitem2022263830.9995514463141i_id
dbt2publicpk_new_ordernew_order9643797200.988178691797056no_w_id, no_d_id, no_o_id
dbt2publicpk_order_lineorder_line306214741726618.33646241811402110ol_w_id, ol_d_id, ol_o_id, ol_number
dbt2publicpk_ordersorders333263797201.000822171746547o_w_id, o_d_id, o_id
dbt2publicpk_stockstock728083106791.00061725232768906s_w_id, s_i_id
dbt2publicpk_warehousewarehouse006155211.00081241160w_id

Setting Parameters

Parameter

namesettingunitsource
TimeZoneJapanconfiguration file
archive_command/bin/cp %p /pgdata/pgarch/arc_dbt2/%fconfiguration file
archive_modeonconfiguration file
autovacuum_freeze_max_age2000000000configuration file
autovacuum_max_workers4configuration file
autovacuum_vacuum_cost_limit400configuration file
checkpoint_completion_target0.7configuration file
checkpoint_segments300configuration file
checkpoint_timeout900sconfiguration file
default_statistics_target10configuration file
default_text_search_configpg_catalog.englishconfiguration file
effective_cache_size11796488kBconfiguration file
hot_standbyonconfiguration file
hot_standby_feedbackonconfiguration file
lc_messagesCconfiguration file
listen_addresses*configuration file
log_autovacuum_min_duration0msconfiguration file
log_checkpointsonconfiguration file
log_destinationcsvlogoverride
log_error_verbosityverboseconfiguration file
log_line_prefix%a:%t configuration file
log_lock_waitsonconfiguration file
log_min_duration_statement10000msconfiguration file
log_min_messageslogconfiguration file
log_rotation_size0kBconfiguration file
log_timezoneJapanconfiguration file
logging_collectoronoverride
maintenance_work_mem65536kBconfiguration file
max_connections300configuration file
max_stack_depth2048kBenvironment variable
max_standby_archive_delay-1msconfiguration file
max_standby_streaming_delay-1msconfiguration file
max_wal_senders4configuration file
pg_statsinfo.enable_maintenance3configuration file
pg_statsinfo.maintenance_time04:00:00configuration file
pg_statsinfo.long_lock_threashold0configuration file
pg_statsinfo.repository_serverhost=172.20.144.30 port=5432 dbname=postgres user=postgresconfiguration file
pg_statsinfo.snapshot_interval60sconfiguration file
pg_statsinfo.textlog_line_prefix%t %p %c-%l %x %q(%u, %d, %r, %a) configuration file
random_page_cost2configuration file
replication_timeout0msconfiguration file
restart_after_crashoffconfiguration file
shared_buffers3146248kBconfiguration file
shared_preload_librariespg_statsinfo,pg_stat_statementsconfiguration file
standard_conforming_stringsoffconfiguration file
superuser_reserved_connections10configuration file
track_functionsallconfiguration file
transaction_isolationread committedoverride
wal_buffers20488kBoverride
wal_keep_segments64configuration file
wal_levelhot_standbyconfiguration file
wal_receiver_status_interval5sconfiguration file

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 I/O Statistics. (Average)

How to see a Current Replication Status table.

How to see a Replication Delays

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.

How to see a Index table.

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.

How to see a Profiles table.