I'd like to look at it from the object level and see how much I/O is being done on specific table or index and then check which sessions are responsible for that.
also, what's the catalog table you would recommend me to use if I want to see I/O activity on an object regardless of the session? On Mon, Sep 13, 2010 at 6:57 PM, Greg Smith <g...@2ndquadrant.com> wrote: > 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 > g...@2ndquadrant.com www.2ndQuadrant.us > >
