On Tue, Dec 17, 2024 at 09:35:37AM +0000, Bertrand Drouvot wrote: > Agree, we may need to add parameters to it but we'll see when the time comes.
Another thing that's been itching me: the loop in pg_stat_get_backend_io() that exists as well in pg_stat_get_io() looks worth refactoring in a single routine. The only difference between both is the reset timestamp, still both of them can pass a value for it depending on their stats kind entry. This shaves a bit more code in your own patch, even if the check on pgstat_tracks_io_bktype() and the Assert are not in the inner routine that fills the tuplestore with the I/O data. See pg_stat_fill_io_data() in v10-0002. If you have a better name for this routine, feel free.. What do you think about this refactoring? This should come in first, of course, so as the final patch introducing the backend stats is easier to parse. -- Michael
From 75a2549543edf418b6e282acab3f8ce272c5c42a Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 28 Oct 2024 12:50:32 +0000
Subject: [PATCH v10 1/2] per backend I/O statistics
While pg_stat_io provides cluster-wide I/O statistics, this commit adds the
ability to track and display per backend I/O statistics.
It adds a new statistics kind and 2 new functions:
- pg_stat_reset_backend_stats() to be able to reset the stats for a given
backend pid.
- pg_stat_get_backend_io() to retrieve I/O statistics for a given backend pid.
The new KIND is named PGSTAT_KIND_BACKEND as it could be used in the future
to store other statistics (than the I/O ones) per backend. The new KIND is
a variable-numbered one and has an automatic cap on the maximum number of
entries (as its hash key contains the proc number).
There is no need to write the per backend I/O stats to disk (no point to
see stats for backends that do not exist anymore after a re-start), so using
"write_to_file = false".
Note that per backend I/O statistics are not collected for the checkpointer,
the background writer, the startup process and the autovacuum launcher as those
are already visible in pg_stat_io and there is only one of those.
XXX: Bump catalog version needs to be done.
---
src/include/catalog/pg_proc.dat | 14 ++
src/include/pgstat.h | 31 +++-
src/include/utils/pgstat_internal.h | 14 ++
src/backend/catalog/system_functions.sql | 2 +
src/backend/utils/activity/Makefile | 1 +
src/backend/utils/activity/backend_status.c | 4 +
src/backend/utils/activity/meson.build | 1 +
src/backend/utils/activity/pgstat.c | 21 +++
src/backend/utils/activity/pgstat_backend.c | 182 +++++++++++++++++++
src/backend/utils/activity/pgstat_io.c | 25 ++-
src/backend/utils/activity/pgstat_relation.c | 2 +
src/backend/utils/adt/pgstatfuncs.c | 169 +++++++++++++++++
src/test/regress/expected/stats.out | 72 +++++++-
src/test/regress/sql/stats.sql | 38 +++-
doc/src/sgml/config.sgml | 8 +-
doc/src/sgml/monitoring.sgml | 37 ++++
src/tools/pgindent/typedefs.list | 3 +
17 files changed, 604 insertions(+), 20 deletions(-)
create mode 100644 src/backend/utils/activity/pgstat_backend.c
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0f22c21723..437157ffa3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5913,6 +5913,15 @@
proargnames => '{backend_type,object,context,reads,read_time,writes,write_time,writebacks,writeback_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',
prosrc => 'pg_stat_get_io' },
+{ oid => '8806', descr => 'statistics: backend IO statistics',
+ proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => 'int4',
+ proallargtypes => '{int4,text,text,text,int8,float8,int8,float8,int8,float8,int8,float8,int8,int8,int8,int8,int8,float8,timestamptz}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{backend_pid,backend_type,object,context,reads,read_time,writes,write_time,writebacks,writeback_time,extends,extend_time,op_bytes,hits,evictions,reuses,fsyncs,fsync_time,stats_reset}',
+ prosrc => 'pg_stat_get_backend_io' },
+
{ oid => '1136', descr => 'statistics: information about WAL activity',
proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
proparallel => 'r', prorettype => 'record', proargtypes => '',
@@ -6052,6 +6061,11 @@
proname => 'pg_stat_reset_single_function_counters', provolatile => 'v',
prorettype => 'void', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_function_counters' },
+{ oid => '9987',
+ descr => 'statistics: reset statistics for a single backend',
+ proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
+ prorettype => 'void', proargtypes => 'int4',
+ prosrc => 'pg_stat_reset_backend_stats' },
{ oid => '2307',
descr => 'statistics: reset collected statistics for a single SLRU',
proname => 'pg_stat_reset_slru', proisstrict => 'f', provolatile => 'v',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ebfeef2f46..479773cfd2 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -49,14 +49,15 @@
#define PGSTAT_KIND_FUNCTION 3 /* per-function statistics */
#define PGSTAT_KIND_REPLSLOT 4 /* per-slot statistics */
#define PGSTAT_KIND_SUBSCRIPTION 5 /* per-subscription statistics */
+#define PGSTAT_KIND_BACKEND 6 /* per-backend statistics */
/* stats for fixed-numbered objects */
-#define PGSTAT_KIND_ARCHIVER 6
-#define PGSTAT_KIND_BGWRITER 7
-#define PGSTAT_KIND_CHECKPOINTER 8
-#define PGSTAT_KIND_IO 9
-#define PGSTAT_KIND_SLRU 10
-#define PGSTAT_KIND_WAL 11
+#define PGSTAT_KIND_ARCHIVER 7
+#define PGSTAT_KIND_BGWRITER 8
+#define PGSTAT_KIND_CHECKPOINTER 9
+#define PGSTAT_KIND_IO 10
+#define PGSTAT_KIND_SLRU 11
+#define PGSTAT_KIND_WAL 12
#define PGSTAT_KIND_BUILTIN_MIN PGSTAT_KIND_DATABASE
#define PGSTAT_KIND_BUILTIN_MAX PGSTAT_KIND_WAL
@@ -362,12 +363,23 @@ typedef struct PgStat_BktypeIO
PgStat_Counter times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
} PgStat_BktypeIO;
+typedef struct PgStat_BackendPendingIO
+{
+ PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
+ instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
+} PgStat_BackendPendingIO;
+
typedef struct PgStat_IO
{
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
+typedef struct PgStat_Backend
+{
+ TimestampTz stat_reset_timestamp;
+ PgStat_BktypeIO stats;
+} PgStat_Backend;
typedef struct PgStat_StatDBEntry
{
@@ -549,6 +561,13 @@ extern bool pgstat_have_entry(PgStat_Kind kind, Oid dboid, uint64 objid);
extern void pgstat_report_archiver(const char *xlog, bool failed);
extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
+/*
+ * Functions in pgstat_backend.c
+ */
+
+extern PgStat_Backend *pgstat_fetch_proc_stat_io(ProcNumber procNumber);
+extern bool pgstat_tracks_backend_bktype(BackendType bktype);
+extern void pgstat_create_backend_stat(ProcNumber procnum);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 7338bc1e28..811ed9b005 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -450,6 +450,11 @@ typedef struct PgStatShared_ReplSlot
PgStat_StatReplSlotEntry stats;
} PgStatShared_ReplSlot;
+typedef struct PgStatShared_Backend
+{
+ PgStatShared_Common header;
+ PgStat_Backend stats;
+} PgStatShared_Backend;
/*
* Central shared memory entry for the cumulative stats system.
@@ -604,6 +609,15 @@ extern void pgstat_archiver_init_shmem_cb(void *stats);
extern void pgstat_archiver_reset_all_cb(TimestampTz ts);
extern void pgstat_archiver_snapshot_cb(void);
+/*
+ * Functions in pgstat_backend.c
+ */
+
+extern void pgstat_flush_backend(bool nowait);
+
+extern PgStat_BackendPendingIO *pgstat_prep_backend_pending(ProcNumber procnum);
+extern bool pgstat_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
+extern void pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index c51dfca802..14eb99cd47 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -711,6 +711,8 @@ REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_table_counters(oid) FROM public;
REVOKE EXECUTE ON FUNCTION pg_stat_reset_single_function_counters(oid) FROM public;
+REVOKE EXECUTE ON FUNCTION pg_stat_reset_backend_stats(integer) FROM public;
+
REVOKE EXECUTE ON FUNCTION pg_stat_reset_replication_slot(text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_stat_have_stats(text, oid, int8) FROM public;
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index b9fd66ea17..24b64a2742 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -20,6 +20,7 @@ OBJS = \
backend_status.o \
pgstat.o \
pgstat_archiver.o \
+ pgstat_backend.o \
pgstat_bgwriter.o \
pgstat_checkpointer.o \
pgstat_database.o \
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 22c6dc378c..e9c3b87ac2 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -426,6 +426,10 @@ pgstat_bestart(void)
PGSTAT_END_WRITE_ACTIVITY(vbeentry);
+ /* Create the backend statistics entry */
+ if (pgstat_tracks_backend_bktype(MyBackendType))
+ pgstat_create_backend_stat(MyProcNumber);
+
/* Update app name to current GUC setting */
if (application_name)
pgstat_report_appname(application_name);
diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build
index f73c22905c..380d3dd70c 100644
--- a/src/backend/utils/activity/meson.build
+++ b/src/backend/utils/activity/meson.build
@@ -5,6 +5,7 @@ backend_sources += files(
'backend_status.c',
'pgstat.c',
'pgstat_archiver.c',
+ 'pgstat_backend.c',
'pgstat_bgwriter.c',
'pgstat_checkpointer.c',
'pgstat_database.c',
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index b4e357c8a4..b72c779b2c 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -77,6 +77,7 @@
*
* Each statistics kind is handled in a dedicated file:
* - pgstat_archiver.c
+ * - pgstat_backend.c
* - pgstat_bgwriter.c
* - pgstat_checkpointer.c
* - pgstat_database.c
@@ -358,6 +359,22 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.reset_timestamp_cb = pgstat_subscription_reset_timestamp_cb,
},
+ [PGSTAT_KIND_BACKEND] = {
+ .name = "backend",
+
+ .fixed_amount = false,
+ .write_to_file = false,
+
+ .accessed_across_databases = true,
+
+ .shared_size = sizeof(PgStatShared_Backend),
+ .shared_data_off = offsetof(PgStatShared_Backend, stats),
+ .shared_data_len = sizeof(((PgStatShared_Backend *) 0)->stats),
+ .pending_size = sizeof(PgStat_BackendPendingIO),
+
+ .flush_pending_cb = pgstat_backend_flush_cb,
+ .reset_timestamp_cb = pgstat_backend_reset_timestamp_cb,
+ },
/* stats for fixed-numbered (mostly 1) objects */
@@ -602,6 +619,10 @@ pgstat_shutdown_hook(int code, Datum arg)
Assert(dlist_is_empty(&pgStatPending));
dlist_init(&pgStatPending);
+ /* drop the backend stats entry */
+ if (!pgstat_drop_entry(PGSTAT_KIND_BACKEND, InvalidOid, MyProcNumber))
+ pgstat_request_entry_refs_gc();
+
pgstat_detach_shmem();
#ifdef USE_ASSERT_CHECKING
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
new file mode 100644
index 0000000000..e2d83024c2
--- /dev/null
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -0,0 +1,182 @@
+/* -------------------------------------------------------------------------
+ *
+ * pgstat_backend.c
+ * Implementation of backend statistics.
+ *
+ * This file contains the implementation of backend statistics. It is kept
+ * separate from pgstat.c to enforce the line between the statistics access /
+ * storage implementation and the details about individual types of statistics.
+ *
+ * Copyright (c) 2024, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ * src/backend/utils/activity/pgstat_backend.c
+ * -------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "utils/pgstat_internal.h"
+
+/*
+ * Returns backend's IO stats.
+ */
+PgStat_Backend *
+pgstat_fetch_proc_stat_io(ProcNumber procNumber)
+{
+ PgStat_Backend *backend_entry;
+
+ backend_entry = (PgStat_Backend *) pgstat_fetch_entry(PGSTAT_KIND_BACKEND,
+ InvalidOid, procNumber);
+
+ return backend_entry;
+}
+
+/*
+ * Flush out locally pending backend statistics
+ *
+ * If no stats have been recorded, this function returns false.
+ */
+bool
+pgstat_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ PgStatShared_Backend *shbackendioent;
+ PgStat_BackendPendingIO *pendingent;
+ PgStat_BktypeIO *bktype_shstats;
+
+ if (!pgstat_lock_entry(entry_ref, nowait))
+ return false;
+
+ shbackendioent = (PgStatShared_Backend *) entry_ref->shared_stats;
+ bktype_shstats = &shbackendioent->stats.stats;
+ pendingent = (PgStat_BackendPendingIO *) entry_ref->pending;
+
+ for (int io_object = 0; io_object < IOOBJECT_NUM_TYPES; io_object++)
+ {
+ for (int io_context = 0; io_context < IOCONTEXT_NUM_TYPES; io_context++)
+ {
+ for (int io_op = 0; io_op < IOOP_NUM_TYPES; io_op++)
+ {
+ instr_time time;
+
+ bktype_shstats->counts[io_object][io_context][io_op] +=
+ pendingent->counts[io_object][io_context][io_op];
+
+ time = pendingent->pending_times[io_object][io_context][io_op];
+
+ bktype_shstats->times[io_object][io_context][io_op] +=
+ INSTR_TIME_GET_MICROSEC(time);
+ }
+ }
+ }
+
+ pgstat_unlock_entry(entry_ref);
+
+ return true;
+}
+
+/*
+ * Simpler wrapper of pgstat_backend_flush_cb()
+ */
+void
+pgstat_flush_backend(bool nowait)
+{
+ if (pgstat_tracks_backend_bktype(MyBackendType))
+ {
+ PgStat_EntryRef *entry_ref;
+
+ entry_ref = pgstat_get_entry_ref(PGSTAT_KIND_BACKEND, InvalidOid,
+ MyProcNumber, false, NULL);
+ (void) pgstat_backend_flush_cb(entry_ref, nowait);
+ }
+}
+
+/*
+ * Create the backend statistics entry for procnum.
+ */
+void
+pgstat_create_backend_stat(ProcNumber procnum)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Backend *shstatent;
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_BACKEND, InvalidOid,
+ procnum, NULL);
+
+ shstatent = (PgStatShared_Backend *) entry_ref->shared_stats;
+
+ /*
+ * NB: need to accept that there might be stats from an older backend,
+ * e.g. if we previously used this proc number.
+ */
+ memset(&shstatent->stats, 0, sizeof(shstatent->stats));
+}
+
+/*
+ * Find or create a local PgStat_BackendPendingIO entry for procnum.
+ */
+PgStat_BackendPendingIO *
+pgstat_prep_backend_pending(ProcNumber procnum)
+{
+ PgStat_EntryRef *entry_ref;
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_BACKEND, InvalidOid,
+ procnum, NULL);
+
+ return entry_ref->pending;
+}
+
+/*
+ * Backend statistics are not collected for all BackendTypes.
+ *
+ * The following BackendTypes do not participate in the backend stats
+ * subsystem:
+ * - The same and for the same reasons as in pgstat_tracks_io_bktype().
+ * - B_BG_WRITER, B_CHECKPOINTER, B_STARTUP and B_AUTOVAC_LAUNCHER because their
+ * I/O stats are already visible in pg_stat_io and there is only one of those.
+ *
+ * Function returns true if BackendType participates in the backend stats
+ * subsystem for IO and false if it does not.
+ *
+ * When adding a new BackendType, also consider adding relevant restrictions to
+ * pgstat_tracks_io_object() and pgstat_tracks_io_op().
+ */
+bool
+pgstat_tracks_backend_bktype(BackendType bktype)
+{
+ /*
+ * List every type so that new backend types trigger a warning about
+ * needing to adjust this switch.
+ */
+ switch (bktype)
+ {
+ case B_INVALID:
+ case B_AUTOVAC_LAUNCHER:
+ case B_DEAD_END_BACKEND:
+ case B_ARCHIVER:
+ case B_LOGGER:
+ case B_WAL_RECEIVER:
+ case B_WAL_WRITER:
+ case B_WAL_SUMMARIZER:
+ case B_BG_WRITER:
+ case B_CHECKPOINTER:
+ case B_STARTUP:
+ return false;
+
+ case B_AUTOVAC_WORKER:
+ case B_BACKEND:
+ case B_BG_WORKER:
+ case B_STANDALONE_BACKEND:
+ case B_SLOTSYNC_WORKER:
+ case B_WAL_SENDER:
+ return true;
+ }
+
+ return false;
+}
+
+void
+pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts)
+{
+ ((PgStatShared_Backend *) header)->stats.stat_reset_timestamp = ts;
+}
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index f9883af2b3..e0c206a453 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -20,13 +20,7 @@
#include "storage/bufmgr.h"
#include "utils/pgstat_internal.h"
-
-typedef struct PgStat_PendingIO
-{
- PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
- instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
-} PgStat_PendingIO;
-
+typedef PgStat_BackendPendingIO PgStat_PendingIO;
static PgStat_PendingIO PendingIOStats;
static bool have_iostats = false;
@@ -87,6 +81,14 @@ pgstat_count_io_op_n(IOObject io_object, IOContext io_context, IOOp io_op, uint3
Assert((unsigned int) io_op < IOOP_NUM_TYPES);
Assert(pgstat_tracks_io_op(MyBackendType, io_object, io_context, io_op));
+ if (pgstat_tracks_backend_bktype(MyBackendType))
+ {
+ PgStat_PendingIO *entry_ref;
+
+ entry_ref = pgstat_prep_backend_pending(MyProcNumber);
+ entry_ref->counts[io_object][io_context][io_op] += cnt;
+ }
+
PendingIOStats.counts[io_object][io_context][io_op] += cnt;
have_iostats = true;
@@ -148,6 +150,15 @@ pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op,
INSTR_TIME_ADD(PendingIOStats.pending_times[io_object][io_context][io_op],
io_time);
+
+ if (pgstat_tracks_backend_bktype(MyBackendType))
+ {
+ PgStat_PendingIO *entry_ref;
+
+ entry_ref = pgstat_prep_backend_pending(MyProcNumber);
+ INSTR_TIME_ADD(entry_ref->pending_times[io_object][io_context][io_op],
+ io_time);
+ }
}
pgstat_count_io_op_n(io_object, io_context, io_op, cnt);
diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c
index faba8b64d2..85e65557bb 100644
--- a/src/backend/utils/activity/pgstat_relation.c
+++ b/src/backend/utils/activity/pgstat_relation.c
@@ -264,6 +264,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared,
* VACUUM command has processed all tables and committed.
*/
pgstat_flush_io(false);
+ pgstat_flush_backend(false);
}
/*
@@ -350,6 +351,7 @@ pgstat_report_analyze(Relation rel,
/* see pgstat_report_vacuum() */
pgstat_flush_io(false);
+ pgstat_flush_backend(false);
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index cdf37403e9..b939551d36 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1474,6 +1474,154 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+Datum
+pg_stat_get_backend_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_Backend *backend_stats;
+ Datum bktype_desc;
+ PgStat_BktypeIO *bktype_stats;
+ BackendType bktype;
+ Datum reset_time;
+ int num_backends = pgstat_fetch_stat_numbackends();
+ int curr_backend;
+ int pid;
+ PGPROC *proc;
+ ProcNumber procNumber;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ pid = PG_GETARG_INT32(0);
+ proc = BackendPidGetProc(pid);
+
+ /*
+ * Could be an auxiliary process but would not report any stats due to
+ * pgstat_tracks_backend_bktype() anyway. So don't need an extra call to
+ * AuxiliaryPidGetProc().
+ */
+ if (!proc)
+ return (Datum) 0;
+
+ procNumber = GetNumberFromPGProc(proc);
+ backend_stats = pgstat_fetch_proc_stat_io(procNumber);
+
+ if (!backend_stats)
+ return (Datum) 0;
+
+ bktype = B_INVALID;
+
+ /* Look for the backend type */
+ for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+ {
+ LocalPgBackendStatus *local_beentry;
+ PgBackendStatus *beentry;
+
+ /* Get the next one in the list */
+ local_beentry = pgstat_get_local_beentry_by_index(curr_backend);
+ beentry = &local_beentry->backendStatus;
+
+ /* Looking for specific PID, ignore all the others */
+ if (beentry->st_procpid != pid)
+ continue;
+
+ bktype = beentry->st_backendType;
+ break;
+ }
+
+ /* Backend is gone */
+ if (bktype == B_INVALID)
+ return (Datum) 0;
+
+ bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
+ bktype_stats = &backend_stats->stats;
+ reset_time = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
+
+ /*
+ * In Assert builds, we can afford an extra loop through all of the
+ * counters checking that only expected stats are non-zero, since it keeps
+ * the non-Assert code cleaner.
+ */
+ Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
+
+ for (int io_obj = 0; io_obj < IOOBJECT_NUM_TYPES; io_obj++)
+ {
+ const char *obj_name = pgstat_get_io_object_name(io_obj);
+
+ for (int io_context = 0; io_context < IOCONTEXT_NUM_TYPES; io_context++)
+ {
+ const char *context_name = pgstat_get_io_context_name(io_context);
+
+ Datum values[IO_NUM_COLUMNS] = {0};
+ bool nulls[IO_NUM_COLUMNS] = {0};
+
+ /*
+ * Some combinations of BackendType, IOObject, and IOContext are
+ * not valid for any type of IOOp. In such cases, omit the entire
+ * row from the view.
+ */
+ if (!pgstat_tracks_io_object(bktype, io_obj, io_context))
+ continue;
+
+ values[IO_COL_BACKEND_TYPE] = bktype_desc;
+ values[IO_COL_CONTEXT] = CStringGetTextDatum(context_name);
+ values[IO_COL_OBJECT] = CStringGetTextDatum(obj_name);
+ if (backend_stats->stat_reset_timestamp != 0)
+ values[IO_COL_RESET_TIME] = reset_time;
+ else
+ nulls[IO_COL_RESET_TIME] = true;
+
+ /*
+ * Hard-code this to the value of BLCKSZ for now. Future values
+ * could include XLOG_BLCKSZ, once WAL IO is tracked, and constant
+ * multipliers, once non-block-oriented IO (e.g. temporary file
+ * IO) is tracked.
+ */
+ values[IO_COL_CONVERSION] = Int64GetDatum(BLCKSZ);
+
+ for (int io_op = 0; io_op < IOOP_NUM_TYPES; io_op++)
+ {
+ int op_idx = pgstat_get_io_op_index(io_op);
+ int time_idx = pgstat_get_io_time_index(io_op);
+
+ /*
+ * Some combinations of BackendType and IOOp, of IOContext and
+ * IOOp, and of IOObject and IOOp are not tracked. Set these
+ * cells in the view NULL.
+ */
+ if (pgstat_tracks_io_op(bktype, io_obj, io_context, io_op))
+ {
+ PgStat_Counter count =
+ bktype_stats->counts[io_obj][io_context][io_op];
+
+ values[op_idx] = Int64GetDatum(count);
+ }
+ else
+ nulls[op_idx] = true;
+
+ /* not every operation is timed */
+ if (time_idx == IO_COL_INVALID)
+ continue;
+
+ if (!nulls[op_idx])
+ {
+ PgStat_Counter time =
+ bktype_stats->times[io_obj][io_context][io_op];
+
+ values[time_idx] = Float8GetDatum(pg_stat_us_to_ms(time));
+ }
+ else
+ nulls[time_idx] = true;
+ }
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+ }
+
+ return (Datum) 0;
+}
+
/*
* Returns statistics of WAL activity
*/
@@ -1779,6 +1927,27 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+Datum
+pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
+{
+ PGPROC *proc;
+ int backend_pid = PG_GETARG_INT32(0);
+
+ proc = BackendPidGetProc(backend_pid);
+
+ /*
+ * Could be an auxiliary process but would not report any stats due to
+ * pgstat_tracks_backend_bktype() anyway. So don't need an extra call to
+ * AuxiliaryPidGetProc().
+ */
+ if (!proc)
+ PG_RETURN_VOID();
+
+ pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, GetNumberFromPGProc(proc));
+
+ PG_RETURN_VOID();
+}
+
/* Reset SLRU counters (a specific one or all of them). */
Datum
pg_stat_reset_slru(PG_FUNCTION_ARGS)
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 56771f83ed..3447e7b75d 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1249,7 +1249,7 @@ SELECT pg_stat_get_subscription_stats(NULL);
(1 row)
--- Test that the following operations are tracked in pg_stat_io:
+-- Test that the following operations are tracked in pg_stat_io and in backend stats:
-- - reads of target blocks into shared buffers
-- - writes of shared buffers to permanent storage
-- - extends of relations using shared buffers
@@ -1259,11 +1259,19 @@ SELECT pg_stat_get_subscription_stats(NULL);
-- be sure of the state of shared buffers at the point the test is run.
-- Create a regular table and insert some data to generate IOCONTEXT_NORMAL
-- extends.
+SELECT pid AS checkpointer_pid FROM pg_stat_activity
+ WHERE backend_type = 'checkpointer' \gset
SELECT sum(extends) AS io_sum_shared_before_extends
FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT sum(extends) AS my_io_sum_shared_before_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
FROM pg_stat_io
WHERE object = 'relation' \gset io_sum_shared_before_
+SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE object = 'relation' \gset my_io_sum_shared_before_
CREATE TABLE test_io_shared(a int);
INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;
SELECT pg_stat_force_next_flush();
@@ -1280,8 +1288,17 @@ SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends;
t
(1 row)
+SELECT sum(extends) AS my_io_sum_shared_after_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends;
+ ?column?
+----------
+ t
+(1 row)
+
-- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes
--- and fsyncs.
+-- and fsyncs in the global stats (not for the backend).
-- See comment above for rationale for two explicit CHECKPOINTs.
CHECKPOINT;
CHECKPOINT;
@@ -1301,6 +1318,31 @@ SELECT current_setting('fsync') = 'off'
t
(1 row)
+SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE object = 'relation' \gset my_io_sum_shared_after_
+SELECT :my_io_sum_shared_after_writes >= :my_io_sum_shared_before_writes;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT current_setting('fsync') = 'off'
+ OR (:my_io_sum_shared_after_fsyncs = :my_io_sum_shared_before_fsyncs
+ AND :my_io_sum_shared_after_fsyncs= 0);
+ ?column?
+----------
+ t
+(1 row)
+
+-- Don't return any rows if querying other backend's stats that are excluded
+-- from the backend stats collection (like the checkpointer).
+SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid);
+ ?column?
+----------
+ t
+(1 row)
+
-- Change the tablespace so that the table is rewritten directly, then SELECT
-- from it to cause it to be read back into shared buffers.
SELECT sum(reads) AS io_sum_shared_before_reads
@@ -1521,6 +1563,8 @@ SELECT pg_stat_have_stats('io', 0, 0);
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_pre_reset
FROM pg_stat_io \gset
+SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT pg_stat_reset_shared('io');
pg_stat_reset_shared
----------------------
@@ -1535,6 +1579,30 @@ SELECT :io_stats_post_reset < :io_stats_pre_reset;
t
(1 row)
+SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
+-- pg_stat_reset_shared() did not reset backend IO stats
+SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
+ ?column?
+----------
+ t
+(1 row)
+
+-- but pg_stat_reset_backend_stats() does
+SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+ pg_stat_reset_backend_stats
+-----------------------------
+
+(1 row)
+
+SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_backend_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
+SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
+ ?column?
+----------
+ t
+(1 row)
+
-- test BRIN index doesn't block HOT update
CREATE TABLE brin_hot (
id integer PRIMARY KEY,
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 7147cc2f89..9c925005be 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -595,7 +595,7 @@ SELECT pg_stat_get_replication_slot(NULL);
SELECT pg_stat_get_subscription_stats(NULL);
--- Test that the following operations are tracked in pg_stat_io:
+-- Test that the following operations are tracked in pg_stat_io and in backend stats:
-- - reads of target blocks into shared buffers
-- - writes of shared buffers to permanent storage
-- - extends of relations using shared buffers
@@ -607,20 +607,32 @@ SELECT pg_stat_get_subscription_stats(NULL);
-- Create a regular table and insert some data to generate IOCONTEXT_NORMAL
-- extends.
+SELECT pid AS checkpointer_pid FROM pg_stat_activity
+ WHERE backend_type = 'checkpointer' \gset
SELECT sum(extends) AS io_sum_shared_before_extends
FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT sum(extends) AS my_io_sum_shared_before_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
FROM pg_stat_io
WHERE object = 'relation' \gset io_sum_shared_before_
+SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE object = 'relation' \gset my_io_sum_shared_before_
CREATE TABLE test_io_shared(a int);
INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;
SELECT pg_stat_force_next_flush();
SELECT sum(extends) AS io_sum_shared_after_extends
FROM pg_stat_io WHERE context = 'normal' AND object = 'relation' \gset
SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends;
+SELECT sum(extends) AS my_io_sum_shared_after_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends;
-- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes
--- and fsyncs.
+-- and fsyncs in the global stats (not for the backend).
-- See comment above for rationale for two explicit CHECKPOINTs.
CHECKPOINT;
CHECKPOINT;
@@ -630,6 +642,17 @@ SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes;
SELECT current_setting('fsync') = 'off'
OR :io_sum_shared_after_fsyncs > :io_sum_shared_before_fsyncs;
+SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE object = 'relation' \gset my_io_sum_shared_after_
+SELECT :my_io_sum_shared_after_writes >= :my_io_sum_shared_before_writes;
+SELECT current_setting('fsync') = 'off'
+ OR (:my_io_sum_shared_after_fsyncs = :my_io_sum_shared_before_fsyncs
+ AND :my_io_sum_shared_after_fsyncs= 0);
+
+-- Don't return any rows if querying other backend's stats that are excluded
+-- from the backend stats collection (like the checkpointer).
+SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid);
-- Change the tablespace so that the table is rewritten directly, then SELECT
-- from it to cause it to be read back into shared buffers.
@@ -762,10 +785,21 @@ SELECT :io_sum_bulkwrite_strategy_extends_after > :io_sum_bulkwrite_strategy_ext
SELECT pg_stat_have_stats('io', 0, 0);
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_pre_reset
FROM pg_stat_io \gset
+SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT pg_stat_reset_shared('io');
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset
FROM pg_stat_io \gset
SELECT :io_stats_post_reset < :io_stats_pre_reset;
+SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
+-- pg_stat_reset_shared() did not reset backend IO stats
+SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
+-- but pg_stat_reset_backend_stats() does
+SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_backend_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
+SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
-- test BRIN index doesn't block HOT update
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 24bd504c21..fbdd6ce574 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8403,9 +8403,11 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
displayed in <link linkend="monitoring-pg-stat-database-view">
<structname>pg_stat_database</structname></link>,
<link linkend="monitoring-pg-stat-io-view">
- <structname>pg_stat_io</structname></link>, in the output of
- <xref linkend="sql-explain"/> when the <literal>BUFFERS</literal> option
- is used, in the output of <xref linkend="sql-vacuum"/> when
+ <structname>pg_stat_io</structname></link>, in the output of the
+ <link linkend="pg-stat-get-backend-io">
+ <function>pg_stat_get_backend_io()</function></link> function, in the
+ output of <xref linkend="sql-explain"/> when the <literal>BUFFERS</literal>
+ option is used, in the output of <xref linkend="sql-vacuum"/> when
the <literal>VERBOSE</literal> option is used, by autovacuum
for auto-vacuums and auto-analyzes, when <xref
linkend="guc-log-autovacuum-min-duration"/> is set and by
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 840d7f8161..4a9464da61 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4790,6 +4790,25 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry id="pg-stat-get-backend-io" role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_stat_get_backend_io</primary>
+ </indexterm>
+ <function>pg_stat_get_backend_io</function> ( <type>integer</type> )
+ <returnvalue>setof record</returnvalue>
+ </para>
+ <para>
+ Returns I/O statistics about the backend with the specified
+ process ID. The output fields are exactly the same as the ones in the
+ <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link>
+ view. The function does not return I/O statistics for the checkpointer,
+ the background writer, the startup process and the autovacuum launcher
+ as they are already visible in the <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link>
+ view and there is only one of those.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -4971,6 +4990,24 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_stat_reset_backend_stats</primary>
+ </indexterm>
+ <function>pg_stat_reset_backend_stats</function> ( <type>integer</type> )
+ <returnvalue>void</returnvalue>
+ </para>
+ <para>
+ Resets statistics for a single backend with the specified process ID
+ to zero.
+ </para>
+ <para>
+ This function is restricted to superusers by default, but other users
+ can be granted EXECUTE to run the function.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ce33e55bf1..398dd92527 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2121,6 +2121,7 @@ PgFdwSamplingMethod
PgFdwScanState
PgIfAddrCallback
PgStatShared_Archiver
+PgStatShared_Backend
PgStatShared_BgWriter
PgStatShared_Checkpointer
PgStatShared_Common
@@ -2136,6 +2137,8 @@ PgStatShared_SLRU
PgStatShared_Subscription
PgStatShared_Wal
PgStat_ArchiverStats
+PgStat_Backend
+PgStat_BackendPendingIO
PgStat_BackendSubEntry
PgStat_BgWriterStats
PgStat_BktypeIO
--
2.45.2
From 0e3c2c066698acaaad69824a619e78f2cc16a358 Mon Sep 17 00:00:00 2001
From: Michael Paquier <mich...@paquier.xyz>
Date: Wed, 18 Dec 2024 13:48:34 +0900
Subject: [PATCH v10 2/2] Tweaks on top of v9
Other notes for commit:
Requires a bump of CATALOG_VERSION_NO.
Requires a bump of PGSTAT_FILE_FORMAT_ID.
---
src/include/catalog/pg_proc.dat | 3 +-
src/include/pgstat.h | 11 +-
src/backend/utils/activity/backend_status.c | 2 +-
src/backend/utils/activity/pgstat_backend.c | 38 ++-
src/backend/utils/activity/pgstat_io.c | 2 -
src/backend/utils/adt/pgstatfuncs.c | 305 ++++++++------------
src/test/regress/expected/stats.out | 28 +-
src/test/regress/sql/stats.sql | 12 +-
doc/src/sgml/monitoring.sgml | 10 +-
9 files changed, 184 insertions(+), 227 deletions(-)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 437157ffa3..2dcc2d42da 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6061,8 +6061,7 @@
proname => 'pg_stat_reset_single_function_counters', provolatile => 'v',
prorettype => 'void', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_function_counters' },
-{ oid => '9987',
- descr => 'statistics: reset statistics for a single backend',
+{ oid => '8807', descr => 'statistics: reset statistics for a single backend',
proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
prorettype => 'void', proargtypes => 'int4',
prosrc => 'pg_stat_reset_backend_stats' },
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 479773cfd2..56ed1b8bb3 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -363,11 +363,11 @@ typedef struct PgStat_BktypeIO
PgStat_Counter times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
} PgStat_BktypeIO;
-typedef struct PgStat_BackendPendingIO
+typedef struct PgStat_PendingIO
{
PgStat_Counter counts[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
instr_time pending_times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
-} PgStat_BackendPendingIO;
+} PgStat_PendingIO;
typedef struct PgStat_IO
{
@@ -375,6 +375,9 @@ typedef struct PgStat_IO
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
+/* Backend statistics store the same amount of IO data as PGSTAT_KIND_IO */
+typedef PgStat_PendingIO PgStat_BackendPendingIO;
+
typedef struct PgStat_Backend
{
TimestampTz stat_reset_timestamp;
@@ -565,9 +568,9 @@ extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
* Functions in pgstat_backend.c
*/
-extern PgStat_Backend *pgstat_fetch_proc_stat_io(ProcNumber procNumber);
+extern PgStat_Backend *pgstat_fetch_stat_backend(ProcNumber procNumber);
extern bool pgstat_tracks_backend_bktype(BackendType bktype);
-extern void pgstat_create_backend_stat(ProcNumber procnum);
+extern void pgstat_create_backend(ProcNumber procnum);
/*
* Functions in pgstat_bgwriter.c
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index e9c3b87ac2..bf33e33a4e 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -428,7 +428,7 @@ pgstat_bestart(void)
/* Create the backend statistics entry */
if (pgstat_tracks_backend_bktype(MyBackendType))
- pgstat_create_backend_stat(MyProcNumber);
+ pgstat_create_backend(MyProcNumber);
/* Update app name to current GUC setting */
if (application_name)
diff --git a/src/backend/utils/activity/pgstat_backend.c b/src/backend/utils/activity/pgstat_backend.c
index e2d83024c2..6b2c9baa8c 100644
--- a/src/backend/utils/activity/pgstat_backend.c
+++ b/src/backend/utils/activity/pgstat_backend.c
@@ -3,11 +3,17 @@
* pgstat_backend.c
* Implementation of backend statistics.
*
- * This file contains the implementation of backend statistics. It is kept
+ * This file contains the implementation of backend statistics. It is kept
* separate from pgstat.c to enforce the line between the statistics access /
- * storage implementation and the details about individual types of statistics.
+ * storage implementation and the details about individual types of
+ * statistics.
*
- * Copyright (c) 2024, PostgreSQL Global Development Group
+ * This statistics kind uses a proc number as object ID for the hash table
+ * of pgstats. Entries are created each time a process is spawned, and are
+ * dropped when the process exits. These are not written to the pgstats file
+ * on disk.
+ *
+ * Copyright (c) 2001-2024, PostgreSQL Global Development Group
*
* IDENTIFICATION
* src/backend/utils/activity/pgstat_backend.c
@@ -19,10 +25,10 @@
#include "utils/pgstat_internal.h"
/*
- * Returns backend's IO stats.
+ * Returns statistics of a backend by proc number.
*/
PgStat_Backend *
-pgstat_fetch_proc_stat_io(ProcNumber procNumber)
+pgstat_fetch_stat_backend(ProcNumber procNumber)
{
PgStat_Backend *backend_entry;
@@ -81,21 +87,21 @@ pgstat_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
void
pgstat_flush_backend(bool nowait)
{
- if (pgstat_tracks_backend_bktype(MyBackendType))
- {
- PgStat_EntryRef *entry_ref;
+ PgStat_EntryRef *entry_ref;
- entry_ref = pgstat_get_entry_ref(PGSTAT_KIND_BACKEND, InvalidOid,
- MyProcNumber, false, NULL);
- (void) pgstat_backend_flush_cb(entry_ref, nowait);
- }
+ if (!pgstat_tracks_backend_bktype(MyBackendType))
+ return;
+
+ entry_ref = pgstat_get_entry_ref(PGSTAT_KIND_BACKEND, InvalidOid,
+ MyProcNumber, false, NULL);
+ (void) pgstat_backend_flush_cb(entry_ref, nowait);
}
/*
- * Create the backend statistics entry for procnum.
+ * Create backend statistics entry for proc number.
*/
void
-pgstat_create_backend_stat(ProcNumber procnum)
+pgstat_create_backend(ProcNumber procnum)
{
PgStat_EntryRef *entry_ref;
PgStatShared_Backend *shstatent;
@@ -113,7 +119,7 @@ pgstat_create_backend_stat(ProcNumber procnum)
}
/*
- * Find or create a local PgStat_BackendPendingIO entry for procnum.
+ * Find or create a local PgStat_BackendPendingIO entry for proc number.
*/
PgStat_BackendPendingIO *
pgstat_prep_backend_pending(ProcNumber procnum)
@@ -136,7 +142,7 @@ pgstat_prep_backend_pending(ProcNumber procnum)
* I/O stats are already visible in pg_stat_io and there is only one of those.
*
* Function returns true if BackendType participates in the backend stats
- * subsystem for IO and false if it does not.
+ * subsystem and false if it does not.
*
* When adding a new BackendType, also consider adding relevant restrictions to
* pgstat_tracks_io_object() and pgstat_tracks_io_op().
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index e0c206a453..011a3326da 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -20,8 +20,6 @@
#include "storage/bufmgr.h"
#include "utils/pgstat_internal.h"
-typedef PgStat_BackendPendingIO PgStat_PendingIO;
-
static PgStat_PendingIO PendingIOStats;
static bool have_iostats = false;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b939551d36..18c74807a4 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1274,8 +1274,9 @@ pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
}
/*
-* When adding a new column to the pg_stat_io view, add a new enum value
-* here above IO_NUM_COLUMNS.
+* When adding a new column to the pg_stat_io view and the
+* pg_stat_get_backend_io() function, add a new enum value here above
+* IO_NUM_COLUMNS.
*/
typedef enum io_stat_col
{
@@ -1365,184 +1366,20 @@ pg_stat_us_to_ms(PgStat_Counter val_ms)
return val_ms * (double) 0.001;
}
-Datum
-pg_stat_get_io(PG_FUNCTION_ARGS)
+/*
+ * pg_stat_fill_io_data
+ *
+ * Helper routine for pg_stat_get_io() and pg_stat_get_backend_io(),
+ * filling in a result tuplestore with one tuple for each object and each
+ * context supported by the caller, based on the contents of bktype_stats.
+ */
+static void
+pg_stat_fill_io_data(ReturnSetInfo *rsinfo,
+ PgStat_BktypeIO *bktype_stats,
+ BackendType bktype,
+ TimestampTz stat_reset_timestamp)
{
- ReturnSetInfo *rsinfo;
- PgStat_IO *backends_io_stats;
- Datum reset_time;
-
- InitMaterializedSRF(fcinfo, 0);
- rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
-
- backends_io_stats = pgstat_fetch_stat_io();
-
- reset_time = TimestampTzGetDatum(backends_io_stats->stat_reset_timestamp);
-
- for (int bktype = 0; bktype < BACKEND_NUM_TYPES; bktype++)
- {
- Datum bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
- PgStat_BktypeIO *bktype_stats = &backends_io_stats->stats[bktype];
-
- /*
- * In Assert builds, we can afford an extra loop through all of the
- * counters checking that only expected stats are non-zero, since it
- * keeps the non-Assert code cleaner.
- */
- Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
-
- /*
- * For those BackendTypes without IO Operation stats, skip
- * representing them in the view altogether.
- */
- if (!pgstat_tracks_io_bktype(bktype))
- continue;
-
- for (int io_obj = 0; io_obj < IOOBJECT_NUM_TYPES; io_obj++)
- {
- const char *obj_name = pgstat_get_io_object_name(io_obj);
-
- for (int io_context = 0; io_context < IOCONTEXT_NUM_TYPES; io_context++)
- {
- const char *context_name = pgstat_get_io_context_name(io_context);
-
- Datum values[IO_NUM_COLUMNS] = {0};
- bool nulls[IO_NUM_COLUMNS] = {0};
-
- /*
- * Some combinations of BackendType, IOObject, and IOContext
- * are not valid for any type of IOOp. In such cases, omit the
- * entire row from the view.
- */
- if (!pgstat_tracks_io_object(bktype, io_obj, io_context))
- continue;
-
- values[IO_COL_BACKEND_TYPE] = bktype_desc;
- values[IO_COL_CONTEXT] = CStringGetTextDatum(context_name);
- values[IO_COL_OBJECT] = CStringGetTextDatum(obj_name);
- values[IO_COL_RESET_TIME] = reset_time;
-
- /*
- * Hard-code this to the value of BLCKSZ for now. Future
- * values could include XLOG_BLCKSZ, once WAL IO is tracked,
- * and constant multipliers, once non-block-oriented IO (e.g.
- * temporary file IO) is tracked.
- */
- values[IO_COL_CONVERSION] = Int64GetDatum(BLCKSZ);
-
- for (int io_op = 0; io_op < IOOP_NUM_TYPES; io_op++)
- {
- int op_idx = pgstat_get_io_op_index(io_op);
- int time_idx = pgstat_get_io_time_index(io_op);
-
- /*
- * Some combinations of BackendType and IOOp, of IOContext
- * and IOOp, and of IOObject and IOOp are not tracked. Set
- * these cells in the view NULL.
- */
- if (pgstat_tracks_io_op(bktype, io_obj, io_context, io_op))
- {
- PgStat_Counter count =
- bktype_stats->counts[io_obj][io_context][io_op];
-
- values[op_idx] = Int64GetDatum(count);
- }
- else
- nulls[op_idx] = true;
-
- /* not every operation is timed */
- if (time_idx == IO_COL_INVALID)
- continue;
-
- if (!nulls[op_idx])
- {
- PgStat_Counter time =
- bktype_stats->times[io_obj][io_context][io_op];
-
- values[time_idx] = Float8GetDatum(pg_stat_us_to_ms(time));
- }
- else
- nulls[time_idx] = true;
- }
-
- tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
- values, nulls);
- }
- }
- }
-
- return (Datum) 0;
-}
-
-Datum
-pg_stat_get_backend_io(PG_FUNCTION_ARGS)
-{
- ReturnSetInfo *rsinfo;
- PgStat_Backend *backend_stats;
- Datum bktype_desc;
- PgStat_BktypeIO *bktype_stats;
- BackendType bktype;
- Datum reset_time;
- int num_backends = pgstat_fetch_stat_numbackends();
- int curr_backend;
- int pid;
- PGPROC *proc;
- ProcNumber procNumber;
-
- InitMaterializedSRF(fcinfo, 0);
- rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
-
- pid = PG_GETARG_INT32(0);
- proc = BackendPidGetProc(pid);
-
- /*
- * Could be an auxiliary process but would not report any stats due to
- * pgstat_tracks_backend_bktype() anyway. So don't need an extra call to
- * AuxiliaryPidGetProc().
- */
- if (!proc)
- return (Datum) 0;
-
- procNumber = GetNumberFromPGProc(proc);
- backend_stats = pgstat_fetch_proc_stat_io(procNumber);
-
- if (!backend_stats)
- return (Datum) 0;
-
- bktype = B_INVALID;
-
- /* Look for the backend type */
- for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
- {
- LocalPgBackendStatus *local_beentry;
- PgBackendStatus *beentry;
-
- /* Get the next one in the list */
- local_beentry = pgstat_get_local_beentry_by_index(curr_backend);
- beentry = &local_beentry->backendStatus;
-
- /* Looking for specific PID, ignore all the others */
- if (beentry->st_procpid != pid)
- continue;
-
- bktype = beentry->st_backendType;
- break;
- }
-
- /* Backend is gone */
- if (bktype == B_INVALID)
- return (Datum) 0;
-
- bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
- bktype_stats = &backend_stats->stats;
- reset_time = TimestampTzGetDatum(backend_stats->stat_reset_timestamp);
-
- /*
- * In Assert builds, we can afford an extra loop through all of the
- * counters checking that only expected stats are non-zero, since it keeps
- * the non-Assert code cleaner.
- */
- Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
+ Datum bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
for (int io_obj = 0; io_obj < IOOBJECT_NUM_TYPES; io_obj++)
{
@@ -1566,8 +1403,8 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
values[IO_COL_BACKEND_TYPE] = bktype_desc;
values[IO_COL_CONTEXT] = CStringGetTextDatum(context_name);
values[IO_COL_OBJECT] = CStringGetTextDatum(obj_name);
- if (backend_stats->stat_reset_timestamp != 0)
- values[IO_COL_RESET_TIME] = reset_time;
+ if (stat_reset_timestamp != 0)
+ values[IO_COL_RESET_TIME] = TimestampTzGetDatum(stat_reset_timestamp);
else
nulls[IO_COL_RESET_TIME] = true;
@@ -1618,7 +1455,104 @@ pg_stat_get_backend_io(PG_FUNCTION_ARGS)
values, nulls);
}
}
+}
+Datum
+pg_stat_get_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_IO *backends_io_stats;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ backends_io_stats = pgstat_fetch_stat_io();
+
+ for (int bktype = 0; bktype < BACKEND_NUM_TYPES; bktype++)
+ {
+ PgStat_BktypeIO *bktype_stats = &backends_io_stats->stats[bktype];
+
+ /*
+ * In Assert builds, we can afford an extra loop through all of the
+ * counters checking that only expected stats are non-zero, since it
+ * keeps the non-Assert code cleaner.
+ */
+ Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
+
+ /*
+ * For those BackendTypes without IO Operation stats, skip
+ * representing them in the view altogether.
+ */
+ if (!pgstat_tracks_io_bktype(bktype))
+ continue;
+
+ /* Save tuples with data from this PgStat_BktypeIO. */
+ pg_stat_fill_io_data(rsinfo, bktype_stats, bktype,
+ backends_io_stats->stat_reset_timestamp);
+ }
+
+ return (Datum) 0;
+}
+
+/*
+ * Returns I/O statistics for a backend with given PID.
+ */
+Datum
+pg_stat_get_backend_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ BackendType bktype;
+ int pid;
+ PGPROC *proc;
+ ProcNumber procNumber;
+ PgStat_Backend *backend_stats;
+ PgStat_BktypeIO *bktype_stats;
+ PgBackendStatus *beentry;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ pid = PG_GETARG_INT32(0);
+ proc = BackendPidGetProc(pid);
+
+ /*
+ * This could be an auxiliary process but these do not report backend
+ * statistics due to pgstat_tracks_backend_bktype(), so there is no need
+ * for an extra call to AuxiliaryPidGetProc().
+ */
+ if (!proc)
+ return (Datum) 0;
+
+ procNumber = GetNumberFromPGProc(proc);
+
+ backend_stats = pgstat_fetch_stat_backend(procNumber);
+
+ if (!backend_stats)
+ return (Datum) 0;
+
+ beentry = pgstat_get_beentry_by_proc_number(procNumber);
+ bktype = beentry->st_backendType;
+
+ /* If PID does not match, leave */
+ if (beentry->st_procpid != pid)
+ return (Datum) 0;
+
+ /* Backend may be gone, so recheck in case */
+ if (bktype == B_INVALID)
+ return (Datum) 0;
+
+ bktype_stats = &backend_stats->stats;
+
+ /*
+ * In Assert builds, we can afford an extra loop through all of the
+ * counters checking that only expected stats are non-zero, since it keeps
+ * the non-Assert code cleaner.
+ */
+ Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
+
+ /* Save tuples with data from this PgStat_BktypeIO */
+ pg_stat_fill_io_data(rsinfo, bktype_stats, bktype,
+ backend_stats->stat_reset_timestamp);
return (Datum) 0;
}
@@ -1927,6 +1861,9 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+/*
+ * Reset statistics of backend with given PID.
+ */
Datum
pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
{
@@ -1936,9 +1873,9 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
proc = BackendPidGetProc(backend_pid);
/*
- * Could be an auxiliary process but would not report any stats due to
- * pgstat_tracks_backend_bktype() anyway. So don't need an extra call to
- * AuxiliaryPidGetProc().
+ * This could be an auxiliary process but these do not report backend
+ * statistics due to pgstat_tracks_backend_bktype(), so there is no need
+ * for an extra call to AuxiliaryPidGetProc().
*/
if (!proc)
PG_RETURN_VOID();
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 3447e7b75d..150b6dcf74 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1249,7 +1249,8 @@ SELECT pg_stat_get_subscription_stats(NULL);
(1 row)
--- Test that the following operations are tracked in pg_stat_io and in backend stats:
+-- Test that the following operations are tracked in pg_stat_io and in
+-- backend stats:
-- - reads of target blocks into shared buffers
-- - writes of shared buffers to permanent storage
-- - extends of relations using shared buffers
@@ -1335,14 +1336,6 @@ SELECT current_setting('fsync') = 'off'
t
(1 row)
--- Don't return any rows if querying other backend's stats that are excluded
--- from the backend stats collection (like the checkpointer).
-SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid);
- ?column?
-----------
- t
-(1 row)
-
-- Change the tablespace so that the table is rewritten directly, then SELECT
-- from it to cause it to be read back into shared buffers.
SELECT sum(reads) AS io_sum_shared_before_reads
@@ -1603,6 +1596,23 @@ SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
t
(1 row)
+-- Check invalid input for pg_stat_get_backend_io()
+SELECT pg_stat_get_backend_io(NULL);
+ pg_stat_get_backend_io
+------------------------
+(0 rows)
+
+SELECT pg_stat_get_backend_io(0);
+ pg_stat_get_backend_io
+------------------------
+(0 rows)
+
+-- Auxiliary processes return no data.
+SELECT pg_stat_get_backend_io(:checkpointer_pid);
+ pg_stat_get_backend_io
+------------------------
+(0 rows)
+
-- test BRIN index doesn't block HOT update
CREATE TABLE brin_hot (
id integer PRIMARY KEY,
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 9c925005be..1e7d0ff665 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -595,7 +595,8 @@ SELECT pg_stat_get_replication_slot(NULL);
SELECT pg_stat_get_subscription_stats(NULL);
--- Test that the following operations are tracked in pg_stat_io and in backend stats:
+-- Test that the following operations are tracked in pg_stat_io and in
+-- backend stats:
-- - reads of target blocks into shared buffers
-- - writes of shared buffers to permanent storage
-- - extends of relations using shared buffers
@@ -650,10 +651,6 @@ SELECT current_setting('fsync') = 'off'
OR (:my_io_sum_shared_after_fsyncs = :my_io_sum_shared_before_fsyncs
AND :my_io_sum_shared_after_fsyncs= 0);
--- Don't return any rows if querying other backend's stats that are excluded
--- from the backend stats collection (like the checkpointer).
-SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid);
-
-- Change the tablespace so that the table is rewritten directly, then SELECT
-- from it to cause it to be read back into shared buffers.
SELECT sum(reads) AS io_sum_shared_before_reads
@@ -801,6 +798,11 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
+-- Check invalid input for pg_stat_get_backend_io()
+SELECT pg_stat_get_backend_io(NULL);
+SELECT pg_stat_get_backend_io(0);
+-- Auxiliary processes return no data.
+SELECT pg_stat_get_backend_io(:checkpointer_pid);
-- test BRIN index doesn't block HOT update
CREATE TABLE brin_hot (
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 4a9464da61..d0d176cc54 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4801,11 +4801,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
Returns I/O statistics about the backend with the specified
process ID. The output fields are exactly the same as the ones in the
- <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link>
- view. The function does not return I/O statistics for the checkpointer,
+ <structname>pg_stat_io</structname> view.
+ </para>
+ <para>
+ The function does not return I/O statistics for the checkpointer,
the background writer, the startup process and the autovacuum launcher
- as they are already visible in the <link linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link>
- view and there is only one of those.
+ as they are already visible in the <structname>pg_stat_io</structname>
+ view and there is only one of each.
</para></entry>
</row>
--
2.45.2
signature.asc
Description: PGP signature
