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;