public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 01/13] doc: Remove 'synchronized' from --no-sync
8+ messages / 4 participants
[nested] [flat]
* [PATCH 01/13] doc: Remove 'synchronized' from --no-sync
@ 2022-02-17 03:07 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Justin Pryzby @ 2022-02-17 03:07 UTC (permalink / raw)
Since it would be more accurate to say "unsynchronized".
The corresponding change was made for pgupgrade.sgml in commit 410aa248
---
doc/src/sgml/ref/pg_rewind.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/pg_rewind.sgml b/doc/src/sgml/ref/pg_rewind.sgml
index e808239aa5b..3231f67845a 100644
--- a/doc/src/sgml/ref/pg_rewind.sgml
+++ b/doc/src/sgml/ref/pg_rewind.sgml
@@ -210,7 +210,7 @@ PostgreSQL documentation
to be written safely to disk. This option causes
<command>pg_rewind</command> to return without waiting, which is
faster, but means that a subsequent operating system crash can leave
- the synchronized data directory corrupt. Generally, this option is
+ the data directory corrupt. Generally, this option is
useful for testing but should not be used on a production
installation.
</para>
--
2.17.1
--KlAEzMkarCnErv5Q
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0002-doc-pg_column_compression-we-say-method-not-algorith.patch"
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: per backend I/O statistics
@ 2024-09-03 07:21 Bertrand Drouvot <[email protected]>
2024-09-05 13:03 ` Re: per backend I/O statistics Alvaro Herrera <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Bertrand Drouvot @ 2024-09-03 07:21 UTC (permalink / raw)
To: Kyotaro Horiguchi <[email protected]>; +Cc: [email protected]
Hi,
On Tue, Sep 03, 2024 at 03:37:49PM +0900, Kyotaro Horiguchi wrote:
> At Mon, 2 Sep 2024 14:55:52 +0000, Bertrand Drouvot <[email protected]> wrote in
> > 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.
> >
>
> I'm not sure about the usefulness of having the stats only available
> from the current session. Since they are stored in shared memory,
> shouldn't we make them accessible to all backends?
Thanks for the feedback!
The stats are accessible to all backends thanks to 0002 and the introduction
of the pg_stat_get_backend_io() function.
> However, this would
> introduce permission considerations and could become complex.
Not sure that the data exposed here is sensible enough to consider permission
restriction.
> When I first looked at this patch, my initial thought was whether we
> should let these stats stay "fixed." The reason why the current
> PGSTAT_KIND_IO is fixed is that there is only one global statistics
> storage for the entire database. If we have stats for a flexible
> number of backends, it would need to be non-fixed, perhaps with the
> entry for INVALID_PROC_NUMBER storing the global I/O stats, I
> suppose. However, one concern with that approach would be the impact
> on performance due to the frequent creation and deletion of stats
> entries caused by high turnover of backends.
>
The pros of using the fixed amount are:
- less code change (I think as I did not write the non fixed counterpart)
- probably better performance and less scalabilty impact (in case of high rate
of backends creation/ deletion)
Cons is probably allocating shared memory space that might not be used (
sizeof(PgStat_IO) is 16392 so that could be a concern for a high number of
unused connection). OTOH, if a high number of connections is not used that might
be worth to reduce the max_connections setting.
"Conceptually" speaking, we know what the maximum number of backend is, so I
think that using the fixed amount approach makes sense (somehow I think it can
be compared to PGSTAT_KIND_SLRU which relies on SLRU_NUM_ELEMENTS).
> Just to be clear, the above comments are not meant to oppose the
> current implementation approach. They are purely for the sake of
> discussing comparisons with other possible approaches.
No problem at all, thanks for your feedback and sharing your thoughts!
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: per backend I/O statistics
2024-09-03 07:21 Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
@ 2024-09-05 13:03 ` Alvaro Herrera <[email protected]>
2024-09-05 14:14 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2024-09-05 13:03 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected]
On 2024-Sep-03, Bertrand Drouvot wrote:
> Cons is probably allocating shared memory space that might not be used (
> sizeof(PgStat_IO) is 16392 so that could be a concern for a high number of
> unused connection). OTOH, if a high number of connections is not used that might
> be worth to reduce the max_connections setting.
I was surprised by the size you mention so I went to look for the
definition of that struct:
typedef struct PgStat_IO
{
TimestampTz stat_reset_timestamp;
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
(It would be good to have more than zero comments here BTW)
So what's happening is that struct PgStat_IO stores the data for every
single process type, be it regular backends, backend-like auxiliary
processes, and all other potential postmaster children. So it seems to
me that storing one of these struct for "my backend stats" is wrong: I
think you should be using PgStat_BktypeIO instead (or maybe another
struct which has a reset timestamp plus BktypeIO, if you care about the
reset time). That struct is only 1024 bytes long, so it seems much more
reasonable to have one of those per backend.
Another way to think about this might be to say that the B_BACKEND
element of the PgStat_Snapshot.io array should really be spread out to
MaxBackends separate elements. This would make it more difficult to
obtain a total value accumulating ops done by all backends (since it's
require to sum the values of each backend), but it allows you to obtain
backend-specific values, including those of remote backends rather than
only your own, as Kyotaro suggests.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: per backend I/O statistics
2024-09-03 07:21 Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-05 13:03 ` Re: per backend I/O statistics Alvaro Herrera <[email protected]>
@ 2024-09-05 14:14 ` Bertrand Drouvot <[email protected]>
2024-09-06 15:03 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Bertrand Drouvot @ 2024-09-05 14:14 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected]
Hi,
On Thu, Sep 05, 2024 at 03:03:32PM +0200, Alvaro Herrera wrote:
> On 2024-Sep-03, Bertrand Drouvot wrote:
>
> > Cons is probably allocating shared memory space that might not be used (
> > sizeof(PgStat_IO) is 16392 so that could be a concern for a high number of
> > unused connection). OTOH, if a high number of connections is not used that might
> > be worth to reduce the max_connections setting.
>
> I was surprised by the size you mention so I went to look for the
> definition of that struct:
>
Thanks for looking at it!
> typedef struct PgStat_IO
> {
> TimestampTz stat_reset_timestamp;
> PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
> } PgStat_IO;
>
> (It would be good to have more than zero comments here BTW)
>
> So what's happening is that struct PgStat_IO stores the data for every
> single process type, be it regular backends, backend-like auxiliary
> processes, and all other potential postmaster children.
Yeap.
> So it seems to
> me that storing one of these struct for "my backend stats" is wrong: I
> think you should be using PgStat_BktypeIO instead (or maybe another
> struct which has a reset timestamp plus BktypeIO, if you care about the
> reset time). That struct is only 1024 bytes long, so it seems much more
> reasonable to have one of those per backend.
Yeah, that could be an area of improvement. Thanks, I'll look at it.
Currently the filtering is done when retrieving the per backend stats but better
to do it when storing the stats.
> Another way to think about this might be to say that the B_BACKEND
> element of the PgStat_Snapshot.io array should really be spread out to
> MaxBackends separate elements. This would make it more difficult to
> obtain a total value accumulating ops done by all backends (since it's
> require to sum the values of each backend), but it allows you to obtain
> backend-specific values, including those of remote backends rather than
> only your own, as Kyotaro suggests.
>
One backend can already see the stats of the other backends thanks to the
pg_stat_get_backend_io() function (that takes a backend pid as parameter)
that is introduced in the 0002 sub-patch.
I'll ensure that's still the case with the next version of the patch.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: per backend I/O statistics
2024-09-03 07:21 Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-05 13:03 ` Re: per backend I/O statistics Alvaro Herrera <[email protected]>
2024-09-05 14:14 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
@ 2024-09-06 15:03 ` Bertrand Drouvot <[email protected]>
2024-09-09 06:29 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-13 04:25 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-13 13:45 ` Re: per backend I/O statistics Nazir Bilal Yavuz <[email protected]>
0 siblings, 3 replies; 8+ messages in thread
From: Bertrand Drouvot @ 2024-09-06 15:03 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected]
Hi,
On Thu, Sep 05, 2024 at 02:14:47PM +0000, Bertrand Drouvot wrote:
> Hi,
>
> On Thu, Sep 05, 2024 at 03:03:32PM +0200, Alvaro Herrera wrote:
> > So it seems to
> > me that storing one of these struct for "my backend stats" is wrong: I
> > think you should be using PgStat_BktypeIO instead (or maybe another
> > struct which has a reset timestamp plus BktypeIO, if you care about the
> > reset time). That struct is only 1024 bytes long, so it seems much more
> > reasonable to have one of those per backend.
>
> Yeah, that could be an area of improvement. Thanks, I'll look at it.
> Currently the filtering is done when retrieving the per backend stats but better
> to do it when storing the stats.
I ended up creating (in v2 attached):
"
typedef struct PgStat_Backend_IO
{
TimestampTz stat_reset_timestamp;
BackendType bktype;
PgStat_BktypeIO stats;
} PgStat_Backend_IO;
"
The stat_reset_timestamp is there so that one knows when a particular backend
had its I/O stats reset. Also the backend type is part of the struct to
be able to filter the stats correctly when we display them.
The struct size is 1040 Bytes and that's much more reasonable than the size
needed for per backend I/O stats in v1 (about 16KB).
> One backend can already see the stats of the other backends thanks to the
> pg_stat_get_backend_io() function (that takes a backend pid as parameter)
> that is introduced in the 0002 sub-patch.
0002 still provides the pg_stat_get_backend_io() function so that one could
get the stats of other backends.
Example:
postgres=# select backend_type,object,context,reads,extends,hits from pg_stat_get_backend_io(3779502);
backend_type | object | context | reads | extends | hits
----------------+---------------+-----------+-------+---------+--------
client backend | relation | bulkread | 0 | | 0
client backend | relation | bulkwrite | 0 | 0 | 0
client backend | relation | normal | 73 | 2216 | 504674
client backend | relation | vacuum | 0 | 0 | 0
client backend | temp relation | normal | 0 | 0 | 0
(5 rows)
could be an individual walsender:
postgres=# select pid, backend_type from pg_stat_activity where backend_type = 'walsender';
pid | backend_type
---------+--------------
3779565 | walsender
(1 row)
postgres=# select backend_type,object,context,reads,hits from pg_stat_get_backend_io(3779565);
backend_type | object | context | reads | hits
--------------+---------------+-----------+-------+------
walsender | relation | bulkread | 0 | 0
walsender | relation | bulkwrite | 0 | 0
walsender | relation | normal | 6 | 48
walsender | relation | vacuum | 0 | 0
walsender | temp relation | normal | 0 | 0
(5 rows)
and so on...
Remarks:
- As stated up-thread, the pg_stat_get_backend_io() behaves as if
stats_fetch_consistency is set to none (each execution re-fetches counters
from shared memory). Indeed, the snapshot is not build in each backend to copy
all the others backends stats, as 1/ there is no use case (there is no need to
get others backends I/O statistics while taking care of the stats_fetch_consistency)
and 2/ that could be memory expensive depending of the number of max connections.
- If we agree on the current proposal then I'll do some refactoring in
pg_stat_get_backend_io(), pg_stat_get_my_io() and pg_stat_get_io() to avoid
duplicated code (it's not done yet to ease the review).
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
[text/x-diff] v2-0001-per-backend-I-O-statistics.patch (30.6K, ../../[email protected]/2-v2-0001-per-backend-I-O-statistics.patch)
download | inline diff:
From edc6da2601350edf8647b309ada4ecec4a86dd64 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Thu, 22 Aug 2024 15:16:50 +0000
Subject: [PATCH v2 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 in each backend to copy all
the others backends stats, as 1/ there is no use case (there is no need to get
others backends I/O statistics while taking care of the stats_fetch_consistency)
and 2/ that could be memory expensive depending of the number of max connections.
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 (each execution
re-fetches counters from shared memory because as stated above there is no
snapshots being created in each backend to copy the other backends stats).
Bump catalog version needs to be done.
---
doc/src/sgml/config.sgml | 4 +-
doc/src/sgml/monitoring.sgml | 28 ++++++
src/backend/catalog/system_views.sql | 22 +++++
src/backend/utils/activity/pgstat.c | 11 ++-
src/backend/utils/activity/pgstat_io.c | 91 ++++++++++++++---
src/backend/utils/activity/pgstat_shmem.c | 4 +-
src/backend/utils/adt/pgstatfuncs.c | 114 +++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 9 ++
src/include/pgstat.h | 14 ++-
src/include/utils/pgstat_internal.h | 14 ++-
src/test/regress/expected/rules.out | 19 ++++
src/test/regress/expected/stats.out | 44 ++++++++-
src/test/regress/sql/stats.sql | 25 ++++-
13 files changed, 366 insertions(+), 33 deletions(-)
8.5% doc/src/sgml/
29.4% src/backend/utils/activity/
23.9% src/backend/utils/adt/
4.4% src/include/catalog/
3.1% src/include/utils/
3.1% src/include/
14.4% src/test/regress/expected/
10.0% 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 933de6fe07..b28ca4e0ca 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
@@ -2948,6 +2958,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 7fd5d256a1..b4939b7bc6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1168,6 +1168,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/pgstat.c b/src/backend/utils/activity/pgstat.c
index 178b5ef65a..53b6e0bcd5 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -406,10 +406,12 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.fixed_amount = true,
- .snapshot_ctl_off = offsetof(PgStat_Snapshot, io),
+ .init_backend_cb = pgstat_io_init_backend_cb,
+ .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,
@@ -581,6 +583,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..f2b36d8efa 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_Backend_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.stat[MyProcNumber].stats;
+ 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] +=
- PendingIOStats.counts[io_object][io_context][io_op];
+ bktype_shstats->counts[io_object][io_context][io_op] += counter;
+
+ 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,35 @@ 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)
+{
+ LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[0];
+ PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stat[index].stats;
+
+
+ /*
+ * Use the lock in the first BackendType's PgStat_BktypeIO to protect the
+ * reset timestamp as well.
+ */
+ LWLockAcquire(bktype_lock, LW_EXCLUSIVE);
+
+ pgStatLocal.shmem->io.stat[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 +318,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);
@@ -285,11 +328,28 @@ pgstat_io_reset_all_cb(TimestampTz ts)
void
pgstat_io_snapshot_cb(void)
{
+ /* First, our own stats */
+ PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stat[MyProcNumber].stats;
+ PgStat_BktypeIO *bktype_snap = &pgStatLocal.snapshot.my_io.stats;
+ LWLock *mybktype_lock = &pgStatLocal.shmem->io.locks[0];
+
+ LWLockAcquire(mybktype_lock, LW_SHARED);
+
+ pgStatLocal.snapshot.my_io.stat_reset_timestamp =
+ pgStatLocal.shmem->io.stat[MyProcNumber].stat_reset_timestamp;
+
+ pgStatLocal.snapshot.my_io.bktype =
+ pgStatLocal.shmem->io.stat[MyProcNumber].bktype;
+
+ *bktype_snap = *bktype_shstats;
+ LWLockRelease(mybktype_lock);
+
+ /* Now, the global stats */
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];
LWLockAcquire(bktype_lock, LW_SHARED);
@@ -298,11 +358,11 @@ 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;
/* using struct assignment due to better type safety */
- *bktype_snap = *bktype_shstats;
+ *bktype_global_snap = *bktype_global_shstats;
LWLockRelease(bktype_lock);
}
}
@@ -485,3 +545,10 @@ pgstat_tracks_io_op(BackendType bktype, IOObject io_object,
return true;
}
+
+void
+pgstat_io_init_backend_cb(void)
+{
+ /* Initialize our backend type in the IO statistics */
+ pgStatLocal.shmem->io.stat[MyProcNumber].bktype = MyBackendType;
+}
diff --git a/src/backend/utils/activity/pgstat_shmem.c b/src/backend/utils/activity/pgstat_shmem.c
index ec93bf6902..3cfcba9609 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_Backend_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_Backend_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 97dc09ac0d..e72dc5daa7 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1363,14 +1363,17 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
InitMaterializedSRF(fcinfo, 0);
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- backends_io_stats = pgstat_fetch_stat_io();
+ backends_io_stats = pgstat_fetch_global_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;
+
+ 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
@@ -1462,6 +1465,111 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+Datum
+pg_stat_get_my_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_Backend_IO *backend_io_stats;
+ Datum bktype_desc;
+ PgStat_BktypeIO *bktype_stats;
+ BackendType bktype;
+ Datum reset_time;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ backend_io_stats = pgstat_fetch_my_stat_io();
+
+ bktype = backend_io_stats->bktype;
+ bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
+ bktype_stats = &backend_io_stats->stats;
+ reset_time = TimestampTzGetDatum(backend_io_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_io_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
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ff5436acac..62658e9a00 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5831,6 +5831,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 => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index be2c91168a..9d63b1a5b7 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -16,6 +16,7 @@
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
#include "replication/conflict.h"
+#include "storage/proc.h"
#include "utils/backend_progress.h" /* for backward compatibility */
#include "utils/backend_status.h" /* for backward compatibility */
#include "utils/relcache.h"
@@ -76,6 +77,8 @@
*/
#define PGSTAT_KIND_EXPERIMENTAL 128
+#define NumProcStatSlots (MaxBackends + NUM_AUXILIARY_PROCS)
+
static inline bool
pgstat_is_kind_builtin(PgStat_Kind kind)
{
@@ -351,6 +354,12 @@ typedef struct PgStat_IO
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
+typedef struct PgStat_Backend_IO
+{
+ TimestampTz stat_reset_timestamp;
+ BackendType bktype;
+ PgStat_BktypeIO stats;
+} PgStat_Backend_IO;
typedef struct PgStat_StatDBEntry
{
@@ -559,7 +568,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_Backend_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);
@@ -568,7 +578,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 25820cbf0a..3b2f2b28dd 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -353,11 +353,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_Backend_IO stat[FLEXIBLE_ARRAY_MEMBER];
} PgStatShared_IO;
typedef struct PgStatShared_SLRU
@@ -450,7 +451,6 @@ typedef struct PgStat_ShmemControl
PgStatShared_Archiver archiver;
PgStatShared_BgWriter bgwriter;
PgStatShared_Checkpointer checkpointer;
- PgStatShared_IO io;
PgStatShared_SLRU slru;
PgStatShared_Wal wal;
@@ -460,6 +460,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;
@@ -481,7 +483,8 @@ typedef struct PgStat_Snapshot
PgStat_CheckpointerStats checkpointer;
- PgStat_IO io;
+ PgStat_Backend_IO my_io;
+ PgStat_IO global_io;
PgStat_SLRUStats slru[SLRU_NUM_ELEMENTS];
@@ -682,6 +685,7 @@ extern bool pgstat_flush_wal(bool nowait);
extern bool pgstat_have_pending_wal(void);
extern void pgstat_wal_init_backend_cb(void);
+extern void pgstat_io_init_backend_cb(void);
extern void pgstat_wal_init_shmem_cb(void *stats);
extern void pgstat_wal_reset_all_cb(TimestampTz ts);
extern void pgstat_wal_snapshot_cb(void);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a1626f3fae..785298d7b0 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 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
[text/x-diff] v2-0002-Add-pg_stat_get_backend_io.patch (14.4K, ../../[email protected]/3-v2-0002-Add-pg_stat_get_backend_io.patch)
download | inline diff:
From 4e9e50f43a83cbba7caa6824c74df1e0f536cc3e Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Wed, 28 Aug 2024 12:59:02 +0000
Subject: [PATCH v2 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 | 9 ++
src/include/pgstat.h | 1 +
src/test/regress/expected/stats.out | 25 ++++++
src/test/regress/sql/stats.sql | 16 +++-
7 files changed, 194 insertions(+), 1 deletion(-)
10.9% doc/src/sgml/
47.1% src/backend/utils/adt/
9.2% src/include/catalog/
15.4% 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 b28ca4e0ca..fb908172f8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4770,6 +4770,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 f2b36d8efa..780ba48d1a 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_Backend_IO *
+pgstat_fetch_proc_stat_io(ProcNumber procNumber)
+{
+ return &pgStatLocal.shmem->io.stat[procNumber];
+}
+
/*
* Flush out locally pending IO statistics
*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index e72dc5daa7..ca22251acd 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1570,6 +1570,126 @@ pg_stat_get_my_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+Datum
+pg_stat_get_backend_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_Backend_IO *backend_io_stats;
+ Datum reset_time;
+ ProcNumber procNumber;
+ PGPROC *proc;
+ BackendType bktype;
+ Datum bktype_desc;
+ PgStat_BktypeIO *bktype_stats;
+
+ int backend_pid = PG_GETARG_INT32(0);
+
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ InitMaterializedSRF(fcinfo, 0);
+
+ proc = BackendPidGetProc(backend_pid);
+
+ /* Maybe an auxiliary process? */
+ if (proc == NULL)
+ proc = AuxiliaryPidGetProc(backend_pid);
+
+ /* This backend_pid does not exist */
+ if (proc != NULL)
+ {
+ procNumber = GetNumberFromPGProc(proc);
+ backend_io_stats = pgstat_fetch_proc_stat_io(procNumber);
+ bktype = backend_io_stats->bktype;
+ reset_time = TimestampTzGetDatum(backend_io_stats->stat_reset_timestamp);
+
+ bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
+ bktype_stats = &backend_io_stats->stats;
+
+ /*
+ * In Assert builds, we can afford an extra loop through all of the
+ * counters checking that only expected stats are non-zero, since it
+ * keeps the non-Assert code cleaner.
+ */
+ Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
+
+ 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_io_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
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62658e9a00..0a540a590f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5840,6 +5840,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_my_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',
proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 9d63b1a5b7..2a2b4a2947 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -570,6 +570,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_Backend_IO *pgstat_fetch_my_stat_io(void);
+extern PgStat_Backend_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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: per backend I/O statistics
2024-09-03 07:21 Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-05 13:03 ` Re: per backend I/O statistics Alvaro Herrera <[email protected]>
2024-09-05 14:14 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-06 15:03 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
@ 2024-09-09 06:29 ` Bertrand Drouvot <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Bertrand Drouvot @ 2024-09-09 06:29 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected]
Hi,
On Fri, Sep 06, 2024 at 03:03:17PM +0000, Bertrand Drouvot wrote:
> - If we agree on the current proposal then I'll do some refactoring in
> pg_stat_get_backend_io(), pg_stat_get_my_io() and pg_stat_get_io() to avoid
> duplicated code (it's not done yet to ease the review).
Please find attached v3, mandatory rebase due to fc415edf8c.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
[text/x-diff] v3-0001-per-backend-I-O-statistics.patch (30.5K, ../../[email protected]/2-v3-0001-per-backend-I-O-statistics.patch)
download | inline diff:
From 335949f1c408a99aed74c0231d5e436b5f319746 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Thu, 22 Aug 2024 15:16:50 +0000
Subject: [PATCH v3 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 in each backend to copy all
the others backends stats, as 1/ there is no use case (there is no need to get
others backends I/O statistics while taking care of the stats_fetch_consistency)
and 2/ that could be memory expensive depending of the number of max connections.
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 (each execution
re-fetches counters from shared memory because as stated above there is no
snapshots being created in each backend to copy the other backends stats).
Bump catalog version needs to be done.
---
doc/src/sgml/config.sgml | 4 +-
doc/src/sgml/monitoring.sgml | 28 ++++++
src/backend/catalog/system_views.sql | 22 +++++
src/backend/utils/activity/pgstat.c | 11 ++-
src/backend/utils/activity/pgstat_io.c | 91 ++++++++++++++---
src/backend/utils/activity/pgstat_shmem.c | 4 +-
src/backend/utils/adt/pgstatfuncs.c | 114 +++++++++++++++++++++-
src/include/catalog/pg_proc.dat | 9 ++
src/include/pgstat.h | 14 ++-
src/include/utils/pgstat_internal.h | 14 ++-
src/test/regress/expected/rules.out | 19 ++++
src/test/regress/expected/stats.out | 44 ++++++++-
src/test/regress/sql/stats.sql | 25 ++++-
13 files changed, 366 insertions(+), 33 deletions(-)
8.5% doc/src/sgml/
29.4% src/backend/utils/activity/
23.9% src/backend/utils/adt/
4.4% src/include/catalog/
3.1% src/include/utils/
3.1% src/include/
14.4% src/test/regress/expected/
10.0% 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 933de6fe07..b28ca4e0ca 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
@@ -2948,6 +2958,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 7fd5d256a1..b4939b7bc6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1168,6 +1168,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/pgstat.c b/src/backend/utils/activity/pgstat.c
index a7f2dfc744..deb30cd2fc 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -406,10 +406,12 @@ static const PgStat_KindInfo pgstat_kind_builtin_infos[PGSTAT_KIND_BUILTIN_SIZE]
.fixed_amount = true,
- .snapshot_ctl_off = offsetof(PgStat_Snapshot, io),
+ .init_backend_cb = pgstat_io_init_backend_cb,
+ .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),
.flush_fixed_cb = pgstat_io_flush_cb,
.have_fixed_pending_cb = pgstat_io_have_pending_cb,
@@ -587,6 +589,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 cc2ffc78aa..611ca21720 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;
static 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_Backend_IO *
+pgstat_fetch_my_stat_io(void)
+{
+ pgstat_snapshot_fixed(PGSTAT_KIND_IO);
+
+ return &pgStatLocal.snapshot.my_io;
}
/*
@@ -192,13 +201,16 @@ pgstat_io_flush_cb(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.stat[MyProcNumber].stats;
+ global_bktype_shstats =
+ &pgStatLocal.shmem->io.global_stats.stats[MyBackendType];
if (!nowait)
LWLockAcquire(bktype_lock, LW_EXCLUSIVE);
@@ -212,19 +224,28 @@ pgstat_io_flush_cb(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] +=
- PendingIOStats.counts[io_object][io_context][io_op];
+ bktype_shstats->counts[io_object][io_context][io_op] += counter;
+
+ 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);
@@ -278,13 +299,35 @@ 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)
+{
+ LWLock *bktype_lock = &pgStatLocal.shmem->io.locks[0];
+ PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stat[index].stats;
+
+
+ /*
+ * Use the lock in the first BackendType's PgStat_BktypeIO to protect the
+ * reset timestamp as well.
+ */
+ LWLockAcquire(bktype_lock, LW_EXCLUSIVE);
+
+ pgStatLocal.shmem->io.stat[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);
@@ -293,7 +336,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);
@@ -303,11 +346,28 @@ pgstat_io_reset_all_cb(TimestampTz ts)
void
pgstat_io_snapshot_cb(void)
{
+ /* First, our own stats */
+ PgStat_BktypeIO *bktype_shstats = &pgStatLocal.shmem->io.stat[MyProcNumber].stats;
+ PgStat_BktypeIO *bktype_snap = &pgStatLocal.snapshot.my_io.stats;
+ LWLock *mybktype_lock = &pgStatLocal.shmem->io.locks[0];
+
+ LWLockAcquire(mybktype_lock, LW_SHARED);
+
+ pgStatLocal.snapshot.my_io.stat_reset_timestamp =
+ pgStatLocal.shmem->io.stat[MyProcNumber].stat_reset_timestamp;
+
+ pgStatLocal.snapshot.my_io.bktype =
+ pgStatLocal.shmem->io.stat[MyProcNumber].bktype;
+
+ *bktype_snap = *bktype_shstats;
+ LWLockRelease(mybktype_lock);
+
+ /* Now, the global stats */
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];
LWLockAcquire(bktype_lock, LW_SHARED);
@@ -316,11 +376,11 @@ 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;
/* using struct assignment due to better type safety */
- *bktype_snap = *bktype_shstats;
+ *bktype_global_snap = *bktype_global_shstats;
LWLockRelease(bktype_lock);
}
}
@@ -503,3 +563,10 @@ pgstat_tracks_io_op(BackendType bktype, IOObject io_object,
return true;
}
+
+void
+pgstat_io_init_backend_cb(void)
+{
+ /* Initialize our backend type in the IO statistics */
+ pgStatLocal.shmem->io.stat[MyProcNumber].bktype = MyBackendType;
+}
diff --git a/src/backend/utils/activity/pgstat_shmem.c b/src/backend/utils/activity/pgstat_shmem.c
index ec93bf6902..3cfcba9609 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_Backend_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_Backend_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 33c7b25560..4c00570396 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1363,14 +1363,17 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
InitMaterializedSRF(fcinfo, 0);
rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
- backends_io_stats = pgstat_fetch_stat_io();
+ backends_io_stats = pgstat_fetch_global_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;
+
+ 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
@@ -1462,6 +1465,111 @@ pg_stat_get_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+Datum
+pg_stat_get_my_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_Backend_IO *backend_io_stats;
+ Datum bktype_desc;
+ PgStat_BktypeIO *bktype_stats;
+ BackendType bktype;
+ Datum reset_time;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+ backend_io_stats = pgstat_fetch_my_stat_io();
+
+ bktype = backend_io_stats->bktype;
+ bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
+ bktype_stats = &backend_io_stats->stats;
+ reset_time = TimestampTzGetDatum(backend_io_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_io_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
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index ff5436acac..62658e9a00 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5831,6 +5831,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 => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index be2c91168a..9d63b1a5b7 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -16,6 +16,7 @@
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
#include "replication/conflict.h"
+#include "storage/proc.h"
#include "utils/backend_progress.h" /* for backward compatibility */
#include "utils/backend_status.h" /* for backward compatibility */
#include "utils/relcache.h"
@@ -76,6 +77,8 @@
*/
#define PGSTAT_KIND_EXPERIMENTAL 128
+#define NumProcStatSlots (MaxBackends + NUM_AUXILIARY_PROCS)
+
static inline bool
pgstat_is_kind_builtin(PgStat_Kind kind)
{
@@ -351,6 +354,12 @@ typedef struct PgStat_IO
PgStat_BktypeIO stats[BACKEND_NUM_TYPES];
} PgStat_IO;
+typedef struct PgStat_Backend_IO
+{
+ TimestampTz stat_reset_timestamp;
+ BackendType bktype;
+ PgStat_BktypeIO stats;
+} PgStat_Backend_IO;
typedef struct PgStat_StatDBEntry
{
@@ -559,7 +568,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_Backend_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);
@@ -568,7 +578,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 bba90e898d..ea4cc6a2c6 100644
--- a/src/include/utils/pgstat_internal.h
+++ b/src/include/utils/pgstat_internal.h
@@ -366,11 +366,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_Backend_IO stat[FLEXIBLE_ARRAY_MEMBER];
} PgStatShared_IO;
typedef struct PgStatShared_SLRU
@@ -463,7 +464,6 @@ typedef struct PgStat_ShmemControl
PgStatShared_Archiver archiver;
PgStatShared_BgWriter bgwriter;
PgStatShared_Checkpointer checkpointer;
- PgStatShared_IO io;
PgStatShared_SLRU slru;
PgStatShared_Wal wal;
@@ -473,6 +473,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;
@@ -494,7 +496,8 @@ typedef struct PgStat_Snapshot
PgStat_CheckpointerStats checkpointer;
- PgStat_IO io;
+ PgStat_Backend_IO my_io;
+ PgStat_IO global_io;
PgStat_SLRUStats slru[SLRU_NUM_ELEMENTS];
@@ -629,6 +632,7 @@ extern bool pgstat_io_flush_cb(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 void pgstat_io_init_backend_cb(void);
/*
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a1626f3fae..785298d7b0 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 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
[text/x-diff] v3-0002-Add-pg_stat_get_backend_io.patch (14.4K, ../../[email protected]/3-v3-0002-Add-pg_stat_get_backend_io.patch)
download | inline diff:
From b5719ca7a1c6257929b71c45ba8cb9da3bd53665 Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email protected]>
Date: Wed, 28 Aug 2024 12:59:02 +0000
Subject: [PATCH v3 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 | 9 ++
src/include/pgstat.h | 1 +
src/test/regress/expected/stats.out | 25 ++++++
src/test/regress/sql/stats.sql | 16 +++-
7 files changed, 194 insertions(+), 1 deletion(-)
10.9% doc/src/sgml/
47.1% src/backend/utils/adt/
9.2% src/include/catalog/
15.4% 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 b28ca4e0ca..fb908172f8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4770,6 +4770,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 611ca21720..cf6a49cf48 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_Backend_IO *
+pgstat_fetch_proc_stat_io(ProcNumber procNumber)
+{
+ return &pgStatLocal.shmem->io.stat[procNumber];
+}
+
/*
* Check if there any IO stats waiting for flush.
*/
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 4c00570396..795ff4e2f6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1570,6 +1570,126 @@ pg_stat_get_my_io(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+Datum
+pg_stat_get_backend_io(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_Backend_IO *backend_io_stats;
+ Datum reset_time;
+ ProcNumber procNumber;
+ PGPROC *proc;
+ BackendType bktype;
+ Datum bktype_desc;
+ PgStat_BktypeIO *bktype_stats;
+
+ int backend_pid = PG_GETARG_INT32(0);
+
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ InitMaterializedSRF(fcinfo, 0);
+
+ proc = BackendPidGetProc(backend_pid);
+
+ /* Maybe an auxiliary process? */
+ if (proc == NULL)
+ proc = AuxiliaryPidGetProc(backend_pid);
+
+ /* This backend_pid does not exist */
+ if (proc != NULL)
+ {
+ procNumber = GetNumberFromPGProc(proc);
+ backend_io_stats = pgstat_fetch_proc_stat_io(procNumber);
+ bktype = backend_io_stats->bktype;
+ reset_time = TimestampTzGetDatum(backend_io_stats->stat_reset_timestamp);
+
+ bktype_desc = CStringGetTextDatum(GetBackendTypeDesc(bktype));
+ bktype_stats = &backend_io_stats->stats;
+
+ /*
+ * In Assert builds, we can afford an extra loop through all of the
+ * counters checking that only expected stats are non-zero, since it
+ * keeps the non-Assert code cleaner.
+ */
+ Assert(pgstat_bktype_io_stats_valid(bktype_stats, bktype));
+
+ 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_io_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
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62658e9a00..0a540a590f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5840,6 +5840,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_my_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',
proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 9d63b1a5b7..2a2b4a2947 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -570,6 +570,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_Backend_IO *pgstat_fetch_my_stat_io(void);
+extern PgStat_Backend_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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: per backend I/O statistics
2024-09-03 07:21 Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-05 13:03 ` Re: per backend I/O statistics Alvaro Herrera <[email protected]>
2024-09-05 14:14 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-06 15:03 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
@ 2024-09-13 04:25 ` Bertrand Drouvot <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Bertrand Drouvot @ 2024-09-13 04:25 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Kyotaro Horiguchi <[email protected]>; [email protected]
Hi,
On Fri, Sep 06, 2024 at 03:03:17PM +0000, Bertrand Drouvot wrote:
> The struct size is 1040 Bytes and that's much more reasonable than the size
> needed for per backend I/O stats in v1 (about 16KB).
One could think that's a high increase of shared memory usage with a high
number of connections (due to the fact that it's implemented as "fixed amount"
stats based on the max number of backends).
So out of curiosity, I did some measurements with:
dynamic_shared_memory_type=sysv
shared_memory_type=sysv
max_connections=20000
On my lab, ipcs -m gives me:
- on master
key shmid owner perms bytes nattch status
0x00113a04 51347487 postgres 600 1149394944 6
0x4bc9f2fa 51347488 postgres 600 4006976 6
0x46790800 51347489 postgres 600 1048576 2
- with v3 applied
key shmid owner perms bytes nattch status
0x00113a04 51347477 postgres 600 1170227200 6
0x08e04b0a 51347478 postgres 600 4006976 6
0x74688c9c 51347479 postgres 600 1048576 2
So, with 20000 max_connections (not advocating that's a reasonable number in
all the cases), that's a 1170227200 - 1149394944 = 20 MB increase of shared
memory (expected with 20K max_connections and the new struct size of 1040 Bytes)
as compare to master which is 1096 MB. It means that v3 produces about 2% shared
memory increase with 20000 max_connections.
Out of curiosity with max_connections=100000, then:
- on master:
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00113a04 52953134 postgres 600 5053915136 6
0x37abf5ce 52953135 postgres 600 20006976 6
0x71c07d5c 52953136 postgres 600 1048576 2
- with v3:
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00113a04 52953144 postgres 600 5157945344 6
0x7afb24de 52953145 postgres 600 20006976 6
0x2695af58 52953146 postgres 600 1048576 2
So, with 100000 max_connections (not advocating that's a reasonable number in
all the cases), that's a 5157945344 - 5053915136 = 100 MB increase of shared
memory (expected with 100K max_connections and the new struct size of 1040 Bytes)
as compare to master which is about 4800 MB. It means that v3 produces about
2% shared memory increase with 100000 max_connections.
Then, based on those numbers, I think that the "fixed amount" approach is a good
one as 1/ the amount of shared memory increase is "relatively" small and 2/ most
probably provides performance benefits as compare to the "non fixed" approach.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: per backend I/O statistics
2024-09-03 07:21 Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-05 13:03 ` Re: per backend I/O statistics Alvaro Herrera <[email protected]>
2024-09-05 14:14 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-06 15:03 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
@ 2024-09-13 13:45 ` Nazir Bilal Yavuz <[email protected]>
2 siblings, 0 replies; 8+ messages in thread
From: Nazir Bilal Yavuz @ 2024-09-13 13:45 UTC (permalink / raw)
To: Bertrand Drouvot <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; Kyotaro Horiguchi <[email protected]>; [email protected]
Hi,
Thanks for working on this!
Your patch applies and builds cleanly.
On Fri, 6 Sept 2024 at 18:03, Bertrand Drouvot
<[email protected]> wrote:
>
> - As stated up-thread, the pg_stat_get_backend_io() behaves as if
> stats_fetch_consistency is set to none (each execution re-fetches counters
> from shared memory). Indeed, the snapshot is not build in each backend to copy
> all the others backends stats, as 1/ there is no use case (there is no need to
> get others backends I/O statistics while taking care of the stats_fetch_consistency)
> and 2/ that could be memory expensive depending of the number of max connections.
I believe this is the correct approach.
I manually tested your patches, and they work as expected. Here is
some feedback:
- The stats_reset column is NULL in both pg_my_stat_io and
pg_stat_get_backend_io() until the first call to reset io statistics.
While I'm not sure if it's necessary, it might be useful to display
the more recent of the two times in the stats_reset column: the
statistics reset time or the backend creation time.
- The pgstat_reset_io_counter_internal() is called in the
pgstat_shutdown_hook(). This causes the stats_reset column showing the
termination time of the old backend when its proc num is reassigned to
a new backend.
--
Regards,
Nazir Bilal Yavuz
Microsoft
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-09-13 13:45 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-17 03:07 [PATCH 01/13] doc: Remove 'synchronized' from --no-sync Justin Pryzby <[email protected]>
2024-09-03 07:21 Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-05 13:03 ` Re: per backend I/O statistics Alvaro Herrera <[email protected]>
2024-09-05 14:14 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-06 15:03 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-09 06:29 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-13 04:25 ` Re: per backend I/O statistics Bertrand Drouvot <[email protected]>
2024-09-13 13:45 ` Re: per backend I/O statistics Nazir Bilal Yavuz <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox