Re: Monitoring Object access - Mailing list pgsql-general

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