MIME-Version: 1.0
References: <2e9b2b7b-79a1-a7f2-e05e-9cefda601c09@gmx.net>
In-Reply-To: <2e9b2b7b-79a1-a7f2-e05e-9cefda601c09@gmx.net>
From: Muhammad Imtiaz <imtiaz.m@bitnine.net>
Date: Wed, 15 May 2024 09:21:52 +0500
Message-ID: <CANwR1OSB93AwAqNfwheCq6xA5KWfpJqh3BkuU0Ta-wPzVzZUYg@mail.gmail.com>
Subject: Re: pg_stat_io clarifications: background worker, writes and reads
To: Dimitrios Apostolou <jimis@gmx.net>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f17eaa061876755c"
Archived-At: <https://www.postgresql.org/message-id/CANwR1OSB93AwAqNfwheCq6xA5KWfpJqh3BkuU0Ta-wPzVzZUYg%40mail.gmail.com>
Precedence: bulk

--000000000000f17eaa061876755c
Content-Type: text/plain; charset="UTF-8"

Hi,

In PostgreSQL, the pg_stat_io view provides detailed statistics on I/O
operations. Background process perform maintenance tasks and other
background 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 by
these background workers are recorded.

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 that it is
> not autovacuum or bgwriter. And I'm not aware of any extension I might
> have with registered background worker.
>
> Additionally, how can it be evictions > writes? I would expect every
> 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" in
> 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 fetched from
> the disk (or OS buffercache)?
>
>     backend_type    |    object     | context |  reads  |  read_time  |
> writes | write_time | writebacks | writeback_time | extends | extend_time |
> op_bytes |   hits    | evictions | reuses | fsyncs | fsync_time |
> stats_reset
>
> -------------------+---------------+---------+---------+-------------+--------+------------+------------+----------------+---------+-------------+----------+-----------+-----------+--------+--------+------------+-------------------------------
>   background worker | relation      | normal  | 5139575 | 2196288.011 |
> 63277 |    1766.94 |          0 |              0 |       0 |           0 |
>    8192 | 876913705 |   5139653 |        |      0 |          0 | 2024-04-08
> 08:50:02.971192+00
>
>
> Thank you in advance,
> Dimitris
>
>
>
>

--000000000000f17eaa061876755c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div dir=3D"auto">Hi,</div><div dir=3D"auto"><br></div><d=
iv dir=3D"auto">In PostgreSQL, the pg_stat_io view provides detailed statis=
tics on I/O operations. Background process perform maintenance tasks and ot=
her background operations essential to the functioning of the PostgreSQL da=
tabase.</div><div dir=3D"auto">They include processes such as:</div><div di=
r=3D"auto"><br></div><div dir=3D"auto">1. Autovacuum Workers</div><div dir=
=3D"auto">2. WAL Writer</div><div dir=3D"auto">3. Background Writer</div><d=
iv dir=3D"auto">4. Logical Replication Workers</div><div dir=3D"auto">5. Cu=
stom Background Workers</div><div dir=3D"auto"><br></div><div dir=3D"auto">=
In the pg_stat_io view, statistics related to I/O operations performed by t=
hese background workers are recorded.=C2=A0</div><div dir=3D"auto"><br></di=
v><div dir=3D"auto">Regards,</div><div dir=3D"auto">Imtiaz</div><div dir=3D=
"auto"><br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" clas=
s=3D"gmail_attr">On Wed, 15 May 2024, 01:26 Dimitrios Apostolou, &lt;<a hre=
f=3D"mailto:jimis@gmx.net">jimis@gmx.net</a>&gt; wrote:<br></div><blockquot=
e class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc sol=
id;padding-left:1ex">Hello list,<br>
<br>
what is the &quot;background worker&quot; in the pg_stat_io statistics view=
? I&#39;m<br>
reading the documentation but can&#39;t figure this one out knowing that it=
 is<br>
not autovacuum or bgwriter. And I&#39;m not aware of any extension I might<=
br>
have with registered background worker.<br>
<br>
Additionally, how can it be evictions &gt; writes? I would expect every<br>
eviction to cause a write.<br>
<br>
Finally about &quot;hits&quot;, I understand they are reads found in shared=
_buffers,<br>
so they never registered into the &quot;reads&quot; counter. So is &quot;re=
ads&quot; in<br>
pg_stat_io the equivalent to misses, i.e. the opposite of &quot;hits&quot;,=
 the read<br>
attempts not found in the shared_buffers, that needed to be fetched from<br=
>
the disk (or OS buffercache)?<br>
<br>
=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 | writes | writ=
e_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<br>
-------------------+---------------+---------+---------+-------------+-----=
---+------------+------------+----------------+---------+-------------+----=
------+-----------+-----------+--------+--------+------------+-------------=
------------------<br>
=C2=A0 background worker | relation=C2=A0 =C2=A0 =C2=A0 | normal=C2=A0 | 51=
39575 | 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<br>
<br>
<br>
Thank you in advance,<br>
Dimitris<br>
<br>
<br>
<br>
</blockquote></div>

--000000000000f17eaa061876755c--