Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 04FCE641554
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 16 Mar 2010 10:26:48 -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 10702-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 16 Mar 2010 13:26:36 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com
 [74.125.82.46])
	by mail.postgresql.org (Postfix) with ESMTP id 3CA67633B88
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Mar 2010 10:26:37 -0300 (ADT)
Received: by wwe15 with SMTP id 15so624808wwe.19
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Mar 2010 06:26:35 -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
	:from:date:message-id:subject:to:cc:content-type;
	bh=ihgCYVdnYM+wwyIMqgEnfUTdMIHqI+NAnsXY7TDWNwY=;
	b=Kwbb8Y6hX1tN6rdFmENrgd5REUO6JjGfcJfrMlT4BkElqeetdzELO86RUjr9MKPhUy
	2p/7KfA/C4hnjb9spicD4dvJJRpS1S03hxNkk9jLBP/sU+pNepVY6zoZpxNRUAfhBXt5
	P0rzx4PgrFlbHmGNJfKGaQO1MXLpqhUvcBkEc=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	b=vJ7tUpv0P596d8CqlaGS3BJ0miqIM3dyTPQoAUZmIXy5gE+Ckygy8lUUBNj8X0bHjT
	K5Vhsh/aRyUj7HD05RZSq0cWEs4UMaduwoWfL+z3r0R+FnYuvw7d/9qPALA9ORSJ3fjy
	Wqrig4zR1wB9yK6tMUzMPo0OParI3X68uwkpI=
MIME-Version: 1.0
Received: by 10.216.85.194 with SMTP id u44mr737824wee.65.1268745995163; Tue,
	16 Mar 2010 06:26:35 -0700 (PDT)
In-Reply-To: <op.u9nrbeeteorkce@localhost>
References: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
	<ca24673e1003152217q117b0fd3q7d2d75ff20185c8b@mail.gmail.com>
	<4B9F3302.4020900@2ndquadrant.com> <op.u9nrbeeteorkce@localhost>
From: Nikolas Everett <nik9000@gmail.com>
Date: Tue, 16 Mar 2010 09:26:15 -0400
Message-ID: <d4e11e981003160626o6c76d902t16073cabb4dadabc@mail.gmail.com>
Subject: Re: shared_buffers advice
To: Pierre C <lists@peufeu.com>
Cc: Greg Smith <greg@2ndquadrant.com>, Dave Crooke <dcrooke@gmail.com>,
	Paul McGarry <paul@paulmcgarry.com>, pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=0016e6d7e7445a188e0481eaf236
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/165
X-Sequence-Number: 37990

--0016e6d7e7445a188e0481eaf236
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Mar 16, 2010 at 7:24 AM, Pierre C <lists@peufeu.com> wrote:
>
> I wonder about something, too : if your DB size is smaller than RAM, you
> could in theory set shared_buffers to a size larger than your DB provided
> you still have enough free RAM left for work_mem and OS writes management.
> How does this interact with the logic which prevents seq-scans hogging
> shared_buffers ?


I think the logic you are referring to is the clock sweep buffer accounting
scheme.  That just makes sure that the most popular pages stay in the
buffers.  If your entire db fits in the buffer pool then it'll all get in
there real fast.

Two things to consider though:
1.  The checkpoint issue still stands.
2.  You should really mess around with your cost estimates if this is the
case.  If you make random IO cost the same as sequential IO postgres will
prefer index scans over bitmap index scans and table scans which makes sense
if everything is in memory.

--0016e6d7e7445a188e0481eaf236
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">On Tue, Mar 16, 2010 at 7:24 AM, Pierre =
C <span dir=3D"ltr">&lt;<a href=3D"mailto:lists@peufeu.com">lists@peufeu.co=
m</a>&gt;</span> wrote:<blockquote class=3D"gmail_quote" style=3D"margin:0 =
0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">


I wonder about something, too : if your DB size is smaller than RAM, you co=
uld in theory set shared_buffers to a size larger than your DB provided you=
 still have enough free RAM left for work_mem and OS writes management. How=
 does this interact with the logic which prevents seq-scans hogging shared_=
buffers ?</blockquote>

<div><br></div><div>I think the logic you are referring to is the clock swe=
ep buffer accounting scheme. =A0That just makes sure that the most popular =
pages stay in the buffers. =A0If your entire db fits in the buffer pool the=
n it&#39;ll all get in there real fast.</div>

<div><br></div><div>Two things to consider though:</div><div>1. =A0The chec=
kpoint issue still stands.</div><div>2. =A0You should really mess around wi=
th your cost estimates if this is the case. =A0If you make random IO cost t=
he same as sequential IO postgres will prefer index scans over bitmap index=
 scans and table scans which makes sense if everything is in memory.</div>

</div>

--0016e6d7e7445a188e0481eaf236--