2015-01-16 20:33 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>: > On 1/16/15 12:30 PM, Pavel Stehule wrote: > >> >> >> 2015-01-16 19:24 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com >> <mailto:pavel.steh...@gmail.com>>: >> >> >> >> 2015-01-16 19:06 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com >> <mailto: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> <mailto: >> Jim.Nasby@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? >> > > If that select is waiting on a lock on t2, then it's waiting on that lock > on that table. It doesn't matter who else has the lock. > > 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. >> > > Locks may be global, but what you're waiting for a lock on certainly > isn't. It's almost always a lock either on a table or a row in a table. Of > course this does mean you can't just blindly report that you're blocked on > some XID; that doesn't tell anyone anything. > > 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). >> > > Meh. SELECT sum(state_change) FROM pg_stat_activity WHERE waiting is just > about as useful. Or just turn on lock logging. > > If you really want to add it at the database level I'm not opposed (so > long as it leaves the door open for more granular locking later), but I > can't really get excited about it either. > > and this proposal has sense only for heavyweight locks - because others >> locks are everywhere >> > > So what if they're everywhere? Right now if you're spending a lot of time > waiting for LWLocks you have no way to know what's going on unless you > happen to have dtrace. Obviously we're not going to something like issue a > stats update every time we attempt to acquire an LWLock, but that doesn't > mean we can't keep some counters on the locks and periodically report that.
I have a plan to update statistics when all necessary keys are acquired - so it is once per statement - it is similar press on stats system like now. Pavel > > -- > Jim Nasby, Data Architect, Blue Treble Consulting > Data in Trouble? Get it in Treble! http://BlueTreble.com >
