Re: Patch: add timing of buffer I/O requests

On 04/10/2012 12:27 PM, Tom Lane wrote:
>Changing the column name will definitely break
> anything more specific than "select * from pg_stat_statements".
> However, it's less clear that changing the units in which the column is
> expressed will break things. It seems likely to me that nobody out
> there is doing anything much more sophisticated than sorting by the
> column, and that's still going to work the same.

I am doing more sophisticated things with it, so I'll celebrate this as
my opportunity to say I did something you didn't see coming for 2012.

All the sites involved will happily shred those scripts and rewrite for
either normalized queries *or* better I/O timing info though, so net
positive for 9.2 changes even if this part breaks on them. I think this
is one of those rare opportunities where there's enough positive
goodwill from changes to ask "what's the best way to handle this
long-term?" and get away with whatever change that requires, too. I'm
really not liking the look of this wart now that Robert has pointed it out.

I'd prefer to see at least usec resolution and 8 bytes of "dynamic
range" for query related statistics. Any of these would be fine from a
UI perspective to me:

-float8 seconds
-float8 msec
-float8 usec
-int8 usec

I don't think int8 msec will be enough resolution to time queries for
very long, if it's not already obsolete. The committed example for
pg_test_timing on good hardware already clocks trivial events at a
single usec. Even I/O is getting there. I've measured my Fusion-io
loaner card peaking at 8GB/s, which works out to 1 usec per 8K page.
None of that is even price no object hardware today; it's the stuff
sitting in my office.

If anything, I'd expect more timing code in the database that only has
ms resolution right now will start looking fat in a year or two, and
more things might need to be shifted to usec instead. Checkpoint timing
can survive having less resolution because its primary drumbeat is very
unlikely to drop below the minutes range.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com