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(at)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(at)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 <snaga(at)snaga(dot)org>

> 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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

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