Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 22D539FB8F6 for ; Mon, 30 Jul 2007 19:35:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 65449-06 for ; Mon, 30 Jul 2007 19:35:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.177]) by postgresql.org (Postfix) with ESMTP id B54AA9FB632 for ; Mon, 30 Jul 2007 19:35:26 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d32so3662743pye for ; Mon, 30 Jul 2007 15:35:25 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=g8N7ekIUysQiwmnIgp6rLi3hB+y2T2+y/OhP8/TNlC4Ys9oZ0B9Q0xFmvne+LR87A6ZY6Lw77XF5kh2pg3jS88ERYmr+IKHyKlGQkIkAsIO9PEx0lwR5eqqGWUShu4iF5RNCd08+x3izluOtvBmw+Fe+JtqjqGjzJDe69qQPMhg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Vd3f08bgY21AT5RGG9IUS0WluXssSFi+lz+SF1yYTcF0UGg9p9Kb0atGkxJ4AO92vIwS/qmDRhKIZZb8JS4CQh+pV0S8t4l3gVd9RDI8Ya20zcJ9cA/Y7ukLOsNQabsEd/aoHcdwzj3f7kVmDP4M0D+DewH67HRXH9Jo5UBeYkU= Received: by 10.65.200.8 with SMTP id c8mr9579395qbq.1185834925367; Mon, 30 Jul 2007 15:35:25 -0700 (PDT) Received: by 10.64.181.3 with HTTP; Mon, 30 Jul 2007 15:35:25 -0700 (PDT) Message-ID: <1c66bda80707301535s24e675f4i483b06f91351289e@mail.gmail.com> Date: Tue, 31 Jul 2007 10:35:25 +1200 From: adey To: "Milen A. Radev" Subject: Re: Lock Statistics Cc: pgsql-admin@postgresql.org In-Reply-To: <32c009ea0707300911h74f588f6se7d03f5f28d598e5@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29120_5034918.1185834925321" References: <32c009ea0707300911h74f588f6se7d03f5f28d598e5@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/341 X-Sequence-Number: 26322 ------=_Part_29120_5034918.1185834925321 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Try this query for a start, and add system tables to the join to find what you want:- -- displays locks with database name and username, but not table -- CAUTION: this query may impact system performance as you are selecting from system tables select relation , c.relname , u.usename , pid , mode , transaction , granted , datname , u.usesysid , usesuper --* from pg_locks l, pg_stat_activity s, pg_user u, pg_class c where l.pid = s.procpid and l.relation = c.relfilenode and s.usesysid = u.usesysid order by l.pid; On 7/31/07, Milen A. Radev wrote: > > I have performance problems with a DB (slow queries) and I suspect the > main cause is that a lot of queries wait for a lock on one small > table. That's why I need some stats about the number and (average) > wait-time for locks (even only for this particular table). > > After a bit of googling I found a project in PgFoundry with a > promising description - http://pgfoundry.org/projects/pglockdiag/. > Unfortunately the projects seems stillborn - no published files and > nothing in CVS. > > > -- > Milen A. Radev > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > ------=_Part_29120_5034918.1185834925321 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Try this query for a start, and add system tables to the join to find what you want:-
 

-- displays locks with database name and username, but not table

-- CAUTION: this query may impact system performance as you are selecting from system tables

select

relation

,

c.relname

,

u.usename

,

pid

,

mode

,

transaction

,

granted

,

datname

,

u.usesysid

,

usesuper

--*

from

pg_locks l, pg_stat_activity s, pg_user u , pg_class c

where

l.pid = s. procpid

and l.relation = c .relfilenode

and s.usesysid = u .usesysid

order

by l.pid;

 
On 7/31/07, Milen A. Radev <milen@radev.net> wrote:
I have performance problems with a DB (slow queries) and I suspect the
main cause is that a lot of queries wait for a lock on one small
table. That's why I need some stats about the number and (average)
wait-time for locks (even only for this particular table).

After a bit of googling I found a project in PgFoundry with a
promising description - http://pgfoundry.org/projects/pglockdiag/.
Unfortunately the projects seems stillborn - no published files and
nothing in CVS.


--
Milen A. Radev

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------=_Part_29120_5034918.1185834925321--