Re: Streaming Replication: Checkpoint_segment and wal_keep_segments on standby

On Thu, May 27, 2010 at 10:13 PM, Sander, Ingo (NSN - DE/Munich)
<ingo(dot)sander(at)nsn(dot)com> wrote:
>
> With the parameter checkpoint_segment and wal_keep_segments the max. number
> of wal segments are set. If now the max number is reached,
>
> (1) the segments are deleted/recycled
> or (2) if the time set by the checkpoint_timeout is over, a checkpoint is
> set and if possible a deletion/recycling is done.
>
> This is the mechanism on the active side of a db server. On the standby side
> however only unused tranferred segments will be deleted if the
> checkpoint_timeout mechanism (2) is executed.
>
> Is this a correct behaviour or it is an error?
>
> I have observed (checkpoint_segment set to 3; wal_keep_segments set to 10
> and checkpoint_timeout set to 30min) that in my stress test the disk usage
> on standby side is increased up to 2GB with xlog segments whereby on the
> active side only ~60MB xlog files are available (we have patched the xlog
> file size to 4MB). To prevent this one possibility is to decreace the
> checkpoint_timeout to a low value (30sec), however this had the disadvantage
> that a checkpoint is often executed on active side which can influence the
> performance. Another possibility is to have different postgresql.conf on
> active and on standby side, but this is not our preferred solution.

I guess this happens because the frequency of checkpoint on the standby is
too lower than that on the master. In the master, checkpoint occurs for every
consumption of three segments because of "checkpoint_segments = 3". On the
other hand, in the standby, only checkpoint_timeout has effect, so checkpoint
occurs for every 30 minutes because of "checkpoint_timeout = 30min".

The walreceiver should signal the bgwriter to start checkpoint if it has
received more than checkpoint_segments WAL files, like normal processing?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center