Inconsistency in reporting checkpointer stats

From 555f280f36c8c8fc0927596610672b5a6898c3e7 Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <nitinjadhav@microsoft.com>
Date: Thu, 15 Dec 2022 10:03:48 +0000
Subject: [PATCH] Fix inconsistency in checkpointer stats

The buffers written information exposed using pg_stat_bgwriter view is having
inconsistent data compared to the checkpoint complete log message. So
incremented the counter in SlruInternalWritePage() to fix the inconsistency.
---
 src/backend/access/transam/slru.c | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 6feda87f57..de23f1d673 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -604,7 +604,10 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 
 	/* If part of a checkpoint, count this as a buffer written. */
 	if (fdata)
+	{
 		CheckpointStats.ckpt_bufs_written++;
+		PendingCheckpointerStats.buf_written_checkpoints++;
+	}
 }
 
 /*
-- 
2.25.1

From 4a78d3a9448625ce817f5ae683c475ee6f0294e2 Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <nitinjadhav@microsoft.com>
Date: Wed, 21 Dec 2022 11:03:57 +0000
Subject: [PATCH 2/2] Separate main buffer and SLRU buffer count from
 checkpoint complete log message

The buffers written information logged in checkpoint complete log message
includes main buffer count as well as SLRU buffer count. This patch separates
these two information.
---
 src/backend/access/transam/slru.c |  2 +-
 src/backend/access/transam/xlog.c | 28 ++++++++++++++++------------
 src/include/access/xlog.h         |  1 +
 3 files changed, 18 insertions(+), 13 deletions(-)

diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 3379e20f30..714842cc1c 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -605,7 +605,7 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 	/* If part of a checkpoint, count this as a buffer written. */
 	if (fdata)
 	{
-		CheckpointStats.ckpt_bufs_written++;
+		CheckpointStats.ckpt_slru_written++;
 		PendingCheckpointerStats.slru_written_checkpoints++;
 	}
 }
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 91473b00d9..0f13b19ade 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6280,14 +6280,16 @@ LogCheckpointEnd(bool restartpoint)
 	 */
 	if (restartpoint)
 		ereport(LOG,
-				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers (%.1f%%); %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
+						(double) CheckpointStats.ckpt_slru_written * 100 / NBuffers,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
@@ -6303,14 +6305,16 @@ LogCheckpointEnd(bool restartpoint)
 						LSN_FORMAT_ARGS(ControlFile->checkPointCopy.redo))));
 	else
 		ereport(LOG,
-				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers (%.1f%%); %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
+						(double) CheckpointStats.ckpt_slru_written * 100 / NBuffers,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 1fbd48fbda..9b6d7cfe57 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -162,6 +162,7 @@ typedef struct CheckpointStatsData
 	TimestampTz ckpt_end_t;		/* end of checkpoint */
 
 	int			ckpt_bufs_written;	/* # of buffers written */
+	int			ckpt_slru_written;	/* # of SLRU buffers written */
 
 	int			ckpt_segs_added;	/* # of new xlog segments created */
 	int			ckpt_segs_removed;	/* # of xlog segments deleted */
-- 
2.25.1

From 1b79e74d404a97aaef62717d6b33fcf54801719e Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <nitinjadhav@microsoft.com>
Date: Wed, 21 Dec 2022 10:13:58 +0000
Subject: [PATCH 1/2] Fix inconsistency in checkpointer stats

The buffers written information exposed using pg_stat_bgwriter view is having
inconsistent data compared to the checkpoint complete log message. Introduced
a new column slru_buffers_checkpoint in pg_stat_bgwriter view to show the SLRU
buffers written during checkpoints. The existing column buffers_checkpoint
remains as-is to represent the main buffers written during checkpoints.
---
 doc/src/sgml/monitoring.sgml                     | 9 +++++++++
 src/backend/access/transam/slru.c                | 3 +++
 src/backend/catalog/system_views.sql             | 1 +
 src/backend/utils/activity/pgstat_checkpointer.c | 1 +
 src/backend/utils/adt/pgstatfuncs.c              | 6 ++++++
 src/include/catalog/pg_proc.dat                  | 5 +++++
 src/include/pgstat.h                             | 1 +
 7 files changed, 26 insertions(+)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 363b183e5f..5c15a40981 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3699,6 +3699,15 @@ 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>slru_buffers_checkpoint</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of SLRU buffers written during checkpoints
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>buffers_clean</structfield> <type>bigint</type>
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 6feda87f57..3379e20f30 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -604,7 +604,10 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 
 	/* If part of a checkpoint, count this as a buffer written. */
 	if (fdata)
+	{
 		CheckpointStats.ckpt_bufs_written++;
+		PendingCheckpointerStats.slru_written_checkpoints++;
+	}
 }
 
 /*
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2d8104b090..6954ad39fc 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1110,6 +1110,7 @@ CREATE VIEW pg_stat_bgwriter AS
         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_slru_written_checkpoints() AS slru_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,
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index af8d513e7b..5a9245296d 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -52,6 +52,7 @@ pgstat_report_checkpointer(void)
 	CHECKPOINTER_ACC(checkpoint_write_time);
 	CHECKPOINTER_ACC(checkpoint_sync_time);
 	CHECKPOINTER_ACC(buf_written_checkpoints);
+	CHECKPOINTER_ACC(slru_written_checkpoints);
 	CHECKPOINTER_ACC(buf_written_backend);
 	CHECKPOINTER_ACC(buf_fsync_backend);
 #undef CHECKPOINTER_ACC
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 46f98fd67f..29e7165c3f 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1182,6 +1182,12 @@ pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buf_written_checkpoints);
 }
 
+Datum
+pg_stat_get_bgwriter_slru_written_checkpoints(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->slru_written_checkpoints);
+}
+
 Datum
 pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 98d90d9338..8bd5425e71 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5649,6 +5649,11 @@
   proname => 'pg_stat_get_bgwriter_buf_written_checkpoints', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => '',
   prosrc => 'pg_stat_get_bgwriter_buf_written_checkpoints' },
+{ oid => '3813',
+  descr => 'statistics: number of SLRU buffers written by the bgwriter during checkpoints',
+  proname => 'pg_stat_get_bgwriter_slru_written_checkpoints', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_stat_get_bgwriter_slru_written_checkpoints' },
 { 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 a3df8d27c3..3a11d23d94 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -272,6 +272,7 @@ typedef struct PgStat_CheckpointerStats
 	PgStat_Counter checkpoint_write_time;	/* times in milliseconds */
 	PgStat_Counter checkpoint_sync_time;
 	PgStat_Counter buf_written_checkpoints;
