trace_checkpoint parameter patch

Hi all,

Here is a brand new patch to log a checkpointing load information
to tune the bgwriter parameter.

When you enable trace_checkpoint parameter and process
the checkpoint, the bgwriter logs the number of
flushed buffer pages and the elapsed time.

-----------------------------------------------------------
{61}snaga@artemis:~/pgsql-snapshot% ./bin/psql -c 'checkpoint' pgbench
LOG:  CheckPoint: 1522/3072 buffer(s) flushed. CPU 0.03s/0.00u sec elapsed 1.57 sec
CHECKPOINT
{62}snaga@artemis:~/pgsql-snapshot%
-----------------------------------------------------------

I think this information is useful to configure
the bgwriter parameter to reduce the performance impact
of the checkpointing.

Any suggestion or comments?
-- 
NAGAYASU Satoshi 
diff -rc postgresql-snapshot.orig/src/backend/access/transam/xlog.c postgresql-snapshot/src/backend/access/transam/xlog.c
*** postgresql-snapshot.orig/src/backend/access/transam/xlog.c	Fri Jun  1 00:13:01 2007
--- postgresql-snapshot/src/backend/access/transam/xlog.c	Tue Jun 12 18:37:47 2007
***************
*** 48,53 ****
--- 48,54 ----
  #include "storage/spin.h"
  #include "utils/builtins.h"
  #include "utils/pg_locale.h"
+ #include "utils/pg_rusage.h"
  
  
  
***************
*** 66,76 ****
  char	   *XLOG_sync_method = NULL;
  const char	XLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
  bool		fullPageWrites = true;
- 
  #ifdef WAL_DEBUG
  bool		XLOG_DEBUG = false;
  #endif
  
  /*
   * XLOGfileslop is used in the code as the allowed "fuzz" in the number of
   * preallocated XLOG segments --- we try to have at least XLOGfiles advance
--- 67,78 ----
  char	   *XLOG_sync_method = NULL;
  const char	XLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
  bool		fullPageWrites = true;
  #ifdef WAL_DEBUG
  bool		XLOG_DEBUG = false;
  #endif
  
+ bool		trace_checkpoint = false;
+ 
  /*
   * XLOGfileslop is used in the code as the allowed "fuzz" in the number of
   * preallocated XLOG segments --- we try to have at least XLOGfiles advance
***************
*** 5699,5708 ****
  static void
  CheckPointGuts(XLogRecPtr checkPointRedo)
  {
  	CheckPointCLOG();
  	CheckPointSUBTRANS();
  	CheckPointMultiXact();
! 	FlushBufferPool();			/* performs all required fsyncs */
  	/* We deliberately delay 2PC checkpointing as long as possible */
  	CheckPointTwoPhase(checkPointRedo);
  }
--- 5701,5722 ----
  static void
  CheckPointGuts(XLogRecPtr checkPointRedo)
  {
+ 	PGRUsage ru0;
+ 	int flushed = 0;
+ 
  	CheckPointCLOG();
  	CheckPointSUBTRANS();
  	CheckPointMultiXact();
! 
! 	if (trace_checkpoint)
! 			pg_rusage_init(&ru0);
! 
! 	flushed = FlushBufferPool();			/* performs all required fsyncs */
! 
! 	if (trace_checkpoint)
! 			elog(LOG, "CheckPoint: %d/%d buffer(s) flushed. %s",
! 				 flushed, NBuffers, pg_rusage_show(&ru0));
! 
  	/* We deliberately delay 2PC checkpointing as long as possible */
  	CheckPointTwoPhase(checkPointRedo);
  }
diff -rc postgresql-snapshot.orig/src/backend/storage/buffer/bufmgr.c postgresql-snapshot/src/backend/storage/buffer/bufmgr.c
*** postgresql-snapshot.orig/src/backend/storage/buffer/bufmgr.c	Thu May 31 05:11:58 2007
--- postgresql-snapshot/src/backend/storage/buffer/bufmgr.c	Tue Jun 12 17:51:25 2007
***************
*** 1001,1012 ****
   *
   * This is called at checkpoint time to write out all dirty shared buffers.
   */
! void
  BufferSync(void)
  {
  	int			buf_id;
  	int			num_to_scan;
  	int			absorb_counter;
  
  	/*
  	 * Find out where to start the circular scan.
--- 1001,1013 ----
   *
   * This is called at checkpoint time to write out all dirty shared buffers.
   */
! int
  BufferSync(void)
  {
  	int			buf_id;
  	int			num_to_scan;
  	int			absorb_counter;
+ 	int			flushed;
  
  	/*
  	 * Find out where to start the circular scan.
***************
*** 1019,1024 ****
--- 1020,1026 ----
  	/*
  	 * Loop over all buffers.
  	 */
+ 	flushed = 0;
  	num_to_scan = NBuffers;
  	absorb_counter = WRITES_PER_ABSORB;
  	while (num_to_scan-- > 0)
***************
*** 1038,1047 ****
--- 1040,1053 ----
  				AbsorbFsyncRequests();
  				absorb_counter = WRITES_PER_ABSORB;
  			}
+ 
+ 			flushed++;
  		}
  		if (++buf_id >= NBuffers)
  			buf_id = 0;
  	}
