Re: Add per-backend AIO statistics
Re: Add per-backend AIO statistics
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. -- Michael
Re: Add per-backend AIO statistics
От:
Andres Freund <andres@anarazel.de>
Дата:
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, 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) 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. 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. Greetings, Andres Freund
Re: Add per-backend AIO statistics
От:
Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Дата:
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
Re: Add per-backend AIO statistics
От:
Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Дата:
Hi, On Thu, Jul 09, 2026 at 04:19:26AM +0000, Bertrand Drouvot wrote: > Hi, > > On Wed, Jul 08, 2026 at 02:08:00PM -0400, Andres Freund wrote: > > > 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? Actually, there is no rush to merge the per-backend AIO stats (we still have plenty of time for 20). So let's try option 2 and implement the new design first and see where it goes. I'll create a dedicated thread once ready. Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com
Re: Add per-backend AIO statistics
От:
solai v <solai.cdac@gmail.com>
Дата:
Hi, I tested the patch "v1-0001-Add-per-backend-AIO-statistics.patch". The patch applied cleanly without conflicts. I rebuilt PostgreSQL, installed the patched binaries, initialized a fresh cluster using initdb, and verified that the new function pg_stat_get_backend_aio() was successfully added and available. For functional testing, I first queried the new function before generating any workload: started = 74 executed_sync = 73 executed_async = 1 completed_self = 73 completed_other = 0 handle_waits = 0 submitted = 1 To generate AIO activity, I created a table with 1,000,000 rows, ran ANALYZE, and executed SELECT COUNT(*) on the table. After the workload, I queried pg_stat_get_backend_aio(pg_backend_pid()) again and observed: started = 26632 executed_sync = 137 executed_async = 26495 completed_self = 224 completed_other = 0 handle_waits = 0 submitted = 26480 The results show a significant increase in the started, executed_async, and submitted counters after the workload, which is consistent with the expected increase in asynchronous I/O activity. The completed_other and handle_waits counters remained at zero during my testing, which was expected for this workload. Overall, the new function returned the expected per-backend AIO statistics, and the observed values reflected the generated workload. I did not encounter any build or functional issues while testing the patch. Regards Solai
check-pgactivity updated to version 2.11-1.pgdg+1
The package check-pgactivity was updated on apt.postgresql.org. apt-listchanges: Changelogs --------------------------- check-pgactivity (2.11-1.pgdg+1) sid-pgdg; urgency=medium * Rebuild for sid-pgdg. * No source changes. -- PostgreSQL on Debian and Ubuntu Tue, 01 Sep 2026 13:34:30 +0200 check-pgactivity (2.11-1) unstable; urgency=medium * New upstream version 2.11. -- Christoph Berg Tue, 01 Sep 2026 13:34:30 +0200 New version 2.11-1.pgdg+1: check-pgactivity | 2.11-1.pgdg+1 | sid-pgdg | amd64, arm64, loong64, ppc64el, source check-pgactivity | 2.10-1.pgdg+1 | sid-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg+1 | sid-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.11-1.pgdg14+1 | forky-pgdg | amd64, arm64, loong64, ppc64el, source check-pgactivity | 2.10-1.pgdg14+1 | forky-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg14+1 | forky-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.11-1.pgdg13+1 | trixie-pgdg | amd64, arm64, loong64, ppc64el, source check-pgactivity | 2.10-1.pgdg13+1 | trixie-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg13+1 | trixie-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.11-1.pgdg12+1 | bookworm-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.10-1.pgdg12+1 | bookworm-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg12+1 | bookworm-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.10-1.pgdg11+1 | bullseye-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg11+1 | bullseye-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.11-1.pgdg26.10+1 | stonking-pgdg | amd64, source check-pgactivity | 2.11-1.pgdg26.04+1 | resolute-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.10-1.pgdg26.04+1 | resolute-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg26.04+1 | resolute-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.11-1.pgdg24.04+1 | noble-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.10-1.pgdg24.04+1 | noble-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg24.04+1 | noble-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.11-1.pgdg22.04+1 | jammy-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.10-1.pgdg22.04+1 | jammy-pgdg | amd64, arm64, ppc64el, source check-pgactivity | 2.9-1.pgdg22.04+1 | jammy-pgdg | amd64, arm64, ppc64el, source The public mirrors serving apt.postgresql.org are synced hourly, the updated packages will be available there shortly.