2015-01-16 19:24 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2015-01-16 19:06 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>: > >> On 1/16/15 11:35 AM, Pavel Stehule wrote: >> >>> >>> >>> 2015-01-16 18:23 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com <mailto: >>> jim.na...@bluetreble.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 >> > >