Just-in-time background writing strategy. This code avoids re-scanning

buffers that cannot possibly need to be cleaned, and estimates how many
buffers it should try to clean based on moving averages of recent allocation
requests and density of reusable buffers.  The patch also adds a couple
more columns to pg_stat_bgwriter to help measure the effectiveness of the
bgwriter.

Greg Smith, building on his own work and ideas from several other people,
in particular a much older patch from Itagaki Takahiro.

5 files changed, 17 insertions, 8 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 4c1b84bd62b..d00770acc21 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.427 2007/09/21 21:25:42 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.428 2007/09/25 20:03:38 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200709211
+#define CATALOG_VERSION_NO	200709241
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6dba4dbc39d..9578ff1e735 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.472 2007/09/24 01:29:29 adunstan Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.473 2007/09/25 20:03:38 tgl Exp $
  *
  * NOTES
  *	  The script catalog/genbki.sh reads this file and generates .bki
@@ -2942,6 +2942,11 @@ DATA(insert OID = 2772 ( pg_stat_get_bgwriter_buf_written_clean PGNSP PGUID 12 1
 DESCR("statistics: number of buffers written by the bgwriter for cleaning dirty buffers");
 DATA(insert OID = 2773 ( pg_stat_get_bgwriter_maxwritten_clean PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_bgwriter_maxwritten_clean - _null_ _null_ ));
 DESCR("statistics: number of times the bgwriter stopped processing when it had written too many buffers while cleaning");
+DATA(insert OID = 2775 ( pg_stat_get_buf_written_backend PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_buf_written_backend - _null_ _null_ ));
+DESCR("statistics: number of buffers written by backends");
+DATA(insert OID = 2859 ( pg_stat_get_buf_alloc			PGNSP PGUID 12 1 0 f f t f s 0 20 "" _null_ _null_ _null_ pg_stat_get_buf_alloc - _null_ _null_ ));
+DESCR("statistics: number of buffer allocations");
+
 DATA(insert OID = 2230 (  pg_stat_clear_snapshot		PGNSP PGUID 12 1 0 f f f f v 0 2278  "" _null_ _null_ _null_	pg_stat_clear_snapshot - _null_ _null_ ));
 DESCR("statistics: discard current transaction's statistics snapshot");
 DATA(insert OID = 2274 (  pg_stat_reset					PGNSP PGUID 12 1 0 f f f f v 0 2278  "" _null_ _null_ _null_	pg_stat_reset - _null_ _null_ ));
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 2f53fdcd7c8..2d3c698953a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -5,7 +5,7 @@
  *
  *	Copyright (c) 2001-2007, PostgreSQL Global Development Group
  *
- *	$PostgreSQL: pgsql/src/include/pgstat.h,v 1.67 2007/09/24 03:12:23 tgl Exp $
+ *	$PostgreSQL: pgsql/src/include/pgstat.h,v 1.68 2007/09/25 20:03:38 tgl Exp $
  * ----------
  */
 #ifndef PGSTAT_H
@@ -294,6 +294,8 @@ typedef struct PgStat_MsgBgWriter
 	PgStat_Counter	m_buf_written_checkpoints;
 	PgStat_Counter	m_buf_written_clean;
 	PgStat_Counter	m_maxwritten_clean;
+	PgStat_Counter  m_buf_written_backend;
+	PgStat_Counter  m_buf_alloc;
 } PgStat_MsgBgWriter;
 
 
@@ -394,6 +396,8 @@ typedef struct PgStat_GlobalStats
 	PgStat_Counter  buf_written_checkpoints;
 	PgStat_Counter  buf_written_clean;
 	PgStat_Counter  maxwritten_clean;
+	PgStat_Counter  buf_written_backend;
+	PgStat_Counter  buf_alloc;
 } PgStat_GlobalStats;
 
 
diff --git a/src/include/storage/buf_internals.h b/src/include/storage/buf_internals.h
index 4afd677b9a6..34ad286ec24 100644
--- a/src/include/storage/buf_internals.h
+++ b/src/include/storage/buf_internals.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/storage/buf_internals.h,v 1.92 2007/07/25 12:22:53 mha Exp $
+ * $PostgreSQL: pgsql/src/include/storage/buf_internals.h,v 1.93 2007/09/25 20:03:38 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -188,7 +188,7 @@ extern void StrategyFreeBuffer(volatile BufferDesc *buf);
 extern bool StrategyRejectBuffer(BufferAccessStrategy strategy,
 								 volatile BufferDesc *buf);
 
-extern int	StrategySyncStart(void);
+extern int	StrategySyncStart(uint32 *complete_passes, uint32 *num_buf_alloc);
 extern Size StrategyShmemSize(void);
 extern void StrategyInitialize(bool init);
 
diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h
index 1324befa1e2..d40f39ccab4 100644
--- a/src/include/storage/bufmgr.h
+++ b/src/include/storage/bufmgr.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/storage/bufmgr.h,v 1.107 2007/09/20 17:56:32 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/storage/bufmgr.h,v 1.108 2007/09/25 20:03:38 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -32,8 +32,8 @@ extern PGDLLIMPORT int NBuffers;
 
 /* in bufmgr.c */
 extern bool zero_damaged_pages;
-extern double bgwriter_lru_percent;
 extern int	bgwriter_lru_maxpages;
+extern double bgwriter_lru_multiplier;
 
 /* in buf_init.c */
 extern PGDLLIMPORT char *BufferBlocks;