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

On Fri, Oct 3, 2014 at 11:15:13PM +0200, Andres Freund wrote:
> > As far as gathering data, I don't think we are going to do any better in
> > terms of performance/simplicity/reliability than to have a single PGPROC
> > entry to record when we enter/exit a lock, and having a secondary
> > process scan the PGPROC array periodically.
>
> I don't think that'll give meaningful results given the very short times
> most lwlocks are held. And it'll give not very interesting results for

I figured you could get more data the more often you sampled.

> multiple lwlocks held at the same time - most of the time the 'earlier'
> held ones are more interesting than the last acquired one...

Well, I thought lock _waiting_ would be the most interesting measure,
not locks held. Doesn't pg_locks show locks held?

> > I am assuming almost no one cares about the number of locks, but rather
> > they care about cummulative lock durations.
>
> 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.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +