public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shardul Borhade <[email protected]>
To: Xuneng Zhou <[email protected]>
Cc: [email protected]
Cc: pgsql-hackers <[email protected]>
Subject: Re: Question on pg_stat_io showing zero reads/writes for I/O workers
Date: Fri, 31 Oct 2025 10:50:02 +0100
Message-ID: <CA+xwDuiMi1zDQOhHjfKT1d9nUwkX2g=5Fyizc2GaVvA2BYw1Mg@mail.gmail.com> (raw)
In-Reply-To: <CABPTF7WOQfPk-eCDAZXfqTPSRTg6AbqNYbRjtauYDYbhrAbSWQ@mail.gmail.com>
References: <CA+xwDujmUnm_vbojvvRz87tuwTA90CZrC8HGor99Nv9RtJERRg@mail.gmail.com>
<CABPTF7WOQfPk-eCDAZXfqTPSRTg6AbqNYbRjtauYDYbhrAbSWQ@mail.gmail.com>
Hi Xuneng,
Thank you so much for the incredibly clear and detailed explanation. Your
analysis of how I/O stats are attributed at submission time was exactly the
information I was missing, and it completely solved the mystery.
You were absolutely correct. I followed your suggested test procedure, and
it confirmed that AIO is indeed working as intended.
The key confirmation for me was checking pg_stat_activity while running a
heavy, cache-cleared sequential scan. Just as you predicted, the wait_event for
my client backend process was *AioIoCompletion*
pid | backend_type | wait_event_type | wait_event
---------+----------------+-----------------+-----------------
3052099 | client backend | IO | AioIoCompletion.
This was the "smoking gun" that proved the main process was delegating the
I/O and waiting for the workers, rather than doing the reads itself.
Thanks again for your help and for the excellent work on PostgreSQL.
Best Regards,
Shardul B
On Fri, Oct 31, 2025 at 10:30 AM Xuneng Zhou <[email protected]> wrote:
> Hi,
>
> On Fri, Oct 31, 2025 at 4:17 PM Shardul Borhade <[email protected]>
> wrote:
> >
> > Hi team,
> >
> > I’m running into an issue with pg_stat_io. When I run the following
> query:
> >
> > SELECT backend_type, reads, writes, read_time
> > FROM pg_stat_io
> > WHERE backend_type LIKE '%io%';
> >
> > I consistently get:
> >
> > backend_type | reads | writes | read_time
> > --------------+-------+--------+-----------
> > io worker | 0 | 0 | 0
> > io worker | 0 | 0 | 0
> > io worker | 0 | 0 | 0
> > io worker | 0 | 0 | 0
> > io worker | 0 | 0 | 0
> > io worker | 0 | 0 | 0
> > io worker | | 0 |
> > io worker | 0 | 0 | 0
> > (8 rows)
> >
> > I tried running a heavy sequential scan on a 55 GB table as well as a
> bitmap heap scan, but the reads and writes columns still show zero.
> >
> > However, I can clearly observe performance differences when I tune the
> io_workers configuration, so I believe they are active.
> >
> > Am I missing something here? Could someone please help me understand why
> the stats aren’t being reflected in pg_stat_io? Do I need to enable any
> other server parameter to log this information?
>
> When your client backend issues a query:
>
> 1. Your backend calls `AsyncReadBuffers()` and counts the IO stats
> 2. The IO worker performs the actual I/O but doesn't increment its own
> counters
> 3. Stats accumulate under "client backend", not "io worker"
>
> Check your *client backend* stats instead:
>
> ```sql
> You should see reads/writes here:
>
> SELECT backend_type, context, object, reads, writes
> FROM pg_stat_io
> WHERE backend_type = 'client backend'
> AND reads > 0
> ORDER BY reads DESC;
>
> -- Verify IO workers exist:
>
> SELECT pid, backend_type, wait_event
> FROM pg_stat_activity
> WHERE backend_type = 'io worker';
>
>
> Quick Test
>
> -- Reset stats
>
> SELECT pg_stat_reset_shared('io');
>
> -- Run your heavy scan
>
> SELECT COUNT(*) FROM your_large_table;
>
> -- Check client backend stats (should increase)
>
> SELECT SUM(reads) as total_reads
> FROM pg_stat_io
> WHERE backend_type = 'client backend';
>
> -- Check IO worker stats (will remain zero)
>
> SELECT SUM(reads) as total_reads
> FROM pg_stat_io
> WHERE backend_type = 'io worker';
>
> See source code:
>
> - `src/backend/storage/buffer/bufmgr.c` (AsyncReadBuffers, line 1938)
> - stats counted at submission
> - `src/backend/utils/activity/pgstat_io.c` (pgstat_count_io_op, line
> 74) - uses submitter's MyBackendType
> - `src/backend/storage/aio/aio_io.c` (pgaio_io_perform_synchronously)
> - IO worker doesn't call stat functions
>
> However, this behavior seems not great here. If the above analysis is
> sound, should we add something like this to not track i/o worker
> // In pgstat_tracks_io_bktype()
> case B_IO_WORKER:
> return false; // Don't show zero-value rows
> or track actual IO worker stats?
>
> Best,
> Xuneng
>
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Question on pg_stat_io showing zero reads/writes for I/O workers
In-Reply-To: <CA+xwDuiMi1zDQOhHjfKT1d9nUwkX2g=5Fyizc2GaVvA2BYw1Mg@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox