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

On Tue, Apr 10, 2012 at 10:06 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Hmm.  So, on further review, this is not as simple as it seems.  I'd
>> like some input from other people on what we should do here.
>
>> pg_stat_statements has long exposed a column called "total_time" as a
>> float8.  It now exposes columns "time_read" and "time_write" which are
>> actually measuring the time spent reading and writing data blocks, and
>> those are also exposed as a float8; all these count seconds.
>
>> Meanwhile, all times exposed by the stats collector (including the new
>> and analagous pg_stat_database.block_read_time and
>> pg_stat_database.block_write_time columns) are exposed as int8; these
>> count milliseconds.
>
>> So, should we make the new columns exposed by pg_stat_statements use
>> milliseconds, so that the block read/write timings are everywhere in
>> milliseconds, or should we keep them as a float8, so that all the
>> times exposed by pg_stat_statements use float8?
>
> Given that we've whacked pg_stat_statements' behavior around rather
> thoroughly in this release, maybe we could get away with redefining
> total_time as being measured in msec rather than sec, thereby aligning
> units as msec across the board.  It's arguably a smaller deal than the
> way we've redefined what the query column contains...

Retyping columns is an awfully good way to produce grumpy users. Then
again, if we're going to do it, it would certainly be better to do it
now rather than later, because right now I'm guessing
pg_stat_statements is a lot less heavily used than it will be after
9.2 hits shelves.

> float8 vs int8 is a distinct issue, and probably one that is not as
> much of an impact on clients if we change it.  It is not hard to predict
> that somebody will eventually want sub-msec resolution on these things,
> which would suggest that float8 would be the better idea.  But perhaps
> we could leave that change for a future release.

Well, internally, the I/O timing stuff as well as the function timing
stuff use microseconds, and the SQL functions expose it as
microseconds, but then the view divides by 1000 to convert to
milliseconds. I made the I/O timing stuff do it that way because
that's how the function timing stuff does it, but it does seem a
little random. One thing in its favor is that it provides a way for
users to get this if they want it, without screwing readability for
the vast majority who don't care.

On the flip side, the new checkpoint timing stuff is in milliseconds
all the way through, though it seems vanishingly unlikely that anyone
needs more resolution in that case. We have lots of other things in
milliseconds, too.

No matter what we end up doing here it will be consistent with
something; I am reminded of the phrase "the good thing about standards
is that there are so many to choose from...".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company