CHECKPOINT_WARNING GUC variable addition - Mailing list pgsql-patches

The following patch adds a GUC variable CHECKPOINT_WARNING which
generates a server log message if the checkpoint caused by WAL file
filling happens more frequently than (default) 30 seconds.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.135
diff -c -c -r1.135 runtime.sgml
*** doc/src/sgml/runtime.sgml    2 Sep 2002 13:45:30 -0000    1.135
--- doc/src/sgml/runtime.sgml    7 Sep 2002 04:37:06 -0000
***************
*** 2017,2022 ****
--- 2017,2034 ----
        </listitem>
       </varlistentry>

+     <variablelist>
+      <varlistentry>
+       <term><varname>CHECKPOINT_WARNING</varname> (<type>integer</type>)</term>
+       <listitem>
+        <para>
+         Send a message to the server logs if checkpoints caused by the
+         filling of checkpoint segment files happens more frequently than
+         this number of seconds.  Zero turns off the warning.
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry>
        <term><varname>COMMIT_DELAY</varname> (<type>integer</type>)</term>
        <listitem>
Index: doc/src/sgml/wal.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/wal.sgml,v
retrieving revision 1.20
diff -c -c -r1.20 wal.sgml
*** doc/src/sgml/wal.sgml    6 Sep 2002 20:26:00 -0000    1.20
--- doc/src/sgml/wal.sgml    7 Sep 2002 04:37:08 -0000
***************
*** 314,319 ****
--- 314,329 ----
    </para>

    <para>
+    Checkpoints are fairly expensive because they force all dirty kernel
+    buffers to disk using the operating system <literal>sync()</> call.
+    Busy servers may fill checkpoint segment files too quickly,
+    causing excessive checkpointing. If such forced checkpoints happen
+    more than <varname>CHECKPOINT_WARNING</varname> seconds, a message
+    will be output to the server logs recommending increasing
+    <varname>CHECKPOINT_SEGMENTS</varname>.
+   </para>
+
+   <para>
     The <varname>COMMIT_DELAY</varname> parameter defines for how many
     microseconds the backend will sleep after writing a commit
     record to the log with <function>LogInsert</function> but before
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.288
diff -c -c -r1.288 postmaster.c
*** src/backend/postmaster/postmaster.c    4 Sep 2002 20:31:24 -0000    1.288
--- src/backend/postmaster/postmaster.c    7 Sep 2002 04:37:34 -0000
***************
*** 198,203 ****
--- 198,205 ----
  int            PreAuthDelay = 0;
  int            AuthenticationTimeout = 60;
  int            CheckPointTimeout = 300;
+ int            CheckPointWarning = 30;
+ time_t        LastSignalledCheckpoint = 0;

  bool        HostnameLookup;        /* for ps display */
  bool        ShowPortNumber;
***************
*** 2316,2321 ****
--- 2318,2339 ----

      if (CheckPostmasterSignal(PMSIGNAL_DO_CHECKPOINT))
      {
+         if (CheckPointWarning != 0)
+         {
+             /*
+              *    This only times checkpoints forced by running out of
+              *    segment files.  Other checkpoints could reduce
+              *    the frequency of forced checkpoints.
+              */
+             time_t now = time(NULL);
+
+             if (now - LastSignalledCheckpoint < CheckPointWarning)
+                 elog(LOG, "Checkpoint segments are being created too frequently (%d secs)\n
+                 Consider increasing CHECKPOINT_SEGMENTS",
+                 now - LastSignalledCheckpoint);
+             LastSignalledCheckpoint = now;
+         }
+
          /*
           * Request to schedule a checkpoint
           *
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.93
diff -c -c -r1.93 guc.c
*** src/backend/utils/misc/guc.c    4 Sep 2002 20:31:33 -0000    1.93
--- src/backend/utils/misc/guc.c    7 Sep 2002 04:37:44 -0000
***************
*** 662,667 ****
--- 662,672 ----
      },

      {
+         {"checkpoint_warning", PGC_SIGHUP}, &CheckPointWarning,
+         30, 0, INT_MAX, NULL, NULL
+     },
+
+     {
          {"wal_buffers", PGC_POSTMASTER}, &XLOGbuffers,
          8, 4, INT_MAX, NULL, NULL
      },
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.52
diff -c -c -r1.52 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    2 Sep 2002 05:42:54 -0000    1.52
--- src/backend/utils/misc/postgresql.conf.sample    7 Sep 2002 04:37:45 -0000
***************
*** 67,72 ****
--- 67,73 ----
  #
  #checkpoint_segments = 3    # in logfile segments, min 1, 16MB each
  #checkpoint_timeout = 300    # range 30-3600, in seconds
+ #checkpoint_warning = 30    # 0 is off, in seconds
  #
  #commit_delay = 0        # range 0-100000, in microseconds
  #commit_siblings = 5        # range 1-1000
Index: src/include/access/xlog.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/access/xlog.h,v
retrieving revision 1.37
diff -c -c -r1.37 xlog.h
*** src/include/access/xlog.h    4 Sep 2002 20:31:37 -0000    1.37
--- src/include/access/xlog.h    7 Sep 2002 04:37:47 -0000
***************
*** 184,189 ****
--- 184,190 ----

  /* these variables are GUC parameters related to XLOG */
  extern int    CheckPointSegments;
+ extern int    CheckPointWarning;
  extern int    XLOGbuffers;
  extern int    XLOG_DEBUG;
  extern char *XLOG_sync_method;