Обсуждение: Monitoring Object access

On 09/12/2010 06:52 PM, adi hirschtein wrote:
> Hi,
> 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.

There isn't really anything like that, no. You have pg_stat_activity,
but it's a pretty coarse tool.

The shared buffer cache and the use of things like synchronized
sequential scans means that it wouldn't even be possible to truly track
who's causing load reliably. As I understand it, if Joe and Fred both to
a "SELECT * FROM sometable", it's quite likely that only one of the
backends will actually appear to read anything from disk. Which one is
pretty much luck.

Adding something like this would require instrumenting not only the
backends' disk I/O code, but also their shared buffer access code. It'd
potentially add a lot of overhead, and I'm not sure the results would
mean very much because the caching effects would make "fred's backend
did X I/O operations" less meaningful. Fred's might just have been the
first that ran after Joe's giant seqscan cross join of two big tables
that forced everything else out of shared_buffers.

Even if you did have that instrumentation, you'd need OS-level
instrumentation (dtrace, prof, etc) to track the OS's buffer cache,
which PostgreSQL relies on heavily. Without that you can't tell the
difference between a query that caused I/O calls from postgresql but
could be satisfied by OS buffer cache and one that required expensive
physical disk I/O to satisfy.

Really, I don't know if it's realistic to do what you're suggesting
unless every user's data set is isolated from every other user's, in
which case you can maybe use OS-level tools like prof or DTrace to
achieve it if you set postgresql up to log whichstarts which backend pid.

If there's any significant overlap in the data sets used by the users
(if they use the same databases or schema) I wouldn't think you'd be
able to get any meaningful results.

--
Craig Ringer

On 09/12/2010 10:02 PM, adi hirschtein wrote:
> Hi Craig,
>
> Thanks a lot for the quick response!
> I'm coming from the Oracle side of the house and In oracle for instance,
> you use shared buffer as well, but you are still able to see which
> session is waiting for which blocks and if one session is doing the
 > "real" I/O then the other one wait on 'wait for other session"
 > event so you are able to know who did the actual I/O

There's nothing like that in PostgreSQL. There's some lock monitoring
support for seeing what transactions hold locks and which other
transactions are waiting on those locks, but AFAIK nothing like that for
I/O. PostgreSQL does have DTrace hooks, so if you're on Solaris or some
BSDs you might be able to use those to get the data you want.

It'd be a pretty significant job to add a decent I/O monitoring system
to PostgreSQL. Personally, if I needed something like that, I'd want to
base it on an existing system-level tracing toolkit like Solaris's
DTrace or Linux's "perf". I'd want to add some additional
instrumentation hooks - some of which already exist in Pg for DTrace -
to permit the tools to beaware of transactions, statements, the current
database, which tables are which, which indexes are associated with
which tables, etc. Then I'd use the data collected by the performance
monitoring tools to report on load associated with particular users,
indexes, tables, queries, etc. That way I'd be able to handle things
like whether a request was satisfied with OS buffer cache or had to go
to real disk, report on disk queue depth, etc as part of the whole
system. It'd be a big job even with the use of existing trace tools to help.

Currently there are some DTrace hooks, but I don't think there's any
kind of integrated toolset like I've described to use the monitoring
hooks plus the existing system hooks to do detailed reporting of
load/user, load/tablespace, etc.

> the reason behind it is that you want to check which objects is being
> heavily hit by which  business processes or users and then tier your
> storage accordingly.

At the moment, all you can really do is turn up the logging levels to
log queries, logins, etc. Then watch pg_stat_activity and use
system-level tools like iostat, vmstat, top, perf/dtrace, etc. If you
see backends that're hogging resources you can look their pid up in
pg_stat_activity or the logs, see what they were doing, and run
controlled tests to see what can be improved.

It's somewhat clumsy, but seems to work pretty well most of the time.

Nobody has stepped up to build a comprehensive tracing and performance
framework - and even if they did, they'd have to make it lightweight
enough that it didn't slow PostgreSQL down when it wasn't in use, show
that it wouldn't add an excessive maintenance burden for the developers,
show that it wouldn't break or produce incorrect results the first time
something changed, etc. The Linux kernel demonstrates just how hard
getting this right can be. So does the amount of effort Sun put in to
DTrace. Sure, PostgreSQL isn't an OS kernel, but it's far from simple.

I guess that's why Oracle charges the big bucks - because of all the
extras they include that round the database out into the kitchen-sink
monster that it is.

> is there any place rather than pg_stat_activity that you think I should
> take a look at?

System-level tools and the postgresql logs, especially after proper
configuration. There are some tools on pgfoundry that help a little with
log analysis.

--
Craig Ringer

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

> 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?

There is a bunch of interesting tables - see pg_stat_ and pg_statio_ tables.

Don't forget the values grow all the time, so you'll have to do snapshots
and subtract them. You could do that by hand, or use a tool for that.
There is a "pgstatspack" (should work fine, although it's not developed
since 8.1 AFAIK) and I'm working on an alternative tool with a web GUI
(http://sourceforge.net/projects/pgmonitor/). Still under development and
I know about several issues, but generally works (thanks in advance for
any feedback).

Tomas

On 2010/09/12 23:02, adi hirschtein wrote:
> I'm coming from the Oracle side of the house and In oracle for instance, you use shared buffer as well, but you are
stillable to see which session is waiting for which blocks 
> and if one session is doing the "real" I/O then the other one wait on 'wait for other session" event so you are able
toknow who did the actual I/O 
> the reason behind it is that you want to check which objects is being heavily hit by which  business processes or
usersand then tier your storage accordingly. 
> I agree with your point about the OS buffer cache, I need to monitor it as well.
> is there any place rather than pg_stat_activity that you think I should take a look at?

I think you should also look at pg_locks to know
which session is processing (or waiting on locks).

http://www.postgresql.org/docs/8.4/interactive/view-pg-locks.html

pg_locks table contains several lock information
including "lock dependencies" which you may need
to monitor session activities.

--
NAGAYASU Satoshi <satoshi.nagayasu@gmail.com>