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(at)vmware(dot)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(dot)linnakangas(at)iki(dot)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