Re: Redesigning checkpoint_segments

On 06.06.2013 20:24, Josh Berkus wrote:
>> Yeah, something like that :-). I was thinking of letting the estimate
>> decrease like a moving average, but react to any increases immediately.
>> Same thing we do in bgwriter to track buffer allocations:
>
> Seems reasonable.

Here's a patch implementing that. Docs not updated yet. I did not change
the way checkpoint_segments triggers checkpoints - that'll can be a
separate patch. This only decouples the segment preallocation behavior
from checkpoint_segments. With the patch, you can set
checkpoint_segments really high, without consuming that much disk space
all the time.

> Given the behavior of xlog, I'd want to adjust the
> algo so that peak usage on a 24-hour basis would affect current
> preallocation.  That is, if a site regularly has a peak from 2-3pm where
> they're using 180 segments/cycle, then they should still be somewhat
> higher at 2am than a database which doesn't have that peak.  I'm pretty
> sure that the bgwriter's moving average cycles much shorter time scales
> than that.

Makes sense. I didn't implement that in the attached, though.

Having a separate option to specify a minimum number of segments (or
rather minimum size in MB) to keep preallocated would at least allow a
DBA to set that manually, based on the observed peak. I didn't implement
such a manual option in the attached, but that would be easy.

- Heikki