Re: Lock Statistics

[adey <adey11(at)gmail(dot)com>]

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(at)radev(dot)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