Re: shared_buffers advice

On Fri, May 28, 2010 at 2:57 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Merlin Moncure wrote:
>>
>> I would prefer to see the annotated performance oriented .conf
>> settings to be written in terms of trade offs (too low? X too high? Y
>> setting in order to get? Z).  For example, did you know that if crank
>> max_locks_per_transaction you also increase the duration of every
>> query that hits pg_locks() -- well, now you do :-).
>>
>
> You can't do this without providing more context and tools for people to
> measure their systems.  At PGCon last week, I presented a talk specifically
> about tuning shared_buffers and the checkpoint settings.  What's come out of
> my research there is that you can stare at the data in pg_buffercache and
> pg_stat_bgwriter and classify systems based on the distribution of usage
> counts in their buffer cache on how the background writer copes with that.
>  The right style of tuning to apply is dependent on whether someone has a
> high proportion of buffers with a usage count >=2.  A tuning guide that
> actually covered that in enough detail to be an improvement over what is in
> the "Tuning Your PostgreSQL Server" would be overwhelming large, defeating
> the purpose of that document--providing a fairly bite-size guide.

Sure. IOW, a .conf guide should answer:
*) What are the symptoms of misconfigured shared_buffers (too low/too high)?
*) How do you confirm this?

Our documentation is completely unclear wrt these questions, other
than to give some vague advice in order to get 'good performance'.
I'm of the opinion (rightly or wrongly) that the prevailing opinions
on how to configure shared_buffers are based on special case
benchmarking information or simply made up. The dangers of setting it
too high are very real (especially on linux) but this isn't mentioned;
contrast that to the care put into the fsync language. This is in the
face of some prevailing myths (more shared_buffers = more cache =
faster) that have some grains of truth but aren't the whole story. I
just helped out a friend that oversubscribed and blew up his linux
box...oom killer strikes again.

I'm not complaining here mind you; I'd just like to filter out all the
anecdotal information and similar noise. shared_buffers is a bit of a
bugaboo because it is fairly subtle in how it interacts with
production workloads and there is so little solid information out
there. I would *love* to see some formal verifiable tests showing >
20% improvements resulting from shared_buffers tweaks that could be
repeated on other hardware/os installations. Got any notes for your
talk? :-)

merlin