From: Scott Marlowe <scott.marlowe@gmail.com>
To: Radhika S <radhika.sambamurti@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Shared Buffer setting in postgresql.conf
Date: Wed, 10 Oct 2007 10:20:02 -0500
Message-ID: <dcc563d10710100820p520698cctb65e5f5dc1a3a6e5@mail.gmail.com> (raw)
In-Reply-To: <fe27bfd40710091312l1d0bf61fx6caecdd74b7dec50@mail.gmail.com>
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
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: scott.marlowe@gmail.com, radhika.sambamurti@gmail.com
Subject: Re: Shared Buffer setting in postgresql.conf
In-Reply-To: <dcc563d10710100820p520698cctb65e5f5dc1a3a6e5@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