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@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
