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. > > ----------------------------------------------------------- > [EMAIL PROTECTED]:~/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 > [EMAIL PROTECTED]:~/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 <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches