Re: Redesigning checkpoint_segments

On Fri, Jan 2, 2015 at 3:27 PM, Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>
wrote:

> On 01/01/2015 03:24 AM, Josh Berkus wrote:
>
>> Please remind me because I'm having trouble finding this in the
>> archives: how does wal_keep_segments interact with the new settings?
>>
>
> It's not very straightforward. First of all, min_recycle_wal_size has a
> different effect than wal_keep_segments. Raising min_recycle_wal_size
> causes more segments to be recycled rather than deleted, while
> wal_keep_segments causes old segments to be retained as old segments, so
> that they can be used for streaming replication. If you raise
> min_recycle_wal_size, it will not do any good for streaming replication.
>
> wal_keep_segments does not affect the calculation of CheckPointSegments.
> If you set wal_keep_segments high enough, checkpoint_wal_size will be
> exceeded. The other alternative would be to force a checkpoint earlier,
> i.e. lower CheckPointSegments, so that checkpoint_wal_size would be
> honored. However, if you set wal_keep_segments high enough, higher than
> checkpoint_wal_size, it's impossible to honor checkpoint_wal_size no matter
> how frequently you checkpoint.
>
>
Doesn't this indicate that we should have some co-relation
between checkpoint_wal_size and wal_keep_segments?

> It's not totally straightforward to calculate what maximum size of WAL a
> given wal_keep_segments settings will force. wal_keep_segments is taken
> into account at a checkpoint, when we recycle old WAL segments. For
> example, imagine that prior checkpoint started at segment 95, a new
> checkpoint finishes at segment 100, and wal_keep_segments=10. Because of
> wal_keep_segments, we have to retain segments 90-95, which could otherwise
> be recycled. So that forces a WAL size of 10 segments, while otherwise 5
> would be enough. However, before we reach the next checkpoint, let's assume
> it will complete at segment 105, we will consume five more segments, so the
> actual max WAL size is 15 segments. However, we could start recycling the
> segments 90-95 before we reach the next checkpoint, because
> wal_keep_segments stipulates how many segments from the current *insert*
> location needs to be retained, with not regard to checkpoints. But we only
> attempt to recycle segments at checkpoints.
>
>
I am thinking that it might make sense to have checkpoint_wal_size
equal to size of wal_keep_segments incase wal_keep_segments is
greater than checkpoint_wal_size size. It will not make any difference
in retaining wal segments, but I think it can make checkpoint trigger
at more appropriate intervals. Won't this help in addressing the above
situation explained by you to an extent as it will make a new checkpoint
to start little later such that it will help in removing segments between
90-95 one cycle earlier.

> So that could be made more straightforward if we recycled old segments in
> the background, between checkpoints. That might allow merging
> wal_keep_segments and min_recycle_wal_size settings, too: instead of
> renaming all old recycleable segments at a checkpoint, you could keep them
> around as old segments until they're actually needed for reuse, so they
> could be used for streaming replication up to that point.
>
>
Are you imagining some other background process to do this
activity? Does it make sense if we try to do the same in
foreground (I understand that it can impact performance of that
session, but such a thing can maintain the WAL size better)?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com