Skip site navigation (1) Skip section navigation (2)

Re: Shared Buffer setting in postgresql.conf

From: "Radhika S" <radhika(dot)sambamurti(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Shared Buffer setting in postgresql.conf
Date: 2007-10-11 00:33:50
Message-ID: fe27bfd40710101733n45db318eib2c5f888c3045ebf@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2007-10-11 00:49:23
Subject: Re: Shared Buffer setting in postgresql.conf
Previous:From: Kevin KempterDate: 2007-10-10 22:21:41
Subject: building a performance test suite

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group