From: | adey <adey11(at)gmail(dot)com> |
---|---|
To: | "Milen A(dot) Radev" <milen(at)radev(dot)net> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Lock Statistics |
Date: | 2007-07-30 22:35:25 |
Message-ID: | 1c66bda80707301535s24e675f4i483b06f91351289e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Decibel! | 2007-07-31 00:44:58 | Re: [GENERAL] Question about Postgres |
Previous Message | Milen A. Radev | 2007-07-30 16:11:11 | Lock Statistics |