Re: Add per-backend AIO statistics - Mailing list pgsql-hackers

Hi,

On Wed, Jul 08, 2026 at 02:08:00PM -0400, Andres Freund wrote:
> Hi,
> 
> On 2026-07-08 15:52:20 +0900, Michael Paquier wrote:
> > On Tue, Jul 07, 2026 at 11:02:03AM +0000, Bertrand Drouvot wrote:
> > > 1/ pg_aios that lists all AIO handles that are currently in use. That shows
> > > what's happening right now, but not what has happened.
> > >
> > > 2/ pg_stat_get_backend_io() that shows how much IO was done, but not how it
> > > was done. There's no way to see whether IOs ran synchronously or
> > > asynchronously, whether a backend was stalling on handle exhaustion, or how
> > > completions are distributed across backends.
> >
> > While the information may be useful, one thing that sounds very
> > important to me is how this impacts workloads by default.
> 
> 
> > Andres is usually able to catch bottlenecks that everybody else is
> > unable to see, so perhaps checking with him the location of these
> > extra function calls would be a good first step.  Your proposal goes
> > down to pgaio_io_stage(), pgaio_io_process_completion() and
> > pgaio_submit_staged() to track these counter increments.
> 
> I think the overhead might be ok,

Thanks for the feedback.

> but I am rather doubtful that all of this
> information is actually useful. You're adding quite a few counters for each
> IO, do we actually need that?
> 
> E.g. what do we gain from counting:
> - started (if you want to see the number of IOs that are in progress,
>   cumulative stats are the wrong tool)
> - executed_async (that's just the number of IOs minus executed_sync)
> - completed_self (that's just the number of IOs minus executed_other)

Yeah, we can remove some fields (as they're derivable).

> Separately, I'm doubtful it makes sense to have only per-backend stats for
> this. I think you'd almost always want the stats for exited backend
> (e.g. parallel workers) too.

Indeed, adding a global view would capture their activity.

> Unfortunately I'm pretty doubtful that pgstat_backend.c is the right
> architectural direction. It'll just end up implementing all kinds of stats,
> since we'll incrementally want more and more per-backend stats.  I think what
> we'd want is rather something where for each applicable stats kind we have a
> shared counter for all exited backends and then per-backend counters for live
> backends, with helpers to aggregate the exited + live stats to a total.

That's a very nice proposal that would avoid the double counting. OTOH, that's
also a major re-design that would benefit all existing per-backend stats kinds.

I can see 2 options:

1/ 

step 1: Implement per-backend AIO stats (like proposed taking into account your
remark about useless, derivable fields) + a global view. 
step 2: work on the re-design

2/

step 1: work on the redesign
step 2: Add AIO stats based on the re-design

The pros of 1/ is that step 1 would most probably land in 20, providing more user
visibility (+ it could be used or improved during the AIO write project). Step 2
is a much larger project that might not land in 20.

The cons, would be double counting (as there is no need to try to implement
something like [1] as we are going to re-design anyway).

I'll be tempted to vote for 1/ to provide faster added value. What do you (Andres,
Michael) think?

[1]: https://postgr.es/m/aNVWe2tR1jj5Tsct@ip-10-97-1-34.eu-west-3.compute.internal

Regards,

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