PostgreSQL Performance - shared buffers

Author

shared buffers

Carlos Henrique Reimer

2005-08-29, 1:24 pm

Hi,

I´ve configured postgresql to use 1GB of shared buffers but meminfo and "top" are indicanting 0 shared buffers page. Why?

It´s a Linux Redhat 9 box with 4GB RAM and postgresql 7.3.

Thanks in advance!

Reimer

---------------------------------
Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!

Christopher Kings-Lynne

2005-08-30, 3:25 am

> I´ve configured postgresql to use 1GB of shared buffers but meminfo and
> "top" are indicanting 0 shared buffers page. Why?

1GB shared buffers is far too much. Set it back to like 30000 buffers
max...

Chris

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Carlos Henrique Reimer

2005-08-30, 3:25 am

I forgot to say that it´s a 12GB database...

Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and "top" shouldn´t show some shared pages?

I heard something about that Redhat 9 can´t handle very well RAM higher than 2GB. Is it right?

Thanks in advance!

Reimer

Christopher Kings-Lynne < chriskl@familyhealth
.com.au> escreveu:
> I´ve configured postgresql to use 1GB of shared buffers but meminfo and
> "top" are indicanting 0 shared buffers page. Why?

1GB shared buffers is far too much. Set it back to like 30000 buffers
max...

Chris

---------------------------------
Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!

Christopher Kings-Lynne

2005-08-30, 3:25 am

> I forgot to say that it´s a 12GB database...

That's actually not that large.

> Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and
> "top" shouldn´t show some shared pages?

Yeah. The reason for not setting buffers so high is because PostgreSQL
cannot efficiently manage huge shared buffers, so you're better off
giving the RAM to Linux's disk cache.

Chris

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2005-08-30, 3:25 am

Carlos Henrique Reimer <carlosreimer@yahoo.com.br> writes:
> I heard something about that Redhat 9 can´t handle very well RAM higher than 2GB. Is it right?

RHL 9 is certainly pretty long in the tooth. Why aren't you using a
more recent distro?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

William Yu

2005-08-30, 3:25 am

Carlos Henrique Reimer wrote:
> I forgot to say that it´s a 12GB database...
>
> Ok, I´ll set shared buffers to 30.000 pages but even so "meminfo" and
> "top" shouldn´t show some shared pages?
>
> I heard something about that Redhat 9 can´t handle very well RAM higher
> than 2GB. Is it right?
> Thanks in advance!

RH9, like any 32-bit OS, is limited to 2GB address space w/o special
tricks. However, it can access > 2GB for the OS disk cache using PAE if
you are running the bigmem kernel.

Martin Nickel

2005-09-05, 3:23 am

Chris,
Would you say that 30000 pages is a good maximum for a Postgres install?
We're running 8.0.3 on 64-bit SUSE on a dual Opteron box with 4G and have
shared_buffers set at 120000. I've moved it up and down (it was 160000
when I got here) without any measurable performance difference.

The reason I ask is because I occasionally see large-ish queries take
forever (like cancel-after-12-hours forever) and wondered if this could
result from shared_buffers being too large.

Thanks for your (and anyone else's) help!
Martin Nickel

On Tue, 30 Aug 2005 10:08:21 +0800, Christopher Kings-Lynne wrote:

>
> That's actually not that large.
>
>
> Yeah. The reason for not setting buffers so high is because PostgreSQL
> cannot efficiently manage huge shared buffers, so you're better off
> giving the RAM to Linux's disk cache.
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

John A Meinel

2005-09-11, 9:23 am

Martin Nickel wrote:
> Chris,
> Would you say that 30000 pages is a good maximum for a Postgres install?
> We're running 8.0.3 on 64-bit SUSE on a dual Opteron box with 4G and have
> shared_buffers set at 120000. I've moved it up and down (it was 160000
> when I got here) without any measurable performance difference.

What I've read on the mailing list, is that usually the sweet spot is
actually around 10k pages. 120k seems far too high.

I believe that the major fixes to the buffer manager are more in 8.1
rather than 8.0, so you probably are hitting some problems. (The biggest
problem was that there were places that require doing a complete scan
through shared memory looking for dirty pages, or some such).

>
> The reason I ask is because I occasionally see large-ish queries take
> forever (like cancel-after-12-hours forever) and wondered if this could
> result from shared_buffers being too large.

There are lots of possibilities for why these take so long, perhaps you
would want to post them, and we can try to help.
For instance, if you have a foreign key reference from one table to
another, and don't have indexes on both sides, then deleting from the
referenced table, will cause a sequential scan on the referring table
for *each* deleted row. (IIRC).

John
=:->

>
> Thanks for your (and anyone else's) help!
> Martin Nickel

Sponsored Links