DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=SZSmVI5o263q8edTtv3G6ErdKcEqFXJEFZEvI/+hISro19cuuk+t+Dpu1Jb16HiZX1hiXrMdOmtJTRTmB0R7IWkieSCQL6o5NSrS21oO4bJKcWECIq7HdIi1hFtAHiTrJJEPtDWY5GkHviMjtUgg3UmrYUzSqTlagTz5QUPvlc0=
Message-ID: <dcc563d10710100820p520698cctb65e5f5dc1a3a6e5@mail.gmail.com>
Date: Wed, 10 Oct 2007 10:20:02 -0500
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Radhika S" <radhika.sambamurti@gmail.com>
Subject: Re: Shared Buffer setting in postgresql.conf
Cc: pgsql-performance@postgresql.org
In-Reply-To: <fe27bfd40710091312l1d0bf61fx6caecdd74b7dec50@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <fe27bfd40710091312l1d0bf61fx6caecdd74b7dec50@mail.gmail.com>

On 10/9/07, Radhika S <radhika.sambamurti@gmail.com> wrote:
> Hi,
> Along with my previous message (slow postgres), I notice the shared buffer
> setting for our production database is set to 1000.
> How much higher can I go?  I don't know how much my kernel can take?
>
> I am running postgres 7.4.6 on Redhat enterprise 3 server.

Your kernel can go much much higher.  However, 7.4 was not very
efficient at handling large amount of shared_buffers, so the rule of
thumb is to make it big enough to hold your largest working set and
test to see if it's faster or slower.

Most of the time it will be faster, but sometimes in 7.4 it will be
slower due to the inefficient caching algorithm it used.

two points:

* 7.4.18 or so is the latest version in that branch.  Updating it is a
simple pg_ctl stop;rpm -Uvh postgresql-7.4.18.rpm;pg_ctl start or
equivalent.  Painless and takes a minute or two, and there are actual
factual data eating bugs in 7.4.6.

* 8.2 (8.3 due out soon) is MUCH faster than 7.4, AND it can handle
much larger shared_buffer settings than 7.4

Back to shared_buffer issues.  Keep in mind the kernel caches too, and
it pretty good at it.  A common school of thought is to give
postgresql about 25% of the memory in the machine for shared_buffers
and let the kernel handle the rest.  It's not a hard fast number.  I
run about 35% of the memory for shared_buffers on my server, and it
works very well.

Keep in mind, memory handed over to shared buffers means less memory
for other things, like sorts or kernel buffering / caching, so
TANSTAAFL (There ain't no such thing as a free lunch) is the key word.

In 7.4, using 25% is often too high a setting for it to handle well,
and the practical useful maximum is usually under 10,000
shared_buffers, and often closer to 1,000 to 5,000