Message-ID: <4B9F3302.4020900@2ndquadrant.com>
Date: Tue, 16 Mar 2010 03:28:02 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Dave Crooke <dcrooke@gmail.com>
CC: Paul McGarry <paul@paulmcgarry.com>,
 pgsql-performance@postgresql.org
Subject: Re: shared_buffers advice
References: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
	<ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
In-Reply-To: <ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@2ndQuadrant.com   www.2ndQuadrant.us