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). > > -- > Jim Nasby, Data Architect, Blue Treble Consulting > Data in Trouble? Get it in Treble! http://BlueTreble.com >
