Re: shared_buffers advice

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.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us