Re: Streaming Replication: Checkpoint_segment and wal_keep_segments on standby

Both nodes (active and standby) have the same configuration parameters.
The observed effect happens too if the checkpoint timeout is decreaased.

The problem seems to be that on standby no checkpoints are written and
only the chekpoint_timeout mechanism is active

Regards
Ingo

-----Original Message-----
From: ext Fujii Masao [mailto:masao(dot)fujii(at)gmail(dot)com]
Sent: Thursday, May 27, 2010 4:10 PM
To: Sander, Ingo (NSN - DE/Munich)
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] 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