From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Two questions.. shared_buffers and long reader issue
Date: Wed, 11 Jul 2007 12:58:26 -0400
Message-ID: <20070711165825.GW1241@phlogiston.dyndns.org> (raw)
In-Reply-To: <4694F8C5.2040005@p-dw.com>
References: <4694F8C5.2040005@p-dw.com>
On Wed, Jul 11, 2007 at 05:35:33PM +0200, Patric de Waha wrote:
> Mainly updates on 1 tuple.
Are you vacuuming that table enough?
> And more or less complex SELECT statements.
> I noticed that the overall performance of postgres is decreasing
> when one or more long
> readers are present. Where a long reader here is already a Select
> count(*) from table.
SELECT count(*) is expensive in Postgres. Do you really need it?
Unqualified count() in PostgreSQL is just a bad thing to do, so if
you can work around it (by doing limited subselects, for instance,
where you never scan more than 50 rows, or by keeping counts using
triggers, or various other tricks), it's a good idea.
> Why do long readers influence the rest of the transactions in such a
> heavy way?
It could be because of all those updated tuples not getting vacuumed
(which results in a bad plan). Or it could be that your connection
pool is exhausted: note that when someone hits "reload", that doesn't
mean your old query goes away. It is still crunching through
whatever work it was doing.
> Second question. What is the right choice for the shared_buffers size?
> On a dedicated postgres server with 4 Giga RAM. Is there any rule of
> thumb?
> Actually I set it to +-256M.
There has been Much Discussion of this lately on this list. I
suggest you have a look through the recent archives on that topic.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
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: ajs@crankycanuck.ca
Subject: Re: Two questions.. shared_buffers and long reader issue
In-Reply-To: <20070711165825.GW1241@phlogiston.dyndns.org>
* 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