From: Radhika S <radhika.sambamurti@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Shared Buffer setting in postgresql.conf
Date: Wed, 10 Oct 2007 20:33:50 -0400
Message-ID: <fe27bfd40710101733n45db318eib2c5f888c3045ebf@mail.gmail.com> (raw)
In-Reply-To: <dcc563d10710100820p520698cctb65e5f5dc1a3a6e5@mail.gmail.com>
References: <fe27bfd40710091312l1d0bf61fx6caecdd74b7dec50@mail.gmail.com>
<dcc563d10710100820p520698cctb65e5f5dc1a3a6e5@mail.gmail.com>
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.marlowe@gmail.com> wrote:
>
> 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
>
--
It is all a matter of perspective. You choose your view by choosing where to
stand. --Larry Wall
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: radhika.sambamurti@gmail.com, scott.marlowe@gmail.com
Subject: Re: Shared Buffer setting in postgresql.conf
In-Reply-To: <fe27bfd40710101733n45db318eib2c5f888c3045ebf@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox