Re: EXPLAIN ANALYZE printing logical and hardware I/O per-node

On Dec 14, 2007, at 11:10 PM, Neil Conway wrote:
>> But it occurred to me just now that the hardware instruction  
>> counter available
>> on just about every platform would be good enough for a heuristic  
>> guess at
>> whether the read(2) was cached.
>
> I'm skeptical that this would be reliable enough to be very useful,
> especially in the face of concurrent, unpredictable system activity  
> on a
> busy system. I agree that it would be useful information, though.
> Perhaps a useful first step would be to teach EXPLAIN ANALYZE to  
> report
> the number of logical and physical I/Os from Postgres' perspective  
> (i.e.
> physical I/O just means we need to go to the kernel).


*watches hands wave*

If we assume that what we *really* want to see the difference of is a  
cache IO vs one that truly hits a platter, I don't see how you could  
come up with enough variation to account for that. Unless you're  
super lucky and the sector you need is just about to hit the head  
when you issue the request, you're going to have a delay measured in  
milliseconds. 1ms on a modern CPU is over 100,000 cycles. That's a  
lot of instructions...

Someone want to throw together some code that actually measures this?  
Maybe something that keeps a histogram of how many instructions take  
place per I/O request? If it turns out that counters do vary too much  
between CPUs, there might be ways that we can account for that.

Also, has anyone looked into adding a class of system calls that  
would actually tell us if the kernel issued physical IO? I find it  
hard to believe that other RDBMSes wouldn't like to have that info...
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828