+	PgStat_Counter slru_written_checkpoints;
 	PgStat_Counter buf_written_backend;
 	PgStat_Counter buf_fsync_backend;
 } PgStat_CheckpointerStats;
-- 
2.25.1

From f4b4a9a6c3ce60b343e799071f6f974a9066ec00 Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <nitinjadhav@microsoft.com>
Date: Sun, 19 Feb 2023 15:11:16 +0530
Subject: [PATCH] Fix inconsistency in reporting checkpointer stats.

The buffers written information exposed using pg_stat_bgwriter view is having
inconsistent data compared to the checkpoint complete log message. Introduced
a new column slru_buffers_checkpoint in pg_stat_bgwriter view to show the SLRU
buffers written during checkpoints. The existing column buffers_checkpoint
remains as-is to represent the main buffers written during checkpoints. Also
the buffers written information logged in checkpoint complete log message
includes main buffer count as well as SLRU buffer count. This patch separates
these two information.
---
 doc/src/sgml/monitoring.sgml                  |  9 ++++++
 src/backend/access/transam/slru.c             |  5 +++-
 src/backend/access/transam/xlog.c             | 28 +++++++++++--------
 src/backend/catalog/system_views.sql          |  1 +
 .../utils/activity/pgstat_checkpointer.c      |  1 +
 src/backend/utils/adt/pgstatfuncs.c           |  6 ++++
 src/include/access/xlog.h                     |  1 +
 src/include/catalog/pg_proc.dat               |  5 ++++
 src/include/pgstat.h                          |  1 +
 src/test/regress/expected/rules.out           |  1 +
 10 files changed, 45 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b0b997f092..d1be6f512a 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4045,6 +4045,15 @@ 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>slru_buffers_checkpoint</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of SLRU buffers written during checkpoints
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>buffers_clean</structfield> <type>bigint</type>
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 5ab86238a9..76c202eccf 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -604,7 +604,10 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 
 	/* If part of a checkpoint, count this as a buffer written. */
 	if (fdata)
