From: Drouvot, Bertrand <bertranddrouvot.pg@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Melanie Plageman <melanieplageman@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Maciek Sakrejda <m.sakrejda@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: smilingsamay@gmail.com, Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: Lukas Fittl <lukas@fittl.com>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: Track IO times in pg_stat_io
Date: Thu, 9 Mar 2023 09:20:39 +0100
Message-ID: <b6f62646-ff99-7d4e-7dce-0e662f6fdc97@gmail.com> (raw)
In-Reply-To: <20230309003438.rectf7xo7pw5t5cj@awork3.anarazel.de>
References: <CAAKRu_ay5iKmnbXZ3DsauViF3eMxu4m1oNnJXqV_HyqYeg55Ww@mail.gmail.com>
<e719378f-7602-8ef7-1c45-4bc3ffa6b461@gmail.com>
<CAAKRu_ZaDEYFY9YY5+dqWa64YurqnnkMkwSz5GMwj+-t13xXYA@mail.gmail.com>
<b5486aa0-e7bc-ada6-3ba0-03d3d29d5a8a@gmail.com>
<CAAKRu_bV70cbMiGTw2drMmgvu4UJcioQ1=H4eAQRCmAamVNCJw@mail.gmail.com>
<20230307184751.jdokdy2xmx3xpaua@awork3.anarazel.de>
<7b450eef-8717-ce4a-a9c8-ccab24ecd89a@gmail.com>
<20230309003438.rectf7xo7pw5t5cj@awork3.anarazel.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bertranddrouvot.pg@gmail.com, andres@anarazel.de, melanieplageman@gmail.com, m.sakrejda@gmail.com, pryzby@telsasoft.com, horikyota.ntt@gmail.com, lukas@fittl.com, magnus@hagander.net, alvherre@alvh.no-ip.org
Subject: Re: Track IO times in pg_stat_io
In-Reply-To: <b6f62646-ff99-7d4e-7dce-0e662f6fdc97@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox