Message-ID: <4CA944EF.5040702@2ndquadrant.com>
Date: Sun, 03 Oct 2010 23:07:27 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Samuel Gendler <sgendler@ideasculptor.com>
CC: =?ISO-8859-1?Q?Fabr=EDcio_dos_Anjos_Silva?=
	<fabricio.silva@linkcom.com.br>, pgsql-performance@postgresql.org
Subject: Re: How does PG know if data is in memory?
References: <AANLkTin5G0jXUbeNdC+1s9LQpk1ZpvDuX-6n90oonen3@mail.gmail.com>
	<AANLkTik5hnOcU=spN0wmWmR6_wHN=sKfVNzJeDuPTVW_@mail.gmail.com>
In-Reply-To: <AANLkTik5hnOcU=spN0wmWmR6_wHN=sKfVNzJeDuPTVW_@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Samuel Gendler wrote:
> As to your question about increasing shared_buffers to be some 
> significant proportion of available RAM - apparently, that is not a 
> good idea.  I've seen advice that said you shouldn't go above 8GB for 
> shared_buffers and I've also seen 12GB suggested as an upper limit, 
> too.  On my host with 48GB of RAM, I didn't see much difference 
> between 8GB and 12GB on a fairly wide variety of tests, so mine is set 
> at 8GB with an efective_cache_size of 36GB.

The publicly discussed tests done at Sun suggested 10GB was the 
effective upper limit on Solaris before performance started dropping 
instead of increasing on some of their internal benchmarks.  And I've 
heard privately from two people who have done similar experiments on 
Linux and found closer to 8GB to be the point where performance started 
to drop.  I'm hoping to get some hardware capable of providing some more 
public results in this area, and some improvements if we can get better 
data about what causes this drop in efficiency.

Given that some write-heavy workloads start to suffer considerable 
checkpoint issues when shared_buffers is set to a really high value, 
there's at least two reasons to be conservative here.  The big win is 
going from the tiny default to hundreds of megabytes.  Performance keeps 
going up for many people into the low gigabytes range, but the odds of 
hitting a downside increase too.  Since PostgreSQL uses the OS cache, 
too, I see some sytems with a whole lot of RAM where the 512MB - 1GB 
range still ends up being optimal, just in terms of balancing the 
improvements you get from things being in the cache vs. the downsides of 
heavy checkpoint writes.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book