Re: Shared Buffer setting in postgresql.conf

Thank you scott.

We plan on upgrading to Postgres 8.2 very soon.
Would it be safe to say I can make my SHARED BUFFER setting 200MB (I have
2GB memory ).
The default is 24MB.

Regds,
Radhika

On 10/10/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> On 10/9/07, Radhika S <radhika(dot)sambamurti(at)gmail(dot)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
>

--
It is all a matter of perspective. You choose your view by choosing where to
stand. --Larry Wall