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

On Tue, Oct 7, 2014 at 4:30 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-10-07 17:22:18 +0300, Heikki Linnakangas wrote:
>> FWIW, I liked Ilya's design. Before going to sleep, store the lock ID in
>> shared memory. When you wake up, clear it. That should be cheap enough to
>> have it always enabled. And it can easily be extended to other "waits", e.g.
>> when you're waiting for input from client.
>
> I think there's a few locks where that's interesting. But in my
> experience many slowdowns aren't caused by actual waits, but because of
> cacheline contention. And for that the number of acquisitions is much
> more relevant than the waiting. The primary example for this is probably
> the procarray lock.

I would say, that to see particular lwlockid 50 times in 100 samples
or to see it 50 times one after another or see it only 2 times,
provides good and representative information for DBA. At least better
than nothing.

>
>> I don't think counting the number of lock acquisition is that interesting.
>> It doesn't give you any information on how long the waits were, for
>> example.
>
> Sure, that's a separate thing that we should be able to answer.

The point is that a lot of short waits sometimes could be as worse as
one long wait. That is why it is important, but I thing propper
sampling provides good estimation for this.

>
> Greetings,
>
> Andres Freund
>
> --
> Andres Freund http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik(at)postgresql-consulting(dot)com