-		CheckpointStats.ckpt_bufs_written++;
+	{
+		CheckpointStats.ckpt_slru_written++;
+		PendingCheckpointerStats.slru_written_checkpoints++;
+	}
 }
 
 /*
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index f9f0f6db8d..3a55a5dd27 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6282,14 +6282,16 @@ LogCheckpointEnd(bool restartpoint)
 	 */
 	if (restartpoint)
 		ereport(LOG,
-				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers (%.1f%%); %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
+						(double) CheckpointStats.ckpt_slru_written * 100 / NBuffers,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
@@ -6305,14 +6307,16 @@ LogCheckpointEnd(bool restartpoint)
 						LSN_FORMAT_ARGS(ControlFile->checkPointCopy.redo))));
 	else
 		ereport(LOG,
-				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers (%.1f%%); %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
+						(double) CheckpointStats.ckpt_slru_written * 100 / NBuffers,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 34ca0e739f..415b71264f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1110,6 +1110,7 @@ CREATE VIEW pg_stat_bgwriter AS
         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_slru_written_checkpoints() AS slru_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,
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index 26dec112f6..b2a9cd0b4c 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -52,6 +52,7 @@ pgstat_report_checkpointer(void)
 	CHECKPOINTER_ACC(checkpoint_write_time);
 	CHECKPOINTER_ACC(checkpoint_sync_time);
 	CHECKPOINTER_ACC(buf_written_checkpoints);
+	CHECKPOINTER_ACC(slru_written_checkpoints);
 	CHECKPOINTER_ACC(buf_written_backend);
 	CHECKPOINTER_ACC(buf_fsync_backend);
 #undef CHECKPOINTER_ACC
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 9d707c3521..45b7cfdf9f 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1193,6 +1193,12 @@ pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buf_written_checkpoints);
 }
 
+Datum
+pg_stat_get_bgwriter_slru_written_checkpoints(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->slru_written_checkpoints);
+}
+
 Datum
 pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index cfe5409738..669235c4e7 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -162,6 +162,7 @@ typedef struct CheckpointStatsData
 	TimestampTz ckpt_end_t;		/* end of checkpoint */
 
 	int			ckpt_bufs_written;	/* # of buffers written */
+	int			ckpt_slru_written;	/* # of SLRU buffers written */
 
 	int			ckpt_segs_added;	/* # of new xlog segments created */
 	int			ckpt_segs_removed;	/* # of xlog segments deleted */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 66b73c3900..0bca329587 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5680,6 +5680,11 @@
   proname => 'pg_stat_get_bgwriter_buf_written_checkpoints', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => '',
   prosrc => 'pg_stat_get_bgwriter_buf_written_checkpoints' },
