pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedShared Buffer setting in postgresql.conf
8+ messages / 5 participants
[nested] [flat]
* Shared Buffer setting in postgresql.conf
@ 2007-10-09 20:12 Radhika S <radhika.sambamurti@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Radhika S @ 2007-10-09 20:12 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Shared Buffer setting in postgresql.conf
@ 2007-10-10 05:25 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Radhika S <radhika.sambamurti@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Marcin Stępnicki @ 2007-10-10 05:25 UTC (permalink / raw)
To: pgsql-performance; +Cc: Radhika S <radhika.sambamurti@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?
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Shared Buffer setting in postgresql.conf
@ 2007-10-10 15:20 Scott Marlowe <scott.marlowe@gmail.com>
parent: Radhika S <radhika.sambamurti@gmail.com>
1 sibling, 2 replies; 8+ messages in thread
From: Scott Marlowe @ 2007-10-10 15:20 UTC (permalink / raw)
To: Radhika S <radhika.sambamurti@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Shared Buffer setting in postgresql.conf
@ 2007-10-10 20:32 Josh Trutwin <josh@trutwins.homeip.net>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Josh Trutwin @ 2007-10-10 20:32 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Shared Buffer setting in postgresql.conf
@ 2007-10-10 20:58 Scott Marlowe <scott.marlowe@gmail.com>
parent: Josh Trutwin <josh@trutwins.homeip.net>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2007-10-10 20:58 UTC (permalink / raw)
To: Josh Trutwin <josh@trutwins.homeip.net>; +Cc: pgsql-performance
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...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Shared Buffer setting in postgresql.conf
@ 2007-10-11 00:33 Radhika S <radhika.sambamurti@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Radhika S @ 2007-10-11 00:33 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: 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.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Shared Buffer setting in postgresql.conf
@ 2007-10-11 00:49 Scott Marlowe <scott.marlowe@gmail.com>
parent: Radhika S <radhika.sambamurti@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Marlowe @ 2007-10-11 00:49 UTC (permalink / raw)
To: Radhika S <radhika.sambamurti@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Shared Buffer setting in postgresql.conf
@ 2007-10-18 01:33 Ow Mun Heng <Ow.Mun.Heng@wdc.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ow Mun Heng @ 2007-10-18 01:33 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Radhika S <radhika.sambamurti@gmail.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-10-18 01:33 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-09 20:12 Shared Buffer setting in postgresql.conf Radhika S <radhika.sambamurti@gmail.com>
2007-10-10 05:25 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-10-10 15:20 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-10 20:32 ` Josh Trutwin <josh@trutwins.homeip.net>
2007-10-10 20:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-11 00:33 ` Radhika S <radhika.sambamurti@gmail.com>
2007-10-11 00:49 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-18 01:33 ` Ow Mun Heng <Ow.Mun.Heng@wdc.com>
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