Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Ants Aasma <ants(at)cybertec(dot)at>
Cc: Greg Smith <greg(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Patch: add timing of buffer I/O requests
Date: 2012-04-05 15:45:58
Message-ID: CA+TgmoYqLZK7x8UvacTsPq3N76gvQKczSsm4NfrYr7B3V8F2CA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Jeff JanesDate: 2012-04-05 15:51:06
Subject: Re: Publish checkpoint timing and sync files summary data to pg_stat_bgwriter
Previous:From: Tom LaneDate: 2012-04-05 15:39:19
Subject: Re: pg_upgrade improvements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group