Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEozW-001Dvb-5F for pgsql-admin@arkaria.postgresql.org; Fri, 31 Oct 2025 13:16:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vEozU-00EiGa-9o for pgsql-admin@arkaria.postgresql.org; Fri, 31 Oct 2025 13:16:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vElm0-00DgPk-5B for pgsql-admin@lists.postgresql.org; Fri, 31 Oct 2025 09:50:19 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vEllw-005E60-1W for pgsql-admin@lists.postgresql.org; Fri, 31 Oct 2025 09:50:18 +0000 Received: by mail-oo1-xc33.google.com with SMTP id 006d021491bc7-6504c33afb1so1125536eaf.1 for ; Fri, 31 Oct 2025 02:50:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=dbtune.com; s=google; t=1761904214; x=1762509014; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=R9a7TFOB5jSpsycOQFgULMalU95lgh/NHYICN5nz/CQ=; b=gLbdol2Qh9b1TwmlfPKEJz9uMXSPC6bM/t5XGgwiNbzx1NW1KS1KpbRXVi6yUQ6E2+ 4GfQtlaihmA/QZym0sQ3Csh1XG9KveUaCqTtsHgRkz2wi8zHSd32mx8ZR3YUuLOd0aBA FrX8DMDe1Sq/NnfsGLNV8DC12jTGA21gpvgl11zC1hWbx0Lc6IZ4EJsqKdBq1yyUiM5V jLwzkF17nd3j9eThgwCr5OzRi60/K5u28DQqnD8vnl3+N86oZgJTSy9E+WhHnhKgVi+m EEu+m9wn7HmeeXgA7UWrCNV9qU2DmcM1ah+6IG/F1c2YaKQYO2oijVM7Y2OS9j2C3G8M DUbg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761904214; x=1762509014; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=R9a7TFOB5jSpsycOQFgULMalU95lgh/NHYICN5nz/CQ=; b=q1DgvcDJAw0xkHvFmnDjJq1/SZGKQKCfMxgZcCV7DuZP78vqpN6h47dyg4sckw2gY1 HJVSgaVJ4EPZmoPL3RGzEFe8izlqKvK+CI19jxya4pLHwJyZv3F5VCWZSylechLY2oj0 eMysACbWoQQVI3Xyaeul9Hqp1zvREv2rZCy0DUHPIV13GtOz6tsoM/WhkEONa+qO7kVO YqEF8wV4y0jOy6CDaIgkyF4jssWzSOSase5gcQ/zXCMRZDjiZo3HpuscVK2HUlpt9v4K g6/xjq6DN99Je8YtTLqNlIwF0cLsfQ5ilRVKXwyModqZ40PZ7xdRoNmrtJTcZLt+hEdY 2djw== X-Gm-Message-State: AOJu0YxOB4vNEs1RXWXL0H7sYoBu+712KkU6g5B82flS2KCFPqMMqkh4 t8My0x5P/ZMLVun77VGtvcSR/GRuBC4RBYiNB3obSni3+N5/Cr7LeCCDnCQUcPVeXu+ZbpfgYIq +hjm7i4JctU7FzQHx9sshkoFc1bJQU5sEPUyKFGGRAg== X-Gm-Gg: ASbGncscbF+foi02RcZhZ0mjIW8n5n7PbahLbXVWHf5v32/+yieX3WJRr59UAtM8Qwk pTERHpplS2dQZEUjCalrQe8hWBN58pnVc2gJgP003BeE1KlXlCVaSHnQcc2jH+ZpgZjw2YcIl8D P5X44kW4yA9lRHzMLA/mVPXy0oNiMexg78rHlSuFHcH0jJUaAxkJoN0DdacBewEbvCOgdMi8c1D qyA745fdVuzvUftIUALVN5VxcvufS4/uNM5Yi683Pg5NKzXUanZHkjb7eAsCXoQVfMXSMEW X-Google-Smtp-Source: AGHT+IHZOPuQmuG9Vl6e+QZsQOYEYxPYeeIL6hk6AEparA+d+t35jGZSn0u79QVoUh1YqwsZMKRNiXhUG6hMa+nYg+s= X-Received: by 2002:a05:6820:258a:b0:651:c2cf:8c8c with SMTP id 006d021491bc7-65681987ac0mr2363159eaf.0.1761904214500; Fri, 31 Oct 2025 02:50:14 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Shardul Borhade Date: Fri, 31 Oct 2025 10:50:02 +0100 X-Gm-Features: AWmQ_bkmR4E3DEVA0-o630JztTQO7NemUEezWe7kTTeNjH9w6umai9FeDrG8Z1o Message-ID: Subject: Re: Question on pg_stat_io showing zero reads/writes for I/O workers To: Xuneng Zhou Cc: pgsql-admin@lists.postgresql.org, pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000d5773e0642714ada" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d5773e0642714ada Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Xuneng Zhou = wrote: > Hi, > > On Fri, Oct 31, 2025 at 4:17=E2=80=AFPM Shardul Borhade > wrote: > > > > Hi team, > > > > I=E2=80=99m running into an issue with pg_stat_io. When I run the follo= wing > 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 wh= y > the stats aren=E2=80=99t being reflected in pg_stat_io? Do I need to enab= le 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 =3D '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 =3D '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 =3D 'client backend'; > > -- Check IO worker stats (will remain zero) > > SELECT SUM(reads) as total_reads > FROM pg_stat_io > WHERE backend_type =3D '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 > --000000000000d5773e0642714ada Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi Xuneng,

