Re: Monitoring Object access - Mailing list pgsql-general

From adi hirschtein
Subject Re: Monitoring Object access
Date
Msg-id AANLkTikL4Y0DNTy+PL=tJMyuErFzx9Z9wT=sPDu3YohH@mail.gmail.com
Whole thread Raw
In response to Re: Monitoring Object access  (Greg Smith <greg@2ndquadrant.com>)
Responses Re: Monitoring Object access  (tv@fuzzy.cz)
List pgsql-general
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 <greg@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
greg@2ndQuadrant.com   www.2ndQuadrant.us


pgsql-general by date:

Previous
From: Michael Glaesemann
Date:
Subject: Re: Int to Interval conversion
Next
From: Luís de Sousa
Date:
Subject: Re: Replacing characters in a string