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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828