Re: trace_checkpoint parameter patch - Mailing list pgsql-patches

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 <snaga@snaga.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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