Re: [COMMITTERS] pgsql: Reduce checkpoints and WAL traffic on low activity database serv

On Wed, Nov 2, 2011 at 12:34 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Wed, Nov 2, 2011 at 4:15 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Wed, Nov 2, 2011 at 11:39 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> Reduce checkpoints and WAL traffic on low activity database server
>>> Previously, we skipped a checkpoint if no WAL had been written since
>>> last checkpoint, though this does not appear in user documentation.
>>> As of now, we skip a checkpoint until we have written at least one
>>> enough WAL to switch the next WAL file. This greatly reduces the
>>> level of activity and number of WAL messages generated by a very
>>> low activity server. This is safe because the purpose of a checkpoint
>>> is to act as a starting place for a recovery, in case of crash.
>>> This patch maintains minimal WAL volume for replay in case of crash,
>>> thus maintaining very low crash recovery time.
>>
>> I think you need to update the docs, for checkpoint_timeout if nothing else.
>
> Checkpoints have always been skipped, if no activity. So the docs
> don't need changing.

Eh, well, I don't agree. The section on WAL configuration says:

>> A checkpoint is created every checkpoint_segments log segments, or every checkpoint_timeout seconds, whichever comes first.

Now, admittedly, that doesn't mention that a checkpoint won't be
created if there's absolutely no activity, but the fact that we might
now not do it even if there has been some minimal activity seems worth
adding.

Similarly, the discussion of checkpoint_segments itself says:

Maximum time between automatic WAL checkpoints, in seconds. The
default is five minutes (5min). Increasing this parameter can increase
the amount of time needed for crash recovery. This parameter can only
be set in the postgresql.conf file or on the server command line.

Again, it's no longer the maximum time between automatic checkpoints.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company