public inbox for [email protected]help / color / mirror / Atom feed
Question on pg_stat_io showing zero reads/writes for I/O workers 5+ messages / 2 participants [nested] [flat]
* Question on pg_stat_io showing zero reads/writes for I/O workers @ 2025-10-31 08:17 Shardul Borhade <[email protected]> 2025-10-31 09:40 ` Re: Question on pg_stat_io showing zero reads/writes for I/O workers Xuneng Zhou <[email protected]> 2025-10-31 09:43 ` Re: Question on pg_stat_io showing zero reads/writes for I/O workers Xuneng Zhou <[email protected]> 2025-10-31 09:50 ` Re: Question on pg_stat_io showing zero reads/writes for I/O workers Shardul Borhade <[email protected]> 0 siblings, 3 replies; 5+ messages in thread From: Shardul Borhade @ 2025-10-31 08:17 UTC (permalink / raw) To: [email protected] 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? Thanks and regards, Shardul B ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on pg_stat_io showing zero reads/writes for I/O workers 2025-10-31 08:17 Question on pg_stat_io showing zero reads/writes for I/O workers Shardul Borhade <[email protected]> @ 2025-10-31 09:40 ` Xuneng Zhou <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Xuneng Zhou @ 2025-10-31 09:40 UTC (permalink / raw) To: [email protected] Hi, On Fri, Oct 31, 2025 at 5:30 PM 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 > Test message sending. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on pg_stat_io showing zero reads/writes for I/O workers 2025-10-31 08:17 Question on pg_stat_io showing zero reads/writes for I/O workers Shardul Borhade <[email protected]> @ 2025-10-31 09:43 ` Xuneng Zhou <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Xuneng Zhou @ 2025-10-31 09:43 UTC (permalink / raw) To: pgsql-hackers <[email protected]> Hi, On Fri, Oct 31, 2025 at 5:30 PM 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on pg_stat_io showing zero reads/writes for I/O workers 2025-10-31 08:17 Question on pg_stat_io showing zero reads/writes for I/O workers Shardul Borhade <[email protected]> @ 2025-10-31 09:50 ` Shardul Borhade <[email protected]> 2025-11-01 00:41 ` Re: Question on pg_stat_io showing zero reads/writes for I/O workers Xuneng Zhou <[email protected]> 2 siblings, 1 reply; 5+ messages in thread From: Shardul Borhade @ 2025-10-31 09:50 UTC (permalink / raw) To: Xuneng Zhou <[email protected]>; +Cc: [email protected]; pgsql-hackers <[email protected]> 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 > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on pg_stat_io showing zero reads/writes for I/O workers 2025-10-31 08:17 Question on pg_stat_io showing zero reads/writes for I/O workers Shardul Borhade <[email protected]> 2025-10-31 09:50 ` Re: Question on pg_stat_io showing zero reads/writes for I/O workers Shardul Borhade <[email protected]> @ 2025-11-01 00:41 ` Xuneng Zhou <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Xuneng Zhou @ 2025-11-01 00:41 UTC (permalink / raw) To: Shardul Borhade <[email protected]>; +Cc: [email protected]; pgsql-hackers <[email protected]> Hi, On Fri, Oct 31, 2025 at 5:50 PM Shardul Borhade <[email protected]> wrote: > > 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. Thanks for your question and kind words — it means a lot to me. I also think this is worth investigating further. Best, Xuneng > > 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-11-01 00:41 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-31 08:17 Question on pg_stat_io showing zero reads/writes for I/O workers Shardul Borhade <[email protected]> 2025-10-31 09:40 ` Xuneng Zhou <[email protected]> 2025-10-31 09:43 ` Xuneng Zhou <[email protected]> 2025-10-31 09:50 ` Shardul Borhade <[email protected]> 2025-11-01 00:41 ` Xuneng Zhou <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox