Thread: Shared Buffer setting in postgresql.conf

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.

Thanks,
Radhika

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

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?

A lot higher. How much memory do you have?

> I am running postgres 7.4.6 on Redhat enterprise 3 server.

Unless you've got a very good reason do yourself a favour and upgrade to 8.2.5.

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

On Wed, 10 Oct 2007 10:20:02 -0500
"Scott Marlowe" <scott.marlowe@gmail.com> wrote:

> 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

Scott - interesting reply.  Is this also true for 8.1?  I currently
have mine set to 16384 - server has 3.5 GB of total memory.

Josh

On 10/10/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> On Wed, 10 Oct 2007 10:20:02 -0500
> "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>
> > 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
>
> Scott - interesting reply.  Is this also true for 8.1?  I currently
> have mine set to 16384 - server has 3.5 GB of total memory.

No, starting with 8.0, the code to manage the shared_buffers is much
more efficient with large numbers of shared buffers.  With 8.0 and up
the primary considerations are that the shared_buffers be big enough
to hold your working set, but not so big as to run the system out of
memory for other things, sorts, kernel caching, etc...

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




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

On 10/10/07, Radhika S <radhika.sambamurti@gmail.com> wrote:
> 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.

On a dedicated db machine with 2 Gigs of ram 500Meg is fine.  I run
768 Meg shared_buffers on a machine that runs postgresql 8.2 and
apache/php and routinely have 1Gig of kernel cache on it.

So yeah, 200M shared_buffers should be no problem.

On Wed, 2007-10-10 at 19:49 -0500, Scott Marlowe wrote:
> On 10/10/07, Radhika S <radhika.sambamurti@gmail.com> wrote:
> > 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.
>
> On a dedicated db machine with 2 Gigs of ram 500Meg is fine.  I run
> 768 Meg shared_buffers on a machine that runs postgresql 8.2 and
> apache/php and routinely have 1Gig of kernel cache on it.
>
> So yeah, 200M shared_buffers should be no problem.

I have 768MB of ram and I'm allocatting 300MB as shared buffers.