Re: trace_checkpoint parameter patch

Now that we have log_checkpoints, consider resubmitting this with just
the new information you want to add.  Thanks.

---------------------------------------------------------------------------

Satoshi Nagayasu wrote:
> 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);
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate

-- 
  Bruce Momjian          http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +