Re: Track IO times in pg_stat_io

Hi,

On 3/9/23 1:34 AM, Andres Freund wrote:
> Hi,
>
> On 2023-03-08 12:55:34 +0100, Drouvot, Bertrand wrote:
>> On 3/7/23 7:47 PM, Andres Freund wrote:
>>> On 2023-03-07 13:43:28 -0500, Melanie Plageman wrote:
>>> No, I don't think we can do that. It can be enabled on a per-session basis.
>>
>> Oh right. So it's even less clear to me to get how one would make use of those new *_time fields, given that:
>>
>> - pg_stat_io is "global" across all sessions. So, even if one session is doing some "testing" and needs to turn track_io_timing on, then it
>> is even not sure it's only reflecting its own testing (as other sessions may have turned it on too).
>
> I think for 17 we should provide access to per-existing-connection pg_stat_io
> stats, and also provide a database aggregated version. Neither should be
> particularly hard.
>

+1 that would be great.
>
> I don't think it's particularly useful to use the time to calculate "per IO"
> costs - they can vary *drastically* due to kernel level buffering.

Exactly and I think that's the reason why it could be useful. I think that could help (with frequent enough sampling)
to try to identify when the IOs are served by the page cache or not (if one knows his infra well enough).

One could say (for example, depending on his environment) that if the read_time > 4ms then the IO is served by spindle disks (if any)
and if <<< ms then by the page cache.

What I mean is that one could try to characterized their IOs based on threshold that they could define.

Adding/reporting histograms in the game would be even better: something we could look for for 17?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com