Re: proposal for 9.5: monitoring lock time for slow queries

Hi

2014-08-13 6:18 GMT+02:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:

> On Wed, Aug 13, 2014 at 4:59 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> > Any idea how to show a lock time in some practical form together with
> logged
> > slow query?
>
> Doing a join on pg_stat_activity and pg_locks is not going to help
> much as you could only get the moment when query has started or its
> state has changed. Have you thought about the addition of a new column
> in pg_locks containing the timestamp of the moment a lock has been
> taken? I am sure that we are concerned about the performance impact
> that extra calls to gettimeofday could have though...
> Regards,
>

There are two relative independent tasks

a) monitor and show total lock time of living queries

b) monitor and log total lock time of executed queries.

I am interested by @b now. When we work with slow query log, then we would
to identify reason for long duration. Locks are important source of these
queries on some systems.

What I know, we do monitoring these times for deadlock identification
trigger and for log_lock_waits - so there should not be any new pressure to
performance.

Regards

Pavel

> --
> Michael
>