From: Andres Freund <andres@anarazel.de>
To: Drouvot, Bertrand <bertranddrouvot.pg@gmail.com>
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: Wed, 8 Mar 2023 16:34:38 -0800
Message-ID: <20230309003438.rectf7xo7pw5t5cj@awork3.anarazel.de> (raw)
In-Reply-To: <7b450eef-8717-ce4a-a9c8-ccab24ecd89a@gmail.com>
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>
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:
> > > > Now I've a second thought: what do you think about resetting the related number
> > > > of operations and *_time fields when enabling/disabling track_io_timing? (And mention it in the doc).
> > > >
> > > > That way it'd prevent bad interpretation (at least as far the time per operation metrics are concerned).
> > > >
> > > > Thinking that way as we'd loose some (most?) benefits of the new *_time columns
> > > > if one can't "trust" their related operations and/or one is not sampling pg_stat_io frequently enough (to discard the samples
> > > > where the track_io_timing changes occur).
> > > >
> > > > But well, resetting the operations could also lead to bad interpretation about the operations...
> > > >
> > > > Not sure about which approach I like the most yet, what do you think?
> > >
> > > Oh, this is an interesting idea. I think you are right about the
> > > synchronization issues making the statistics untrustworthy and, thus,
> > > unuseable.
> >
> > 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.
> - There is the risk mentioned above of bad interpretations for the "time per operation" metrics.
>
> - Even if there is frequent enough sampling of it pg_stat_io, one does not know which samples contain track_io_timing changes (at the cluster or session level).
You'd just make the same use of them you do with pg_stat_database.blks_read
etc today.
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. The point
of having the time available is that it provides information that the number
of operations doesn't provide.
> > I think we simply shouldn't do anything here. This is a pre-existing issue.
>
> Oh, never thought about it. You mean like for pg_stat_database.blks_read and pg_stat_database.blk_read_time for example?
Yes.
Greetings,
Andres Freund
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: andres@anarazel.de, bertranddrouvot.pg@gmail.com, 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: <20230309003438.rectf7xo7pw5t5cj@awork3.anarazel.de>
* 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