Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 052EA65ABB7 for ; Tue, 16 Mar 2010 02:18:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20733-08 for ; Tue, 16 Mar 2010 05:17:54 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 2824965AB0F for ; Tue, 16 Mar 2010 02:17:53 -0300 (ADT) Received: by bwz8 with SMTP id 8so3602607bwz.3 for ; Mon, 15 Mar 2010 22:17:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=Kv2ySkHukPq/mYHOI7g1b75xjRo+NkoXt+xiGwX1XEo=; b=HDAQZBqUiG00C6YCEp6ahCdarA0GNglWgKvr7hthT7qrcsw8OGq+UVp66pCsn9Vpjj hochQjExH0wvKhV8dSerrs8UiBHipLjJAIFi83hY08kio0NXQ9Ugb3quC0nz9EwK2Hw1 iJNybamHtDvs1HASJ48aFaRDoiLNJXe/I235U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=LV58oBIUbiv7Swzn8PwQ/2Mk7a8cWGXCiXrUm+eFl3ayrZV50HkvSkK0YNWwI8CXtz UT8z0ov/ptHZENs7nouTYbXPWO85Lux6OkL5aGJxpGLDaKOCflrW72bVdWxGJufS0v9s N6Qm20636NOMmG589XFDEaCtGC7W9cjH7HxOo= MIME-Version: 1.0 Received: by 10.204.32.196 with SMTP id e4mr5624962bkd.131.1268716672532; Mon, 15 Mar 2010 22:17:52 -0700 (PDT) In-Reply-To: References: Date: Tue, 16 Mar 2010 00:17:51 -0500 Message-ID: Subject: Re: shared_buffers advice From: Dave Crooke To: Paul McGarry Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000325554e4a9652850481e41e7b X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/161 X-Sequence-Number: 37986 --000325554e4a9652850481e41e7b Content-Type: text/plain; charset=ISO-8859-1 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 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 > --000325554e4a9652850481e41e7b Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable There seems to be a wide range of opinion on this .... I am new to PG and g= rew up on Oracle, where more SGA is always a good thing ... I know people w= ho run Oracle on 2TB Superdome's with titanic SGA sizes to keep the who= le 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 hav= e the opportunity, it'd be interesting to test it both ways with a repr= esentative workload before going live. I for one would be very curious to s= ee the results.

One caveat: the PG child processes can collectively use a lot of transi= ent RAM, e.g. for sorts and vaccuming, depending on config and workload. If= this causes swapping, or even uses up enough memory to effectively elimina= te the OS buffer cache, it's going to hurt performance.

Cheers
Dave

On Wed, Mar 10, 2010 a= t 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<= br> 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

--000325554e4a9652850481e41e7b--