+{ oid => '3813',
+  descr => 'statistics: number of SLRU buffers written by the bgwriter during checkpoints',
+  proname => 'pg_stat_get_bgwriter_slru_written_checkpoints', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_stat_get_bgwriter_slru_written_checkpoints' },
 { 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 db9675884f..380dd1ad4e 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;
+	PgStat_Counter slru_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 a3a5a62329..85c40faa98 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1819,6 +1819,7 @@ pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints
     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_slru_written_checkpoints() AS slru_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,
-- 
2.34.1

From f17300193ab350c20100c8101bc2ca5f74b5c109 Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <nitinjadhav@microsoft.com>
Date: Tue, 16 Jul 2024 08:32:00 +0000
Subject: [PATCH] Fix inconsistency in reporting checkpointer stats.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

The information about buffers written, which is revealed through the
pg_stat_checkpointer view, has been found to be inconsistent with the
data in the checkpoint complete log message. To address this, a new
column named ‘slru_written’ has been added to the pg_stat_checkpointer
view. This column displays the SLRU buffers written during checkpoints.
The existing ‘buffers_written’ column continues to represent the main
buffers written during checkpoints. Furthermore, the information about
buffers written, which is logged in the checkpoint complete log message,
includes both the main buffer count and the SLRU buffer count. This
patch distinguishes between these two types of information.
---
 doc/src/sgml/monitoring.sgml                  |  9 ++++++
 src/backend/access/transam/slru.c             |  5 +++-
 src/backend/access/transam/xlog.c             | 28 +++++++++++--------
 src/backend/catalog/system_views.sql          |  1 +
 .../utils/activity/pgstat_checkpointer.c      |  1 +
 src/backend/utils/adt/pgstatfuncs.c           |  6 ++++
 src/include/access/xlog.h                     |  1 +
 src/include/catalog/pg_proc.dat               |  5 ++++
 src/include/pgstat.h                          |  1 +
 src/test/regress/expected/rules.out           |  1 +
 10 files changed, 45 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 55417a6fa9..b2fe425268 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3049,6 +3049,15 @@ 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>slru_written</structfield> <type>bigint</type>
+      </para>
+      <para>
+        Number of SLRU buffers written during checkpoints and restartpoints
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index 77b05cc0a7..de59bcdf1c 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -703,7 +703,10 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 
 	/* If part of a checkpoint, count this as a buffer written. */
 	if (fdata)
-		CheckpointStats.ckpt_bufs_written++;
+	{
+		CheckpointStats.ckpt_slru_written++;
+		PendingCheckpointerStats.slru_written++;
+	}
 }
 
 /*
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 33e27a6e72..19d7fbf70a 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6666,14 +6666,16 @@ LogCheckpointEnd(bool restartpoint)
 	 */
 	if (restartpoint)
 		ereport(LOG,
-				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers (%.1f%%); %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
+						(double) CheckpointStats.ckpt_slru_written * 100 / NBuffers,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
@@ -6689,14 +6691,16 @@ LogCheckpointEnd(bool restartpoint)
 						LSN_FORMAT_ARGS(ControlFile->checkPointCopy.redo))));
 	else
 		ereport(LOG,
-				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers (%.1f%%); %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
+						(double) CheckpointStats.ckpt_slru_written * 100 / NBuffers,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 19cabc9a47..aaaa2b3474 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1144,6 +1144,7 @@ CREATE VIEW pg_stat_checkpointer AS
         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,
+        pg_stat_get_checkpointer_slru_written() AS slru_written,
         pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
 
 CREATE VIEW pg_stat_io AS
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index bbfc9c7e18..98d16c7868 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -55,6 +55,7 @@ pgstat_report_checkpointer(void)
 	CHECKPOINTER_ACC(write_time);
 	CHECKPOINTER_ACC(sync_time);
 	CHECKPOINTER_ACC(buffers_written);
+	CHECKPOINTER_ACC(slru_written);
 #undef CHECKPOINTER_ACC
 
 	pgstat_end_changecount_write(&stats_shmem->changecount);
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 3876339ee1..f3ca746818 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1215,6 +1215,12 @@ pg_stat_get_checkpointer_buffers_written(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buffers_written);
 }
 
+Datum
+pg_stat_get_checkpointer_slru_written(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->slru_written);
+}
+
 Datum
 pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 1a1f11a943..23a264ef23 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -165,6 +165,7 @@ typedef struct CheckpointStatsData
 	TimestampTz ckpt_end_t;		/* end of checkpoint */
 
 	int			ckpt_bufs_written;	/* # of buffers written */
+	int			ckpt_slru_written;	/* # of SLRU buffers written */
 
 	int			ckpt_segs_added;	/* # of new xlog segments created */
 	int			ckpt_segs_removed;	/* # of xlog segments deleted */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 73d9cf8582..6adb683553 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5757,6 +5757,11 @@
   proname => 'pg_stat_get_checkpointer_buffers_written', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => '',
   prosrc => 'pg_stat_get_checkpointer_buffers_written' },
