From: Greg Smith <greg@2ndquadrant.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: Paul McGarry <paul@paulmcgarry.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared_buffers advice
Date: Tue, 16 Mar 2010 03:28:02 -0400
Message-ID: <4B9F3302.4020900@2ndquadrant.com> (raw)
In-Reply-To: <ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
References: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
<ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
Dave Crooke wrote:
> 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.
I wouldn't call it opinion so much as a series of anecdotes all
suggesting the same thing: that you cannot translate SGA practice into
PostgreSQL and expect that to work the same way. Some data points:
-An academic study at Duke suggested 40% of RAM was optimal for their
mixed workload, but that was a fairly small amount of RAM.
http://www.cs.duke.edu/~shivnath/papers/ituned.pdf
-Tests done by Jignesh Shah at Sun not too long ago put diminishing
returns on a system with a bunch of RAM at 10GB, probably due to buffer
lock contention issues (details beyond that number not in the slides,
recalling from memory of the talk itself):
http://blogs.sun.com/jkshah/entry/postgresql_east_2008_talk_best
-My warnings about downsides related to checkpoint issues with larger
buffer pools isn't an opinion at all; that's a fact based on limitations
in how Postgres does its checkpoints. If we get something more like
Oracle's incremental checkpoint logic, this particular concern might go
away.
-Concerns about swapping, work_mem, etc. are all very real. All of us
who have had the database server process killed by the Linux OOM killer
at least once know that's one OS you absolutely cannot push this too
hard on. This is not unique to here, that issue exists in Oracle+SGA
land as well:
http://lkml.indiana.edu/hypermail/linux/kernel/0103.3/0906.html
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
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: greg@2ndquadrant.com, dcrooke@gmail.com, paul@paulmcgarry.com
Subject: Re: shared_buffers advice
In-Reply-To: <4B9F3302.4020900@2ndquadrant.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