Re: Redesigning checkpoint_segments

On 02/03/2015 05:19 PM, Robert Haas wrote:
> On Tue, Feb 3, 2015 at 7:31 AM, Heikki Linnakangas
> <hlinnakangas@vmware.com> wrote:
>> On 02/02/2015 03:36 PM, Robert Haas wrote:
>>> Second, I*think*  that these settings are symmetric and, if that's
>>> right, then I suggest that they ought to be named symmetrically.
>>> Basically, I think you've got min_checkpoint_segments (the number of
>>> recycled segments we keep around always) and max_checkpoint_segments
>>> (the maximum number of segments we can have between checkpoints),
>>> essentially splitting the current role of checkpoint_segments in half.
>>> I'd go so far as to suggest we use exactly that naming.  It would be
>>> reasonable to allow the value to be specified in MB rather than in
>>> 16MB units, and to specify it that way by default, but maybe a
>>> unit-less value should have the old interpretation since everybody's
>>> used to it.  That would require adding GUC_UNIT_XSEG or similar, but
>>> that seems OK.
>>
>> Works for me. However, note that "max_checkpoint_segments = 10" doesn't mean
>> the same as current "checkpoint_segments = 10". With checkpoint_segments =
>> 10 you end up with about 2x-3x as much WAL as with max_checkpoint_segments =
>> 10. So the "everybody's used to it" argument doesn't hold much water.
>
> Hmm, that's surprising.  Why does that happen?

That's the whole point of this patch. "max_checkpoint_segments = 10", or 
"max_checkpoint_segments = 160 MB", means that the system will begin a 
checkpoint so that when the checkpoint completes, and it truncates away 
or recycles old WAL, the total size of pg_xlog is 160 MB.

That's different from our current checkpoint_segments setting. With 
checkpoint_segments, the documented formula for calculating the disk 
usage is (2 + checkpoint_completion_target) * checkpoint_segments * 16 
MB. That's a lot less intuitive to set.

- Heikki