+{ oid => '3814',
+  descr => 'statistics: number of SLRU buffers written during checkpoints and restartpoints',
+  proname => 'pg_stat_get_checkpointer_slru_written', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_stat_get_checkpointer_slru_written' },
 { oid => '6314', descr => 'statistics: last reset for the checkpointer',
   proname => 'pg_stat_get_checkpointer_stat_reset_time', provolatile => 's',
   proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 6b99bb8aad..eedcc7d4a2 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -268,6 +268,7 @@ typedef struct PgStat_CheckpointerStats
 	PgStat_Counter write_time;	/* times in milliseconds */
 	PgStat_Counter sync_time;
 	PgStat_Counter buffers_written;
+	PgStat_Counter slru_written;
 	TimestampTz stat_reset_timestamp;
 } PgStat_CheckpointerStats;
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 4c789279e5..4b1ff2d26e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1830,6 +1830,7 @@ pg_stat_checkpointer| SELECT pg_stat_get_checkpointer_num_timed() AS num_timed,
     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,
+    pg_stat_get_checkpointer_slru_written() AS slru_written,
     pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
 pg_stat_database| SELECT oid AS datid,
     datname,
-- 
2.43.0

From e81427ad6da4bac608d188180569abe02914013e Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <nitinjadhav@microsoft.com>
Date: Sun, 22 Sep 2024 17:04:23 +0530
Subject: [PATCH] Fix inconsistency in reporting checkpointer stats.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

The information about buffers written, which is revealed through the
pg_stat_checkpointer view, has been found to be inconsistent with the
data in the checkpoint complete log message. To address this, a new
column named ‘slru_written’ has been added to the pg_stat_checkpointer
view. This column displays the SLRU buffers written during checkpoints.
The existing ‘buffers_written’ column continues to represent the main
buffers written during checkpoints. Furthermore, the information about
buffers written, which is logged in the checkpoint complete log message,
includes both the main buffer count and the SLRU buffer count. This
patch distinguishes between these two types of information.
---
 doc/src/sgml/monitoring.sgml                  |  9 +++++++
 src/backend/access/transam/slru.c             |  7 +++--
 src/backend/access/transam/xlog.c             | 26 ++++++++++---------
 src/backend/catalog/system_views.sql          |  1 +
 .../utils/activity/pgstat_checkpointer.c      |  2 ++
 src/backend/utils/adt/pgstatfuncs.c           |  6 +++++
 src/include/access/xlog.h                     |  1 +
 src/include/catalog/pg_proc.dat               |  5 ++++
 src/include/pgstat.h                          |  1 +
 src/test/regress/expected/rules.out           |  1 +
 10 files changed, 45 insertions(+), 14 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index a2fda4677d..6cbbb1ca5c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3125,6 +3125,15 @@ 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>slru_written</structfield> <type>bigint</type>
+      </para>
+      <para>
+        Number of SLRU buffers written during checkpoints and restartpoints
+      </para></entry>
+     </row>
+
      <row>
       <entry role="catalog_table_entry"><para role="column_definition">
        <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index e7f73bf427..bf8deccdc0 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -716,9 +716,12 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 	if (!ok)
 		SlruReportIOError(ctl, pageno, InvalidTransactionId);
 
-	/* If part of a checkpoint, count this as a buffer written. */
+	/* If part of a checkpoint, count this as a slru written. */
 	if (fdata)
-		CheckpointStats.ckpt_bufs_written++;
+	{
+		CheckpointStats.ckpt_slru_written++;
+		PendingCheckpointerStats.slru_written++;
+	}
 }
 
 /*
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 853ab06812..d9f25a9046 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6727,14 +6727,15 @@ LogCheckpointEnd(bool restartpoint)
 	 */
 	if (restartpoint)
 		ereport(LOG,
-				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers; %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
@@ -6750,14 +6751,15 @@ LogCheckpointEnd(bool restartpoint)
 						LSN_FORMAT_ARGS(ControlFile->checkPointCopy.redo))));
 	else
 		ereport(LOG,
-				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d slru buffers; %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7fd5d256a1..a12db38d45 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1144,6 +1144,7 @@ CREATE VIEW pg_stat_checkpointer AS
         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,
+        pg_stat_get_checkpointer_slru_written() AS slru_written,
         pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
 
 CREATE VIEW pg_stat_io AS
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index bbfc9c7e18..21fda77276 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -55,6 +55,7 @@ pgstat_report_checkpointer(void)
 	CHECKPOINTER_ACC(write_time);
 	CHECKPOINTER_ACC(sync_time);
 	CHECKPOINTER_ACC(buffers_written);
+	CHECKPOINTER_ACC(slru_written);
 #undef CHECKPOINTER_ACC
 
 	pgstat_end_changecount_write(&stats_shmem->changecount);
@@ -133,5 +134,6 @@ pgstat_checkpointer_snapshot_cb(void)
 	CHECKPOINTER_COMP(write_time);
 	CHECKPOINTER_COMP(sync_time);
 	CHECKPOINTER_COMP(buffers_written);
+	CHECKPOINTER_COMP(slru_written);
 #undef CHECKPOINTER_COMP
 }
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 9c23ac7c8c..58d72e4361 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1215,6 +1215,12 @@ pg_stat_get_checkpointer_buffers_written(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buffers_written);
 }
 
