From: John A Meinel <john@arbash-meinel.com>
To: Martin Nickel <martin@portant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared buffers
Date: Sun, 11 Sep 2005 08:35:53 -0400
Message-ID: <432424A9.4060403@arbash-meinel.com> (raw)
In-Reply-To: <pan.2005.09.05.02.55.50.340618@portant.com>
References: <20050830015454.54152.qmail@web53204.mail.yahoo.com>
<4313BF95.3030105@familyhealth.com.au>
<pan.2005.09.05.02.55.50.340618@portant.com>
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
Attachments:
[application/pgp-signature] signature.asc (252B, ../432424A9.4060403@arbash-meinel.com/2-signature.asc)
download
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: john@arbash-meinel.com, martin@portant.com
Subject: Re: shared buffers
In-Reply-To: <432424A9.4060403@arbash-meinel.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