public inbox for [email protected]
help / color / mirror / Atom feedpg_stat_io clarifications: background worker, writes and reads
6+ messages / 3 participants
[nested] [flat]
* pg_stat_io clarifications: background worker, writes and reads
@ 2024-05-14 20:26 Dimitrios Apostolou <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Dimitrios Apostolou @ 2024-05-14 20:26 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stat_io clarifications: background worker, writes and reads
@ 2024-05-15 04:03 Kashif Zeeshan <[email protected]>
parent: Dimitrios Apostolou <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Kashif Zeeshan @ 2024-05-15 04:03 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: [email protected]
Hi
background workers are used to perform tasks on db e.g. I/O, replication,
clone schema, vacuum etc, you can find more details on following links.
https://www.postgresql.org/docs/current/bgworker.html#:~:text=PostgreSQL%20can%20be%20extended%20to,...
.
Regards
Kashif Zeeshan
Bitnine
On Wed, May 15, 2024 at 1:26 AM Dimitrios Apostolou <[email protected]> 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
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stat_io clarifications: background worker, writes and reads
@ 2024-05-15 04:21 Muhammad Imtiaz <[email protected]>
parent: Dimitrios Apostolou <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Muhammad Imtiaz @ 2024-05-15 04:21 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: [email protected]
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, <[email protected]> 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
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stat_io clarifications: background worker, writes and reads
@ 2024-05-15 12:59 Dimitrios Apostolou <[email protected]>
parent: Muhammad Imtiaz <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Dimitrios Apostolou @ 2024-05-15 12:59 UTC (permalink / raw)
To: Muhammad Imtiaz <[email protected]>; Kashif Zeeshan <[email protected]>; +Cc: [email protected]
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 code 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 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, <[email protected]> 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
>
>
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stat_io clarifications: background worker, writes and reads
@ 2024-05-15 14:32 Kashif Zeeshan <[email protected]>
parent: Dimitrios Apostolou <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Kashif Zeeshan @ 2024-05-15 14:32 UTC (permalink / raw)
To: Dimitrios Apostolou <[email protected]>; +Cc: Muhammad Imtiaz <[email protected]>; [email protected]
Hi
parallel worker are used for parallel execution of the queries and you can
find the help in the below link.
https://www.postgresql.org/docs/current/how-parallel-query-works.html
Its controlled by following parameters.
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6
The limit of concurrent parallel workers for the whole cluster is
max_parallel_workers, which must be ≤ max_worker_processes. The limit of
parallel workers per query is max_parallel_workers_per_gather.
Thanks
Kashif Zeeshan
Bitnine Global
On Wed, May 15, 2024 at 5:59 PM Dimitrios Apostolou <[email protected]> wrote:
> 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
> code 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
> 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, <[email protected]> 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
> >
> >
> >
> >
> >
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_stat_io clarifications: background worker, writes and reads
@ 2024-05-21 19:20 Dimitrios Apostolou <[email protected]>
parent: Kashif Zeeshan <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Dimitrios Apostolou @ 2024-05-21 19:20 UTC (permalink / raw)
To: Kashif Zeeshan <[email protected]>; +Cc: Muhammad Imtiaz <[email protected]>; [email protected]
Hi Kashif, just to clarify my question, are you saying that the I/O from
parallel workers is recorded into the "background worker" entry of
pg_stat_io?
Thanks,
Dimitris
On Wed, 15 May 2024, Kashif Zeeshan wrote:
> Hi
> parallel worker are used for parallel execution of the queries and you can find the help in the below link.
>
> https://www.postgresql.org/docs/current/how-parallel-query-works.html
>
> Its controlled by following parameters.
>
> max_worker_processes = 6
> max_parallel_workers_per_gather = 3
> max_parallel_workers = 6
> The limit of concurrent parallel workers for the whole cluster is max_parallel_workers, which must be ≤ max_worker_processes. The limit of parallel workers per query is max_parallel_workers_per_gather.
>
> Thanks
> Kashif Zeeshan
> Bitnine Global
>
> On Wed, May 15, 2024 at 5:59 PM Dimitrios Apostolou <[email protected]> wrote:
> 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 code 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-21 19:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-14 20:26 pg_stat_io clarifications: background worker, writes and reads Dimitrios Apostolou <[email protected]>
2024-05-15 04:03 ` Kashif Zeeshan <[email protected]>
2024-05-15 04:21 ` Muhammad Imtiaz <[email protected]>
2024-05-15 12:59 ` Dimitrios Apostolou <[email protected]>
2024-05-15 14:32 ` Kashif Zeeshan <[email protected]>
2024-05-21 19:20 ` Dimitrios Apostolou <[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