From: Gaetano Mendola <mendola@bigfoot.com>
To: Jason Coene <jcoene@gotfrag.com>
Subject: Re: Caching of Queries
Date: Thu, 23 Sep 2004 19:12:16 +0200
Message-ID: <415303F0.3050409@bigfoot.com> (raw)
In-Reply-To: <200409231653.i8NGrUaX017580@ms-smtp-02.nyroc.rr.com>
References: <20040923152925.18234.qmail@web41114.mail.yahoo.com>
<200409231653.i8NGrUaX017580@ms-smtp-02.nyroc.rr.com>
Jason Coene wrote:
> I'm not an expert, but I've been hunting down a killer performance problem
> for a while now. It seems this may be the cause.
>
> At peak load, our database slows to a trickle. The CPU and disk utilization
> are normal - 20-30% used CPU and disk performance good.
For a peak load 20-30% used CPU this mean you reached your IO bottleneck.
> All of our "postgres" processes end up in the "semwai" state - seemingly
> waiting on other queries to complete. If the system isn't taxed in CPU or
> disk, I have a good feeling that this may be the cause. I didn't know that
> planning queries could create such a gridlock, but based on Mr Pink's
> explanation, it sounds like a very real possibility.
>
> We're running on SELECT's, and the number of locks on our "high traffic"
> tables grows to the hundreds. If it's not the SELECT locking (and we don't
> get that many INSERT/UPDATE on these tables), could the planner be doing it?
>
> At peak load (~ 1000 queries/sec on highest traffic table, all very
> similar), the serialized queries pile up and essentially create a DoS on our
> service - requiring a restart of the PG daemon. Upon stop & start, it's
> back to normal.
Give us informations on this queries, a explain analyze could be a good start
point.
> I've looked at PREPARE, but apparently it only lasts per-session - that's
> worthless in our case (web based service, one connection per data-requiring
> connection).
Trust me the PREPARE is not doing miracle in shenarios like yours . If you use postgres
in a web service environment what you can use is a connection pool ( look for pgpoll IIRC ),
if you use a CMS then try to enable the cache in order to avoid to hit the DB for each
request.
Regards
Gaetano Mendola
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: mendola@bigfoot.com, jcoene@gotfrag.com
Subject: Re: Caching of Queries
In-Reply-To: <415303F0.3050409@bigfoot.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