Re: Lock Wait Statistics (next commitfest)

Jaime Casanova wrote:
>
> it applies with some hunks, compiles fine and seems to work...
> i'm still not sure this is what we need, some more comments could be helpful.
>
>   
Yeah, that's the big question. Are the current capabilities (logging 'em 
for waits > deadlock timeout + dtrace hooks) enough?  I'm thinking that 
if we had dtrace for linux generally available, then the need for this 
patch would be lessened.

> what kind of questions are we capable of answer with this and and what
> kind of questions are we still missing?
>
> for example, now we know "number of locks that had to wait", "total
> time waiting" and "max time waiting for a single lock"... but still we
> can have an inaccurate understanding if we have lots of locks waiting
> little time and a few waiting a huge amount of time...
>
> something i have been asked when system starts to slow down is "can we
> know if there were a lock contention on that period"? for now the only
> way to answer that is logging locks
>
>   
Right - there still may be other aggregates that need to be captured.... 
it would be great to have some more feedback from the field about this. 
In my case, I was interested in seeing if the elapsed time was being 
spent waiting for locks or actually executing (in fact it turned out to 
be the latter - but was still very useful to be able to rule out locking 
issues).  However , as you mention - there maybe cases where the 
question is more about part of the system suffering a disproportional 
number/time of lock waits...

> about the patch itself:
> you haven't documented either. what is the pg_stat_lock_waits view
> for? and what are those fieldx it has?
>
>   

Yeah, those fields are straight from the LOCKTAG structure. I need to 
redo the view to be more like pg_locks, and also do the doco. However 
I've been a bit hesitant to dive into these last two steps until I see 
that there is some real enthusiasm for this patch (or failing that, a 
feeling that it is needed...).

> i'll let this patch as "needs review" for more people to comment on it...
>
>   
Agreed, thanks for the comments.

Mark