Re: Lock Wait Statistics (next commitfest)

Gokulakannan Somasundaram wrote:
> Statspack works by the following way -a) it takes a copy of important 
> catalog tables(pg_ tables) which store the information like wait 
> statistics against wait events, i/o statistics cumulative against each 
> SQL_Hash( and SQL_Text), whether a particular plan went for hard 
> parse/ soft parse(because of plan caching) and the status of different 
> in-memory data structures etc.

This is actually missing the real work that went into building this 
feature into Oracle.  Before it was possible to build statspack, first 
they went to the trouble of noting every source of this form of 
latency--lock waits, I/O statistics and waits, buffer pool waits--and 
instrumented every single one of them internally.  Basically, every time 
something that might wait for a resource you later wanted to monitor the 
wait for happens, a start/end timestamp for that wait is noted, and 
ultimately the difference between them noting how long the event took is 
stored into the database.  That's the data you must have collected at 
some point in order to get the summary.

Meanwhile, PostgreSQL development is resistant to making any changes in 
this direction under the theory that a) it adds a lot of code complexity 
and b) constant calls to get the current system time are too expensive 
on some platforms to do them all the time.  Until those two things are 
sorted out, what the high-level interface to the direction you're 
suggesting looks like doesn't really matter.  DTrace support has managed 
to clear both of those hurdles due to its optional nature, perceived low 
overhead, and removing *storing* all the events generated to something 
that happens outside of the database.

I agree with you that something like statspack is the direction 
PostgreSQL eventually needs to go, but it's going to be an uphill battle 
the whole time to get it built.  The objections will be that it will add 
too much overhead at the lowest levels, where the data needed to support 
it is collected at.  Once that is cleared, the high-level interface is 
easy to build.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us