+ 
+ 	return flushed;
  }
  
  /*
***************
*** 1340,1350 ****
   * Local relations do not participate in checkpoints, so they don't need to be
   * flushed.
   */
! void
  FlushBufferPool(void)
  {
! 	BufferSync();
  	smgrsync();
  }
  
  
--- 1346,1358 ----
   * Local relations do not participate in checkpoints, so they don't need to be
   * flushed.
   */
! int
  FlushBufferPool(void)
  {
! 	int flushed = BufferSync();
  	smgrsync();
+ 
+ 	return flushed;
  }
  
  
diff -rc postgresql-snapshot.orig/src/backend/utils/misc/guc.c postgresql-snapshot/src/backend/utils/misc/guc.c
*** postgresql-snapshot.orig/src/backend/utils/misc/guc.c	Sat Jun  9 03:23:52 2007
--- postgresql-snapshot/src/backend/utils/misc/guc.c	Tue Jun 12 18:43:42 2007
***************
*** 109,114 ****
--- 109,117 ----
  #ifdef TRACE_SORT
  extern bool trace_sort;
  #endif
+ #ifdef TRACE_CHECKPOINT
+ extern bool trace_checkpoint;
+ #endif
  #ifdef TRACE_SYNCSCAN
  extern bool trace_syncscan;
  #endif
***************
*** 973,978 ****
--- 976,993 ----
  	},
  #endif
  
+ #ifdef TRACE_CHECKPOINT
+ 	{
+ 		{"trace_checkpoint", PGC_SIGHUP, DEVELOPER_OPTIONS,
+ 			gettext_noop("Emit information about resource usage in checkpointing."),
+ 			NULL,
+ 			GUC_NOT_IN_SAMPLE
+ 		},
+ 		&trace_checkpoint,
+ 		false, NULL, NULL
+ 	},
+ #endif
+ 
  #ifdef TRACE_SYNCSCAN
  	/* this is undocumented because not exposed in a standard build */
  	{
diff -rc postgresql-snapshot.orig/src/include/pg_config_manual.h postgresql-snapshot/src/include/pg_config_manual.h
*** postgresql-snapshot.orig/src/include/pg_config_manual.h	Sat Jun  9 03:23:53 2007
--- postgresql-snapshot/src/include/pg_config_manual.h	Tue Jun 12 18:42:56 2007
***************
*** 250,255 ****
--- 250,261 ----
  #define TRACE_SORT 1
  
  /*
+  * Enable tracing of resource consumption during checkpoint operations;
+  * see also the trace_checkpoint GUC var.  For 8.3 this is enabled by default.
+  */
+ #define TRACE_CHECKPOINT 1
+ 
+ /*
   * Enable tracing of syncscan operations (see also the trace_syncscan GUC var).
   */
  /* #define TRACE_SYNCSCAN */
diff -rc postgresql-snapshot.orig/src/include/storage/bufmgr.h postgresql-snapshot/src/include/storage/bufmgr.h
*** postgresql-snapshot.orig/src/include/storage/bufmgr.h	Thu May 31 05:12:03 2007
--- postgresql-snapshot/src/include/storage/bufmgr.h	Tue Jun 12 17:51:38 2007
***************
*** 136,142 ****
  extern void ResetBufferUsage(void);
  extern void AtEOXact_Buffers(bool isCommit);
  extern void PrintBufferLeakWarning(Buffer buffer);
! extern void FlushBufferPool(void);
  extern BlockNumber BufferGetBlockNumber(Buffer buffer);
  extern BlockNumber RelationGetNumberOfBlocks(Relation relation);
  extern void RelationTruncate(Relation rel, BlockNumber nblocks);
--- 136,142 ----
  extern void ResetBufferUsage(void);
  extern void AtEOXact_Buffers(bool isCommit);
  extern void PrintBufferLeakWarning(Buffer buffer);
! extern int FlushBufferPool(void);
  extern BlockNumber BufferGetBlockNumber(Buffer buffer);
  extern BlockNumber RelationGetNumberOfBlocks(Relation relation);
  extern void RelationTruncate(Relation rel, BlockNumber nblocks);
***************
*** 161,167 ****
  extern void AbortBufferIO(void);
  
  extern void BufmgrCommit(void);
! extern void BufferSync(void);
  extern void BgBufferSync(void);
  
  extern void AtProcExit_LocalBuffers(void);
--- 161,167 ----
  extern void AbortBufferIO(void);
  
  extern void BufmgrCommit(void);
! extern int BufferSync(void);
  extern void BgBufferSync(void);
  
  extern void AtProcExit_LocalBuffers(void);