Re: Monitoring Object access

Thanks!
I'll look into those system tools and probably come back with some more
questions...

Best,
Adi

On Mon, Sep 13, 2010 at 4:58 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au>wrote:

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