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@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