Thank you so much for the inc= redibly clear and detailed explanation. Your analysis of how I/O stats are = attributed at submission time was exactly the information I was missing, an= d it completely solved the mystery.

You were absolutely correct.= I followed your suggested test procedure, and it confirmed that AIO is ind= eed working as intended.

The key confirmation for me was checkin= g=C2=A0pg_stat_activity=C2=A0while running a heavy, cache-cleared sequential scan. Just as you pr= edicted, the=C2=A0wait_ev= ent=C2=A0for my=C2=A0c= lient backend=C2=A0process was=C2=A0AioIoCompletion

= =C2=A0pid | backend_type | wait_event_type | wait_event<= /p>

---------+----------------+-----------------+-----------------=
3052099 | client backend | IO | AioIoCompleti= on.=C2=A0

This was the "smoking gun" that prove= d the main process was delegating the I/O and waiting for the workers, rath= er than doing the reads itself.

Thanks again for= your help and for the excellent work on PostgreSQL.
=
Best Regards,

<= /div>
Shardul B

On Fri, Oct 31, = 2025 at 10:30=E2=80=AFAM Xuneng Zhou <xunengzhou@gmail.com> wrote:
Hi,

On Fri, Oct 31, 2025 at 4:17=E2=80=AFPM Shardul Borhade <shardul@dbtune.com> wrote:<= br> >
> Hi team,
>
> I=E2=80=99m running into an issue with pg_stat_io. When I run the foll= owing query:
>
> SELECT backend_type, reads, writes, read_time
> FROM pg_stat_io
> WHERE backend_type LIKE '%io%';
>
> I consistently get:
>
>=C2=A0 backend_type | reads | writes | read_time
> --------------+-------+--------+-----------
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2= =A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2= =A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2= =A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2= =A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2= =A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2= =A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2= =A0 =C2=A0 0 |
>=C2=A0 io worker=C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2= =A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00
> (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 w= hy the stats aren=E2=80=99t being reflected in pg_stat_io? Do I need to ena= ble 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&q= uot;

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 =3D 'client backend'
=C2=A0 AND reads > 0
ORDER BY reads DESC;

-- Verify IO workers exist:

SELECT pid, backend_type, wait_event
FROM pg_stat_activity
WHERE backend_type =3D '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 =3D 'client backend';

-- Check IO worker stats (will remain zero)

SELECT SUM(reads) as total_reads
FROM pg_stat_io
WHERE backend_type =3D '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:
=C2=A0 =C2=A0 return false;=C2=A0 // Don't show zero-value rows
or track actual IO worker stats?

Best,
Xuneng
--000000000000d5773e0642714ada--