X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F2176D1B188 for ; Wed, 19 May 2004 00:09:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11339-04 for ; Wed, 19 May 2004 00:08:45 -0300 (ADT) Received: from mammoth.utopiasystems.net (meridian1.utopiasystems.net [66.150.45.40]) by svr1.postgresql.org (Postfix) with ESMTP id CD939D1B19C for ; Wed, 19 May 2004 00:08:44 -0300 (ADT) Received: from [24.13.180.87] ([24.13.180.87]) by mammoth.utopiasystems.net with Microsoft SMTPSVC(6.0.3790.0); Tue, 18 May 2004 23:07:50 -0400 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Tue, 18 May 2004 22:08:42 -0500 Subject: Re: Interpreting query debug output From: Steve Lane To: Message-ID: In-Reply-To: <10080.1084928803@sss.pgh.pa.us> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-OriginalArrivalTime: 19 May 2004 03:07:50.0687 (UTC) FILETIME=[78379AF0:01C43D4E] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200405/189 X-Sequence-Number: 13505 > From: Tom Lane > Date: Tue, 18 May 2004 21:06:43 -0400 > To: Steve Lane > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Interpreting query debug output > > Steve Lane writes: >> I have a database that is exhibiting sluggishness under load. Suspecting >> that some queries may be poorly optimized, I turned on a fair amount of >> debugging output in the logs. But I could use some help interpreting it. > > I think you're going at this all wrong. EXPLAIN ANALYZE should be the > first tool you turn to, not low-level stats. Browsing the archives of > the pgsql-performance mailing list may help you get started. > > regards, tom lane > Hmm. When I do a process listing I can see that there are postgres processes occupying large chunks of CPU, sometimes in the 60-99% range, for long enough to be noticeable in the process list. I'd like to capture those process IDs and then correlate them with the stats captured in the log to see why they take so much CPU. If I want to EXPLAIN ANALYZE, I have to pick individual queries. The query logic of the application is distributed across many source files. I'd have to do quite some combing to recover a list of all queries the system runs. I figure distilling the log output might be the best approach. Given these points, is this still the wrong approach? Even if so, I'd still love to know how to read the debug output. -- sgl