Re: Redesigning checkpoint_segments

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

It's possible that it won't matter. Performance testing will tell us.

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

Yeah, I'd really like to get away from adding manual options which need
to be used in non-specialty cases. I think we'll need one at some point
-- there are DB applications which are VERY bursty -- but let's not
start there and see if we can make reasonable autotuning work.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com