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(at)2ndQuadrant(dot)com www.2ndQuadrant.us