Re: auto-sizing wal_buffers

On Thu, Jan 13, 2011 at 23:19, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Jan 6, 2011 at 11:37 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>> If it defaulted to 3% of shared_buffers, min 64K & max 16MB for the auto
>> setting, it would for the most part become an autotuned parameter.  That
>> would make it 0.75 to 1MB at the standard anemic Linux default kernel
>> parameters.  Maybe more than some would like, but dropping shared_buffers
>> from 24MB to 23MB to keep this from being ridiculously undersized is
>> probably a win.  That percentage would reach 16MB by the time shared_buffers
>> was increased to 533MB, which also seems about right to me.  On a really bad
>> setup (brief pause to flip off Apple) with only 4MB to work with total,
>> you'd end up with wal_buffers between 64 and 128K, so very close to the
>> status quo.
>>
>> Code that up, and we could probably even remove the parameter as a tunable
>> altogether.  Very few would see a downside relative to any sensible
>> configuration under the current situation, and many people would notice
>> better automagic performance with one less parameter to tweak.  Given the
>> recent investigations about the serious downsides of tiny wal_buffers values
>> on new Linux kernels when using open_datasync, a touch more aggression about
>> this setting seems particularly appropriate to consider now.  That's been
>> swapped out as the default, but it's still possible people will switch to
>> it.
>
> Would anyone like to argue vigorously for or against the above proposal?
>
> I'll start: I think this is a good idea.  I don't have a strong
> opinion on whether the exact details of Greg proposes above are
> precisely optimal, but I think they're in the right ballpark.
> Furthermore, we already have other things that are tuned in somewhat
> similar ways (e.g. the size of the fsync request queue defaults to the
> number of shared buffers) so there's precedent for it.  It's one less
> parameter that you have to set to make things just work.

+1, I like the idea. Would it still be there to override if necessary?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/