Thanks for remarks!
On 28.11.2023 21:34, Alexander Korotkov wrote:
After examining the second patch
("v2-0001-Add-restartpoint-stats.patch"), it appears that adding
additional statistics as outlined in the patch is the most suitable
approach to address the concerns raised. This solution provides more
visibility into the system's behavior without altering its core
mechanics.
Agreed. I left only this variant of the patch and rework it due to commit
96f05261.
So the new counters is in the pg_stat_checkpointer view now.
Please see the v3-0001-add-restartpoints-stats.patch attached.
However, it's essential that this additional functionality
is accompanied by comprehensive documentation to ensure clear
understanding and ease of use by the PostgreSQL community.
Please consider expanding the documentation to include detailed
explanations of the new statistics and their implications in various
scenarios.
In the separate v3-0002-doc-for-restartpoints-stats.patch i added the
definitions
of the new counters into the "28.2.15. pg_stat_checkpointer" section
and explanation of them with examples into the "30.5.WAL Configuration" one.
Would be glad for any comments and and concerns.
With the best wishes,
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
commit 5711c09dbfbe02586a247a98e0ae41cd71a221a3
Author: Anton A. Melnikov <a.melni...@postgrespro.ru>
Date: Sun Dec 3 12:49:11 2023 +0300
Add statistic about restartpoints into pg_stat_checkpointer
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 11d18ed9dd..058fc47c91 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1141,6 +1141,9 @@ CREATE VIEW pg_stat_checkpointer AS
SELECT
pg_stat_get_checkpointer_num_timed() AS num_timed,
pg_stat_get_checkpointer_num_requested() AS num_requested,
+ pg_stat_get_checkpointer_restartpoints_timed() AS restartpoints_timed,
+ pg_stat_get_checkpointer_restartpoints_requested() AS restartpoints_req,
+ pg_stat_get_checkpointer_restartpoints_performed() AS restartpoints_done,
pg_stat_get_checkpointer_write_time() AS write_time,
pg_stat_get_checkpointer_sync_time() AS sync_time,
pg_stat_get_checkpointer_buffers_written() AS buffers_written,
diff --git a/src/backend/postmaster/checkpointer.c b/src/backend/postmaster/checkpointer.c
index dc2da5a2cd..67ecb177e7 100644
--- a/src/backend/postmaster/checkpointer.c
+++ b/src/backend/postmaster/checkpointer.c
@@ -340,6 +340,8 @@ CheckpointerMain(void)
pg_time_t now;
int elapsed_secs;
int cur_timeout;
+ bool chkpt_or_rstpt_requested = false;
+ bool chkpt_or_rstpt_timed = false;
/* Clear any already-pending wakeups */
ResetLatch(MyLatch);
@@ -358,7 +360,7 @@ CheckpointerMain(void)
if (((volatile CheckpointerShmemStruct *) CheckpointerShmem)->ckpt_flags)
{
do_checkpoint = true;
- PendingCheckpointerStats.num_requested++;
+ chkpt_or_rstpt_requested = true;
}
/*
@@ -372,7 +374,7 @@ CheckpointerMain(void)
if (elapsed_secs >= CheckPointTimeout)
{
if (!do_checkpoint)
- PendingCheckpointerStats.num_timed++;
+ chkpt_or_rstpt_timed = true;
do_checkpoint = true;
flags |= CHECKPOINT_CAUSE_TIME;
}
@@ -408,6 +410,24 @@ CheckpointerMain(void)
if (flags & CHECKPOINT_END_OF_RECOVERY)
do_restartpoint = false;
+ if (chkpt_or_rstpt_timed)
+ {
+ chkpt_or_rstpt_timed = false;
+ if (do_restartpoint)
+ PendingCheckpointerStats.restartpoints_timed++;
+ else
+ PendingCheckpointerStats.num_timed++;
+ }
+
+ if (chkpt_or_rstpt_requested)
+ {
+ chkpt_or_rstpt_requested = false;
+ if (do_restartpoint)
+ PendingCheckpointerStats.restartpoints_requested++;
+ else
+ PendingCheckpointerStats.num_requested++;
+ }
+
/*
* We will warn if (a) too soon since last checkpoint (whatever
* caused it) and (b) somebody set the CHECKPOINT_CAUSE_XLOG flag
@@ -471,6 +491,9 @@ CheckpointerMain(void)
* checkpoints happen at a predictable spacing.
*/
last_checkpoint_time = now;
+
+ if (do_restartpoint)
+ PendingCheckpointerStats.restartpoints_performed++;
}
else
{
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index 301a0bc7bd..6ee258f240 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -49,6 +49,9 @@ pgstat_report_checkpointer(void)
#define CHECKPOINTER_ACC(fld) stats_shmem->stats.fld += PendingCheckpointerStats.fld
CHECKPOINTER_ACC(num_timed);
CHECKPOINTER_ACC(num_requested);
+ CHECKPOINTER_ACC(restartpoints_timed);
+ CHECKPOINTER_ACC(restartpoints_requested);
+ CHECKPOINTER_ACC(restartpoints_performed);
CHECKPOINTER_ACC(write_time);
CHECKPOINTER_ACC(sync_time);
CHECKPOINTER_ACC(buffers_written);
@@ -116,6 +119,9 @@ pgstat_checkpointer_snapshot_cb(void)
#define CHECKPOINTER_COMP(fld) pgStatLocal.snapshot.checkpointer.fld -= reset.fld;
CHECKPOINTER_COMP(num_timed);
CHECKPOINTER_COMP(num_requested);
+ CHECKPOINTER_COMP(restartpoints_timed);
+ CHECKPOINTER_COMP(restartpoints_requested);
+ CHECKPOINTER_COMP(restartpoints_performed);
CHECKPOINTER_COMP(write_time);
CHECKPOINTER_COMP(sync_time);
CHECKPOINTER_COMP(buffers_written);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 0cea320c00..e65cbf41e9 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1193,6 +1193,24 @@ pg_stat_get_checkpointer_num_requested(PG_FUNCTION_ARGS)
PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->num_requested);
}
+Datum
+pg_stat_get_checkpointer_restartpoints_timed(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->restartpoints_timed);
+}
+
+Datum
+pg_stat_get_checkpointer_restartpoints_requested(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->restartpoints_requested);
+}
+
+Datum
+pg_stat_get_checkpointer_restartpoints_performed(PG_FUNCTION_ARGS)
+{
+ PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->restartpoints_performed);
+}
+
Datum
pg_stat_get_checkpointer_buffers_written(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fb58dee3bc..f6926c864d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5721,6 +5721,21 @@
proname => 'pg_stat_get_checkpointer_num_requested', provolatile => 's',
proparallel => 'r', prorettype => 'int8', proargtypes => '',
prosrc => 'pg_stat_get_checkpointer_num_requested' },
+{ oid => '8743',
+ descr => 'statistics: number of timed restartpoints started by the checkpointer',
+ proname => 'pg_stat_get_checkpointer_restartpoints_timed', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => '',
+ prosrc => 'pg_stat_get_checkpointer_restartpoints_timed' },
+{ oid => '8744',
+ descr => 'statistics: number of backend requested restartpoints started by the checkpointer',
+ proname => 'pg_stat_get_checkpointer_restartpoints_requested', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => '',
+ prosrc => 'pg_stat_get_checkpointer_restartpoints_requested' },
+{ oid => '8745',
+ descr => 'statistics: number of backend performed restartpoints',
+ proname => 'pg_stat_get_checkpointer_restartpoints_performed', provolatile => 's',
+ proparallel => 'r', prorettype => 'int8', proargtypes => '',
+ prosrc => 'pg_stat_get_checkpointer_restartpoints_performed' },
{ oid => '2771',
descr => 'statistics: number of buffers written by the checkpointer',
proname => 'pg_stat_get_checkpointer_buffers_written', provolatile => 's',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f95d8db0c4..3cc2762ef5 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -262,6 +262,9 @@ typedef struct PgStat_CheckpointerStats
{
PgStat_Counter num_timed;
PgStat_Counter num_requested;
+ PgStat_Counter restartpoints_timed;
+ PgStat_Counter restartpoints_requested;
+ PgStat_Counter restartpoints_performed;
PgStat_Counter write_time; /* times in milliseconds */
PgStat_Counter sync_time;
PgStat_Counter buffers_written;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 05070393b9..f645e8486b 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1822,6 +1822,9 @@ pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_cle
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
pg_stat_checkpointer| SELECT pg_stat_get_checkpointer_num_timed() AS num_timed,
pg_stat_get_checkpointer_num_requested() AS num_requested,
+ pg_stat_get_checkpointer_restartpoints_timed() AS restartpoints_timed,
+ pg_stat_get_checkpointer_restartpoints_requested() AS restartpoints_req,
+ pg_stat_get_checkpointer_restartpoints_performed() AS restartpoints_done,
pg_stat_get_checkpointer_write_time() AS write_time,
pg_stat_get_checkpointer_sync_time() AS sync_time,
pg_stat_get_checkpointer_buffers_written() AS buffers_written,
commit 4acf7f57f1476611b70f027b4fddd7cc276204af
Author: Anton A. Melnikov <a.melni...@postgrespro.ru>
Date: Mon Dec 4 04:19:47 2023 +0300
Add docs about restartpoints related counters in the pg_stat_checkpointer view.
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 42509042ad..45cc091ea7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2982,6 +2982,33 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>restartpoints_timed</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of scheduled restartpoints due to timeout or after failed attempt to perform it
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>restartpoints_req</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of requested restartpoints
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>restartpoints_done</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Number of restartpoints that have been performed
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>write_time</structfield> <type>double precision</type>
diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml
index 2ed4eb659d..678b0489d3 100644
--- a/doc/src/sgml/wal.sgml
+++ b/doc/src/sgml/wal.sgml
@@ -655,14 +655,41 @@
directory.
Restartpoints can't be performed more frequently than checkpoints on the
primary because restartpoints can only be performed at checkpoint records.
- A restartpoint is triggered when a checkpoint record is reached if at
- least <varname>checkpoint_timeout</varname> seconds have passed since the last
- restartpoint, or if WAL size is about to exceed
- <varname>max_wal_size</varname>. However, because of limitations on when a
- restartpoint can be performed, <varname>max_wal_size</varname> is often exceeded
- during recovery, by up to one checkpoint cycle's worth of WAL.
+ A restartpoint can be demanded by a shedule or by an external request.
+ The <structfield>restartpoints_timed</structfield> counter in the
+ <link linkend="monitoring-pg-stat-checkpointer-view"><structname>pg_stat_checkpointer</structname></link>
+ view counts the first ones while the <structfield>restartpoints_req</structfield>
+ the second.
+ A restartpoint is triggered by shedule when a checkpoint record is reached
+ if at least <xref linkend="guc-checkpoint-timeout"/> seconds have passed since
+ the last performed restartpoint or when the previous attempt to perform
+ the restartpoint have been failed. In the last case the next restartpoint
+ will be scheduled in 15s.
+ A restartpoint is triggered by request due to similar reasons like checkpoint
+ but mostly if WAL size is about to exceed <xref linkend="guc-max-wal-size"/>
+ However, because of limitations on when a restartpoint can be performed,
+ <varname>max_wal_size</varname> is often exceeded during recovery,
+ by up to one checkpoint cycle's worth of WAL.
(<varname>max_wal_size</varname> is never a hard limit anyway, so you should
always leave plenty of headroom to avoid running out of disk space.)
+ The <structfield>restartpoints_done</structfield> counter in the
+ <link linkend="monitoring-pg-stat-checkpointer-view"><structname>pg_stat_checkpointer</structname></link>
+ view counts the restartpoints that have really been performed.
+ </para>
+
+ <para>
+ In some cases, when the WAL size on the primary increases quickly,
+ for instance during massive INSERT,
+ the <structfield>restartpoints_req</structfield> counter on the standby
+ may demonstarte a spike growth.
+ This occurs since requests to create a new restartpoint due to increased
+ XLOG consumption cannot be performed because the safe checkpoint record
+ since last restartpoint has not yet been replayed on the standby.
+ This behavior is normal does not lead to increase in system resources
+ consumption.
+ Only the <structfield>restartpoints_done</structfield>
+ counter among the restartpoint related ones indicates that noticable system
+ resources have been spent.
</para>
<para>
