MIME-Version: 1.0
References: <D6MJOHS7HZ80.3B17NDGUV6T47@jeltef.nl> <Z3LII9YEgp3kAZG-@momjian.us>
 <3861403.1735576764@sss.pgh.pa.us> <D6PENGW1PL79.266GQNPSMRIGA@jeltef.nl>
 <1f22794321b745549d54359d343e37b8@oss.nttdata.com> <D6V9GJ2LOLC5.1ZF3BWKKEBZWC@jeltef.nl>
 <CAM6-o=BE=oewSsdNKrbbNjR2muzQR49STXsuta+Wpq6CSyxTag@mail.gmail.com>
 <2c9d6eaf26df17bec13bb03bf1e9bcbb@oss.nttdata.com> <CAGECzQRvcLx44N3zd_DGCjY02XX4AqXX8mq4BiS8C9Froy+Jhg@mail.gmail.com>
 <CAGECzQQdc-k=M2fMCKa98kVZntc=6d3rpd6edt8Qs45cayfUeQ@mail.gmail.com>
 <myw76agwrlmisvamwbfl6ibxgwh5glzitydiwnfmtb5aui232i@274yxxtnbnsp> <2035079.1739124342@sss.pgh.pa.us>
In-Reply-To: <2035079.1739124342@sss.pgh.pa.us>
From: Jelte Fennema-Nio <postgres@jeltef.nl>
Date: Sun, 9 Feb 2025 21:06:02 +0100
Message-ID: <CAGECzQTm6oEEY4yO_FO0ZBtUuJX+pYiXhw2GPPRMzq_5DP5_fQ@mail.gmail.com>
Subject: Re: RFC: Allow EXPLAIN to Output Page Fault Information
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andres Freund <andres@anarazel.de>, torikoshia <torikoshia@oss.nttdata.com>, 
	pgsql-hackers@postgresql.org, rjuju123@gmail.com, 
	Bruce Momjian <bruce@momjian.us>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAGECzQTm6oEEY4yO_FO0ZBtUuJX%2BpYiXhw2GPPRMzq_5DP5_fQ%40mail.gmail.com>
Precedence: bulk

On Sun, 9 Feb 2025 at 19:05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Andres Freund <andres@anarazel.de> writes:
> > I'm somewhat against this patch, as it's fairly fundamentally incompatible
> > with AIO. There's no real way to get information in this manner if the IO
> > isn't executed synchronously in process context...

Hmm, I had not considered how this would interact with your AIO work.
I agree that getting this info would be hard/impossible to do
efficiently, when IOs are done by background IO processes that
interleave IOs from different queries. But I'd expect that AIOs that
are done using iouring would be tracked correctly without having to
change this code at all (because I assume those are done from the
query backend process).

One other thought: I think the primary benefit of this feature is
being able to see how many read IOs actually hit the disk, as opposed
to hitting OS page cache. That benefit disappears when using Direct
IO, because then there's no OS page cache.

How many years away do you think that widespread general use of
AIO+Direct IO is, though? I think that for the N years from now until
then, it would be very nice to have this feature to help debug query
performance problems. Then once the numbers become too
inaccurate/useless at some point, we could simply remove them again.

> Even without looking ahead to AIO, there's bgwriter, walwriter, and
> checkpointer processes that all take I/O load away from foreground
> processes.  I don't really believe that this will produce useful
> numbers.

The bgwriter, walwriter, and checkpointer should only take away
*write* IOs. For read IOs the numbers should be very accurate and as
explained above read IOs is where I think the primary benefit of this
feature is.

But even for write IOs I think the numbers would be useful when
looking at them with the goal of finding out why a particular query is
slow: If the bgwriter or checkpointer do the writes, then the query
should be roughly as fast as if no writes to the disk had taken place
at all, but if the query process does the writes then those writes are
probably blocking further execution of the query and thus slowing it
down.