Re: shared_buffers advice

On Mar 10, 2010, at 6:22 PM, Paul McGarry 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).

Cache isn't all you have to worry about. There's also work_mem and the number of concurrent queries that you expect, and those may end up leaving you less than 25% of ram for shared_buffers - though probably not in your case. Also, I've read that 10GB is the upper end of where shared_buffers becomes useful, though I'm not entirely sure why. I think that rule of thumb has its roots in some heuristics around the double buffering effects you're asking about.

I *can* say a 10GB shared_buffer value is working "well" with my 128GB of RAM..... whether or not it's "optimal," I couldn't say without a lot of experimentation I can't afford to do right now. You might have a look at the pg_buffercache contrib module. It can tell you how utilized your shared buffers are.