From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: Volunteer to build a configuration tool
Date: Wed, 27 Jun 2007 16:19:45 -0700
User-Agent: KMail/1.8
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E53@SAB-DC1.sab.uiuc.edu>
	<Pine.GSO.4.64.0706210244340.29867@westnet.com>
	<A3AC4FA47DC0B1458C3E5396E685E63302395E74@SAB-DC1.sab.uiuc.edu>
In-Reply-To: <A3AC4FA47DC0B1458C3E5396E685E63302395E74@SAB-DC1.sab.uiuc.edu>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200706271619.45834.josh@agliodbs.com>

Lance,

> I have a PostgreSQL database that runs on a dedicated server.  The
> server has 24Gig of memory.  What would be the max size I would ever
> want to set the shared_buffers to if I where to relying on the OS for
> disk caching approach?  It seems that no matter how big your dedicated
> server is there would be a top limit to the size of shared_buffers.

There's not, actually.  Under some circumstances (mainly Solaris 10 + UFS 
on AMD) it can actually be beneficial to have s_b be 80% of RAM and bypass 
the FS cache entirely.  This isn't usually the case, but it's not to be 
ruled out.

If you're relying on the FS cache and not using direct I/O, though, you 
want to keep at least 50% of memory free for use by the cache.  At below 
50%, you lose a significant part of the benefit of the cache without 
losing the cost of it.  Of course, that assumes that your database is 
bigger than ram; there isn't much need to have either s_b or the f.s.c. be 
more than twice the size of your whole database.

In general, a setting s_b to 25% of RAM on a dedicated machine, and 10% 
(with a max of 512MB) on a shared machine, is a nice safe default which 
will do OK for most applications. 

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco