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

On Thu, Nov 3, 2011 at 12:21 AM, Greg Smith <greg@2ndquadrant.com> wrote:

> With some trivial checkpoints containing a small amount of data skipped now,
> aren't there some cases where less WAL data will be written than before?  In
> that case, the user visible behavior here would be different.  I'd be most
> concerned about file-based log shipping case.

Typical settings are checkpoint_timeout = 300 and archive_timeout =
30. So file-based replication users won't notice any difference.

The only people who will see a difference are people with
archive_timeout = 0 and who either store or stream WAL. For those
people, a keepalive message will be available to ensure we can check
the link is up, even if no WAL data flows, which I am working on next.

The change also increases durability, since the secondary checkpoint
is usually in a separate file.

> In cases where there are little or no writes to the WAL, checkpoints will be
> skipped even if checkpoint_timeout has passed.  At least one new WAL segment
> must have been created before an automatic checkpoint occurs.  The time
> between checkpoints and when new WAL segments are created are not related in
> any other way.  If file-based WAL shipping is being used and you want to
> bound how often files are sent to standby server, to reduce potential data
> loss you should adjust archive_timeout parameter rather than the checkpoint
> ones.

Committed, thanks.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services