Re: Lock Statistics

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
>