Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9BE5B64FD5F for ; Wed, 17 Dec 2008 11:38:35 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 98406-05 for ; Wed, 17 Dec 2008 11:38:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f10.google.com (mail-gx0-f10.google.com [209.85.217.10]) by mail.postgresql.org (Postfix) with ESMTP id 8D19164FD3B for ; Wed, 17 Dec 2008 11:38:31 -0400 (AST) Received: by gxk3 with SMTP id 3so335496gxk.19 for ; Wed, 17 Dec 2008 07:38:30 -0800 (PST) Received: by 10.150.230.8 with SMTP id c8mr1578542ybh.81.1229528309909; Wed, 17 Dec 2008 07:38:29 -0800 (PST) Received: by 10.151.153.17 with HTTP; Wed, 17 Dec 2008 07:38:29 -0800 (PST) Message-ID: <3073cc9b0812170738v69e43d89r8d6dae87453b4993@mail.gmail.com> Date: Wed, 17 Dec 2008 10:38:29 -0500 From: "Jaime Casanova" To: "Robert Haas" Subject: Re: measure database contention Cc: "psql performance list" In-Reply-To: <603c8f070812170618g3116a90du94cbf102f0336d59@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <3073cc9b0812161132w3ae157a8n69e2b0cccea05ca2@mail.gmail.com> <603c8f070812170618g3116a90du94cbf102f0336d59@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/163 X-Sequence-Number: 32199 On Wed, Dec 17, 2008 at 9:18 AM, Robert Haas wrote: > On Tue, Dec 16, 2008 at 2:32 PM, Jaime Casanova > wrote: >> we have a some bad queries (developers are working on that), some of >> them run in 17 secs and that is the average but when analyzing logs i >> found that from time to time some of them took upto 3 mins (the same >> query that normally runs in 17secs). >> >> so my question is: how could i look for contention problems? > > Is it the exact same query? is the exact query... i think it will be removed later today because is a bad query anyway... but my fear is that something like happens even with good ones... maybe chekpoints could be the problem? i have 8.3.5 and condigured checkpoint_timeout in 15 minutes, chekpoint_segments 6 and checkpoint_completion_target to 0.5 i'm putting log_checkpoints to on, but should be good if there is way to analyze them better than looking through the log > Sometimes you might find that the query > plan changes depending on the particular values you have in there; it > is worth running "EXPLAIN ANALYZE" to look for such cases. > don't think that could happen in this query, because there is no way it will choose something better than seqscan > You might also want to look at pg_locks. > Only Shared ones... PS: more info about my system (sorry for don't giving it in the first post) 2 PROCESSORS Xeon(R) CPU E5430 @ 2.66GHz with 4 cores each 18Gb in Ram (4gb in shared_buffers, 4mb in work_mem) the db size is 2gb (reported by pg_database_size) max. concurrent connections seen until now: 256 --=20 Atentamente, Jaime Casanova Soporte y capacitaci=F3n de PostgreSQL Asesor=EDa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157