Date: Wed, 15 May 2024 14:59:39 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Muhammad Imtiaz <imtiaz.m@bitnine.net>, 
    Kashif Zeeshan <kashi.zeeshan@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: pg_stat_io clarifications: background worker, writes and reads
In-Reply-To: <CANwR1OSB93AwAqNfwheCq6xA5KWfpJqh3BkuU0Ta-wPzVzZUYg@mail.gmail.com>
Message-ID: <12733373-8f3c-550e-771d-fa7b5ff9343d@gmx.net>
References: <2e9b2b7b-79a1-a7f2-e05e-9cefda601c09@gmx.net> <CANwR1OSB93AwAqNfwheCq6xA5KWfpJqh3BkuU0Ta-wPzVzZUYg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; BOUNDARY="0-1462144763-1715777923=:19344"
Content-ID: <881c0afe-a063-1113-9684-a2d2d9b049f7@NO.WHERE>
UI-OutboundReport: notjunk:1;M01:P0:zo2bg6qEoko=;3u1zCiUQlkq39B7swxk/HBbPf0D
 DWa5vh/RRvzanPKYwk5iQL4DxsfRiCtFwsKQfZ7nwmlqyJdVHOKAK6rslJSDANxDIh5jYzfdr
 EudOltoK0OjdtzvsucpevEBAxkFMIpxs163jRAXGOyeNOIf/l0o6j8SXJkS415TGYB/wpfWPn
 0BUuNfG8VYG4WMjVBOec+g63/VW+9aWkomtqxgHZHY7BGDR0X4De9QmXaGMb8COkip5D5F5Ss
 FcNzl3J13N/voa73upFcHb4fZRo9WzIpBOuc9/HzkhNgztr76J+OZ+KGdyN2gmr8JXTI1GgER
 5PfjOLmQjrCYpisQaNE5Nb++VFKO+qDVr6SSoXp9T8iMPZypL9zQV4wOr2hcnm1pqYGn7pOIH
 wg85/kWhOzZwZIs5hyD7aZ2T1SZBptZwJ5h5JZE9Y8KQxhW8PGoqzaa8L53dccY62NSAPYxrl
 gNAjNAijkKS6foZL2Lc48Dcn86XEgNQX3t+HrtR8gtCLV8JQxaE2Ugqwz79saDcJqHaZHiXiB
 6ciIw7XW+uVvBY12/f8jIuG9CHoLOjs8p47qRq+V/cSlrk8HH2rhXXcPEh3927WI575juU6jx
 ZWE+E1yYFz2Ht4HqiuIIJ+wZAFETm4Ud8EHT5c+Ef8pupRk1Byv/xnIGe0gqOXd9Ir/Ok1+YN
 MAECEZNJVFTRPHluVgxo8O6SoR3h2mWm3YSqjfW8Teh+XYCsyP+Dgo3H+6S5bnPxLAWeT6qcO
 /NqYHw3dQTwwbj2crBGlwV8gsEjMhLJrL8rFNTgRtouMb8Dxjo8wFBmpTqCKtKsq2OYxfn5Ko
 f4xl24+QOcT6veUMCkk/h/vS3l304hJCOsFuLK13juLAw38zghDwIiC26DQDXzifhsHxyU1eO
 qZyEAVdFJ+Gc5Rw9hho0TEVIe3QvMReDYdbQ=
Archived-At: <https://www.postgresql.org/message-id/12733373-8f3c-550e-771d-fa7b5ff9343d%40gmx.net>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-1462144763-1715777923=:19344
Content-Type: text/plain; CHARSET=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-ID: <abc556a4-f993-3097-7a2b-0d38308548b9@NO.WHERE>

So what is this particular "background worker" I'm seeing, given that I
have no replication or extensions?

Searching the logs I found entries like the following:

LOG:  background worker "parallel worker" (PID 93384) exited with exit cod=
e 1

This got logged when I killed a simple SELECT query that took too long
doing parallel seqscans. Could it be that the entry in pg_stat_io named
"background worker" also includes the parallel workers from a SELECT
query?

Thanks,
Dimitris

On Wed, 15 May 2024, Muhammad Imtiaz wrote:

> Hi,
>
> In PostgreSQL, the pg_stat_io view provides detailed statistics on I/O o=
perations. Background process perform maintenance tasks and other backgrou=
nd operations essential to the functioning of the PostgreSQL database.
> They include processes such as:
>
> 1. Autovacuum Workers
> 2. WAL Writer
> 3. Background Writer
> 4. Logical Replication Workers
> 5. Custom Background Workers
>
> In the pg_stat_io view, statistics related to I/O operations performed b=
y these background workers are recorded.=C2=A0
>
> Regards,
> Imtiaz
>
>
> On Wed, 15 May 2024, 01:26 Dimitrios Apostolou, <jimis@gmx.net> wrote:
>       Hello list,
>
>       what is the "background worker" in the pg_stat_io statistics view?=
 I'm
>       reading the documentation but can't figure this one out knowing th=
at it is
>       not autovacuum or bgwriter. And I'm not aware of any extension I m=
ight
>       have with registered background worker.
>
>       Additionally, how can it be evictions > writes? I would expect eve=
ry
>       eviction to cause a write.
>
>       Finally about "hits", I understand they are reads found in shared_=
buffers,
>       so they never registered into the "reads" counter. So is "reads" i=
n
>       pg_stat_io the equivalent to misses, i.e. the opposite of "hits", =
the read
>       attempts not found in the shared_buffers, that needed to be fetche=
d from
>       the disk (or OS buffercache)?
>
>       =C2=A0 =C2=A0 backend_type=C2=A0 =C2=A0 |=C2=A0 =C2=A0 object=C2=
=A0 =C2=A0 =C2=A0| context |=C2=A0 reads=C2=A0 |=C2=A0 read_time=C2=A0 | w=
rites | write_time | writebacks | writeback_time | extends | extend_time |=
 op_bytes |=C2=A0 =C2=A0hits=C2=A0 =C2=A0 | evictions | reuses | fsyncs | =
fsync_time |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stats_reset
>       -------------------+---------------+---------+---------+----------=
---+--------+------------+------------+----------------+---------+--------=
-----+----------+-----------+-----------+--------+--------+------------+--=
-----------------------------
>       =C2=A0 background worker | relation=C2=A0 =C2=A0 =C2=A0 | normal=
=C2=A0 | 5139575 | 2196288.011 |=C2=A0 63277 |=C2=A0 =C2=A0 1766.94 |=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 0 |=C2=A0 =C2=A0 =C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A00 |=C2=A0 =C2=A0 =C2=A08192 | 876913705 |=C2=A0 =C2=A05139653=
 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 0 |=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 0 | 2024-04-08 08:50:02.971192+00
>
>
>       Thank you in advance,
>       Dimitris
>
>
>
>
>

--0-1462144763-1715777923=:19344--