Re: Lock Statistics

-- 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;

 

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