Re: Redesigning checkpoint_segments

On 05.06.2013 22:24, Fujii Masao wrote:
> On Thu, Jun 6, 2013 at 3:35 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com>  wrote:
>> The checkpoint spreading code already tracks if the checkpoint is "on
>> schedule", and it takes into account both checkpoint_timeout and
>> checkpoint_segments. Ie. if you consume segments faster than expected, the
>> checkpoint will speed up as well. Once checkpoint_segments is reached, the
>> checkpoint will complete ASAP, with no delays to spread it out.
>
> Yep, right. One problem is that this mechanism doesn't work in the standby.

Sure it does:

> commit 71815306e9e1ba7e95752779d2ad51d0c2b9c747
> Author: Heikki Linnakangas <heikki.linnakangas@iki.fi>
> Date:   Wed Jun 9 15:04:07 2010 +0000
>
>     In standby mode, respect checkpoint_segments in addition to
>     checkpoint_timeout to trigger restartpoints. We used to deliberately only
>     do time-based restartpoints, because if checkpoint_segments is small we
>     would spend time doing restartpoints more often than really necessary.
>     But now that restartpoints are done in bgwriter, they're not as
>     disruptive as they used to be. Secondly, because streaming replication
>     stores the streamed WAL files in pg_xlog, we want to clean it up more
>     often to avoid running out of disk space when checkpoint_timeout is large
>     and checkpoint_segments small.
>
>     Patch by Fujii Masao, with some minor changes by me.

One problam with that is that if you set checkpoint_segments (or 
max_wal_size, under the proposal) lower in the standby than in the 
master, we can't do restartpoints any more frequently than checkpoints 
have happened in the master. I wasn't planning to do anything about that.

- Heikki