From: Dave Crooke <dcrooke@gmail.com>
To: Paul McGarry <paul@paulmcgarry.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared_buffers advice
Date: Tue, 16 Mar 2010 00:17:51 -0500
Message-ID: <ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com> (raw)
In-Reply-To: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
References: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
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.
A lot of the folks here say that there isn't much performance to be gained
by giving PG's buffer cache the bulk of the RAM .... if you have the
opportunity, it'd be interesting to test it both ways with a representative
workload before going live. I for one would be very curious to see the
results.
One caveat: the PG child processes can collectively use a lot of transient
RAM, e.g. for sorts and vaccuming, depending on config and workload. If this
causes swapping, or even uses up enough memory to effectively eliminate the
OS buffer cache, it's going to hurt performance.
Cheers
Dave
On Wed, Mar 10, 2010 at 8:28 PM, Paul McGarry <paul@paulmcgarry.com> wrote:
> Hi there,
>
> I'm after a little bit of advice on the shared_buffers setting (I have
> read the various docs on/linked from the performance tuning wiki page,
> some very helpful stuff there so thanks to those people).
>
> I am setting up a 64bit Linux server running Postgresql 8.3, the
> server has 64gigs of memory and Postgres is the only major application
> running on it. (This server is to go alongside some existing 8.3
> servers, we will look at 8.4/9 migration later)
>
> I'm basically wondering how the postgresql cache (ie shared_buffers)
> and the OS page_cache interact. The general advice seems to be to
> assign 1/4 of RAM to shared buffers.
>
> I don't have a good knowledge of the internals but I'm wondering if
> this will effectively mean that roughly the same amount of RAM being
> used for the OS page cache will be used for redundantly caching
> something the Postgres is caching as well?
>
> IE when Postgres reads something from disk it will go into both the OS
> page cache and the Postgresql shared_buffers and the OS page cache
> copy is unlikely to be useful for anything.
>
> If that is the case what are the downsides to having less overlap
> between the caches, IE heavily favouring one or the other, such as
> allocating shared_buffers to a much larger percentage (such as 90-95%
> of expected 'free' memory).
>
> Paul
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dcrooke@gmail.com, paul@paulmcgarry.com
Subject: Re: shared_buffers advice
In-Reply-To: <ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox