Re: shared_buffers in 8.3 w/ lots of RAM on dedicated PG machine

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD