Hi hackers,
Please find attached a patch to implement $SUBJECT.
While pg_stat_io provides cluster-wide I/O statistics, this patch adds a new
pg_my_stat_io view to display "my" backend I/O statistics and a new
pg_stat_get_backend_io() function to retrieve the I/O statistics for a given
backend pid.
By having the per backend level of granularity, one could for example identify
which running backend is responsible for most of the reads, most of the extends
and so on... The pg_my_stat_io view could also be useful to check the
impact on the I/O made by some operations, queries,... in the current session.
Some remarks:
- it is split in 2 sub patches: 0001 introducing the necessary changes to
provide
the pg_my_stat_io view and 0002 to add the pg_stat_get_backend_io() function.
- the idea of having per backend I/O statistics has already been mentioned in
[1] by Andres.
Some implementation choices:
- The KIND_IO stats are still "fixed amount" ones as the maximum number of
backend is fixed.
- The statistics snapshot is made for the global stats (the aggregated ones) and
for my backend stats. The snapshot is not build for all the backend stats (that
could be memory expensive depending on the number of max connections and given
the fact that PgStat_IO is 16KB long).
- The above point means that pg_stat_get_backend_io() behaves as if
stats_fetch_consistency is set to none (each execution re-fetches counters
from shared memory).
- The above 2 points are also the reasons why the pg_my_stat_io view has been
added (as its results takes care of the stats_fetch_consistency setting). I
think
that makes sense to rely on it in that case, while I'm not sure that would make
a lot of sense to retrieve other's backend I/O stats and taking care of
stats_fetch_consistency.
[1]:
https://www.postgresql.org/message-id/20230309003438.rectf7xo7pw5t5cj%40awork3.anarazel.de
Looking forward to your feedback,
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
>From 04c0a9ed462850a620df61e5d0dc5053717f2b26 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Thu, 22 Aug 2024 15:16:50 +0000
Subject: [PATCH v1 1/2] per backend I/O statistics
While pg_stat_io provides cluster-wide I/O statistics, this commit adds a new
pg_my_stat_io view to display "my" backend I/O statistics. The KIND_IO stats
are still "fixed amount" ones as the maximum number of backend is fixed.
The statistics snapshot is made for the global stats (the aggregated ones) and
for my backend stats. The snapshot is not build for all the backend stats (that
could be memory expensive depending of the number of max connections and given
the fact that PgStat_IO is 16K bytes long).
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.
Bump catalog version.
---
doc/src/sgml/config.sgml | 4 +-
doc/src/sgml/monitoring.sgml | 28 +++++++++
src/backend/catalog/system_views.sql | 24 +++++++-
src/backend/utils/activity/pgstat.c | 10 ++-
src/backend/utils/activity/pgstat_io.c | 75 +++++++++++++++++++----
src/backend/utils/activity/pgstat_shmem.c | 4 +-
src/backend/utils/adt/pgstatfuncs.c | 18 +++++-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 8 +--
src/include/pgstat.h | 8 ++-
src/include/utils/pgstat_internal.h | 13 ++--
src/test/regress/expected/rules.out | 21 ++++++-
src/test/regress/expected/stats.out | 44 ++++++++++++-
src/test/regress/sql/stats.sql | 25 +++++++-
14 files changed, 245 insertions(+), 39 deletions(-)
10.7% doc/src/sgml/
4.1% src/backend/catalog/
30.9% src/backend/utils/activity/
5.2% src/backend/utils/adt/
7.9% src/include/catalog/
3.4% src/include/utils/
22.0% src/test/regress/expected/
12.7% src/test/regress/sql/
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0aec11f443..2a59b97093 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -8331,7 +8331,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 55417a6fa9..27d2548d61 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
@@ -2875,6 +2885,24 @@ description | Waiting for a newly initialized WAL file to reach durable storage
+ </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>
<sect2 id="monitoring-pg-stat-bgwriter-view">
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 19cabc9a47..7fa050893a 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1166,7 +1166,29 @@ SELECT
b.fsyncs,
b.fsync_time,
b.stats_reset
-FROM pg_stat_get_io() b;
+FROM pg_stat_get_io(true) 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_io(false) b;
CREATE VIEW pg_stat_wal AS
SELECT
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index b2ca3f39b7..b0905e6bf1 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -406,10 +406,11 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.fixed_amount = true,
- .snapshot_ctl_off = offsetof(PgStat_Snapshot, io),
+ .snapshot_ctl_off = offsetof(PgStat_Snapshot, global_io),
.shared_ctl_off = offsetof(PgStat_ShmemControl, io),
- .shared_data_off = offsetof(PgStatShared_IO, stats),
- .shared_data_len = sizeof(((PgStatShared_IO *) 0)->stats),
+ /* [de-]serialize global_stats only */
+ .shared_data_off = offsetof(PgStatShared_IO, global_stats),
+ .shared_data_len = sizeof(((PgStatShared_IO *) 0)->global_stats),
.init_shmem_cb = pgstat_io_init_shmem_cb,
.reset_all_cb = pgstat_io_reset_all_cb,
@@ -580,6 +581,9 @@ pgstat_shutdown_hook(int code, Datum arg)
pgstat_report_stat(true);
+ /* reset the pgstat_io counter related to this proc number */
+ pgstat_reset_io_counter_internal(MyProcNumber, GetCurrentTimestamp());
+
/* there shouldn't be any pending changes left */
Assert(dlist_is_empty(&pgStatPending));
dlist_init(&pgStatPending);
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index 8af55989ee..43280f4892 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -31,6 +31,7 @@ typedef struct PgStat_PendingIO
static PgStat_PendingIO PendingIOStats;
bool have_iostats = false;
+void pgstat_reset_io_counter_internal(int index, TimestampTz ts);
/*
* Check that stats have not been counted for any combination of IOObject,
@@ -154,11 +155,19 @@ pgstat_count_io_op_time(IOObject io_object, IOContext io_context, IOOp io_op,
}
PgStat_IO *
-pgstat_fetch_stat_io(void)
+pgstat_fetch_global_stat_io(void)
{
pgstat_snapshot_fixed(PGSTAT_KIND_IO);
- return &pgStatLocal.snapshot.io;
+ return &pgStatLocal.snapshot.global_io;
+}
+
+PgStat_IO *
+pgstat_fetch_my_stat_io(void)
+{
+ pgstat_snapshot_fixed(PGSTAT_KIND_IO);
+
+ return &pgStatLocal.snapshot.my_io;
}
/*
@@ -174,13 +183,16 @@ pgstat_flush_io(bool nowait)
{
LWLock *bktype_lock;
PgStat_BktypeIO *bktype_shstats;
+ PgStat_BktypeIO *global_bktype_shstats;
if (!have_iostats)
return false;
bktype_lock = &pgStatLocal.shmem->io.locks[MyBackendType];
bktype_shstats =
- &pgStatLocal.shmem->io.stats.stats[MyBackendType];
+ &pgStatLocal.shmem->io.stats[MyProcNumber].stats[MyBackendType];
+ global_bktype_shstats =
+ &pgStatLocal.shmem->io.global_stats.stats[MyBackendType];
if (!nowait)
LWLockAcquire(bktype_lock, LW_EXCLUSIVE);
@@ -194,19 +206,28 @@ pgstat_flush_io(bool nowait)
for (int io_op = 0; io_op < IOOP_NUM_TYPES; io_op++)
{
instr_time time;
+ PgStat_Counter counter;
+
+ counter = PendingIOStats.counts[io_object][io_context][io_op];
+
+ bktype_shstats->counts[io_object][io_context][io_op] += counter;
- bktype_shstats->counts[io_object][io_context][io_op] +=
- PendingIOStats.counts[io_object][io_context][io_op];
+ global_bktype_shstats->counts[io_object][io_context][io_op] +=
+ counter;
time = PendingIOStats.pending_times[io_object][io_context][io_op];
bktype_shstats->times[io_object][io_context][io_op] +=
INSTR_TIME_GET_MICROSEC(time);
+
+ global_bktype_shstats->times[io_object][io_context][io_op] +=
+ INSTR_TIME_GET_MICROSEC(time);
}
}
}
Assert(pgstat_bktype_io_stats_valid(bktype_shstats, MyBackendType));
+ Assert(pgstat_bktype_io_stats_valid(global_bktype_shstats, MyBackendType));
LWLockRelease(bktype_lock);
@@ -260,13 +281,38 @@ pgstat_io_init_shmem_cb(void *stats)
LWLockInitialize(&stat_shmem->locks[i], LWTRANCHE_PGSTATS_DATA);
}
+void
+pgstat_reset_io_counter_internal(int index, TimestampTz ts)
+{
+ for (int i = 0; i < BACKEND_NUM_TYPES; i++)
+ {
+ LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[i];
+ PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stats[index].stats[i];
+
+ LWLockAcquire(bktype_lock, LW_EXCLUSIVE);
+
+ /*
+ * Use the lock in the first BackendType's PgStat_BktypeIO to protect
+ * the reset timestamp as well.
+ */
+ if (i == 0)
+ pgStatLocal.shmem->io.stats[index].stat_reset_timestamp = ts;
+
+ memset(bktype_shstats, 0, sizeof(*bktype_shstats));
+ LWLockRelease(bktype_lock);
+ }
+}
+
void
pgstat_io_reset_all_cb(TimestampTz ts)
{
+ for (int i = 0; i < NumProcStatSlots; i++)
+ pgstat_reset_io_counter_internal(i, ts);
+
for (int i = 0; i < BACKEND_NUM_TYPES; i++)
{
LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[i];
- PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stats.stats[i];
+ PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.global_stats.stats[i];
LWLockAcquire(bktype_lock, LW_EXCLUSIVE);
@@ -275,7 +321,7 @@ pgstat_io_reset_all_cb(TimestampTz ts)
* the reset timestamp as well.
*/
if (i == 0)
- pgStatLocal.shmem->io.stats.stat_reset_timestamp = ts;
+ pgStatLocal.shmem->io.global_stats.stat_reset_timestamp = ts;
memset(bktype_shstats, 0, sizeof(*bktype_shstats));
LWLockRelease(bktype_lock);
@@ -288,8 +334,10 @@ pgstat_io_snapshot_cb(void)
for (int i = 0; i < BACKEND_NUM_TYPES; i++)
{
LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[i];
- PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stats.stats[i];
- PgStat_BktypeIO *bktype_snap = &pgStatLocal.snapshot.io.stats[i];
+ PgStat_BktypeIO *bktype_global_shstats = &pgStatLocal.shmem->io.global_stats.stats[i];
+ PgStat_BktypeIO *bktype_global_snap = &pgStatLocal.snapshot.global_io.stats[i];
+ PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stats[MyProcNumber].stats[i];
+ PgStat_BktypeIO *bktype_snap = &pgStatLocal.snapshot.my_io.stats[i];
LWLockAcquire(bktype_lock, LW_SHARED);
@@ -298,10 +346,15 @@ pgstat_io_snapshot_cb(void)
* the reset timestamp as well.
*/
if (i == 0)
- pgStatLocal.snapshot.io.stat_reset_timestamp =
- pgStatLocal.shmem->io.stats.stat_reset_timestamp;
+ {
+ pgStatLocal.snapshot.global_io.stat_reset_timestamp =
+ pgStatLocal.shmem->io.global_stats.stat_reset_timestamp;
+ pgStatLocal.snapshot.my_io.stat_reset_timestamp =
+ pgStatLocal.shmem->io.stats[MyProcNumber].stat_reset_timestamp;
+ }
/* using struct assignment due to better type safety */
+ *bktype_global_snap = *bktype_global_shstats;
*bktype_snap = *bktype_shstats;
LWLockRelease(bktype_lock);
}
diff --git a/src/backend/utils/activity/pgstat_shmem.c b/src/backend/utils/activity/pgstat_shmem.c
index ec93bf6902..7cf1c64f9e 100644
--- a/src/backend/utils/activity/pgstat_shmem.c
+++ b/src/backend/utils/activity/pgstat_shmem.c
@@ -128,7 +128,7 @@ StatsShmemSize(void)
{
Size sz;
- sz = MAXALIGN(sizeof(PgStat_ShmemControl));
+ sz = MAXALIGN(sizeof(PgStat_ShmemControl) + mul_size(sizeof(PgStat_IO), NumProcStatSlots));
sz = add_size(sz, pgstat_dsa_init_size());
/* Add shared memory for all the custom fixed-numbered statistics */
@@ -172,7 +172,7 @@ StatsShmemInit(void)
Assert(!found);
/* the allocation of pgStatLocal.shmem itself */
- p += MAXALIGN(sizeof(PgStat_ShmemControl));
+ p += MAXALIGN(sizeof(PgStat_ShmemControl) + mul_size(sizeof(PgStat_IO), NumProcStatSlots));
/*
* Create a small dsa allocation in plain shared memory. This is
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 3221137123..58e321f421 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1359,18 +1359,30 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
ReturnSetInfo *rsinfo;
PgStat_IO *backends_io_stats;
Datum reset_time;
+ bool is_global;
+
+ is_global = PG_GETARG_BOOL(0);
InitMaterializedSRF(fcinfo, 0);
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- backends_io_stats = pgstat_fetch_stat_io();
+ if (is_global)
+ backends_io_stats = pgstat_fetch_global_stat_io();
+ else
+ backends_io_stats = pgstat_fetch_my_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];
+ Datum bktype_desc;
+ PgStat_BktypeIO *bktype_stats;
+
+ if (!is_global && bktype != MyBackendType)
+ continue;
+
+ bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
+ bktype_stats = &backends_io_stats->stats[bktype];
/*
* In Assert builds, we can afford an extra loop through all of the
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 1980d492c3..fbee0db2eb 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202408301
+#define CATALOG_VERSION_NO 202409021
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 85f42be1b3..1184341fc8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5825,10 +5825,10 @@
{ oid => '6214', descr => 'statistics: per backend type IO statistics',
proname => 'pg_stat_get_io', prorows => '30', 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}',
+ proargtypes => 'bool',
+ proallargtypes => '{bool,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 => '{is_global,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 => '1136', descr => 'statistics: information about WAL activity',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f63159c55c..9ec4053a3e 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -15,6 +15,7 @@
#include "datatype/timestamp.h"
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
+#include "storage/proc.h"
#include "utils/backend_progress.h" /* for backward compatibility */
#include "utils/backend_status.h" /* for backward compatibility */
#include "utils/relcache.h"
@@ -75,6 +76,8 @@
*/
#define PGSTAT_KIND_EXPERIMENTAL 128
+#define NumProcStatSlots (MaxBackends + NUM_AUXILIARY_PROCS)
+
static inline bool
pgstat_is_kind_builtin(PgStat_Kind kind)
{
@@ -556,7 +559,8 @@ extern instr_time pgstat_prepare_io_time(bool track_io_guc);
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_IO *pgstat_fetch_global_stat_io(void);
+extern PgStat_IO *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);
@@ -565,7 +569,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_reset_io_counter_internal(int index, TimestampTz ts);
/*
* Functions in pgstat_database.c
diff --git a/src/include/utils/pgstat_internal.h b/src/include/utils/pgstat_internal.h
index fb132e439d..1eeb8aa107 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -347,11 +347,12 @@ typedef struct PgStatShared_Checkpointer
typedef struct PgStatShared_IO
{
/*
- * locks[i] protects stats.stats[i]. locks[0] also protects
- * stats.stat_reset_timestamp.
+ * locks[i] protects global_stats.stats[i]. locks[0] also protects
+ * global_stats.stat_reset_timestamp.
*/
LWLock locks[BACKEND_NUM_TYPES];
- PgStat_IO stats;
+ PgStat_IO global_stats;
+ PgStat_IO stats[FLEXIBLE_ARRAY_MEMBER];
} PgStatShared_IO;
typedef struct PgStatShared_SLRU
@@ -444,7 +445,6 @@ typedef struct PgStat_ShmemControl
PgStatShared_Archiver archiver;
PgStatShared_BgWriter bgwriter;
PgStatShared_Checkpointer checkpointer;
- PgStatShared_IO io;
PgStatShared_SLRU slru;
PgStatShared_Wal wal;
@@ -454,6 +454,8 @@ typedef struct PgStat_ShmemControl
*/
void *custom_data[PGSTAT_KIND_CUSTOM_SIZE];
+ /* has to be at the end due to FLEXIBLE_ARRAY_MEMBER */
+ PgStatShared_IO io;
} PgStat_ShmemControl;
@@ -475,7 +477,8 @@ typedef struct PgStat_Snapshot
PgStat_CheckpointerStats checkpointer;
- PgStat_IO io;
+ PgStat_IO my_io;
+ PgStat_IO global_io;
PgStat_SLRUStats slru[SLRU_NUM_ELEMENTS];
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 862433ee52..30dcb80f7b 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_io(false) 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,
@@ -1902,7 +1921,7 @@ pg_stat_io| SELECT backend_type,
fsyncs,
fsync_time,
stats_reset
- FROM pg_stat_get_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_io(true) 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_stat_progress_analyze| SELECT s.pid,
s.datid,
d.datname,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 6e08898b18..c489e528e0 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,14 @@ 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
+SELECT :my_io_stats_post_reset < :my_io_stats_pre_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 d8ac0d06f4..c95cb71652 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;
@@ -630,7 +638,13 @@ 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_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 +776,15 @@ 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
+SELECT :my_io_stats_post_reset < :my_io_stats_pre_reset;
-- test BRIN index doesn't block HOT update
--
2.34.1
>From 3e4ea302e9ae554cae76e7a4dc4f90b4503cf842 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot...@gmail.com>
Date: Wed, 28 Aug 2024 12:59:02 +0000
Subject: [PATCH v1 2/2] 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 this function behaves as if stats_fetch_consistency
is set to none.
---
doc/src/sgml/monitoring.sgml | 18 ++++
src/backend/utils/activity/pgstat_io.c | 6 ++
src/backend/utils/adt/pgstatfuncs.c | 120 +++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 ++
src/include/pgstat.h | 1 +
src/test/regress/expected/stats.out | 25 ++++++
src/test/regress/sql/stats.sql | 16 +++-
7 files changed, 193 insertions(+), 1 deletion(-)
10.8% doc/src/sgml/
47.6% src/backend/utils/adt/
9.2% src/include/catalog/
15.3% src/test/regress/expected/
14.4% src/test/regress/sql/
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 27d2548d61..ad89d9caa7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4713,6 +4713,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_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 behaves as if <varname>stats_fetch_consistency</varname>
+ is set to <literal>none</literal> (means each execution re-fetches
+ counters from shared memory).
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/activity/pgstat_io.c b/src/backend/utils/activity/pgstat_io.c
index 43280f4892..8fe8c224a9 100644
--- a/src/backend/utils/activity/pgstat_io.c
+++ b/src/backend/utils/activity/pgstat_io.c
@@ -170,6 +170,12 @@ pgstat_fetch_my_stat_io(void)
return &pgStatLocal.snapshot.my_io;
}
+PgStat_IO *
+pgstat_fetch_proc_stat_io(ProcNumber procNumber)
+{
+ return &pgStatLocal.shmem->io.stats[procNumber];
+}
+
/*
* Flush out locally pending IO statistics
*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 58e321f421..502b9662a1 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1474,6 +1474,126 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+Datum
+pg_stat_get_backend_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_IO *backends_io_stats;
+ Datum reset_time;
+ ProcNumber procNumber;
+ PGPROC *proc;
+
+ int backend_pid = PG_GETARG_INT32(0);
+
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ InitMaterializedSRF(fcinfo, 0);
+
+ proc = BackendPidGetProc(backend_pid);
+
+ /* This backend_pid does not exist */
+
+ if (proc != NULL)
+ {
+ procNumber = GetNumberFromPGProc(proc);
+ backends_io_stats = pgstat_fetch_proc_stat_io(procNumber);
+ 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] = TimestampTzGetDatum(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;
+}
+
/*
* Returns statistics of WAL activity
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1184341fc8..8c1e2d6a46 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5830,6 +5830,14 @@
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
proargnames => '{is_global,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 => '8896', descr => 'statistics: per backend type IO statistics',
+ proname => 'pg_stat_get_backend_io', prorows => '30', 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',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 9ec4053a3e..963fa36be9 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -561,6 +561,7 @@ extern void pgstat_count_io_op_time(IOObject io_object, IOContext io_context,
extern PgStat_IO *pgstat_fetch_global_stat_io(void);
extern PgStat_IO *pgstat_fetch_my_stat_io(void);
+extern PgStat_IO *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/stats.out b/src/test/regress/expected/stats.out
index c489e528e0..aced015c2f 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.
@@ -1553,6 +1568,8 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
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 sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS backend_io_stats_pre_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT pg_stat_reset_shared('io');
pg_stat_reset_shared
----------------------
@@ -1575,6 +1592,14 @@ SELECT :my_io_stats_post_reset < :my_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 backend_io_stats_post_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
+SELECT :backend_io_stats_post_reset < :backend_io_stats_pre_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 c95cb71652..d05009e1f5 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).
@@ -778,6 +788,8 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
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 sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS backend_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
@@ -785,7 +797,9 @@ 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
SELECT :my_io_stats_post_reset < :my_io_stats_pre_reset;
-
+SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS backend_io_stats_post_reset
+ FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
+SELECT :backend_io_stats_post_reset < :backend_io_stats_pre_reset;
-- test BRIN index doesn't block HOT update
CREATE TABLE brin_hot (
--
2.34.1