+Datum
+pg_stat_get_checkpointer_slru_written(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->slru_written);
+}
+
 Datum
 pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 083810f5b4..5296be3d6c 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -167,6 +167,7 @@ typedef struct CheckpointStatsData
 	TimestampTz ckpt_end_t;		/* end of checkpoint */
 
 	int			ckpt_bufs_written;	/* # of buffers written */
+	int			ckpt_slru_written;	/* # of SLRU buffers written */
 
 	int			ckpt_segs_added;	/* # of new xlog segments created */
 	int			ckpt_segs_removed;	/* # of xlog segments deleted */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 43f608d7a0..0eb6e5c57b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5839,6 +5839,11 @@
   proname => 'pg_stat_get_checkpointer_buffers_written', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => '',
   prosrc => 'pg_stat_get_checkpointer_buffers_written' },
+{ oid => '3814',
+  descr => 'statistics: number of SLRU buffers written during checkpoints and restartpoints',
+  proname => 'pg_stat_get_checkpointer_slru_written', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_stat_get_checkpointer_slru_written' },
 { oid => '6314', descr => 'statistics: last reset for the checkpointer',
   proname => 'pg_stat_get_checkpointer_stat_reset_time', provolatile => 's',
   proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 4752dfe719..ea437f4273 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -300,6 +300,7 @@ typedef struct PgStat_CheckpointerStats
 	PgStat_Counter write_time;	/* times in milliseconds */
 	PgStat_Counter sync_time;
 	PgStat_Counter buffers_written;
+	PgStat_Counter slru_written;
 	TimestampTz stat_reset_timestamp;
 } PgStat_CheckpointerStats;
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a1626f3fae..322e24d16a 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1830,6 +1830,7 @@ pg_stat_checkpointer| SELECT pg_stat_get_checkpointer_num_timed() AS num_timed,
     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,
+    pg_stat_get_checkpointer_slru_written() AS slru_written,
     pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
 pg_stat_database| SELECT oid AS datid,
     datname,
-- 
2.25.1

From 150a915995115a882b18ad94c104b58dd1673cb1 Mon Sep 17 00:00:00 2001
From: Nitin Jadhav <nitinjadhav@microsoft.com>
Date: Sun, 22 Sep 2024 17:04:23 +0530
Subject: [PATCH v7] Fix inconsistent reporting of checkpointer stats

Previously, the pg_stat_checkpointer view and the checkpoint completion
log message could show different numbers for buffers written
during checkpoints. The view only counted shared buffers,
while the log message included both shared and SLRU buffers,
causing inconsistencies.

This commit resolves the issue by updating both the view and the log message
to separately report shared and SLRU buffers written during checkpoints.
A new slru_written column is added to the pg_stat_checkpointer view
to track SLRU buffers, while the existing buffers_written column now
tracks only shared buffers. This change would help users distinguish
between the two types of buffers, in the pg_stat_checkpointer view and
the checkpoint complete log message, respectively.

Bump catalog version.

Author: Nitin Jadhav
Reviewed-by: Bharath Rupireddy, Michael Paquier, Kyotaro Horiguchi, Robert Haas
Reviewed-by: Andres Freund, vignesh C
Discussion: https://postgr.es/m/CAMm1aWb18EpT0whJrjG+-nyhNouXET6ZUw0pNYYAe+NezpvsAA@mail.gmail.com
---
 doc/src/sgml/monitoring.sgml                  | 11 +++++++-
 src/backend/access/transam/slru.c             |  7 +++--
 src/backend/access/transam/xlog.c             | 26 ++++++++++---------
 src/backend/catalog/system_views.sql          |  1 +
 .../utils/activity/pgstat_checkpointer.c      |  2 ++
 src/backend/utils/adt/pgstatfuncs.c           |  6 +++++
 src/include/access/xlog.h                     |  1 +
 src/include/catalog/catversion.h              |  2 +-
 src/include/catalog/pg_proc.dat               |  5 ++++
 src/include/pgstat.h                          |  1 +
 src/test/regress/expected/rules.out           |  1 +
 11 files changed, 47 insertions(+), 16 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 48ffe87241..331315f8d3 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3127,7 +3127,16 @@ description | Waiting for a newly initialized WAL file to reach durable storage
        <structfield>buffers_written</structfield> <type>bigint</type>
       </para>
       <para>
-       Number of buffers written during checkpoints and restartpoints
+       Number of shared buffers written during checkpoints and restartpoints
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+        <structfield>slru_written</structfield> <type>bigint</type>
+      </para>
+      <para>
+        Number of SLRU buffers written during checkpoints and restartpoints
       </para></entry>
      </row>
 
diff --git a/src/backend/access/transam/slru.c b/src/backend/access/transam/slru.c
index e7f73bf427..889eff1815 100644
--- a/src/backend/access/transam/slru.c
+++ b/src/backend/access/transam/slru.c
@@ -716,9 +716,12 @@ SlruInternalWritePage(SlruCtl ctl, int slotno, SlruWriteAll fdata)
 	if (!ok)
 		SlruReportIOError(ctl, pageno, InvalidTransactionId);
 
-	/* If part of a checkpoint, count this as a buffer written. */
+	/* If part of a checkpoint, count this as a SLRU buffer written. */
 	if (fdata)
-		CheckpointStats.ckpt_bufs_written++;
+	{
+		CheckpointStats.ckpt_slru_written++;
+		PendingCheckpointerStats.slru_written++;
+	}
 }
 
 /*
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 64304d77d3..9102c8d772 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6727,14 +6727,15 @@ LogCheckpointEnd(bool restartpoint)
 	 */
 	if (restartpoint)
 		ereport(LOG,
-				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("restartpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d SLRU buffers; %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
@@ -6750,14 +6751,15 @@ LogCheckpointEnd(bool restartpoint)
 						LSN_FORMAT_ARGS(ControlFile->checkPointCopy.redo))));
 	else
 		ereport(LOG,
-				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%); "
-						"%d WAL file(s) added, %d removed, %d recycled; "
-						"write=%ld.%03d s, sync=%ld.%03d s, total=%ld.%03d s; "
-						"sync files=%d, longest=%ld.%03d s, average=%ld.%03d s; "
-						"distance=%d kB, estimate=%d kB; "
-						"lsn=%X/%X, redo lsn=%X/%X",
+				(errmsg("checkpoint complete: wrote %d buffers (%.1f%%), "
+						"wrote %d SLRU buffers; %d WAL file(s) added, "
+						"%d removed, %d recycled; write=%ld.%03d s, "
+						"sync=%ld.%03d s, total=%ld.%03d s; sync files=%d, "
+						"longest=%ld.%03d s, average=%ld.%03d s; distance=%d kB, "
+						"estimate=%d kB; lsn=%X/%X, redo lsn=%X/%X",
 						CheckpointStats.ckpt_bufs_written,
 						(double) CheckpointStats.ckpt_bufs_written * 100 / NBuffers,
+						CheckpointStats.ckpt_slru_written,
 						CheckpointStats.ckpt_segs_added,
 						CheckpointStats.ckpt_segs_removed,
 						CheckpointStats.ckpt_segs_recycled,
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 49109dbdc8..3456b821bc 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1145,6 +1145,7 @@ CREATE VIEW pg_stat_checkpointer AS
         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,
+        pg_stat_get_checkpointer_slru_written() AS slru_written,
         pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
 
 CREATE VIEW pg_stat_io AS
diff --git a/src/backend/utils/activity/pgstat_checkpointer.c b/src/backend/utils/activity/pgstat_checkpointer.c
index 4a0a2d1493..5a3fb4a9e0 100644
--- a/src/backend/utils/activity/pgstat_checkpointer.c
+++ b/src/backend/utils/activity/pgstat_checkpointer.c
@@ -56,6 +56,7 @@ pgstat_report_checkpointer(void)
 	CHECKPOINTER_ACC(write_time);
 	CHECKPOINTER_ACC(sync_time);
 	CHECKPOINTER_ACC(buffers_written);
+	CHECKPOINTER_ACC(slru_written);
 #undef CHECKPOINTER_ACC
 
 	pgstat_end_changecount_write(&stats_shmem->changecount);
@@ -135,5 +136,6 @@ pgstat_checkpointer_snapshot_cb(void)
 	CHECKPOINTER_COMP(write_time);
 	CHECKPOINTER_COMP(sync_time);
 	CHECKPOINTER_COMP(buffers_written);
+	CHECKPOINTER_COMP(slru_written);
 #undef CHECKPOINTER_COMP
 }
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 17b0fc02ef..f7b50e0b5a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1221,6 +1221,12 @@ pg_stat_get_checkpointer_buffers_written(PG_FUNCTION_ARGS)
 	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->buffers_written);
 }
 
