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

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
> 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.

I'm interested in b, too. I was thinking of proposing a performance
diagnostics feature like Oracle's wait events (V$SYSTEM_EVENT and
V$SESSION_EVENT). So, if you do this, I'd like to contribute to the
functional design, code and doc review, and testing.

The point is to collect as much information about bottlenecks as possible,
including lock waits. The rough sketch is:

What info to collect:
* heavyweight lock waits shown by pg_locks
* lightweight lock waits
* latch waits
* socket waits (mainly for client input)

How the info is delivered:
* pg_stat_system_events shows the accumulated total accross the server
instance
* pg_stat_session_events shows the accumulated total for each session
* EXPLAIN ANALYZE and auto_explain shows the accumulated total for each
query

We need to describe in the manual how to diagnose and tne the system with
these event info.

Regards
MauMau