Re: proposal: lock_time for pg_stat_database

2015-01-16 19:24 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 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).
>

and this proposal has sense only for heavyweight locks - because others
locks are everywhere

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