adi hirschtein wrote:
> Using the catalog tables, is there any way to correlate session
> id/user id to which object (i.e. tables, indexes etc) it access and
> much how disk reads or I/O wait has been done against the objects.
> in general, I'd like to see which objects are being accessed by which
> user and the time/amount of I/O wait/reads.
On recent Linux systems, the iotop utility is handy to figure out which
individual users are doing lots of I/O. There are some cases where the
user doing the I/O and the one who caused the I/O are different, which
includes things from synchronized scans to background writer writes.
But for the most part that utility gives a useful view into per-user I/O.
Mark Wong has done some good work toward integrating that same data
source on Linux into something you can query and match against database
activity in his pg_proctab project:
http://www.slideshare.net/markwkm/pgproctab-accessing-system-stats-in-postgresql-3573304
And if you're on Solaris you can extract of a lot of this data with
custom DTrace scripting.
I have a rough plan for directly instrumenting more of this information
from within the database, more like what Oracle does here. But that's
going to take months of development time, and I'm not sure the
PostgreSQL core will even accept the overhead it would add in all
cases. If we could get one Oracle user who's on the fence over a
PostgreSQL conversion to throw a small portion of the money they'd save
toward that project, I'm sure I could get it developed. It's just that
nobody has been interested enough in such a thing to sponsor it so far.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us