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

On Tue, Mar 27, 2012 at 8:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Mar 27, 2012 at 3:22 PM, Ants Aasma <ants(at)cybertec(dot)at> wrote:
>> On Tue, Mar 27, 2012 at 9:58 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> I've committed the core of this.  I left out the stats collector
>>> stuff, because it's still per-table and I think perhaps we should back
>>> off to just per-database.  I changed it so that it does not conflate
>>> wait time with I/O time.  Maybe there should be a separate method of
>>> measuring wait time, but I don't think it's a good idea for the
>>> per-backend stats to measure a different thing than what gets reported
>>> up to the stats collector - we should have ONE definition of each
>>> counter.  I also tweaked the EXPLAIN output format a bit, and the
>>> docs.
>>
>> Thank you for working on this.
>>
>> Taking a fresh look at it, I agree with you that conflating waiting
>> for backend local IO, and IO performed by some other backend might
>> paint us into a corner. For most workloads the wait for IO performed
>> by others should be the minority anyway.
>>
>> I won't really miss the per table stats. But if the pg_stat_statements
>> normalisation patch gets commited, it would be really neat to also
>> have IO waits there. It would be much easier to quickly diagnose "what
>> is causing all this IO" issues.
>
> So, the pg_stat_statements part of this is committed now.  Do you want
> to prepare a revised patch to add per-database counters to the
> statistics collector?  I think that might be a good idea...

Hearing nothing further on this point, I went and did it myself.

In the process I noticed a couple of things that I think we need to fix.

Currently, the statistics views that include timing information are
displayed in milliseconds (see pg_stat_user_functions), while the
underlying SQL-callable functions return the data in microseconds.
Whether or not this was a good design decision, we're stuck with it
now; the documentation implies that the views and functions use the
same units. I'll go fix that next.

Meanwhile, pg_stat_statements converts the same data to seconds but
makes it a double rather than a bigint. I think that was a bad idea
and we should make it consistent use a bigint and milliseconds while
we still can.

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