+Datum
+pg_stat_get_checkpointer_slru_written(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_stat_checkpointer()->slru_written);
+}
+
 Datum
 pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h
index 36f6e4e4b4..34ad46c067 100644
--- a/src/include/access/xlog.h
+++ b/src/include/access/xlog.h
@@ -167,6 +167,7 @@ typedef struct CheckpointStatsData
 	TimestampTz ckpt_end_t;		/* end of checkpoint */
 
 	int			ckpt_bufs_written;	/* # of buffers written */
+	int			ckpt_slru_written;	/* # of SLRU buffers written */
 
 	int			ckpt_segs_added;	/* # of new xlog segments created */
 	int			ckpt_segs_removed;	/* # of xlog segments deleted */
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 3c89b70f9e..946ee29af2 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202409302
+#define CATALOG_VERSION_NO	202410011
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 05fcbf7515..77f54a79e6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5847,6 +5847,11 @@
   proname => 'pg_stat_get_checkpointer_buffers_written', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => '',
   prosrc => 'pg_stat_get_checkpointer_buffers_written' },
+{ oid => '8573',
+  descr => 'statistics: number of SLRU buffers written during checkpoints and restartpoints',
+  proname => 'pg_stat_get_checkpointer_slru_written', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_stat_get_checkpointer_slru_written' },
 { oid => '6314', descr => 'statistics: last reset for the checkpointer',
   proname => 'pg_stat_get_checkpointer_stat_reset_time', provolatile => 's',
   proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 476acd680c..df53fa2d4f 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -301,6 +301,7 @@ typedef struct PgStat_CheckpointerStats
 	PgStat_Counter write_time;	/* times in milliseconds */
 	PgStat_Counter sync_time;
 	PgStat_Counter buffers_written;
+	PgStat_Counter slru_written;
 	TimestampTz stat_reset_timestamp;
 } PgStat_CheckpointerStats;
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f5434d8365..2b47013f11 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1831,6 +1831,7 @@ pg_stat_checkpointer| SELECT pg_stat_get_checkpointer_num_timed() AS num_timed,
     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,
+    pg_stat_get_checkpointer_slru_written() AS slru_written,
     pg_stat_get_checkpointer_stat_reset_time() AS stats_reset;
 pg_stat_database| SELECT oid AS datid,
     datname,
-- 
2.45.2