On Fri, Feb 10, 2023 at 6:16 AM Andres Freund <and...@anarazel.de> wrote:
>
> That's true for buffers_fsync_backend, but not true for
> buffers_backend/buffers_written_backend.
>
> That isn't tied to checkpointer.
>
> I think if we end up breaking compat, we should just drop that column. The
> pg_stat_io patch from Melanie, which I hope to finish committing by tomorrow,
> provides that in a more useful way, in a less confusing place.
On Fri, Feb 10, 2023 at 11:29 AM Michael Paquier <mich...@paquier.xyz> wrote:
>
> On Thu, Feb 09, 2023 at 04:46:04PM -0800, Andres Freund wrote:
> > I think if we end up breaking compat, we should just drop that
> > column.
>
> Indeed.
Yeah, pg_stat_io is a better place to track the backend IO stats. I
removed buffers_backend, please see the attached 0001 patch.
On Fri, Feb 10, 2023 at 6:16 AM Andres Freund <and...@anarazel.de> wrote:
>
> I'm not sure it's worth having buffers_fsync_backend in pg_stat_checkpointer
> in that case. You can get nearly the same information from pg_stat_io as well
> (except fsyncs for SLRUs that couldn't be put into the queue, which you'd not
> see right now - hard to believe that ever happens at a relelvant frequency).
I think it'd be better to move the SLRU fsync stats during checkpoints
to the pg_stat_slru view, then it can be a one-stop view to track all
SLRU IO stats. This lets us remove buffers_fsync_backend too, please
see the attached 0002 patch. However, one metric we might miss is the
number of times checkpointer missed to absorb the fsync requests. If
needed, this metric can be added to the new pg_stat_checkpointer view.
> > Yep, and I think you are all wrong, and that this is just going to cause
> > unnecessary pain :). I'm not going to try to prevent the patch from going in
> > because of this, just to be clear.
>
> Catalog attributes have faced a lot of renames across the years, with
> the same potential of breakages for monitoring tools. I am not saying
> that all of them are justified, but we have usually done so because it
> makes sense to reshape things in the way they are now, thinking
> long-term. Splitting pg_stat_bgwriter into two views does not strike
> me as something that bad, TBH, because it becomes clearer which stats
> are attached to which process (bgwriter or checkpointer). (Note: I
> have not checked in details the stats switching to the new view and
> how pertinent each choice is.)
Thanks. FWIW, I've attached the patch introducing pg_stat_checkpointer
as 0003 here.
Please review the attached v7 patch set.
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From edb5ee994b179034273edd42c139aa57c9106922 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 10 Feb 2023 11:45:14 +0000
Subject: [PATCH v7] Drop buffers_backend column from pg_stat_bgwriter view
---
doc/src/sgml/monitoring.sgml | 9 ---------
src/backend/catalog/system_views.sql | 1 -
src/backend/postmaster/checkpointer.c | 20 +++++--------------
.../utils/activity/pgstat_checkpointer.c | 2 --
src/backend/utils/adt/pgstatfuncs.c | 6 ------
src/include/catalog/pg_proc.dat | 4 ----
src/include/pgstat.h | 1 -
src/test/regress/expected/rules.out | 1 -
8 files changed, 5 insertions(+), 39 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b246ddc634..380085eedd 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3766,15 +3766,6 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</para></entry>
</row>
- <row>
- <entry role="catalog_table_entry"><para role="column_definition">
- <structfield>buffers_backend</structfield> <type>bigint</type>
- </para>
- <para>
- Number of buffers written directly by a backend
- </para></entry>
- </row>
-
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>buffers_backend_fsync</structfield> <type>bigint</type>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8608e3fa5b..2b1b2bf7c3 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1112,7 +1112,6 @@ CREATE VIEW pg_stat_bgwriter AS
pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
- pg_stat_get_buf_written_backend() AS buffers_backend,
pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
diff --git a/src/backend/postmaster/checkpointer.c b/src/backend/postmaster/checkpointer.c
index aaad5c5228..baadd08044 100644
--- a/src/backend/postmaster/checkpointer.c
+++ b/src/backend/postmaster/checkpointer.c
@@ -91,17 +91,15 @@
* requesting backends since the last checkpoint start. The flags are
* chosen so that OR'ing is the correct way to combine multiple requests.
*
- * num_backend_writes is used to count the number of buffer writes performed
- * by user backend processes. This counter should be wide enough that it
- * can't overflow during a single processing cycle. num_backend_fsync
- * counts the subset of those writes that also had to do their own fsync,
- * because the checkpointer failed to absorb their request.
+ * num_backend_fsync counts the subset of buffer writes performed by user
+ * backend processes that also had to do their own fsync, because the
+ * checkpointer failed to absorb their request.
*
* The requests array holds fsync requests sent by backends and not yet
* absorbed by the checkpointer.
*
- * Unlike the checkpoint fields, num_backend_writes, num_backend_fsync, and
- * the requests fields are protected by CheckpointerCommLock.
+ * Unlike the checkpoint fields, num_backend_fsync and the requests fields are
+ * protected by CheckpointerCommLock.
*----------
*/
typedef struct
@@ -125,7 +123,6 @@ typedef struct
ConditionVariable start_cv; /* signaled when ckpt_started advances */
ConditionVariable done_cv; /* signaled when ckpt_done advances */
- uint32 num_backend_writes; /* counts user backend buffer writes */
uint32 num_backend_fsync; /* counts user backend fsync calls */
int num_requests; /* current # of requests */
@@ -1096,10 +1093,6 @@ ForwardSyncRequest(const FileTag *ftag, SyncRequestType type)
LWLockAcquire(CheckpointerCommLock, LW_EXCLUSIVE);
- /* Count all backend writes regardless of if they fit in the queue */
- if (!AmBackgroundWriterProcess())
- CheckpointerShmem->num_backend_writes++;
-
/*
* If the checkpointer isn't running or the request queue is full, the
* backend will have to perform its own fsync request. But before forcing
@@ -1272,12 +1265,9 @@ AbsorbSyncRequests(void)
LWLockAcquire(CheckpointerCommLock, LW_EXCLUSIVE);
/* Transfer stats counts into pending pgstats message */
- PendingCheckpointerStats.buf_written_backend
- += CheckpointerShmem->num_backend_writes;
PendingCheckpointerStats.buf_fsync_backend
+= CheckpointerShmem->num_backend_fsync;
- CheckpointerShmem->num_backend_writes = 0;
CheckpointerShmem->num_backend_fsync = 0;
/*
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index 26dec112f6..a048205d6e 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -52,7 +52,6 @@ pgstat_report_checkpointer(void)
CHECKPOINTER_ACC(checkpoint_write_time);
CHECKPOINTER_ACC(checkpoint_sync_time);
CHECKPOINTER_ACC(buf_written_checkpoints);
- CHECKPOINTER_ACC(buf_written_backend);
CHECKPOINTER_ACC(buf_fsync_backend);
#undef CHECKPOINTER_ACC
@@ -120,7 +119,6 @@ pgstat_checkpointer_snapshot_cb(void)
CHECKPOINTER_COMP(checkpoint_write_time);
CHECKPOINTER_COMP(checkpoint_sync_time);
CHECKPOINTER_COMP(buf_written_checkpoints);
- CHECKPOINTER_COMP(buf_written_backend);
CHECKPOINTER_COMP(buf_fsync_backend);
#undef CHECKPOINTER_COMP
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 924698e6ae..ce16884f1b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1227,12 +1227,6 @@ pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMPTZ(pgstat_fetch_stat_bgwriter()->stat_reset_timestamp);
}
-Datum
-pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
-{
- PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buf_written_backend);
-}
-
Datum
pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c0f2a8a77c..3188d9ee1f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5704,10 +5704,6 @@
proname => 'pg_stat_get_checkpoint_sync_time', provolatile => 's',
proparallel => 'r', prorettype => 'float8', proargtypes => '',
prosrc => 'pg_stat_get_checkpoint_sync_time' },
-{ oid => '2775', descr => 'statistics: number of buffers written by backends',
- proname => 'pg_stat_get_buf_written_backend', provolatile => 's',
- proparallel => 'r', prorettype => 'int8', proargtypes => '',
- prosrc => 'pg_stat_get_buf_written_backend' },
{ oid => '3063',
descr => 'statistics: number of backend buffer writes that did their own fsync',
proname => 'pg_stat_get_buf_fsync_backend', provolatile => 's',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index db9675884f..d1675909db 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -273,7 +273,6 @@ typedef struct PgStat_CheckpointerStats
PgStat_Counter checkpoint_write_time; /* times in milliseconds */
PgStat_Counter checkpoint_sync_time;
PgStat_Counter buf_written_checkpoints;
- PgStat_Counter buf_written_backend;
PgStat_Counter buf_fsync_backend;
} PgStat_CheckpointerStats;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e7a2f5856a..c98f9cd747 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1821,7 +1821,6 @@ pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints
pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
- pg_stat_get_buf_written_backend() AS buffers_backend,
pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
--
2.34.1
From 8f2ca94acc8773d6c6ff6a4b2a00a4767ec63911 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 10 Feb 2023 11:51:20 +0000
Subject: [PATCH v7] Drop buffers_backend_fsync column from pg_stat_bgwriter
view
---
doc/src/sgml/monitoring.sgml | 11 ----------
src/backend/access/transam/slru.c | 4 ++++
src/backend/catalog/system_views.sql | 1 -
src/backend/postmaster/checkpointer.c | 22 ++-----------------
.../utils/activity/pgstat_checkpointer.c | 2 --
src/backend/utils/adt/pgstatfuncs.c | 6 -----
src/include/catalog/pg_proc.dat | 5 -----
src/include/pgstat.h | 1 -
src/test/regress/expected/rules.out | 1 -
9 files changed, 6 insertions(+), 47 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 380085eedd..5fe39b305d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3766,17 +3766,6 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</para></entry>
</row>
- <row>
- <entry role="catalog_table_entry"><para role="column_definition">
- <structfield>buffers_backend_fsync</structfield> <type>bigint</type>
- </para>
- <para>
- Number of times a backend had to execute its own
- <function>fsync</function> call (normally the background writer handles those
- even when the backend does its own write)
- </para></entry>
- </row>
-
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>buffers_alloc</structfield> <type>bigint</type>
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 5ab86238a9..4ae2040f39 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -1593,10 +1593,14 @@ SlruScanDirectory(SlruCtl ctl, SlruScanCallback callback, void *data)
int
SlruSyncFileTag(SlruCtl ctl, const FileTag *ftag, char *path)
{
+ SlruShared shared = ctl->shared;
int fd;
int save_errno;
int result;
+ /* update the stats counter of flushes */
+ pgstat_count_slru_flush(shared->slru_stats_idx);
+
SlruFileName(ctl, path, ftag->segno);
fd = OpenTransientFile(path, O_RDWR | PG_BINARY);
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2b1b2bf7c3..7ef3d2ebd4 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1112,7 +1112,6 @@ CREATE VIEW pg_stat_bgwriter AS
pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
- pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
diff --git a/src/backend/postmaster/checkpointer.c b/src/backend/postmaster/checkpointer.c
index baadd08044..12c92f3de9 100644
--- a/src/backend/postmaster/checkpointer.c
+++ b/src/backend/postmaster/checkpointer.c
@@ -91,15 +91,11 @@
* requesting backends since the last checkpoint start. The flags are
* chosen so that OR'ing is the correct way to combine multiple requests.
*
- * num_backend_fsync counts the subset of buffer writes performed by user
- * backend processes that also had to do their own fsync, because the
- * checkpointer failed to absorb their request.
- *
* The requests array holds fsync requests sent by backends and not yet
* absorbed by the checkpointer.
*
- * Unlike the checkpoint fields, num_backend_fsync and the requests fields are
- * protected by CheckpointerCommLock.
+ * Unlike the checkpoint fields, reqquests related fields are protected by
+ * CheckpointerCommLock.
*----------
*/
typedef struct
@@ -123,8 +119,6 @@ typedef struct
ConditionVariable start_cv; /* signaled when ckpt_started advances */
ConditionVariable done_cv; /* signaled when ckpt_done advances */
- uint32 num_backend_fsync; /* counts user backend fsync calls */
-
int num_requests; /* current # of requests */
int max_requests; /* allocated array size */
CheckpointerRequest requests[FLEXIBLE_ARRAY_MEMBER];
@@ -1102,12 +1096,6 @@ ForwardSyncRequest(const FileTag *ftag, SyncRequestType type)
(CheckpointerShmem->num_requests >= CheckpointerShmem->max_requests &&
!CompactCheckpointerRequestQueue()))
{
- /*
- * Count the subset of writes where backends have to do their own
- * fsync
- */
- if (!AmBackgroundWriterProcess())
- CheckpointerShmem->num_backend_fsync++;
LWLockRelease(CheckpointerCommLock);
return false;
}
@@ -1264,12 +1252,6 @@ AbsorbSyncRequests(void)
LWLockAcquire(CheckpointerCommLock, LW_EXCLUSIVE);
- /* Transfer stats counts into pending pgstats message */
- PendingCheckpointerStats.buf_fsync_backend
- += CheckpointerShmem->num_backend_fsync;
-
- CheckpointerShmem->num_backend_fsync = 0;
-
/*
* We try to avoid holding the lock for a long time by copying the request
* array, and processing the requests after releasing the lock.
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index a048205d6e..03ed5dddda 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -52,7 +52,6 @@ pgstat_report_checkpointer(void)
CHECKPOINTER_ACC(checkpoint_write_time);
CHECKPOINTER_ACC(checkpoint_sync_time);
CHECKPOINTER_ACC(buf_written_checkpoints);
- CHECKPOINTER_ACC(buf_fsync_backend);
#undef CHECKPOINTER_ACC
pgstat_end_changecount_write(&stats_shmem->changecount);
@@ -119,6 +118,5 @@ pgstat_checkpointer_snapshot_cb(void)
CHECKPOINTER_COMP(checkpoint_write_time);
CHECKPOINTER_COMP(checkpoint_sync_time);
CHECKPOINTER_COMP(buf_written_checkpoints);
- CHECKPOINTER_COMP(buf_fsync_backend);
#undef CHECKPOINTER_COMP
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index ce16884f1b..7f4c39f052 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1227,12 +1227,6 @@ pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
PG_RETURN_TIMESTAMPTZ(pgstat_fetch_stat_bgwriter()->stat_reset_timestamp);
}
-Datum
-pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS)
-{
- PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buf_fsync_backend);
-}
-
Datum
pg_stat_get_buf_alloc(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3188d9ee1f..84bacd0329 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5704,11 +5704,6 @@
proname => 'pg_stat_get_checkpoint_sync_time', provolatile => 's',
proparallel => 'r', prorettype => 'float8', proargtypes => '',
prosrc => 'pg_stat_get_checkpoint_sync_time' },
-{ oid => '3063',
- descr => 'statistics: number of backend buffer writes that did their own fsync',
- proname => 'pg_stat_get_buf_fsync_backend', provolatile => 's',
- proparallel => 'r', prorettype => 'int8', proargtypes => '',
- prosrc => 'pg_stat_get_buf_fsync_backend' },
{ oid => '2859', descr => 'statistics: number of buffer allocations',
proname => 'pg_stat_get_buf_alloc', provolatile => 's', proparallel => 'r',
prorettype => 'int8', proargtypes => '', prosrc => 'pg_stat_get_buf_alloc' },
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index d1675909db..28fb6292d9 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -273,7 +273,6 @@ typedef struct PgStat_CheckpointerStats
PgStat_Counter checkpoint_write_time; /* times in milliseconds */
PgStat_Counter checkpoint_sync_time;
PgStat_Counter buf_written_checkpoints;
- PgStat_Counter buf_fsync_backend;
} PgStat_CheckpointerStats;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index c98f9cd747..d32f47cdb0 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1821,7 +1821,6 @@ pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints
pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
- pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
pg_stat_database| SELECT oid AS datid,
--
2.34.1
From c7dd583d288add34a7fdb412675d2b6258480f1e Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Fri, 10 Feb 2023 16:04:27 +0000
Subject: [PATCH v7] Introduce a new view for checkpointer related stats
pg_stat_bgwriter view currently reports checkpointer stats as well.
It is that way because historically checkpointer was part of
bgwriter until the commits 806a2ae and bf405ba, that went into
PG 9.2, separated them out.
It is time for us to separate checkpointer stats to its own view
called pg_stat_checkpointer.
Bump catalog version.
---
doc/src/sgml/monitoring.sgml | 98 ++++++++++++++-----
src/backend/catalog/system_views.sql | 14 ++-
.../utils/activity/pgstat_checkpointer.c | 1 +
src/backend/utils/adt/pgstatfuncs.c | 19 ++--
src/include/catalog/pg_proc.dat | 22 +++--
src/include/pgstat.h | 1 +
src/test/recovery/t/029_stats_restart.pl | 6 +-
src/test/regress/expected/rules.out | 13 +--
src/test/regress/expected/stats.out | 21 +++-
src/test/regress/sql/stats.sql | 12 ++-
10 files changed, 146 insertions(+), 61 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 5fe39b305d..13f9ded5f3 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -451,6 +451,15 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
</entry>
</row>
+ <row>
+ <entry><structname>pg_stat_checkpointer</structname><indexterm><primary>pg_stat_checkpointer</primary></indexterm></entry>
+ <entry>One row only, showing statistics about the
+ checkpointer process's activity. See
+ <link linkend="monitoring-pg-stat-checkpointer-view">
+ <structname>pg_stat_checkpointer</structname></link> for details.
+ </entry>
+ </row>
+
<row>
<entry><structname>pg_stat_database</structname><indexterm><primary>pg_stat_database</primary></indexterm></entry>
<entry>One row per database, showing database-wide statistics. See
@@ -3681,7 +3690,7 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<para>
The <structname>pg_stat_bgwriter</structname> view will always have a
- single row, containing global data for the cluster.
+ single row, containing data about the bgwriter of the cluster.
</para>
<table id="pg-stat-bgwriter-view" xreflabel="pg_stat_bgwriter">
@@ -3701,77 +3710,118 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
<tbody>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>checkpoints_timed</structfield> <type>bigint</type>
+ <structfield>buffers_clean</structfield> <type>bigint</type>
</para>
<para>
- Number of scheduled checkpoints that have been performed
+ Number of buffers written by the background writer
</para></entry>
</row>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>checkpoints_req</structfield> <type>bigint</type>
+ <structfield>maxwritten_clean</structfield> <type>bigint</type>
</para>
<para>
- Number of requested checkpoints that have been performed
+ Number of times the background writer stopped a cleaning
+ scan because it had written too many buffers
</para></entry>
</row>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>checkpoint_write_time</structfield> <type>double precision</type>
+ <structfield>buffers_alloc</structfield> <type>bigint</type>
</para>
<para>
- Total amount of time that has been spent in the portion of
- checkpoint processing where files are written to disk, in milliseconds
+ Number of buffers allocated
</para></entry>
</row>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>checkpoint_sync_time</structfield> <type>double precision</type>
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
</para>
<para>
- Total amount of time that has been spent in the portion of
- checkpoint processing where files are synchronized to disk, in
- milliseconds
+ Time at which these statistics were last reset
</para></entry>
</row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+ <sect2 id="monitoring-pg-stat-checkpointer-view">
+ <title><structname>pg_stat_checkpointer</structname></title>
+
+ <indexterm>
+ <primary>pg_stat_checkpointer</primary>
+ </indexterm>
+
+ <para>
+ The <structname>pg_stat_checkpointer</structname> view will always have a
+ single row, containing data about the checkpointer process of the cluster.
+ </para>
+
+ <table id="pg-stat-checkpointer-view" xreflabel="pg_stat_checkpointer">
+ <title><structname>pg_stat_checkpointer</structname> View</title>
+ <tgroup cols="1">
+ <thead>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>buffers_checkpoint</structfield> <type>bigint</type>
+ Column Type
</para>
<para>
- Number of buffers written during checkpoints
+ Description
</para></entry>
</row>
+ </thead>
+ <tbody>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>buffers_clean</structfield> <type>bigint</type>
+ <structfield>timed_checkpoints</structfield> <type>bigint</type>
</para>
<para>
- Number of buffers written by the background writer
+ Number of scheduled checkpoints that have been performed
</para></entry>
</row>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>maxwritten_clean</structfield> <type>bigint</type>
+ <structfield>requested_checkpoints</structfield> <type>bigint</type>
</para>
<para>
- Number of times the background writer stopped a cleaning
- scan because it had written too many buffers
+ Number of requested checkpoints that have been performed
</para></entry>
</row>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>buffers_alloc</structfield> <type>bigint</type>
+ <structfield>checkpoint_write_time</structfield> <type>double precision</type>
</para>
<para>
- Number of buffers allocated
+ Total amount of time that has been spent in the portion of
+ checkpoint processing where files are written to disk, in milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>checkpoint_sync_time</structfield> <type>double precision</type>
+ </para>
+ <para>
+ Total amount of time that has been spent in the portion of
+ checkpoint processing where files are synchronized to disk, in
+ milliseconds
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>buffers_written_checkpoints</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of buffers written during checkpoints
</para></entry>
</row>
@@ -5421,8 +5471,10 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
Resets some cluster-wide statistics counters to zero, depending on the
argument. The argument can be <literal>bgwriter</literal> to reset
all the counters shown in
- the <structname>pg_stat_bgwriter</structname>
- view, <literal>archiver</literal> to reset all the counters shown in
+ the <structname>pg_stat_bgwriter</structname> view,
+ <literal>checkpointer</literal> to reset all the counters shown in
+ the <structname>pg_stat_checkpointer</structname> view,
+ <literal>archiver</literal> to reset all the counters shown in
the <structname>pg_stat_archiver</structname> view,
<literal>io</literal> to reset all the counters shown in the
<structname>pg_stat_io</structname> view,
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7ef3d2ebd4..8fc8c0908f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1105,16 +1105,20 @@ CREATE VIEW pg_stat_archiver AS
CREATE VIEW pg_stat_bgwriter AS
SELECT
- pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
- pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
- pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
- pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
- pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
+CREATE VIEW pg_stat_checkpointer AS
+ SELECT
+ pg_stat_get_timed_checkpoints() AS timed_checkpoints,
+ pg_stat_get_requested_checkpoints() AS requested_checkpoints,
+ pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
+ pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
+ pg_stat_get_buf_written_checkpoints() AS buffers_written_checkpoints,
+ pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
+
CREATE VIEW pg_stat_wal AS
SELECT
w.wal_records,
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index 03ed5dddda..aa0b7d2c06 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -92,6 +92,7 @@ pgstat_checkpointer_reset_all_cb(TimestampTz ts)
&stats_shmem->stats,
sizeof(stats_shmem->stats),
&stats_shmem->changecount);
+ stats_shmem->stats.stat_reset_timestamp = ts;
LWLockRelease(&stats_shmem->lock);
}
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7f4c39f052..1a2390977e 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1176,19 +1176,19 @@ PG_STAT_GET_DBENTRY_FLOAT8(idle_in_transaction_time)
PG_STAT_GET_DBENTRY_FLOAT8(session_time)
Datum
-pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
+pg_stat_get_timed_checkpoints(PG_FUNCTION_ARGS)
{
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->timed_checkpoints);
}
Datum
-pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS)
+pg_stat_get_requested_checkpoints(PG_FUNCTION_ARGS)
{
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->requested_checkpoints);
}
Datum
-pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
+pg_stat_get_buf_written_checkpoints(PG_FUNCTION_ARGS)
{
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buf_written_checkpoints);
}
@@ -1221,6 +1221,12 @@ pg_stat_get_checkpoint_sync_time(PG_FUNCTION_ARGS)
pgstat_fetch_stat_checkpointer()->checkpoint_sync_time);
}
+Datum
+pg_stat_get_checkpointer_stat_reset_time(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_TIMESTAMPTZ(pgstat_fetch_stat_checkpointer()->stat_reset_timestamp);
+}
+
Datum
pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
{
@@ -1589,14 +1595,9 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
if (strcmp(target, "archiver") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
else if (strcmp(target, "bgwriter") == 0)
- {
- /*
- * Historically checkpointer was part of bgwriter, continue to reset
- * both for now.
- */
pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
+ else if (strcmp(target, "checkpointer") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
- }
else if (strcmp(target, "io") == 0)
pgstat_reset_of_kind(PGSTAT_KIND_IO);
else if (strcmp(target, "recovery_prefetch") == 0)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 84bacd0329..e5e716f349 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5666,20 +5666,24 @@
proargnames => '{archived_count,last_archived_wal,last_archived_time,failed_count,last_failed_wal,last_failed_time,stats_reset}',
prosrc => 'pg_stat_get_archiver' },
{ oid => '2769',
- descr => 'statistics: number of timed checkpoints started by the bgwriter',
- proname => 'pg_stat_get_bgwriter_timed_checkpoints', provolatile => 's',
+ descr => 'statistics: number of timed checkpoints started by the checkpointer',
+ proname => 'pg_stat_get_timed_checkpoints', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => '',
- prosrc => 'pg_stat_get_bgwriter_timed_checkpoints' },
+ prosrc => 'pg_stat_get_timed_checkpoints' },
{ oid => '2770',
- descr => 'statistics: number of backend requested checkpoints started by the bgwriter',
- proname => 'pg_stat_get_bgwriter_requested_checkpoints', provolatile => 's',
+ descr => 'statistics: number of backend requested checkpoints started by the checkpointer',
+ proname => 'pg_stat_get_requested_checkpoints', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => '',
- prosrc => 'pg_stat_get_bgwriter_requested_checkpoints' },
+ prosrc => 'pg_stat_get_requested_checkpoints' },
{ oid => '2771',
- descr => 'statistics: number of buffers written by the bgwriter during checkpoints',
- proname => 'pg_stat_get_bgwriter_buf_written_checkpoints', provolatile => 's',
+ descr => 'statistics: number of buffers written by the checkpointer',
+ proname => 'pg_stat_get_buf_written_checkpoints', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => '',
- prosrc => 'pg_stat_get_bgwriter_buf_written_checkpoints' },
+ prosrc => 'pg_stat_get_buf_written_checkpoints' },
+{ oid => '8206', descr => 'statistics: last reset for the checkpointer',
+ proname => 'pg_stat_get_checkpointer_stat_reset_time', provolatile => 's',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
+ prosrc => 'pg_stat_get_checkpointer_stat_reset_time' },
{ oid => '2772',
descr => 'statistics: number of buffers written by the bgwriter for cleaning dirty buffers',
proname => 'pg_stat_get_bgwriter_buf_written_clean', provolatile => 's',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 28fb6292d9..b08928cefc 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -273,6 +273,7 @@ typedef struct PgStat_CheckpointerStats
PgStat_Counter checkpoint_write_time; /* times in milliseconds */
PgStat_Counter checkpoint_sync_time;
PgStat_Counter buf_written_checkpoints;
+ TimestampTz stat_reset_timestamp;
} PgStat_CheckpointerStats;
diff --git a/src/test/recovery/t/029_stats_restart.pl b/src/test/recovery/t/029_stats_restart.pl
index 83d6647d32..463f29101e 100644
--- a/src/test/recovery/t/029_stats_restart.pl
+++ b/src/test/recovery/t/029_stats_restart.pl
@@ -173,7 +173,7 @@ is($wal_start->{reset}, $wal_restart->{reset},
## Check that checkpoint stats are reset, WAL stats aren't affected
-$node->safe_psql($connect_db, "SELECT pg_stat_reset_shared('bgwriter')");
+$node->safe_psql($connect_db, "SELECT pg_stat_reset_shared('checkpointer')");
$sect = "post ckpt reset";
my $ckpt_reset = checkpoint_stats();
@@ -323,9 +323,9 @@ sub checkpoint_stats
my %results;
$results{count} = $node->safe_psql($connect_db,
- "SELECT checkpoints_timed + checkpoints_req FROM pg_stat_bgwriter");
+ "SELECT timed_checkpoints + requested_checkpoints FROM pg_stat_checkpointer");
$results{reset} = $node->safe_psql($connect_db,
- "SELECT stats_reset FROM pg_stat_bgwriter");
+ "SELECT stats_reset FROM pg_stat_checkpointer");
return \%results;
}
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index d32f47cdb0..7eae7f4184 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1814,15 +1814,16 @@ pg_stat_archiver| SELECT archived_count,
last_failed_time,
stats_reset
FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
-pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
- pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
- pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
- pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
- pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
- pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
+pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_alloc() AS buffers_alloc,
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
+pg_stat_checkpointer| SELECT pg_stat_get_timed_checkpoints() AS timed_checkpoints,
+ pg_stat_get_requested_checkpoints() AS requested_checkpoints,
+ pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
+ pg_stat_get_checkpoint_sync_time() AS checkpoint_sync_time,
+ pg_stat_get_buf_written_checkpoints() AS buffers_written_checkpoints,
+ pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
pg_stat_database| SELECT oid AS datid,
datname,
CASE
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 1d84407a03..85aaebe7b6 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -782,8 +782,8 @@ SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELEC
t
(1 row)
--- Test pg_stat_bgwriter checkpointer-related stats, together with pg_stat_wal
-SELECT checkpoints_req AS rqst_ckpts_before FROM pg_stat_bgwriter \gset
+-- Test pg_stat_checkpointer checkpointer-related stats, together with pg_stat_wal
+SELECT requested_checkpoints AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
-- Test pg_stat_wal (and make a temp table so our temp schema exists)
SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
CREATE TEMP TABLE test_stats_temp AS SELECT 17;
@@ -793,7 +793,7 @@ DROP TABLE test_stats_temp;
-- results of the first.
CHECKPOINT;
CHECKPOINT;
-SELECT checkpoints_req > :rqst_ckpts_before FROM pg_stat_bgwriter;
+SELECT requested_checkpoints > :rqst_ckpts_before FROM pg_stat_checkpointer;
?column?
----------
t
@@ -884,6 +884,21 @@ SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
(1 row)
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
+-- Test that reset_shared with checkpointer specified as the stats type works
+SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
+SELECT pg_stat_reset_shared('checkpointer');
+ pg_stat_reset_shared
+----------------------
+
+(1 row)
+
+SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
SELECT pg_stat_reset_shared('wal');
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index b4d6753c71..e867bc15e3 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -387,8 +387,8 @@ SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELEC
SELECT pg_stat_force_next_flush();
SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
--- Test pg_stat_bgwriter checkpointer-related stats, together with pg_stat_wal
-SELECT checkpoints_req AS rqst_ckpts_before FROM pg_stat_bgwriter \gset
+-- Test pg_stat_checkpointer checkpointer-related stats, together with pg_stat_wal
+SELECT requested_checkpoints AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
-- Test pg_stat_wal (and make a temp table so our temp schema exists)
SELECT wal_bytes AS wal_bytes_before FROM pg_stat_wal \gset
@@ -402,7 +402,7 @@ DROP TABLE test_stats_temp;
CHECKPOINT;
CHECKPOINT;
-SELECT checkpoints_req > :rqst_ckpts_before FROM pg_stat_bgwriter;
+SELECT requested_checkpoints > :rqst_ckpts_before FROM pg_stat_checkpointer;
SELECT wal_bytes > :wal_bytes_before FROM pg_stat_wal;
-- Test pg_stat_get_backend_idset() and some allied functions.
@@ -440,6 +440,12 @@ SELECT pg_stat_reset_shared('bgwriter');
SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
+-- Test that reset_shared with checkpointer specified as the stats type works
+SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
+SELECT pg_stat_reset_shared('checkpointer');
+SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
+SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
+
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
SELECT pg_stat_reset_shared('wal');
--
2.34.1
