Re: shared_buffers advice

Dave Crooke wrote:
> There seems to be a wide range of opinion on this .... I am new to PG
> and grew up on Oracle, where more SGA is always a good thing ... I
> know people who run Oracle on 2TB Superdome's with titanic SGA sizes
> to keep the whole DB in RAM. I'd be using a 40GB+ Oracle SGA on that
> box of yours.

I wouldn't call it opinion so much as a series of anecdotes all
suggesting the same thing: that you cannot translate SGA practice into
PostgreSQL and expect that to work the same way. Some data points:

-An academic study at Duke suggested 40% of RAM was optimal for their
mixed workload, but that was a fairly small amount of RAM.
http://www.cs.duke.edu/~shivnath/papers/ituned.pdf

-Tests done by Jignesh Shah at Sun not too long ago put diminishing
returns on a system with a bunch of RAM at 10GB, probably due to buffer
lock contention issues (details beyond that number not in the slides,
recalling from memory of the talk itself):
http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_best

-My warnings about downsides related to checkpoint issues with larger
buffer pools isn't an opinion at all; that's a fact based on limitations
in how Postgres does its checkpoints. If we get something more like
Oracle's incremental checkpoint logic, this particular concern might go
away.

-Concerns about swapping, work_mem, etc. are all very real. All of us
who have had the database server process killed by the Linux OOM killer
at least once know that's one OS you absolutely cannot push this too
hard on. This is not unique to here, that issue exists in Oracle+SGA
land as well:
http://lkml.indiana.edu/hypermail/linux/kernel/0103.3/0906.html

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