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 1s7FgZ-004aTE-6W for pgsql-general@arkaria.postgresql.org; Wed, 15 May 2024 14:32:52 +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 1s7FgZ-0006ud-8I for pgsql-general@arkaria.postgresql.org; Wed, 15 May 2024 14:32:51 +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 1s7FgY-0006uU-Oo for pgsql-general@lists.postgresql.org; Wed, 15 May 2024 14:32:50 +0000 Received: from mail-oi1-x229.google.com ([2607:f8b0:4864:20::229]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s7FgU-000Ndk-Sb for pgsql-general@lists.postgresql.org; Wed, 15 May 2024 14:32:50 +0000 Received: by mail-oi1-x229.google.com with SMTP id 5614622812f47-3c9b94951e2so840207b6e.0 for ; Wed, 15 May 2024 07:32:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1715783564; x=1716388364; 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=HPLVSFgu/EjzUbYcqu1FNMfPPkVspdaNk/lP4SUjsmk=; b=ZQk4IfTrMI2PTPoBupGQG2pn+T+ndrLmt0NO2GcgC3RDwVyd3PDP4szTPr0sMjpvCO 5GMoux1IS6Y3TmyclcnfmUnuomgcTU3Qtdj/9tYRsM+cxjPQKbB5iCQOlGg8bs7+orhv t10PfGTDLyTOCzCEIEQbZAf2NRKpP9H+2WM2TNS4eSSsTaMMRHC8XKRSSyi08f/cpyjl EorCkpR39ArieQudW2iXynx+wLIwd6Q/Cz0bh9Rdkw7cyxkeuXPys/iw1fdIzstfuhqs ny1ZxyChheSgixa8Yz5FiRWz5cXujDvSJD0GabG7aFKJR4fYzCdcw69Fsg91VNZ0D4fF dX+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715783564; x=1716388364; 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=HPLVSFgu/EjzUbYcqu1FNMfPPkVspdaNk/lP4SUjsmk=; b=l8+mxzAx9hfSt6JZdjlReS7yom0W0zBPCIjLFFL9curY+tL5htTH/1RVp4qtKIlfMf QSpn8vlNbK7Ru7r/lfO/Nvi4ToNmb5exd3/cWKlP3s3a6NPZ7FknddnhlNlNjRKlzLX8 GAE8UN8kJQ7OHR8bBwxVU2Djwrz07oZOUaVRmCAhncwU3hlAy0BpSK/RG8zdtzQJ3B0g d3Le0XISAOeauEv4dNbGMw7JE3BQVzgOJ/5QvbLP5k505sgIc2qXLj3vaVZy4mbbfH4o c8tDp962bGNaoBfz0YQOE94//K5W0F9q/gG+O3gTKMxXA2G/CE7cmCKWwNraradDcoIN LdUw== X-Forwarded-Encrypted: i=1; AJvYcCWpXslcuFREWJm0cSfMQo+J8pxY7j6cWegoB+4PGON6tVGhOIKQDRNrLdNg73aSjoRmP6Y0E+suXvhYyOwjsC2xTprK9V+xgsNtY8vOagTcKh8i X-Gm-Message-State: AOJu0YzmxqP7USL1RXMcbwlaTPkT+QScXJKHiWQWuKBL//9gE9GvT3GI v2se5h4zq5MMqrGtu9s/+cNl4tAlMx1P31HBizY1h7GWj27TO8olS6tACT2qcFJfO3aG23JNgTr k5wSWTA9mbUVCfCIHRmfAZ0TRMlYXb53s3vQ= X-Google-Smtp-Source: AGHT+IF38Dl3311XZqq/2Dhh/FWllor4tnT82P+0I1ziJIkoG7YeJA+RQe4GWg9ogJRVPhZ5QYFB4mSRhgbrRLEvEWI= X-Received: by 2002:a05:6808:18a7:b0:3c9:9517:e0ab with SMTP id 5614622812f47-3c997072211mr21216264b6e.32.1715783564075; Wed, 15 May 2024 07:32:44 -0700 (PDT) MIME-Version: 1.0 References: <2e9b2b7b-79a1-a7f2-e05e-9cefda601c09@gmx.net> <12733373-8f3c-550e-771d-fa7b5ff9343d@gmx.net> In-Reply-To: <12733373-8f3c-550e-771d-fa7b5ff9343d@gmx.net> From: Kashif Zeeshan Date: Wed, 15 May 2024 19:32:32 +0500 Message-ID: Subject: Re: pg_stat_io clarifications: background worker, writes and reads To: Dimitrios Apostolou Cc: Muhammad Imtiaz , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000d92b7806187efd0d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d92b7806187efd0d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D 6 max_parallel_workers_per_gather =3D 3 max_parallel_workers =3D 6 The limit of concurrent parallel workers for the whole cluster is max_parallel_workers, which must be =E2=89=A4 max_worker_processes. The lim= it of parallel workers per query is max_parallel_workers_per_gather. Thanks Kashif Zeeshan Bitnine Global On Wed, May 15, 2024 at 5:59=E2=80=AFPM Dimitrios Apostolou = 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, 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 > > > > > > > > > > > --000000000000d92b7806187efd0d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

parallel worker are used for paralle= l execution of the queries and you can find the help in the below link.
=


Its controlled= by following parameters.

max_worker_processes =3D 6
max_parallel_workers_per_gather =3D 3
max_parallel_workers =3D 6
The limit of concurrent parallel workers for the whole cluster is ma= x_parallel_workers, which must be =E2=89=A4 max_worker_processes. The limit= of parallel workers per query is max_parallel_workers_per_gather.

Thanks
Kashif Zeeshan
Bitnine Glob= al

On Wed, May 15, 2024 at 5:59=E2=80=AFPM Dimitrios Apostolou <jimis@gmx.net> 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:=C2=A0 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 SEL= ECT
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 backgro= und 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.=C2=A0
>
> Regards,
> Imtiaz
>
>
> On Wed, 15 May 2024, 01:26 Dimitrios Apostolou, <jimis@gmx.net> wrote:
>=C2=A0 =C2=A0 =C2=A0 =C2=A0Hello list,
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0what is the "background worker" in= the pg_stat_io statistics view? I'm
>=C2=A0 =C2=A0 =C2=A0 =C2=A0reading the documentation but can't figu= re this one out knowing that it is
>=C2=A0 =C2=A0 =C2=A0 =C2=A0not autovacuum or bgwriter. And I'm not = aware of any extension I might
>=C2=A0 =C2=A0 =C2=A0 =C2=A0have with registered background worker.
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0Additionally, how can it be evictions > w= rites? I would expect every
>=C2=A0 =C2=A0 =C2=A0 =C2=A0eviction to cause a write.
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0Finally about "hits", I understand= they are reads found in shared_buffers,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0so they never registered into the "read= s" counter. So is "reads" in
>=C2=A0 =C2=A0 =C2=A0 =C2=A0pg_stat_io the equivalent to misses, i.e. th= e opposite of "hits", the read
>=C2=A0 =C2=A0 =C2=A0 =C2=A0attempts not found in the shared_buffers, th= at needed to be fetched from
>=C2=A0 =C2=A0 =C2=A0 =C2=A0the disk (or OS buffercache)?
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0=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 | write_time | writebacks | writeback_time | exten= ds | extend_time | op_bytes |=C2=A0 =C2=A0hits=C2=A0 =C2=A0 | evictions | r= euses | fsyncs | fsync_time |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stats_reset=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0-------------------+---------------+--------= -+---------+-------------+--------+------------+------------+--------------= --+---------+-------------+----------+-----------+-----------+--------+----= ----+------------+-------------------------------
>=C2=A0 =C2=A0 =C2=A0 =C2=A0=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
>
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0Thank you in advance,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0Dimitris
>
>
>
>
>
--000000000000d92b7806187efd0d--