Re: Streaming Replication: Checkpoint_segment and wal_keep_segments on standby

On Thu, May 27, 2010 at 10:09 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> 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?

Is this also an issue when using log shipping, or just with SR?

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