Re: Redesigning checkpoint_segments

On 2015-01-05 11:34:54 +0200, Heikki Linnakangas wrote:
> On 01/04/2015 11:44 PM, Josh Berkus wrote:
> >On 01/03/2015 12:56 AM, Heikki Linnakangas wrote:
> >>On 01/03/2015 12:28 AM, Josh Berkus wrote:
> >>>On 01/02/2015 01:57 AM, Heikki Linnakangas wrote:
> >>>>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.
> >>>
> >>>So you're saying that wal_keep_segments is part of the max_wal_size
> >>>total, NOT in addition to it?
> >>
> >>Not sure what you mean. wal_keep_segments is an extra control that can
> >>prevent WAL segments from being recycled. It has the same effect as
> >>archive_command failing for N most recent segments, if that helps.
> >
> >I mean, if I have these settings:
> >
> >max_wal_size* = 256MB
> >wal_keep_segments = 8
> >
> >... then my max wal size is *still* 256MB, NOT 384MB?
>
> Right.

With that you mean that wal_keep_segments has *no* influence over
checkpoint pacing or the contrary? Because upthread you imply that it
doesn't, but later comments may mean the contrary.

I think that influencing the pacing would be pretty insane - the user
certainly doesn't expect drastic performance changes when changing
wal_keep_segments. It's confusing enough that it can cause slight
peformance variations due to recycling, but we shouldn't make it have a
larger influence.

> >If that's the case (and I think it's a good plan), then as a follow-on,
> >we should prevent users from setting wal_keep_segments to more than 50%
> >of max_wal_size, no?
>
> Not sure if the 50% figure is correct, but I see what you mean: don't allow
> setting wal_keep_segments so high that we would exceed max_wal_size because
> of it.

That seems a unrealistic goal. I've seen setups that have set
checkpoint_segments intentionally, and with good reasoning, north of
50k.

Neither wal_keep_segments, nor failing archive_commands nor replication
slot should have an influence on checkpoint pacing.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services