Re: proposal: lock_time for pg_stat_database

2015-01-16 19:06 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 1/16/15 11:35 AM, Pavel Stehule wrote:
>
>>
>>
>> 2015-01-16 18:23 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com <mailto:
>> Jim(dot)Nasby(at)bluetreble(dot)com>>:
>>
>> On 1/16/15 11:00 AM, Pavel Stehule wrote:
>>
>> Hi all,
>>
>> some time ago, I proposed a lock time measurement related to
>> query. A main issue was a method, how to show this information. Today
>> proposal is little bit simpler, but still useful. We can show a total lock
>> time per database in pg_stat_database statistics. High number can be signal
>> about lock issues.
>>
>>
>> Would this not use the existing stats mechanisms? If so, couldn't we
>> do this per table? (I realize that won't handle all cases; we'd still need
>> a "lock_time_other" somewhere).
>>
>>
>>
>> it can use a current existing stats mechanisms
>>
>> I afraid so isn't possible to assign waiting time to table - because it
>> depends on order
>>
>
> Huh? Order of what?
>

when you have a SELECT FROM T1, T2 and T1 is locked for t1, and T2 is
locked for t2 -- but if t2 < t1 then t2 is not important -- so what I have
to cont as lock time for T1 and T2?

DDL statements are exception - there is almost simple mapping between
relations and lock time reason.

>
> Also, what do you mean by 'lock'? Heavyweight? We already have some
>> visibility there. What I wish we had was some way to know if we're spending
>> a lot of time in a particular non-heavy lock. Actually measuring time
>> probably wouldn't make sense but we might be able to count how often we
>> fail initial acquisition or something.
>>
>>
>> now, when I am thinking about it, lock_time is not good name - maybe
>> "waiting lock time" (lock time should not be interesting, waiting is
>> interesting) - it can be divided to some more categories - in GoodData we
>> use Heavyweight, pages, and others categories.
>>
>
> So do you see this somehow encompassing locks other than heavyweight
> locks? Because I think that's the biggest need here. Basically, something
> akin to TRACE_POSTGRESQL_LWLOCK_WAIT_START() that doesn't depend on
> dtrace.

For these global statistics I see as important a common total waiting time
for locks - we can use a more detailed granularity but I am not sure, if a
common statistics are best tool.

My motivations is - look to statistics -- and I can see ... lot of
rollbacks -- issue, lot of deadlocks -- issue, lot of waiting time -- issue
too. It is tool for people without possibility to use dtrace and similar
tools and for everyday usage - simple check if locks are not a issue (or if
locking is stable).

>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>