Date: Fri, 15 Feb 2008 09:29:05 -0500 (EST)
From: Greg Smith <gsmith@gregsmith.com>
To: Peter Schuller <peter.schuller@infidyne.com>
cc: pgsql-performance@postgresql.org
Subject: Re: shared_buffers in 8.3 w/ lots of RAM on dedicated PG
 machine
In-Reply-To: <20080215135846.GA17327@hyperion.scode.org>
Message-ID: <Pine.GSO.4.64.0802150916380.28161@westnet.com>
References: <20080215123528.GA16532@hyperion.scode.org>
	<20080215133734.GU12156@it.is.rice.edu>
	<20080215135846.GA17327@hyperion.scode.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Fri, 15 Feb 2008, Peter Schuller wrote:

> Or is it a matter of PostgreSQL doing non-direct I/O, such that
> anything cached in shared_buffers will also be cached by the OS?

PostgreSQL only uses direct I/O for writing to the WAL; everything else 
goes through the regular OS buffer cache unless you force it to do 
otherwise at the OS level (like some Solaris setups do with 
forcedirectio).  This is one reason it still make not make sense to give 
an extremely high percentage of RAM to PostgreSQL even with improvements 
in managing it.  Another is that shared_buffers memory has to be 
reconciled with disk at every checkpoint, where OS buffers do not.  A 
third is that your OS may just be more efficient at buffering--it knows 
more about the underlying hardware, and the clock-sweep method used 
internally by PostgreSQL to simulate a LRU cache is not extremely 
sophisticated.

However, don't feel limited by the general 25% rule; it's certainly worth 
exploring whether 50% or more works better for your workload.  You'll have 
to benchmark that yourself though, and I'd suggest using pg_buffercache: 
http://www.postgresql.org/docs/8.3/static/pgbuffercache.html to get an 
idea just what the pages are being used for.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD