X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 75231329CF7 for ; Thu, 23 Sep 2004 18:15:05 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04612-04 for ; Thu, 23 Sep 2004 17:14:46 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 65616329CAE for ; Thu, 23 Sep 2004 18:14:46 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i8NHEjfg006035 for ; Thu, 23 Sep 2004 17:14:45 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i8NHCMCY005137 for pgsql-performance@postgresql.org; Thu, 23 Sep 2004 17:12:22 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Caching of Queries Date: Thu, 23 Sep 2004 19:12:16 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 41 Message-ID: <415303F0.3050409@bigfoot.com> References: <20040923152925.18234.qmail@web41114.mail.yahoo.com> <200409231653.i8NGrUaX017580@ms-smtp-02.nyroc.rr.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Jason Coene User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en In-Reply-To: <200409231653.i8NGrUaX017580@ms-smtp-02.nyroc.rr.com> X-Enigmail-Version: 0.86.1.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/243 X-Sequence-Number: 8371 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