Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0D09B7992C9 for ; Wed, 17 Mar 2010 12:08:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 00983-04 for ; Wed, 17 Mar 2010 15:08:05 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.210.174]) by mail.postgresql.org (Postfix) with ESMTP id 1471C79922E for ; Wed, 17 Mar 2010 12:08:04 -0300 (ADT) Received: by yxe4 with SMTP id 4so132306yxe.28 for ; Wed, 17 Mar 2010 08:08:03 -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:content-type; bh=wWu59CqaPnPAZoje9sQ9oGk7CkLva066t3W5GaKJj2Y=; b=RjcfQBS7i/TUgVcJgewGufk6rUPtvgzjb4B2z97BEVn8mEdDdi3ieY3SPVPb01ig0B H9tiHz0LHDaZrVim4R944z5T6CvDaTqUsJsDL3DPz2P/b/yXW+eN6pwk1gghvrvoo5Kz dTWxbloC4fFD0XsuVs2c4t6ZFjApsKt3iEtoc= 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 :content-type; b=UoKhlFUEBd/XBYdF9BI0Eul3BYwAK9LGvqZ95veITrKmS66aGB3SS3A14pm7jiLCce lzpurCnTNClhIeWRvnDsDLxAM0YgmMftFG3WjxS+Wcfwx2vPtRR4yVMXWCFHiCELTapX +KwbFOt7ZyoEj5HLhYGkkiWp9PQMBrD8e0OPw= MIME-Version: 1.0 Received: by 10.151.5.9 with SMTP id h9mr2627346ybi.141.1268838483195; Wed, 17 Mar 2010 08:08:03 -0700 (PDT) In-Reply-To: <600ad6df1003170543m7f056ax63ce9a2ffdcd6f8@mail.gmail.com> References: <600ad6df1003170543m7f056ax63ce9a2ffdcd6f8@mail.gmail.com> Date: Wed, 17 Mar 2010 11:08:03 -0400 Message-ID: <600ad6df1003170808ne9304eeha745ca5a7946a467@mail.gmail.com> Subject: Fwd: shared_buffers advice From: VJK To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd25b7c1178720482007b90 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/200 X-Sequence-Number: 38025 --000e0cd25b7c1178720482007b90 Content-Type: text/plain; charset=ISO-8859-1 See below: On Wed, Mar 10, 2010 at 9: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). > > Pg apparently does not have an option of using direct IO with reads which some other databases do (the O_DIRECT mode). Therefore, double-buffering with read operations seems unavoidable. Counterintuitively, it may be a good idea to just rely on OS buffering and keep shared_buffers rather small, say, 512MB. VJ > Paul > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --000e0cd25b7c1178720482007b90 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

See below:


On W= ed, Mar 10, 2010 at 9: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).

Pg apparently does not have an option of using = direct IO with reads which some other databases do (the O_DIRECT mode).=A0 Therefore,=A0 double-buffering with read operations seems unavoidable.=A0 Counterintuitively,=A0=A0 it may be a good idea to just rely on OS buffering and keep shared_buffers rather small, say, 512MB.

VJ


=A0
Paul

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


--000e0cd25b7c1178720482007b90--