Re: Redesigning checkpoint_segments

On 01/05/2015 12:06 PM, Andres Freund wrote:
> 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.

wal_keep_segments does not influence checkpoint pacing.

>>> 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.

I wasn't clear on my opinion here. I think I understood what Josh meant,
but I don't think we should do it. Seems like unnecessary nannying of
the DBA. Let's just mention in the manual that if you set
wal_keep_segments higher than [insert formula here], you will routinely
have more WAL in pg_xlog than what checkpoint_wal_size is set to.

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

So? I don't see how that's relevant.

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

Agreed.

- Heikki