v19-0009-Add-stats-to-pg_stat_bgwriter.patch

From 88cf6d9237f98b55d75947700d541afaeb7ee4ca Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Mon, 20 Dec 2021 16:48:06 -0500
Subject: [PATCH v19 9/9] Add stats to pg_stat_bgwriter

Rename pg_stat_bgwriter stat "maxwritten_clean" to "rounds_hit_limit".
This stat tracks the number of rounds in which the bgwriter hit the
limit imposed by bgwriter_lru_maxpages for the number of pages which are
allowed to be cleaned in a round.

Also, add two new additional stats to this view. The first,
"rounds_cleaned_estimate", is the number of rounds in which the bgwriter
was able to clean the number of buffers which it has estimated will be
allocated in the next cycle.

The second, "rounds_lapped_clock", is the number of rounds in which the
bgwriter lapped the freelist clock sweep scan.

Both of these are conditions to exit the LRU cleaning scan.
---
 doc/src/sgml/monitoring.sgml         | 22 +++++++++++++++++++++-
 src/backend/catalog/system_views.sql |  4 +++-
 src/backend/postmaster/pgstat.c      |  5 ++++-
 src/backend/storage/buffer/bufmgr.c  | 21 +++++++++++++++------
 src/backend/utils/adt/pgstatfuncs.c  | 16 ++++++++++++++--
 src/include/catalog/pg_proc.dat      | 15 +++++++++++++--
 src/include/pgstat.h                 |  8 ++++++--
 src/test/regress/expected/rules.out  |  4 +++-
 8 files changed, 79 insertions(+), 16 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 84c37151b8..e029a596c9 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3523,7 +3523,17 @@ 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>maxwritten_clean</structfield> <type>bigint</type>
+       <structfield>rounds_cleaned_estimate</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of rounds in which the bgwriter cleaned the number of buffers it
+       estimated would be allocated in the next cycle. 
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>rounds_hit_limit</structfield> <type>bigint</type>
       </para>
       <para>
        Number of times the background writer stopped a cleaning
@@ -3531,6 +3541,16 @@ 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>rounds_lapped_clock</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of rounds in which the bgwriter lapped the clock sweep scan while
+       cleaning buffers.
+      </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/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 0caf50421c..8ac1988d21 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1064,7 +1064,9 @@ CREATE VIEW pg_stat_archiver AS
 
 CREATE VIEW pg_stat_bgwriter AS
     SELECT
