Hi,
On Thu, Nov 07, 2024 at 04:32:44PM +0000, Bertrand Drouvot wrote:
> Hi,
>
> On Thu, Nov 07, 2024 at 09:50:59AM +0900, Michael Paquier wrote:
> > On Wed, Nov 06, 2024 at 01:51:02PM +0000, Bertrand Drouvot wrote:
> > > That's not needed, the patch I'm working on stores the proc number in the
> > > objid field of the key.
> >
> > Relying on the procnumber for the object ID gets a +1 here.
>
> Thanks!
>
> > That
> > provides an automatic cap on the maximum number of entries that can
> > exist at once for this new stats kind.
>
> Yeah.
>
> POC patch is now working, will wear a reviewer hat now and will share in one
> or 2 days.
>
Please find attached v5 that implements the per-backend IO stats as
variable-numbered stats kind (as per the up-thread discussion).
It is split into 4 sub-patches:
==== 0001 (the largest one)
Introduces a new statistics KIND. It is named PGSTAT_KIND_PER_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 serialize 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 a
new "to_serialize" field is added in the PgStat_KindInfo struct.
It adds a new pg_my_stat_io view to display "my" backend I/O statistics.
It also adds a new function pg_stat_reset_single_backend_io_counters() to be
able to reset the I/O stats for a given backend pid.
It contains doc updates and dedicated tests.
A few remarks:
1. one assertion in pgstat_drop_entry_internal() is not necessary true anymore
with this new stat kind. So, adding an extra bool as parameter to take care of
it.
2. a new struct "PgStat_Backend" is created and does contain the backend type.
The backend type is used for filtering purpose when the stats are displayed.
3. when the stats are reset, we need to keep the backend type so the change
in shared_stat_reset_contents().
4. the pending stats are updated in the existing pgstat_count_io_op_n() and
pgstat_count_io_op_time() functions.
5. this new kind has its own flush callback: pgstat_per_backend_flush_cb().
But there is no need to maintain 2 callbacks for the I/O stats, so 0002 will
merge both and remove the one from the fixed stats. The reason it's not done
in 0001 is to ease the review.
==== 0002
Merge both IO stats flush callback. There is no need to keep both callbacks.
Merging both allows to save O(N^3) while looping on IOOBJECT_NUM_TYPES,
IOCONTEXT_NUM_TYPES and IOCONTEXT_NUM_TYPES.
The patch removes:
1. pgstat_io_flush_cb()
2. PendingIOStats (as the same information is already stored in the pending
entries)
3. have_iostats (the patch relies on the pending entries instead)
==== 0003
Don't include other backend's stats in the snapshot.
When stats_fetch_consistency is set to 'snapshot', don't include other backend's
stats in the snapshot. There is no use case, so save memory usage.
==== 0004
Adding the pg_stat_get_backend_io() function to retrieve I/O statistics for
a particular backend pid.
Note that this function does not return any rows if stats_fetch_consistency
is set to 'snapshot' and the pid of interest is not our own pid (there is no use
case of retrieving other backend's stats with stats_fetch_consistency set to
'snapshot').
The patch:
1. replaces pg_stat_get_my_io() with pg_stat_get_backend_io()
2. adds pgstat_fetch_proc_stat_io()
3. adds doc
4. adds tests
=== Remarks
R1. The key field is still "objid" even if it stores the proc number. I think
that's fine and there is no need to rename it as it's related comment states:
"
/* object ID (table, function, etc.), or identifier. */
"
R2. pg_stat_get_io() and pg_stat_get_backend_io() could probably be merged (
duplicated code). That's not mandatory to provide the new per backend I/O stats
feature. So let's keep it as future work to ease the review.
R3. There is no use case of retrieving other backend's IO stats with
stats_fetch_consistency set to 'snapshot'. Avoiding this behavior allows us to
save memory (that could be non negligeable as pgstat_build_snapshot() would add
_all_ the other backend's stats in the snapshot). I choose to document it and to
not return any rows: I think that's better than returning some data (that would
not "ensure" the snapshot consistency) or an error.
Looking forward to your feedback,
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From 89de6e71540912ca294be38fea0ed5636b718521 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Mon, 28 Oct 2024 12:50:32 +0000
Subject: [PATCH v5 1/4] per backend I/O statistics
While pg_stat_io provides cluster-wide I/O statistics, this commit adds a new
statistics kind and a new pg_my_stat_io view to display "my" backend I/O statistics.
The new KIND is named PGSTAT_KIND_PER_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 serialize 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 a
new "to_serialize" field is added in the PgStat_KindInfo struct.
Also adding a new function pg_stat_reset_single_backend_io_counters() to be
able to reset the I/O stats for a given backend pid.
A subsequent commit will add a new pg_stat_get_backend_io() function to be
able to retrieve the I/O statistics for a given backend pid.
XXX: Bump catalog version needs to be done.
---
doc/src/sgml/config.sgml | 4 +-
doc/src/sgml/monitoring.sgml | 43 ++++++
src/backend/catalog/system_functions.sql | 2 +
src/backend/catalog/system_views.sql | 22 ++++
src/backend/utils/activity/backend_status.c | 3 +
src/backend/utils/activity/pgstat.c | 34 ++++-
src/backend/utils/activity/pgstat_io.c | 114 ++++++++++++++--
src/backend/utils/activity/pgstat_shmem.c | 30 ++++-
src/backend/utils/adt/pgstatfuncs.c | 124 ++++++++++++++++++
src/include/catalog/pg_proc.dat | 14 ++
src/include/pgstat.h | 27 +++-
src/include/utils/pgstat_internal.h | 13 ++
.../injection_points/injection_stats.c | 1 +
src/test/regress/expected/rules.out | 19 +++
src/test/regress/expected/stats.out | 60 ++++++++-
src/test/regress/sql/stats.sql | 31 ++++-
src/tools/pgindent/typedefs.list | 2 +
17 files changed, 517 insertions(+), 26 deletions(-)
10.2% doc/src/sgml/
29.7% src/backend/utils/activity/
19.2% src/backend/utils/adt/
5.1% src/include/catalog/
7.2% src/include/
14.7% src/test/regress/expected/
10.4% src/test/regress/sql/
3.1% src/
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index d54f904956..fbabf371a6 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8361,7 +8361,9 @@ 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
+ <structname>pg_stat_io</structname></link>,
+ <link linkend="monitoring-pg-my-stat-io-view">
+ <structname>pg_my_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
the <literal>VERBOSE</literal> option is used, by autovacuum
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 331315f8d3..fc6aded3da 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -488,6 +488,16 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_my_stat_io</structname><indexterm><primary>pg_my_stat_io</primary></indexterm></entry>
+ <entry>
+ One row for each combination of context and target object containing
+ my backend I/O statistics.
+ See <link linkend="monitoring-pg-my-stat-io-view">
+ <structname>pg_my_stat_io</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_replication_slots</structname><indexterm><primary>pg_stat_replication_slots</primary></indexterm></entry>
<entry>One row per replication slot, showing statistics about the
@@ -2946,7 +2956,23 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para>
</note>
+ </sect2>
+ <sect2 id="monitoring-pg-my-stat-io-view">
+ <title><structname>pg_my_stat_io</structname></title>
+
+ <indexterm>
+ <primary>pg_my_stat_io</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_my_stat_io</structname> view will contain one row for each
+ combination of target I/O object and I/O context, showing
+ my backend I/O statistics. Combinations which do not make sense are
+ omitted. The fields are exactly the same as the ones in the <link
+ linkend="monitoring-pg-stat-io-view"> <structname>pg_stat_io</structname></link>
+ view.
+ </para>
</sect2>
@@ -4971,6 +4997,23 @@ 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_single_backend_io_counters</primary>
+ </indexterm>
+ <function>pg_stat_reset_single_backend_io_counters</function> ( <type>int4</type> )
+ <returnvalue>void</returnvalue>
+ </para>
+ <para>
+ Resets I/O statistics for a single backend 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/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index c51dfca802..8a1ae44fca 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_single_backend_io_counters(int4) 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/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 3456b821bc..09af4a40a8 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1170,6 +1170,28 @@ SELECT
b.stats_reset
FROM pg_stat_get_io() b;
+CREATE VIEW pg_my_stat_io AS
+SELECT
+ b.backend_type,
+ b.object,
+ b.context,
+ b.reads,
+ b.read_time,
+ b.writes,
+ b.write_time,
+ b.writebacks,
+ b.writeback_time,
+ b.extends,
+ b.extend_time,
+ b.op_bytes,
+ b.hits,
+ b.evictions,
+ b.reuses,
+ b.fsyncs,
+ b.fsync_time,
+ b.stats_reset
+FROM pg_stat_get_my_io() b;
+
CREATE VIEW pg_stat_wal AS
SELECT
w.wal_records,
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index bdb3a296ca..aa683e150c 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -249,6 +249,9 @@ pgstat_beinit(void)
Assert(MyProcNumber >= 0 && MyProcNumber < NumBackendStatSlots);
MyBEEntry = &BackendStatusArray[MyProcNumber];
+ /* Create the per backend stat entry */
+ pgstat_create_backend_stat(MyProcNumber);
+
/* Set up a process-exit hook to clean up */
on_shmem_exit(pgstat_beshutdown_hook, 0);
}
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index ea8c5691e8..aacf61c9a4 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -281,6 +281,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "database",
.fixed_amount = false,
+ .to_serialize = true,
/* so pg_stat_database entries can be seen in all databases */
.accessed_across_databases = true,
@@ -297,6 +298,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "relation",
.fixed_amount = false,
+ .to_serialize = true,
.shared_size = sizeof(PgStatShared_Relation),
.shared_data_off = offsetof(PgStatShared_Relation, stats),
@@ -311,6 +313,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "function",
.fixed_amount = false,
+ .to_serialize = true,
.shared_size = sizeof(PgStatShared_Function),
.shared_data_off = offsetof(PgStatShared_Function, stats),
@@ -324,6 +327,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "replslot",
.fixed_amount = false,
+ .to_serialize = true,
.accessed_across_databases = true,
@@ -340,6 +344,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "subscription",
.fixed_amount = false,
+ .to_serialize = true,
/* so pg_stat_subscription_stats entries can be seen in all databases */
.accessed_across_databases = true,
@@ -352,6 +357,22 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.reset_timestamp_cb = pgstat_subscription_reset_timestamp_cb,
},
+ [PGSTAT_KIND_PER_BACKEND] = {
+ .name = "perbackend",
+
+ .fixed_amount = false,
+ .to_serialize = 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_PendingIO),
+
+ .flush_pending_cb = pgstat_per_backend_flush_cb,
+ .reset_timestamp_cb = pgstat_backend_reset_timestamp_cb,
+ },
/* stats for fixed-numbered (mostly 1) objects */
@@ -359,6 +380,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "archiver",
.fixed_amount = true,
+ .to_serialize = true,
.snapshot_ctl_off = offsetof(PgStat_Snapshot, archiver),
.shared_ctl_off = offsetof(PgStat_ShmemControl, archiver),
@@ -374,6 +396,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "bgwriter",
.fixed_amount = true,
+ .to_serialize = true,
.snapshot_ctl_off = offsetof(PgStat_Snapshot, bgwriter),
.shared_ctl_off = offsetof(PgStat_ShmemControl, bgwriter),
@@ -389,6 +412,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "checkpointer",
.fixed_amount = true,
+ .to_serialize = true,
.snapshot_ctl_off = offsetof(PgStat_Snapshot, checkpointer),
.shared_ctl_off = offsetof(PgStat_ShmemControl, checkpointer),
@@ -404,6 +428,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "io",
.fixed_amount = true,
+ .to_serialize = true,
.snapshot_ctl_off = offsetof(PgStat_Snapshot, io),
.shared_ctl_off = offsetof(PgStat_ShmemControl, io),
@@ -421,6 +446,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "slru",
.fixed_amount = true,
+ .to_serialize = true,
.snapshot_ctl_off = offsetof(PgStat_Snapshot, slru),
.shared_ctl_off = offsetof(PgStat_ShmemControl, slru),
@@ -438,6 +464,7 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.name = "wal",
.fixed_amount = true,
+ .to_serialize = true,
.snapshot_ctl_off = offsetof(PgStat_Snapshot, wal),
.shared_ctl_off = offsetof(PgStat_ShmemControl, wal),
@@ -515,7 +542,7 @@ pgstat_discard_stats(void)
/*
* Reset stats contents. This will set reset timestamps of fixed-numbered
- * stats to the current time (no variable stats exist).
+ * stats to the current time (the per-backend variable stat exists too).
*/
pgstat_reset_after_failure();
}
@@ -756,7 +783,7 @@ pgstat_report_stat(bool force)
partial_flush = false;
- /* flush database / relation / function / ... stats */
+ /* flush database / relation / function / backend / ... stats */
partial_flush |= pgstat_flush_pending_entries(nowait);
/* flush of fixed-numbered stats */
@@ -1660,6 +1687,9 @@ pgstat_write_statsfile(XLogRecPtr redo)
kind_info = pgstat_get_kind_info(ps->key.kind);
+ if (!kind_info->to_serialize)
+ continue;
+
/* if not dropped the valid-entry refcount should exist */
Assert(pg_atomic_read_u32(&ps->refcount) > 0);
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index cc2ffc78aa..a2bb11adc9 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -21,13 +21,6 @@
#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;
-
-
static PgStat_PendingIO PendingIOStats;
static bool have_iostats = false;
@@ -82,12 +75,17 @@ pgstat_count_io_op(IOObject io_object, IOContext io_context, IOOp io_op)
void
pgstat_count_io_op_n(IOObject io_object, IOContext io_context, IOOp io_op, uint32 cnt)
{
+ PgStat_PendingIO *entry_ref;
+
Assert((unsigned int) io_object < IOOBJECT_NUM_TYPES);
Assert((unsigned int) io_context < IOCONTEXT_NUM_TYPES);
Assert((unsigned int) io_op < IOOP_NUM_TYPES);
Assert(pgstat_tracks_io_op(MyBackendType, io_object, io_context, io_op));
+ entry_ref = pgstat_prep_per_backend_pending(MyProcNumber);
+
PendingIOStats.counts[io_object][io_context][io_op] += cnt;
+ entry_ref->counts[io_object][io_context][io_op] += cnt;
have_iostats = true;
}
@@ -122,6 +120,10 @@ void
pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op,
instr_time start_time, uint32 cnt)
{
+ PgStat_PendingIO *entry_ref;
+
+ entry_ref = pgstat_prep_per_backend_pending(MyProcNumber);
+
if (track_io_timing)
{
instr_time io_time;
@@ -148,6 +150,8 @@ 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);
+ 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);
@@ -161,6 +165,13 @@ pgstat_fetch_stat_io(void)
return &pgStatLocal.snapshot.io;
}
+PgStat_Backend *
+pgstat_fetch_my_stat_io(void)
+{
+ return (PgStat_Backend *)
+ pgstat_fetch_entry(PGSTAT_KIND_PER_BACKEND, InvalidOid, MyProcNumber);
+}
+
/*
* Check if there any IO stats waiting for flush.
*/
@@ -171,12 +182,18 @@ pgstat_io_have_pending_cb(void)
}
/*
- * Simpler wrapper of pgstat_io_flush_cb()
+ * Simpler wrapper of pgstat_io_flush_cb() and pgstat_per_backend_flush_cb().
*/
void
pgstat_flush_io(bool nowait)
{
+ PgStat_EntryRef *entry_ref;
+
+ entry_ref = pgstat_get_entry_ref(PGSTAT_KIND_PER_BACKEND, InvalidOid,
+ MyProcNumber, false, NULL);
+
(void) pgstat_io_flush_cb(nowait);
+ (void) pgstat_per_backend_flush_cb(entry_ref, nowait);
}
/*
@@ -235,6 +252,49 @@ pgstat_io_flush_cb(bool nowait)
return false;
}
+/*
+ * Flush out locally pending backend statistics
+ *
+ * If no stats have been recorded, this function returns false.
+ */
+bool
+pgstat_per_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
+ PgStatShared_Backend *shbackendioent;
+ PgStat_PendingIO *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_PendingIO *) 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;
+}
+
const char *
pgstat_get_io_context_name(IOContext io_context)
{
@@ -325,6 +385,38 @@ pgstat_io_snapshot_cb(void)
}
}
+void
+pgstat_create_backend_stat(ProcNumber procnum)
+{
+ PgStat_EntryRef *entry_ref;
+ PgStatShared_Backend *shstatent;
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_PER_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));
+
+ /* set the backend type */
+ shstatent->stats.bktype = MyBackendType;
+}
+
+PgStat_PendingIO *
+pgstat_prep_per_backend_pending(ProcNumber procnum)
+{
+ PgStat_EntryRef *entry_ref;
+
+ entry_ref = pgstat_prep_pending_entry(PGSTAT_KIND_PER_BACKEND, InvalidOid,
+ procnum, NULL);
+
+ return entry_ref->pending;
+}
+
/*
* IO statistics are not collected for all BackendTypes.
*
@@ -503,3 +595,9 @@ pgstat_tracks_io_op(BackendType bktype, IOObject io_object,
return true;
}
+
+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_shmem.c b/src/backend/utils/activity/pgstat_shmem.c
index c1b7ff76b1..b871c1d7d0 100644
--- a/src/backend/utils/activity/pgstat_shmem.c
+++ b/src/backend/utils/activity/pgstat_shmem.c
@@ -813,13 +813,24 @@ pgstat_free_entry(PgStatShared_HashEntry *shent, dshash_seq_status *hstat)
*/
static bool
pgstat_drop_entry_internal(PgStatShared_HashEntry *shent,
- dshash_seq_status *hstat)
+ dshash_seq_status *hstat, bool ignore_aux_proc)
{
Assert(shent->body != InvalidDsaPointer);
/* should already have released local reference */
if (pgStatEntryRefHash)
- Assert(!pgstat_entry_ref_hash_lookup(pgStatEntryRefHash, shent->key));
+ {
+ /*
+ * The following assertion is not correct when coming from
+ * pgstat_discard_stats() and for per backend stats linked to an
+ * auxiliary process.
+ */
+ if (ignore_aux_proc && shent->key.kind == PGSTAT_KIND_PER_BACKEND &&
+ MyProcNumber >= MaxBackends)
+ return false;
+ else
+ Assert(!pgstat_entry_ref_hash_lookup(pgStatEntryRefHash, shent->key));
+ }
/*
* Signal that the entry is dropped - this will eventually cause other
@@ -882,7 +893,7 @@ pgstat_drop_database_and_contents(Oid dboid)
if (p->key.dboid != dboid)
continue;
- if (!pgstat_drop_entry_internal(p, &hstat))
+ if (!pgstat_drop_entry_internal(p, &hstat, false))
{
/*
* Even statistics for a dropped database might currently be
@@ -941,7 +952,7 @@ pgstat_drop_entry(PgStat_Kind kind, Oid dboid, uint64 objid)
shent = dshash_find(pgStatLocal.shared_hash, &key, true);
if (shent)
{
- freed = pgstat_drop_entry_internal(shent, NULL);
+ freed = pgstat_drop_entry_internal(shent, NULL, false);
/*
* Database stats contain other stats. Drop those as well when
@@ -969,7 +980,7 @@ pgstat_drop_all_entries(void)
if (ps->dropped)
continue;
- if (!pgstat_drop_entry_internal(ps, &hstat))
+ if (!pgstat_drop_entry_internal(ps, &hstat, true))
not_freed_count++;
}
dshash_seq_term(&hstat);
@@ -982,11 +993,20 @@ static void
shared_stat_reset_contents(PgStat_Kind kind, PgStatShared_Common *header,
TimestampTz ts)
{
+ BackendType bktype;
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
+ /* save the bktype */
+ if (kind == PGSTAT_KIND_PER_BACKEND)
+ bktype = ((PgStatShared_Backend *) header)->stats.bktype;
+
memset(pgstat_get_entry_data(kind, header), 0,
pgstat_get_entry_len(kind));
+ /* restore the bktype */
+ if (kind == PGSTAT_KIND_PER_BACKEND)
+ ((PgStatShared_Backend *) header)->stats.bktype = bktype;
+
if (kind_info->reset_timestamp_cb)
kind_info->reset_timestamp_cb(header, ts);
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f7b50e0b5a..fd16986156 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1474,6 +1474,111 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+Datum
+pg_stat_get_my_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_Backend *backend_stats;
+ Datum bktype_desc;
+ PgStat_BktypeIO *bktype_stats;
+ BackendType bktype;
+ Datum reset_time;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ backend_stats = pgstat_fetch_my_stat_io();
+
+ bktype = backend_stats->bktype;
+ 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
*/
@@ -1722,6 +1827,7 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ pgstat_reset_of_kind(PGSTAT_KIND_PER_BACKEND);
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
pgstat_reset_of_kind(PGSTAT_KIND_WAL);
@@ -1779,6 +1885,24 @@ pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+Datum
+pg_stat_reset_single_backend_io_counters(PG_FUNCTION_ARGS)
+{
+ PGPROC *proc;
+ int backend_pid = PG_GETARG_INT32(0);
+
+ proc = BackendPidGetProc(backend_pid);
+
+ /* Maybe an auxiliary process? */
+ if (proc == NULL)
+ proc = AuxiliaryPidGetProc(backend_pid);
+
+ if (proc)
+ pgstat_reset(PGSTAT_KIND_PER_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/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f23321a41f..89eb89efe4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5903,6 +5903,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: my backend IO statistics',
+ proname => 'pg_stat_get_my_io', prorows => '5', proretset => 't',
+ provolatile => 'v', proparallel => 'r', prorettype => 'record',
+ proargtypes => '',
+ proallargtypes => '{text,text,text,int8,float8,int8,float8,int8,float8,int8,float8,int8,int8,int8,int8,int8,float8,timestamptz}',
+ proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ 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_my_io' },
+
{ oid => '1136', descr => 'statistics: information about WAL activity',
proname => 'pg_stat_get_wal', proisstrict => 'f', provolatile => 's',
proparallel => 'r', prorettype => 'record', proargtypes => '',
@@ -6042,6 +6051,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 collected IO statistics for a single backend',
+ proname => 'pg_stat_reset_single_backend_io_counters', provolatile => 'v',
+ prorettype => 'void', proargtypes => 'int4',
+ prosrc => 'pg_stat_reset_single_backend_io_counters' },
{ 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 df53fa2d4f..13b9c71759 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_PER_BACKEND 6
/* 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
@@ -347,12 +348,24 @@ typedef struct PgStat_BktypeIO
PgStat_Counter times[IOOBJECT_NUM_TYPES][IOCONTEXT_NUM_TYPES][IOOP_NUM_TYPES];
} PgStat_BktypeIO;
+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 struct PgStat_IO
{
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
+typedef struct PgStat_Backend
+{
+ TimestampTz stat_reset_timestamp;
+ BackendType bktype;
+ PgStat_BktypeIO stats;
+} PgStat_Backend;
typedef struct PgStat_StatDBEntry
{
@@ -562,6 +575,7 @@ extern void pgstat_count_io_op_time(IOObject io_object, IOContext io_context,
IOOp io_op, instr_time start_time, uint32 cnt);
extern PgStat_IO *pgstat_fetch_stat_io(void);
+extern PgStat_Backend *pgstat_fetch_my_stat_io(void);
extern const char *pgstat_get_io_context_name(IOContext io_context);
extern const char *pgstat_get_io_object_name(IOObject io_object);
@@ -570,6 +584,7 @@ extern bool pgstat_tracks_io_object(BackendType bktype,
IOObject io_object, IOContext io_context);
extern bool pgstat_tracks_io_op(BackendType bktype, IOObject io_object,
IOContext io_context, IOOp io_op);
+extern void pgstat_create_backend_stat(ProcNumber procnum);
/*
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index 61b2e1f96b..44a00d4779 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -194,6 +194,11 @@ typedef struct PgStat_KindInfo
*/
bool accessed_across_databases:1;
+ /*
+ * Do serialize or not this kind of stats.
+ */
+ bool to_serialize:1;
+
/*
* The size of an entry in the shared stats hash table (pointed to by
* PgStatShared_HashEntry->body). For fixed-numbered statistics, this is
@@ -428,6 +433,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.
@@ -630,9 +640,12 @@ extern void pgstat_flush_io(bool nowait);
extern bool pgstat_io_have_pending_cb(void);
extern bool pgstat_io_flush_cb(bool nowait);
+extern bool pgstat_per_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
extern void pgstat_io_init_shmem_cb(void *stats);
extern void pgstat_io_reset_all_cb(TimestampTz ts);
extern void pgstat_io_snapshot_cb(void);
+extern PgStat_PendingIO *pgstat_prep_per_backend_pending(ProcNumber procnum);
+extern void pgstat_backend_reset_timestamp_cb(PgStatShared_Common *header, TimestampTz ts);
/*
diff --git a/src/test/modules/injection_points/injection_stats.c b/src/test/modules/injection_points/injection_stats.c
index d89d055913..ca4b6b6e9f 100644
--- a/src/test/modules/injection_points/injection_stats.c
+++ b/src/test/modules/injection_points/injection_stats.c
@@ -39,6 +39,7 @@ static bool injection_stats_flush_cb(PgStat_EntryRef *entry_ref, bool nowait);
static const PgStat_KindInfo injection_stats = {
.name = "injection_points",
.fixed_amount = false, /* Bounded by the number of points */
+ .to_serialize = true,
/* Injection points are system-wide */
.accessed_across_databases = true,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2b47013f11..f92516b047 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1398,6 +1398,25 @@ pg_matviews| SELECT n.nspname AS schemaname,
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'm'::"char");
+pg_my_stat_io| SELECT 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
+ FROM pg_stat_get_my_io() b(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);
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 56771f83ed..846e693483 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_[my_]stat_io:
-- - reads of target blocks into shared buffers
-- - writes of shared buffers to permanent storage
-- - extends of relations using shared buffers
@@ -1261,9 +1261,14 @@ SELECT pg_stat_get_subscription_stats(NULL);
-- extends.
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_my_stat_io 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_my_stat_io
+ 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 +1285,16 @@ 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_my_stat_io 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 +1314,23 @@ SELECT current_setting('fsync') = 'off'
t
(1 row)
+SELECT sum(writes) AS writes, sum(fsyncs) AS fsyncs
+ FROM pg_my_stat_io
+ 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)
+
-- 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 +1551,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_my_stat_io \gset
SELECT pg_stat_reset_shared('io');
pg_stat_reset_shared
----------------------
@@ -1535,6 +1567,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_my_stat_io \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_single_backend_io_counters() does
+SELECT pg_stat_reset_single_backend_io_counters(pg_backend_pid());
+ pg_stat_reset_single_backend_io_counters
+------------------------------------------
+
+(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_my_stat_io \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..9cb14b7182 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_[my_]stat_io:
-- - reads of target blocks into shared buffers
-- - writes of shared buffers to permanent storage
-- - extends of relations using shared buffers
@@ -609,18 +609,26 @@ SELECT pg_stat_get_subscription_stats(NULL);
-- extends.
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_my_stat_io 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_my_stat_io
+ 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_my_stat_io 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;
@@ -631,6 +639,14 @@ 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_my_stat_io
+ 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);
+
-- 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
@@ -762,10 +778,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_my_stat_io \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_my_stat_io \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_single_backend_io_counters() does
+SELECT pg_stat_reset_single_backend_io_counters(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_my_stat_io \gset
+SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
-- test BRIN index doesn't block HOT update
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 1847bbfa95..4188d056ca 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2117,6 +2117,7 @@ PgFdwSamplingMethod
PgFdwScanState
PgIfAddrCallback
PgStatShared_Archiver
+PgStatShared_Backend
PgStatShared_BgWriter
PgStatShared_Checkpointer
PgStatShared_Common
@@ -2132,6 +2133,7 @@ PgStatShared_SLRU
PgStatShared_Subscription
PgStatShared_Wal
PgStat_ArchiverStats
+PgStat_Backend
PgStat_BackendSubEntry
PgStat_BgWriterStats
PgStat_BktypeIO
--
2.34.1
>From edd45c3aa0731a9645dbcfe0475e40c642e92269 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Wed, 6 Nov 2024 16:19:44 +0000
Subject: [PATCH v5 2/4] Merge both IO stats flush callbacks
There is no need to keep both callbacks.
Merging both allows to save O(N^3) while looping on IOOBJECT_NUM_TYPES,
IOCONTEXT_NUM_TYPES and IOCONTEXT_NUM_TYPES.
---
src/backend/utils/activity/pgstat.c | 2 -
src/backend/utils/activity/pgstat_io.c | 97 ++++++--------------------
2 files changed, 23 insertions(+), 76 deletions(-)
100.0% src/backend/utils/activity/
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index aacf61c9a4..d051db5c10 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -435,8 +435,6 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.shared_data_off = offsetof(PgStatShared_IO, stats),
.shared_data_len = sizeof(((PgStatShared_IO *) 0)->stats),
- .flush_fixed_cb = pgstat_io_flush_cb,
- .have_fixed_pending_cb = pgstat_io_have_pending_cb,
.init_shmem_cb = pgstat_io_init_shmem_cb,
.reset_all_cb = pgstat_io_reset_all_cb,
.snapshot_cb = pgstat_io_snapshot_cb,
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index a2bb11adc9..1384f8103e 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -21,10 +21,6 @@
#include "utils/pgstat_internal.h"
-static PgStat_PendingIO PendingIOStats;
-static bool have_iostats = false;
-
-
/*
* Check that stats have not been counted for any combination of IOObject,
* IOContext, and IOOp which are not tracked for the passed-in BackendType. If
@@ -83,11 +79,7 @@ pgstat_count_io_op_n(IOObject io_object, IOContext io_context, IOOp io_op, uint3
Assert(pgstat_tracks_io_op(MyBackendType, io_object, io_context, io_op));
entry_ref = pgstat_prep_per_backend_pending(MyProcNumber);
-
- PendingIOStats.counts[io_object][io_context][io_op] += cnt;
entry_ref->counts[io_object][io_context][io_op] += cnt;
-
- have_iostats = true;
}
/*
@@ -148,8 +140,6 @@ pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op,
INSTR_TIME_ADD(pgBufferUsage.local_blk_read_time, io_time);
}
- INSTR_TIME_ADD(PendingIOStats.pending_times[io_object][io_context][io_op],
- io_time);
INSTR_TIME_ADD(entry_ref->pending_times[io_object][io_context][io_op],
io_time);
}
@@ -173,16 +163,7 @@ pgstat_fetch_my_stat_io(void)
}
/*
- * Check if there any IO stats waiting for flush.
- */
-bool
-pgstat_io_have_pending_cb(void)
-{
- return have_iostats;
-}
-
-/*
- * Simpler wrapper of pgstat_io_flush_cb() and pgstat_per_backend_flush_cb().
+ * Simpler wrapper of pgstat_per_backend_flush_cb().
*/
void
pgstat_flush_io(bool nowait)
@@ -192,81 +173,39 @@ pgstat_flush_io(bool nowait)
entry_ref = pgstat_get_entry_ref(PGSTAT_KIND_PER_BACKEND, InvalidOid,
MyProcNumber, false, NULL);
- (void) pgstat_io_flush_cb(nowait);
(void) pgstat_per_backend_flush_cb(entry_ref, nowait);
}
/*
- * Flush out locally pending IO statistics
+ * Flush out locally pending backend statistics
*
* If no stats have been recorded, this function returns false.
- *
- * If nowait is true, this function returns true if the lock could not be
- * acquired. Otherwise, return false.
*/
bool
-pgstat_io_flush_cb(bool nowait)
+pgstat_per_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
{
- LWLock *bktype_lock;
+ PgStatShared_Backend *shbackendioent;
+ PgStat_PendingIO *pendingent;
PgStat_BktypeIO *bktype_shstats;
+ LWLock *bktype_lock;
+ PgStat_BktypeIO *bktype_global_shstats;
- if (!have_iostats)
+ if (!pgstat_lock_entry(entry_ref, nowait))
return false;
+ /* global IO stats */
bktype_lock = &pgStatLocal.shmem->io.locks[MyBackendType];
- bktype_shstats =
- &pgStatLocal.shmem->io.stats.stats[MyBackendType];
+ bktype_global_shstats = &pgStatLocal.shmem->io.stats.stats[MyBackendType];
if (!nowait)
LWLockAcquire(bktype_lock, LW_EXCLUSIVE);
else if (!LWLockConditionalAcquire(bktype_lock, LW_EXCLUSIVE))
- return true;
-
- 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] +=
- PendingIOStats.counts[io_object][io_context][io_op];
-
- time = PendingIOStats.pending_times[io_object][io_context][io_op];
-
- bktype_shstats->times[io_object][io_context][io_op] +=
- INSTR_TIME_GET_MICROSEC(time);
- }
- }
- }
-
- Assert(pgstat_bktype_io_stats_valid(bktype_shstats, MyBackendType));
-
- LWLockRelease(bktype_lock);
-
- memset(&PendingIOStats, 0, sizeof(PendingIOStats));
-
- have_iostats = false;
-
- return false;
-}
-
-/*
- * Flush out locally pending backend statistics
- *
- * If no stats have been recorded, this function returns false.
- */
-bool
-pgstat_per_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
-{
- PgStatShared_Backend *shbackendioent;
- PgStat_PendingIO *pendingent;
- PgStat_BktypeIO *bktype_shstats;
-
- if (!pgstat_lock_entry(entry_ref, nowait))
+ pgstat_unlock_entry(entry_ref);
return false;
+ }
+ /* per backend IO stats */
shbackendioent = (PgStatShared_Backend *) entry_ref->shared_stats;
bktype_shstats = &shbackendioent->stats.stats;
pendingent = (PgStat_PendingIO *) entry_ref->pending;
@@ -279,6 +218,7 @@ pgstat_per_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
{
instr_time time;
+ /* per backend IO stats */
bktype_shstats->counts[io_object][io_context][io_op] +=
pendingent->counts[io_object][io_context][io_op];
@@ -286,10 +226,19 @@ pgstat_per_backend_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
bktype_shstats->times[io_object][io_context][io_op] +=
INSTR_TIME_GET_MICROSEC(time);
+
+ /* global IO stats */
+ bktype_global_shstats->counts[io_object][io_context][io_op] +=
+ pendingent->counts[io_object][io_context][io_op];
+
+ bktype_global_shstats->times[io_object][io_context][io_op] +=
+ INSTR_TIME_GET_MICROSEC(time);
}
}
}
+ LWLockRelease(bktype_lock);
+
pgstat_unlock_entry(entry_ref);
return true;
--
2.34.1
>From 2d710a44af4d2758a0f33703893712693dfaf0af Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Thu, 7 Nov 2024 12:10:37 +0000
Subject: [PATCH v5 3/4] Don't include other backend's stats in the snapshot
When stats_fetch_consistency is set to 'snapshot', don't include other backend's
stats in the snapshot. There is no use case, so save memory usage.
---
src/backend/utils/activity/pgstat.c | 4 ++++
1 file changed, 4 insertions(+)
100.0% src/backend/utils/activity/
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index d051db5c10..4982dec8a9 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -1181,6 +1181,10 @@ pgstat_build_snapshot(void)
!kind_info->accessed_across_databases)
continue;
+ /* there is no need to include other backend's stats */
+ if (kind == PGSTAT_KIND_PER_BACKEND && p->key.objid != MyProcNumber)
+ continue;
+
if (p->dropped)
continue;
--
2.34.1
>From 2bf372f079e9cbecd82ce431622f7e683f659596 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Thu, 7 Nov 2024 14:27:05 +0000
Subject: [PATCH v5 4/4] Add pg_stat_get_backend_io()
Adding the pg_stat_get_backend_io() function to retrieve I/O statistics for
a particular backend pid.
Note that this function does not return any rows if stats_fetch_consistency
is set to 'snapshot' and the pid of interest is not our own pid (there is no use
case of retrieving other backend's stats with stats_fetch_consistency set to
'snapshot').
---
doc/src/sgml/monitoring.sgml | 17 ++++++++
src/backend/catalog/system_views.sql | 2 +-
src/backend/utils/activity/pgstat_io.c | 18 ++++++++
src/backend/utils/adt/pgstatfuncs.c | 27 +++++++++++-
src/include/catalog/pg_proc.dat | 14 +++----
src/include/pgstat.h | 1 +
src/test/regress/expected/rules.out | 2 +-
src/test/regress/expected/stats.out | 57 ++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 33 +++++++++++++++
9 files changed, 160 insertions(+), 11 deletions(-)
10.4% doc/src/sgml/
5.8% src/backend/utils/activity/
8.6% src/backend/utils/adt/
16.0% src/include/catalog/
32.9% src/test/regress/expected/
24.1% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index fc6aded3da..a6672548b8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4814,6 +4814,23 @@ 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_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. This function does not return any rows if <varname>stats_fetch_consistency</varname>
+ is set to <literal>snapshot</literal> and the process ID is not our own.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 09af4a40a8..6f17f505f1 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1190,7 +1190,7 @@ SELECT
b.fsyncs,
b.fsync_time,
b.stats_reset
-FROM pg_stat_get_my_io() b;
+FROM pg_stat_get_backend_io(NULL) b;
CREATE VIEW pg_stat_wal AS
SELECT
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index 1384f8103e..1063c6bec3 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -162,6 +162,24 @@ pgstat_fetch_my_stat_io(void)
pgstat_fetch_entry(PGSTAT_KIND_PER_BACKEND, InvalidOid, MyProcNumber);
}
+/*
+ * Returns other backend's IO stats or NULL if pgstat_fetch_consistency is set
+ * to 'snapshot'.
+ */
+PgStat_Backend *
+pgstat_fetch_proc_stat_io(ProcNumber procNumber)
+{
+ PgStat_Backend *backend_entry;
+
+ if (pgstat_fetch_consistency == PGSTAT_FETCH_CONSISTENCY_SNAPSHOT)
+ return NULL;
+
+ backend_entry = (PgStat_Backend *) pgstat_fetch_entry(PGSTAT_KIND_PER_BACKEND,
+ InvalidOid, procNumber);
+
+ return backend_entry;
+}
+
/*
* Simpler wrapper of pgstat_per_backend_flush_cb().
*/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index fd16986156..e2041be237 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1475,7 +1475,7 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
}
Datum
-pg_stat_get_my_io(PG_FUNCTION_ARGS)
+pg_stat_get_backend_io(PG_FUNCTION_ARGS)
{
ReturnSetInfo *rsinfo;
PgStat_Backend *backend_stats;
@@ -1487,7 +1487,30 @@ pg_stat_get_my_io(PG_FUNCTION_ARGS)
InitMaterializedSRF(fcinfo, 0);
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- backend_stats = pgstat_fetch_my_stat_io();
+ if (PG_ARGISNULL(0) || PG_GETARG_INT32(0) == MyProcPid)
+ backend_stats = pgstat_fetch_my_stat_io();
+ else
+ {
+ PGPROC *proc;
+ ProcNumber procNumber;
+ int pid = PG_GETARG_INT32(0);
+
+ proc = BackendPidGetProc(pid);
+
+ /* maybe an auxiliary process? */
+ if (proc == NULL)
+ proc = AuxiliaryPidGetProc(pid);
+
+ if (proc != NULL)
+ {
+ procNumber = GetNumberFromPGProc(proc);
+ backend_stats = pgstat_fetch_proc_stat_io(procNumber);
+ if (!backend_stats)
+ return (Datum) 0;
+ }
+ else
+ return (Datum) 0;
+ }
bktype = backend_stats->bktype;
bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 89eb89efe4..fafee5a947 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5903,14 +5903,14 @@
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: my backend IO statistics',
- proname => 'pg_stat_get_my_io', prorows => '5', proretset => 't',
+{ oid => '8806', descr => 'statistics: per backend IO statistics',
+ proname => 'pg_stat_get_backend_io', prorows => '5', proretset => 't',
provolatile => 'v', proparallel => 'r', prorettype => 'record',
- proargtypes => '',
- proallargtypes => '{text,text,text,int8,float8,int8,float8,int8,float8,int8,float8,int8,int8,int8,int8,int8,float8,timestamptz}',
- proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
- 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_my_io' },
+ proargtypes => 'int4', proisstrict => 'f',
+ 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',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 13b9c71759..54c9f8b45c 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -576,6 +576,7 @@ extern void pgstat_count_io_op_time(IOObject io_object, IOContext io_context,
extern PgStat_IO *pgstat_fetch_stat_io(void);
extern PgStat_Backend *pgstat_fetch_my_stat_io(void);
+extern PgStat_Backend *pgstat_fetch_proc_stat_io(ProcNumber procNumber);
extern const char *pgstat_get_io_context_name(IOContext io_context);
extern const char *pgstat_get_io_object_name(IOObject io_object);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f92516b047..3cc0cfbcb5 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1416,7 +1416,7 @@ pg_my_stat_io| SELECT backend_type,
fsyncs,
fsync_time,
stats_reset
- FROM pg_stat_get_my_io() b(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);
+ FROM pg_stat_get_backend_io(NULL::integer) b(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);
pg_policies| SELECT n.nspname AS schemaname,
c.relname AS tablename,
pol.polname AS policyname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 846e693483..846ffb59f2 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1263,12 +1263,18 @@ 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_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT sum(extends) AS backend_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_my_stat_io
WHERE object = 'relation' \gset my_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 backend_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();
@@ -1293,6 +1299,15 @@ SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends;
t
(1 row)
+SELECT sum(extends) AS backend_io_sum_shared_after_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :backend_io_sum_shared_after_extends > :backend_io_sum_shared_before_extends;
+ ?column?
+----------
+ t
+(1 row)
+
-- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes
-- and fsyncs in the global stats (not for the backend).
-- See comment above for rationale for two explicit CHECKPOINTs.
@@ -1331,6 +1346,48 @@ SELECT current_setting('fsync') = 'off'
t
(1 row)
+-- Check the stats_fetch_consistency behavior on per backend I/O stats
+SELECT pid AS checkpointer_pid FROM pg_stat_activity
+ WHERE backend_type = 'checkpointer' \gset
+BEGIN;
+SET LOCAL stats_fetch_consistency = snapshot;
+SELECT sum(extends) AS my_io_sum_shared_before_extends
+ FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT sum(extends) AS backend_io_sum_shared_before_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;
+SELECT pg_stat_force_next_flush();
+ pg_stat_force_next_flush
+--------------------------
+
+(1 row)
+
+SELECT sum(extends) AS my_io_sum_shared_after_extends
+ FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :my_io_sum_shared_after_extends = :my_io_sum_shared_before_extends;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT sum(extends) AS backend_io_sum_shared_after_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :backend_io_sum_shared_after_extends = :backend_io_sum_shared_before_extends;
+ ?column?
+----------
+ t
+(1 row)
+
+-- Don't return any rows if querying other backend's stats with snapshot set
+SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid);
+ ?column?
+----------
+ t
+(1 row)
+
+ROLLBACK;
-- 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
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 9cb14b7182..e597c4d597 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -611,12 +611,18 @@ 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_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT sum(extends) AS backend_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_my_stat_io
WHERE object = 'relation' \gset my_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 backend_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();
@@ -626,6 +632,10 @@ SELECT :io_sum_shared_after_extends > :io_sum_shared_before_extends;
SELECT sum(extends) AS my_io_sum_shared_after_extends
FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset
SELECT :my_io_sum_shared_after_extends > :my_io_sum_shared_before_extends;
+SELECT sum(extends) AS backend_io_sum_shared_after_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :backend_io_sum_shared_after_extends > :backend_io_sum_shared_before_extends;
-- After a checkpoint, there should be some additional IOCONTEXT_NORMAL writes
-- and fsyncs in the global stats (not for the backend).
@@ -647,6 +657,29 @@ 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);
+-- Check the stats_fetch_consistency behavior on per backend I/O stats
+SELECT pid AS checkpointer_pid FROM pg_stat_activity
+ WHERE backend_type = 'checkpointer' \gset
+BEGIN;
+SET LOCAL stats_fetch_consistency = snapshot;
+SELECT sum(extends) AS my_io_sum_shared_before_extends
+ FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT sum(extends) AS backend_io_sum_shared_before_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+INSERT INTO test_io_shared SELECT i FROM generate_series(1,100)i;
+SELECT pg_stat_force_next_flush();
+SELECT sum(extends) AS my_io_sum_shared_after_extends
+ FROM pg_my_stat_io WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :my_io_sum_shared_after_extends = :my_io_sum_shared_before_extends;
+SELECT sum(extends) AS backend_io_sum_shared_after_extends
+ FROM pg_stat_get_backend_io(pg_backend_pid())
+ WHERE context = 'normal' AND object = 'relation' \gset
+SELECT :backend_io_sum_shared_after_extends = :backend_io_sum_shared_before_extends;
+-- Don't return any rows if querying other backend's stats with snapshot set
+SELECT count(1) = 0 FROM pg_stat_get_backend_io(:checkpointer_pid);
+ROLLBACK;
+
-- 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
--
2.34.1
