Re: Redesigning checkpoint_segments

On Tue, Feb 3, 2015 at 10:44 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
>>> 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.

Hmm, that's different from what I was thinking. We probably shouldn't
call that max_checkpoint_segments, then. I got confused and thought
you were just trying to decouple the number of segments that it takes
to trigger a checkpoint from the number we keep preallocated.

But I'm confused: how can we know how much new WAL will be written
before the checkpoint completes?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company