-        pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
+        pg_stat_get_bgwriter_rounds_cleaned_estimate() AS rounds_cleaned_estimate,
+        pg_stat_get_bgwriter_rounds_hit_limit() AS rounds_hit_limit,
+        pg_stat_get_bgwriter_rounds_lapped_clock() AS rounds_lapped_clock,
         pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
 
 CREATE VIEW pg_stat_buffers AS
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index a080ec78b4..a441216d25 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -5951,7 +5951,10 @@ pgstat_recv_archiver(PgStat_MsgArchiver *msg, int len)
 static void
 pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
 {
-	globalStats.bgwriter.maxwritten_clean += msg->m_maxwritten_clean;
+	globalStats.bgwriter.rounds_cleaned_estimate +=
+		msg->m_rounds_cleaned_estimate;
+	globalStats.bgwriter.rounds_hit_limit += msg->m_rounds_hit_limit;
+	globalStats.bgwriter.rounds_lapped_clock += msg->m_rounds_lapped_clock;
 }
 
 /* ----------
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 6ec15ea00e..5830b971d6 100644
--- a/src/backend/storage/buffer/bufmgr.c
+++ b/src/backend/storage/buffer/bufmgr.c
@@ -2454,20 +2454,29 @@ BgBufferSync(WritebackContext *wb_context)
 			next_to_clean = 0;
 			next_passes++;
 		}
-		num_to_scan--;
 
-		if (sync_state & BUF_WRITTEN)
+		if (--num_to_scan == 0)
+			PendingBgWriterStats.m_rounds_lapped_clock++;
+
+		if (sync_state & BUF_WRITTEN || sync_state & BUF_REUSABLE)
 		{
 			reusable_buffers++;
-			if (++num_written >= bgwriter_lru_maxpages)
+			if (reusable_buffers >= upcoming_alloc_est)
+				PendingBgWriterStats.m_rounds_cleaned_estimate++;
+		}
+
+		if (sync_state & BUF_WRITTEN)
+		{
+			num_written++;
+			if (num_written >= bgwriter_lru_maxpages)
 			{
-				PendingBgWriterStats.m_maxwritten_clean++;
+				PendingBgWriterStats.m_rounds_hit_limit++;
 				break;
 			}
 		}
-		else if (sync_state & BUF_REUSABLE)
-			reusable_buffers++;
+
 	}
+	
 
 #ifdef BGW_DEBUG
 	elog(DEBUG1, "bgwriter: recent_alloc=%u smoothed=%.2f delta=%ld ahead=%d density=%.2f reusable_est=%d upcoming_est=%d scanned=%d wrote=%d reusable=%d",
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 6ee9705ea3..eafcaf4abf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1727,9 +1727,21 @@ pg_stat_get_db_sessions_killed(PG_FUNCTION_ARGS)
 }
 
 Datum
-pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
+pg_stat_get_bgwriter_rounds_cleaned_estimate(PG_FUNCTION_ARGS)
 {
-	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->maxwritten_clean);
+	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->rounds_cleaned_estimate);
+}
+
+Datum
+pg_stat_get_bgwriter_rounds_hit_limit(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->rounds_hit_limit);
+}
+
+Datum
+pg_stat_get_bgwriter_rounds_lapped_clock(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(pgstat_fetch_stat_bgwriter()->rounds_lapped_clock);
 }
 
 Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d4911ada45..64efbeaa7e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5585,11 +5585,22 @@
   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 => '8461',
+  descr => 'statistics: number of times the bgwriter cleaned the number of buffers in a round estimated to be allocated in the next cycle.',
+  proname => 'pg_stat_get_bgwriter_rounds_cleaned_estimate', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_stat_get_bgwriter_rounds_cleaned_estimate' },
 { oid => '2773',
   descr => 'statistics: number of times the bgwriter stopped processing when it had written too many buffers while cleaning',
-  proname => 'pg_stat_get_bgwriter_maxwritten_clean', provolatile => 's',
+  proname => 'pg_stat_get_bgwriter_rounds_hit_limit', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => '',
+  prosrc => 'pg_stat_get_bgwriter_rounds_hit_limit' },
+{ oid => '8462',
+  descr => 'statistics: number of times the bgwriter lapped the clock sweep scan while cleaning buffers during a round',
+  proname => 'pg_stat_get_bgwriter_rounds_lapped_clock', provolatile => 's',
   proparallel => 'r', prorettype => 'int8', proargtypes => '',
-  prosrc => 'pg_stat_get_bgwriter_maxwritten_clean' },
+  prosrc => 'pg_stat_get_bgwriter_rounds_lapped_clock' },
+
 { oid => '3075', descr => 'statistics: last reset for the bgwriter',
   proname => 'pg_stat_get_bgwriter_stat_reset_time', provolatile => 's',
   proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 36d981c9ea..89bf8c6b81 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -524,7 +524,9 @@ typedef struct PgStat_MsgBgWriter
 {
 	PgStat_MsgHdr m_hdr;
 
-	PgStat_Counter m_maxwritten_clean;
+	PgStat_Counter m_rounds_cleaned_estimate;
+	PgStat_Counter m_rounds_hit_limit;
+	PgStat_Counter m_rounds_lapped_clock;
 } PgStat_MsgBgWriter;
 
 /* ----------
@@ -967,7 +969,9 @@ typedef struct PgStat_ArchiverStats
  */
 typedef struct PgStat_BgWriterStats
 {
-	PgStat_Counter maxwritten_clean;
+	PgStat_Counter rounds_cleaned_estimate;
+	PgStat_Counter rounds_hit_limit;
+	PgStat_Counter rounds_lapped_clock;
 	TimestampTz stat_reset_timestamp;
 } PgStat_BgWriterStats;
 
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 97af066c6b..5373fed143 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1817,7 +1817,9 @@ pg_stat_archiver| SELECT s.archived_count,
     s.last_failed_time,
     s.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_maxwritten_clean() AS maxwritten_clean,
+pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_rounds_cleaned_estimate() AS rounds_cleaned_estimate,
+    pg_stat_get_bgwriter_rounds_hit_limit() AS rounds_hit_limit,
+    pg_stat_get_bgwriter_rounds_lapped_clock() AS rounds_lapped_clock,
     pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
 pg_stat_buffers| SELECT b.backend_type,
     b.io_path,
-- 
2.30.2