On 23 January 2012 02:08, Robert Haas <robertmh...@gmail.com> wrote: > On Sat, Jan 21, 2012 at 6:32 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: >> I'm finding the backend_writes column pretty unfortunate. The only >> use I know of for it is to determine if the bgwriter is lagging >> behind. Yet it doesn't serve even this purpose because it lumps >> together the backend writes due to lagging background writes, and the >> backend writes "by design" due to the use buffer access strategy >> during bulk inserts. > > +1 for separating those.
I decided to have a go myself. Attached patch breaks out strategy allocations in pg_stat_bgwriter, but not strategy writes. My thinking is that this may serve to approximate non-BAS_NORMAL writes, with the considerable advantage of not requiring that I work backwards to figure out strategy from some block when backend-local syncing (yes, syncing, not writing) a buffer to work out which strategy object references the buffer. The bookkeeping that that would likely entail seems to make it infeasible. Incidentally, it seems Postgres doesn't currently record backend writes when the buffer doesn't go on to be sync'd. That seems problematic to me, or at the very least a misrepresentation, since temporary tables will be written out by the backend for example. Not sure if it's worth fixing, though I've added a comment to that effect at the site of where backend_writes is bumped. I have corrected the md.c bug. This previously would have prevented the sync_files (number of relation segments synced) value from being valid in non-log_checkpoints configurations. I'm not currently confident that the strategy_alloc filed is a very useful proxy for a strategy_backend_writes field. I think that rather than bumping the strategy allocation count analogously to the way the overall count is bumped (within StrategyGetBuffer()), I should have bumped earlier within BufferAlloc() so that it'd count if the buffer was requested with non-BAS_NORMAL strategy but was found in shared_buffers (so control never got as far as StrategyGetBuffer() ). That might make the value more closely line-up to the would-be value of a strategy_backend_writes column. What do you think? -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml new file mode 100644 index 840e54a..f714cb8 *** a/doc/src/sgml/monitoring.sgml --- b/doc/src/sgml/monitoring.sgml *************** SELECT pg_stat_get_backend_pid(s.backend *** 806,811 **** --- 806,818 ---- the <function>pg_stat_get_buf_alloc</function> function.</entry> </row> <row> + <entry>buffers_strat_alloc</entry> + <entry><type>bigint</type></entry> + <entry>Number of buffers allocated with non-default buffer access strategy. + This value can also be returned by directly calling + the <function>pg_stat_get_buf_strat_alloc</function> function.</entry> + </row> + <row> <entry>stats_reset</entry> <entry><type>bigint</type></entry> <entry>The last time these statistics were reset. *************** SELECT pg_stat_get_backend_pid(s.backend *** 1703,1708 **** --- 1710,1741 ---- </entry> </row> + <row> + <entry><literal><function>pg_stat_get_bgwriter_write_time()</function></literal></entry> + <entry><type>bigint</type></entry> + <entry> + Total amount of time that has been spent in the part of checkpoint + processing where files are written to disk, in milliseconds. + </entry> + </row> + + <row> + <entry><literal><function>pg_stat_get_bgwriter_sync_time()</function></literal></entry> + <entry><type>bigint</type></entry> + <entry> + Total amount of time that has been spent in the part of checkpoint + processing where files are synchronized to disk, in milliseconds. + </entry> + </row> + + <row> + <entry><literal><function>pg_stat_get_bgwriter_sync_files()</function></literal></entry> + <entry><type>bigint</type></entry> + <entry> + Total number of files that have been synchronized to disk during + checkpoint processing. + </entry> + </row> <row> <entry><literal><function>pg_stat_get_wal_senders()</function></literal></entry> diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c new file mode 100644 index ff7f521..e481be3 *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *************** LogCheckpointStart(int flags, bool resta *** 7492,7501 **** } /* ! * Log end of a checkpoint. */ static void ! LogCheckpointEnd(bool restartpoint) { long write_secs, sync_secs, --- 7492,7501 ---- } /* ! * Time and potentially log the end of a checkpoint. */ static void ! TimeCheckpointEnd(bool restartpoint) { long write_secs, sync_secs, *************** LogCheckpointEnd(bool restartpoint) *** 7511,7519 **** CheckpointStats.ckpt_end_t = GetCurrentTimestamp(); - TimestampDifference(CheckpointStats.ckpt_start_t, - CheckpointStats.ckpt_end_t, - &total_secs, &total_usecs); TimestampDifference(CheckpointStats.ckpt_write_t, CheckpointStats.ckpt_sync_t, --- 7511,7516 ---- *************** LogCheckpointEnd(bool restartpoint) *** 7523,7528 **** --- 7520,7541 ---- CheckpointStats.ckpt_sync_end_t, &sync_secs, &sync_usecs); + /* Record checkpoint timing summary data. */ + BgWriterStats.m_write_time = write_secs * 1000 + write_usecs / 1000; + BgWriterStats.m_sync_time = sync_secs * 1000 + sync_usecs / 1000; + BgWriterStats.m_sync_files = CheckpointStats.ckpt_sync_rels; + + /* + * All of the published timing statistics are accounted for. Only + * continue if a log message is to be written. + */ + if (!log_checkpoints) + return; + + TimestampDifference(CheckpointStats.ckpt_start_t, + CheckpointStats.ckpt_end_t, + &total_secs, &total_usecs); + /* * Timing values returned from CheckpointStats are in microseconds. * Convert to the second plus microsecond form that TimestampDifference *************** CreateCheckPoint(int flags) *** 7971,7979 **** if (!RecoveryInProgress()) TruncateSUBTRANS(GetOldestXmin(true, false)); ! /* All real work is done, but log before releasing lock. */ ! if (log_checkpoints) ! LogCheckpointEnd(false); TRACE_POSTGRESQL_CHECKPOINT_DONE(CheckpointStats.ckpt_bufs_written, NBuffers, --- 7984,7994 ---- if (!RecoveryInProgress()) TruncateSUBTRANS(GetOldestXmin(true, false)); ! /* ! * All real work is done, but record timings and potentially log before ! * releasing lock. ! */ ! TimeCheckpointEnd(false); TRACE_POSTGRESQL_CHECKPOINT_DONE(CheckpointStats.ckpt_bufs_written, NBuffers, *************** CreateRestartPoint(int flags) *** 8237,8245 **** if (EnableHotStandby) TruncateSUBTRANS(GetOldestXmin(true, false)); ! /* All real work is done, but log before releasing lock. */ ! if (log_checkpoints) ! LogCheckpointEnd(true); xtime = GetLatestXTime(); ereport((log_checkpoints ? LOG : DEBUG2), --- 8252,8262 ---- if (EnableHotStandby) TruncateSUBTRANS(GetOldestXmin(true, false)); ! /* ! * All real work is done, but record timings and potentially log before ! * releasing lock. ! */ ! TimeCheckpointEnd(true); xtime = GetLatestXTime(); ereport((log_checkpoints ? LOG : DEBUG2), diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql new file mode 100644 index ab594eb..23e7566 *** a/src/backend/catalog/system_views.sql --- b/src/backend/catalog/system_views.sql *************** CREATE VIEW pg_stat_bgwriter AS *** 652,658 **** pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, ! pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; CREATE VIEW pg_user_mappings AS SELECT --- 652,662 ---- pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, ! pg_stat_get_buf_strat_alloc() AS buffers_strategy_alloc, ! pg_stat_get_bgwriter_stat_reset_time() AS stats_reset, ! pg_stat_get_bgwriter_write_time() AS write_time, ! pg_stat_get_bgwriter_sync_time() AS sync_time, ! pg_stat_get_bgwriter_sync_files() AS sync_files; CREATE VIEW pg_user_mappings AS SELECT diff --git a/src/backend/postmaster/checkpointer.c b/src/backend/postmaster/checkpointer.c new file mode 100644 index c9473f7..9bdabbe *** a/src/backend/postmaster/checkpointer.c --- b/src/backend/postmaster/checkpointer.c *************** ForwardFsyncRequest(RelFileNodeBackend r *** 1064,1070 **** LWLockAcquire(BgWriterCommLock, LW_EXCLUSIVE); ! /* Count all backend writes regardless of if they fit in the queue */ BgWriterShmem->num_backend_writes++; /* --- 1064,1074 ---- LWLockAcquire(BgWriterCommLock, LW_EXCLUSIVE); ! /* ! * Count all backend writes regardless of if they fit in the queue. ! * Technically this figure isn't quite correct, as some buffers will have ! * been written without needing to be synced, but it hardly matters. ! */ BgWriterShmem->num_backend_writes++; /* diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c new file mode 100644 index a53fc52..9e5c4d2 *** a/src/backend/postmaster/pgstat.c --- b/src/backend/postmaster/pgstat.c *************** pgstat_recv_bgwriter(PgStat_MsgBgWriter *** 4449,4454 **** --- 4449,4458 ---- globalStats.buf_written_backend += msg->m_buf_written_backend; globalStats.buf_fsync_backend += msg->m_buf_fsync_backend; globalStats.buf_alloc += msg->m_buf_alloc; + globalStats.buf_strat_alloc += msg->m_buf_strat_alloc; + globalStats.bgwriter_write += msg->m_write_time; + globalStats.bgwriter_sync += msg->m_sync_time; + globalStats.sync_files += msg->m_sync_files; } /* ---------- diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c new file mode 100644 index 3924a51..30d0f97 *** a/src/backend/storage/buffer/bufmgr.c --- b/src/backend/storage/buffer/bufmgr.c *************** BufferSync(int flags) *** 1250,1256 **** * Note that we don't read the buffer alloc count here --- that should be * left untouched till the next BgBufferSync() call. */ ! buf_id = StrategySyncStart(NULL, NULL); num_to_scan = NBuffers; num_written = 0; while (num_to_scan-- > 0) --- 1250,1256 ---- * Note that we don't read the buffer alloc count here --- that should be * left untouched till the next BgBufferSync() call. */ ! buf_id = StrategySyncStart(NULL, NULL, NULL); num_to_scan = NBuffers; num_written = 0; while (num_to_scan-- > 0) *************** BgBufferSync(void) *** 1327,1332 **** --- 1327,1333 ---- int strategy_buf_id; uint32 strategy_passes; uint32 recent_alloc; + uint32 recent_strat_alloc; /* * Information saved between calls so we can determine the strategy *************** BgBufferSync(void) *** 1364,1373 **** * Find out where the freelist clock sweep currently is, and how many * buffer allocations have happened since our last call. */ ! strategy_buf_id = StrategySyncStart(&strategy_passes, &recent_alloc); /* Report buffer alloc counts to pgstat */ BgWriterStats.m_buf_alloc += recent_alloc; /* * If we're not running the LRU scan, just stop after doing the stats --- 1365,1376 ---- * Find out where the freelist clock sweep currently is, and how many * buffer allocations have happened since our last call. */ ! strategy_buf_id = StrategySyncStart(&strategy_passes, &recent_alloc, ! &recent_strat_alloc); /* Report buffer alloc counts to pgstat */ BgWriterStats.m_buf_alloc += recent_alloc; + BgWriterStats.m_buf_strat_alloc += recent_strat_alloc; /* * If we're not running the LRU scan, just stop after doing the stats diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c new file mode 100644 index 3e62448..e37d83c *** a/src/backend/storage/buffer/freelist.c --- b/src/backend/storage/buffer/freelist.c *************** typedef struct *** 41,46 **** --- 41,47 ---- */ uint32 completePasses; /* Complete cycles of the clock sweep */ uint32 numBufferAllocs; /* Buffers allocated since last reset */ + uint32 numStratBufferAllocs; /* of which are non-BAS_NORMAL */ } BufferStrategyControl; /* Pointers to shared state */ *************** StrategyGetBuffer(BufferAccessStrategy s *** 134,139 **** --- 135,143 ---- */ StrategyControl->numBufferAllocs++; + if (strategy && strategy->btype != BAS_NORMAL) + StrategyControl->numStratBufferAllocs++; + /* * Try to get a buffer from the freelist. Note that the freeNext fields * are considered to be protected by the BufFreelistLock not the *************** StrategyFreeBuffer(volatile BufferDesc * *** 252,258 **** * being read. */ int ! StrategySyncStart(uint32 *complete_passes, uint32 *num_buf_alloc) { int result; --- 256,263 ---- * being read. */ int ! StrategySyncStart(uint32 *complete_passes, uint32 *num_buf_alloc, ! uint32 *num_buf_strat_alloc) { int result; *************** StrategySyncStart(uint32 *complete_passe *** 265,270 **** --- 270,280 ---- *num_buf_alloc = StrategyControl->numBufferAllocs; StrategyControl->numBufferAllocs = 0; } + if (num_buf_strat_alloc) + { + *num_buf_strat_alloc = StrategyControl->numStratBufferAllocs; + StrategyControl->numStratBufferAllocs= 0; + } LWLockRelease(BufFreelistLock); return result; } diff --git a/src/backend/storage/smgr/md.c b/src/backend/storage/smgr/md.c new file mode 100644 index bfc9f06..eeb20fd *** a/src/backend/storage/smgr/md.c --- b/src/backend/storage/smgr/md.c *************** mdsync(void) *** 1094,1120 **** entry->tag.segno * ((BlockNumber) RELSEG_SIZE), false, EXTENSION_RETURN_NULL); ! if (log_checkpoints) ! INSTR_TIME_SET_CURRENT(sync_start); ! else ! INSTR_TIME_SET_ZERO(sync_start); if (seg != NULL && FileSync(seg->mdfd_vfd) >= 0) { ! if (log_checkpoints && (!INSTR_TIME_IS_ZERO(sync_start))) ! { ! INSTR_TIME_SET_CURRENT(sync_end); ! sync_diff = sync_end; ! INSTR_TIME_SUBTRACT(sync_diff, sync_start); ! elapsed = INSTR_TIME_GET_MICROSEC(sync_diff); ! if (elapsed > longest) ! longest = elapsed; ! total_elapsed += elapsed; ! processed++; elog(DEBUG1, "checkpoint sync: number=%d file=%s time=%.3f msec", processed, FilePathName(seg->mdfd_vfd), (double) elapsed / 1000); - } break; /* success; break out of retry loop */ } --- 1094,1115 ---- entry->tag.segno * ((BlockNumber) RELSEG_SIZE), false, EXTENSION_RETURN_NULL); ! INSTR_TIME_SET_CURRENT(sync_start); if (seg != NULL && FileSync(seg->mdfd_vfd) >= 0) { ! INSTR_TIME_SET_CURRENT(sync_end); ! sync_diff = sync_end; ! INSTR_TIME_SUBTRACT(sync_diff, sync_start); ! elapsed = INSTR_TIME_GET_MICROSEC(sync_diff); ! if (elapsed > longest) ! longest = elapsed; ! total_elapsed += elapsed; ! processed++; ! if (log_checkpoints) elog(DEBUG1, "checkpoint sync: number=%d file=%s time=%.3f msec", processed, FilePathName(seg->mdfd_vfd), (double) elapsed / 1000); break; /* success; break out of retry loop */ } diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c new file mode 100644 index 68b2527..83b49af *** a/src/backend/utils/adt/pgstatfuncs.c --- b/src/backend/utils/adt/pgstatfuncs.c *************** extern Datum pg_stat_get_bgwriter_buf_wr *** 89,97 **** --- 89,101 ---- extern Datum pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS); extern Datum pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS); extern Datum pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_bgwriter_write_time(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_bgwriter_sync_time(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_bgwriter_sync_files(PG_FUNCTION_ARGS); extern Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS); extern Datum pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS); extern Datum pg_stat_get_buf_alloc(PG_FUNCTION_ARGS); + extern Datum pg_stat_get_buf_strat_alloc(PG_FUNCTION_ARGS); extern Datum pg_stat_get_xact_numscans(PG_FUNCTION_ARGS); extern Datum pg_stat_get_xact_tuples_returned(PG_FUNCTION_ARGS); *************** pg_stat_get_bgwriter_maxwritten_clean(PG *** 1388,1393 **** --- 1392,1415 ---- } Datum + pg_stat_get_bgwriter_write_time(PG_FUNCTION_ARGS) + { + PG_RETURN_INT64(pgstat_fetch_global()->bgwriter_write); + } + + Datum + pg_stat_get_bgwriter_sync_time(PG_FUNCTION_ARGS) + { + PG_RETURN_INT64(pgstat_fetch_global()->bgwriter_sync); + } + + Datum + pg_stat_get_bgwriter_sync_files(PG_FUNCTION_ARGS) + { + PG_RETURN_INT64(pgstat_fetch_global()->sync_files); + } + + Datum pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS) { PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp); *************** pg_stat_get_buf_alloc(PG_FUNCTION_ARGS) *** 1412,1417 **** --- 1434,1445 ---- } Datum + pg_stat_get_buf_strat_alloc(PG_FUNCTION_ARGS) + { + PG_RETURN_INT64(pgstat_fetch_global()->buf_strat_alloc); + } + + Datum pg_stat_get_xact_numscans(PG_FUNCTION_ARGS) { Oid relid = PG_GETARG_OID(0); diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h new file mode 100644 index 2db8489..8320f27 *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** DATA(insert OID = 2773 ( pg_stat_get_bgw *** 2674,2685 **** --- 2674,2693 ---- DESCR("statistics: number of times the bgwriter stopped processing when it had written too many buffers while cleaning"); DATA(insert OID = 3075 ( pg_stat_get_bgwriter_stat_reset_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 1184 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_stat_reset_time _null_ _null_ _null_ )); DESCR("statistics: last reset for the bgwriter"); + DATA(insert OID = 3160 ( pg_stat_get_bgwriter_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_write_time _null_ _null_ _null_ )); + DESCR("statistics: total amount of time the bgwriter has spent writing buffers to disk"); + DATA(insert OID = 3161 ( pg_stat_get_bgwriter_sync_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_sync_time _null_ _null_ _null_ )); + DESCR("statistics: total amount of time the bgwriter has spent syncing buffers to disk"); + DATA(insert OID = 3166 ( pg_stat_get_bgwriter_sync_files PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_sync_files _null_ _null_ _null_ )); + DESCR("statistics: total number of files the bgwriter has synced to disk"); DATA(insert OID = 2775 ( pg_stat_get_buf_written_backend PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_buf_written_backend _null_ _null_ _null_ )); DESCR("statistics: number of buffers written by backends"); DATA(insert OID = 3063 ( pg_stat_get_buf_fsync_backend PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_buf_fsync_backend _null_ _null_ _null_ )); DESCR("statistics: number of backend buffer writes that did their own fsync"); DATA(insert OID = 2859 ( pg_stat_get_buf_alloc PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_buf_alloc _null_ _null_ _null_ )); DESCR("statistics: number of buffer allocations"); + DATA(insert OID = 3167 ( pg_stat_get_buf_strat_alloc PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_buf_strat_alloc _null_ _null_ _null_ )); + DESCR("statistics: number of strategy buffer allocations"); DATA(insert OID = 2978 ( pg_stat_get_function_calls PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_function_calls _null_ _null_ _null_ )); DESCR("statistics: number of function calls"); diff --git a/src/include/pgstat.h b/src/include/pgstat.h new file mode 100644 index 1281bd8..161eea2 *** a/src/include/pgstat.h --- b/src/include/pgstat.h *************** typedef struct PgStat_MsgBgWriter *** 364,369 **** --- 364,373 ---- PgStat_Counter m_buf_written_backend; PgStat_Counter m_buf_fsync_backend; PgStat_Counter m_buf_alloc; + PgStat_Counter m_buf_strat_alloc; + PgStat_Counter m_write_time; + PgStat_Counter m_sync_time; + PgStat_Counter m_sync_files; } PgStat_MsgBgWriter; /* ---------- *************** typedef struct PgStat_GlobalStats *** 614,619 **** --- 618,628 ---- PgStat_Counter buf_written_backend; PgStat_Counter buf_fsync_backend; PgStat_Counter buf_alloc; + PgStat_Counter buf_strat_alloc; + PgStat_Counter bgwriter_write; /* times in milliseconds */ + PgStat_Counter bgwriter_sync; + + PgStat_Counter sync_files; TimestampTz stat_reset_timestamp; } PgStat_GlobalStats; diff --git a/src/include/storage/buf_internals.h b/src/include/storage/buf_internals.h new file mode 100644 index e43719e..9f73b30 *** a/src/include/storage/buf_internals.h --- b/src/include/storage/buf_internals.h *************** extern void StrategyFreeBuffer(volatile *** 187,193 **** extern bool StrategyRejectBuffer(BufferAccessStrategy strategy, volatile BufferDesc *buf); ! extern int StrategySyncStart(uint32 *complete_passes, uint32 *num_buf_alloc); extern Size StrategyShmemSize(void); extern void StrategyInitialize(bool init); --- 187,195 ---- extern bool StrategyRejectBuffer(BufferAccessStrategy strategy, volatile BufferDesc *buf); ! extern int StrategySyncStart(uint32 *complete_passes, uint32 *num_buf_alloc, ! uint32 *num_buf_strat_alloc); ! extern Size StrategyShmemSize(void); extern void StrategyInitialize(bool init); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out new file mode 100644 index aaf0cca..a0fd6e7 *** a/src/test/regress/expected/rules.out --- b/src/test/regress/expected/rules.out *************** SELECT viewname, definition FROM pg_view *** 1295,1301 **** pg_stat_activity | SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, s.state, s.query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, 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 FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, 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_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset; pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, 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_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d; pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, 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 FROM pg_database d; pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid)); --- 1295,1301 ---- pg_stat_activity | SELECT s.datid, d.datname, s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, s.xact_start, s.query_start, s.state_change, s.waiting, s.state, s.query FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, 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 FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, 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_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; ! pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_buf_strat_alloc() AS buffers_strategy_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset, pg_stat_get_bgwriter_write_time() AS write_time, pg_stat_get_bgwriter_sync_time() AS sync_time, pg_stat_get_bgwriter_sync_files() AS sync_files; pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, 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_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d; pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, 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 FROM pg_database d; pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers