Re: Monitoring Object access

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