On Fri, Feb 10, 2023 at 10:00 PM Bharath Rupireddy
<bharath.rupireddyforpostg...@gmail.com> wrote:
>
> Thanks. FWIW, I've attached the patch introducing pg_stat_checkpointer
> as 0003 here.
>
> Please review the attached v7 patch set.
Needed a rebase. Please review the attached v8 patch set.
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From c7d1a10b113c90e3712e5f836c21b5a40f909a24 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Mon, 13 Feb 2023 05:01:52 +0000
Subject: [PATCH v8] 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 dca50707ad..c0cae0f4f3 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4059,15 +4059,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 34ca0e739f..9bb96e27aa 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 9d707c3521..28075fa6fb 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 66b73c3900..e800fdfd5f 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 174b725fff..5b4f441245 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 09afb4d2da776b863aee9d87f475eb8cd852d44c Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Mon, 13 Feb 2023 05:02:17 +0000
Subject: [PATCH v8] 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 c0cae0f4f3..098ca5999d 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4059,17 +4059,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 9bb96e27aa..2b8e462532 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 28075fa6fb..087ba87a86 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 e800fdfd5f..5ed66af0e3 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 5b4f441245..9c9adb6140 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 5b349d6b3a3b8db1498e038aed81909fd1f15fe4 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Mon, 13 Feb 2023 05:05:07 +0000
Subject: [PATCH v8] 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 098ca5999d..47bf6ff247 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
@@ -3974,7 +3983,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">
@@ -3994,77 +4003,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>
@@ -5714,8 +5764,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 2b8e462532..ad2992fd25 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_io AS
SELECT
b.backend_type,
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 087ba87a86..f2395fc13c 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)
{
@@ -1730,14 +1736,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 5ed66af0e3..7030597d67 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 9c9adb6140..f18c047a42 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 937b2101b3..2bbe17ba7d 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 74e592aa8a..cfc9aa3a37 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
