Re: Streaming Replication: Checkpoint_segment and wal_keep_segments on standby

On Fri, May 28, 2010 at 11:12 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Thu, May 27, 2010 at 11:13 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> 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?
>
> When using log shipping, checkpoint_segments always doesn't trigger a
> checkpoint. So recovery after the standby crashes might take unexpectedly
> long since redo starting point might be old.
>
> But in file-based log shipping, since WAL files don't accumulate in
> pg_xlog directory on the standby, even if the frequency of checkpoint
> is very low, pg_xlog will not be filled with many WAL files. That
> accumulation occurs only when using SR.
>
> If we should avoid low frequency of checkpoint itself rather than
> accumulation of WAL files, the bgwriter instead of the walreceiver
> should check if we've consumed too much WAL, I think. Thought?

I attached the patch, which changes the startup process so that it signals
bgwriter to perform a restartpoint if we've already replayed too much WAL
files. This leads checkpoint_segments to trigger a restartpoint.

This patch is worth applying for 9.0? If not, I'll add it into the next CF.

Regards,

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