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 <cr...@postnewspapers.com.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 >
