Re: shared_buffers advice

On Fri, May 28, 2010 at 5:02 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Merlin Moncure wrote:
>>
>> 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.
>
> Well, you're wrong, but it's OK; we'll forgive you this time.  It's true
> that a lot of the earlier advice here wasn't very well tested across
> multiple systems.  I have a stack of data that supports the anecdotal
> guidelines are in the right ballpark now though, most of which is protected
> by NDA.  If you look at the spreadsheet at
> http://www.pgcon.org/2010/schedule/events/218.en.html you'll see three
> examples I was able to liberate for public consumption, due to some
> contributions by list regulars here (I'm working on a fourth right now).
>  The first one has shared_buffers set at 8GB on a 96GB server, at the upper
> limit of where it's useful, and the database is using every bit of that more
> effectively than had it been given to the OS to manage.  (I'm starting to
> get access to test hardware to investigate why there's an upper limit wall
> around 10GB for shared_buffers too)  The other two are smaller systems, and
> they don't benefit nearly as much from giving the database memory given
> their workload.  Basically it comes down to two things:
>
> 1) Are you getting a lot of buffers where the usage count is >=3?  If not,
> you can probably reduce shared_buffers and see better performance.  This is
> not the case with the first system shown, but is true on the second and
> third.
>
> 2) Is the average size of the checkpoints too large?  If so, you might have
> to reduce shared_buffers in order to pull that down.  Might even need to
> pull down the checkpoint parameters too.
>
> Workloads that don't like the database to have RAM certainly exist, but
> there are just as many that appreciate every bit of memory you dedicated to
> it.
>
> With all the tuning work I've been doing the last few months, the only thing
> I've realized the standard guidelines (as embodied by pgtune and the wiki
> pages) are wrong is in regards to work_mem.  You have to be much more
> careful with that than what pgtune in particular suggests.  The rest of the
> rules of thumb pgtune is based on and "Tuning your PostgreSQL Server"
> suggests are not bad.
>
> Accomplishing a major advance over the current state of things really needs
> some captures of real application load from a production system of both
> major types that we can playback, to give something more realistic than one
> of the boring benchmark loads.  Dimitri Fontaine is working on some neat
> tools in that area, and now that we're working together more closely I'm
> hoping we can push that work forward further.  That's the real limiting
> factor here now, assembling repeatable load testing that looks like an
> application rather than a benchmark.

This is great information -- exactly the kind of research I'm talking
about. btw I like being proved wrong! :-) I need some time to
process this.

merlin