Re: Dynamic LWLock tracing via pg_stat_lwlock (proof of concept)

On Fri, Oct  3, 2014 at 06:06:24PM -0400, Bruce Momjian wrote:
> > I actually don't think that's true. Every lock acquiration implies a
> > number of atomic locks. Those are expensive. And if you see individual
> > locks acquired a high number of times in multiple proceses that's
> > something important. It causes significant bus traffic between sockets,
> > while not necessarily visible in the lock held times.
> 
> True, but I don't think users are going to get much value from those
> numbers, and they are hard to get.  Server developers might want to know
> lock counts, but in those cases performance might not be as important.

In summary, I think there are three measurements we can take on locks:

1.  lock wait, from request to acquisition
2.  lock duration, from acquisition to release
3.  lock count

I think #1 is the most useful, and can be tracked by scanning a single
PGPROC lock entry per session (as already outlined), because you can't
wait on more than one lock at a time.

#2 would probably require multiple PGPROC lock entries, though I am
unclear how often a session holds multiple light-weight locks
concurrently.  #3 might require global counters in memory.

#1 seems the most useful from a user perspective, and we can perhaps
experiment with #2 and #3 once that is done.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +