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 1sMSV2-002Vqd-Gi for pgsql-general@arkaria.postgresql.org; Wed, 26 Jun 2024 13:15:48 +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 1sMSV0-004RFq-Hu for pgsql-general@arkaria.postgresql.org; Wed, 26 Jun 2024 13:15:46 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sMSUz-004RFh-Ip for pgsql-general@lists.postgresql.org; Wed, 26 Jun 2024 13:15:46 +0000 Received: from mail-oo1-xc36.google.com ([2607:f8b0:4864:20::c36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sMSUw-003E9d-86 for pgsql-general@lists.postgresql.org; Wed, 26 Jun 2024 13:15:44 +0000 Received: by mail-oo1-xc36.google.com with SMTP id 006d021491bc7-5bb36de2171so3271920eaf.2 for ; Wed, 26 Jun 2024 06:15:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1719407741; x=1720012541; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=7QIs2YiIDeNKDtSy6FunazSc3cPTX4nOVoVw8bx0fbU=; b=UK9ZlSrgTOHQAvhUNex1WP0ZMGCFFnFyRnVYKwUZSp+dJr+dLVfr37EshgPK6UddX6 hXRY38QTFUynshCmvkikc3lgSHqWQZNTFzOt+XybLF3wVIxn8fPM9LdyK9/0kYRjnmPm +MxBxr7jhI6chXW8yjodR2Xv0shUX628jIcQb4ihK+4hrug77OPAUb9COx8XitgvHYhu zPVRwW/5x772kenMrXH84N1TREV8sedzWgdpNiMKlTwdXt+YcxkILs8NtXLLOhpTjud+ 0adqFIx2hfMCZYMlw46P3RUmOcwkPwUmR0ra3MZn73aV6itFDJIIrf5TGnENaNKjm2Wo nhKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719407741; x=1720012541; h=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=7QIs2YiIDeNKDtSy6FunazSc3cPTX4nOVoVw8bx0fbU=; b=lBqHlOt5EtZxumh6lbvAhWlXDfQ5fbebml4oU2uNlveuCE35jeLI8qzPDWEN0r3QxB 9yHVsyfRbt7riDt2nwj0EnImXDZjv/13qybQHOJjq3GgVOZW7M82bIIdWxHDsM5GeG1A 7p8cgj7bDCoJ18ObWmoiWFSjbi5DvZ8MY9dOcTph9l4Y3Sn9/HGB+KEJvbTKOg7QHQ1n LphJT7KQMyge7ZSMju8bjy37EYE81s5KYmxcTTXail2oZACGR83QPdoBhMRWSCxEbMNH 9YEJRe5/f4YZW2hg6uFkSuMjy3aEIcCPFCb9f8HwsxG259rpTNSrZhlqXVggSMaI/5++ Yg+w== X-Gm-Message-State: AOJu0YyFZ90cd1LwH7aeuNB/y4KZYzBLsU0DYMEaCiZOMVn8tgHn+j9U qS4bSAl+6hwBU0jrULIwr1w1FG3t5tV5lTKqWhL/m0+qgHhQqMbaMAPeliDj2/8gWfiYkoQ42ud L1+5OzMNgKqlT70qY/RL+23HlpQaI5PjI X-Google-Smtp-Source: AGHT+IFimSdARFFCFphDZV4wlFCEXur3jaXLLnJVv+Rau+j4sdp4ky+XXhKdtNsomaR5OgycUvKI4iQFBVI6JXFRWyc= X-Received: by 2002:a05:6870:b528:b0:259:58b3:a44b with SMTP id 586e51a60fabf-25d0690d791mr11217789fac.0.1719407741078; Wed, 26 Jun 2024 06:15:41 -0700 (PDT) MIME-Version: 1.0 References: <2f2aafe6-3e29-4305-a279-1b3d56e9389e@cloud.gatewaynet.com> <53da5a20-ca35-40fa-85a3-c59d4302a512@aklaver.com> In-Reply-To: From: Ron Johnson Date: Wed, 26 Jun 2024 09:15:30 -0400 Message-ID: Subject: Re: Autovacuum, dead tuples and bloat To: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000a1986a061bcacfc7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a1986a061bcacfc7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jun 26, 2024 at 3:03=E2=80=AFAM Shenavai, Manuel wrote: > Thanks for the suggestions. > I checked pg_locks shows and pg_stat_activity but I could not find a LOC= K > or an transaction on this (at this point in time). > > I assume that this problem may relate to long running transactions which > write a lot of data. Is there already something in place that would help = me > to: > 1) identify long running transactions > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG= -STAT-ACTIVITY-VIEW https://www.postgresql.org/docs/current/pgstatstatements.html > 2) get an idea of the data-volume a single transaction writes? > > I tested the log_statement=3D'mod' but this writes too much data (includi= ng > all payloads). I rather would like to get a summary entry of each > transaction like: > "Tx 4752 run for 1hour and 1GB data was written." > > Is there something like this already available in postgres? > *Maybe* you can interpolate that by seeing how much wal activity is written during the transaction, but I'm dubious. > > Best regards, > Manuel > > -----Original Message----- > From: Adrian Klaver > Sent: 22 June 2024 23:17 > To: Shenavai, Manuel ; Achilleas Mantzios < > a.mantzios@cloud.gatewaynet.com>; pgsql-general@lists.postgresql.org > Subject: Re: Autovacuum, dead tuples and bloat > > On 6/22/24 13:13, Shenavai, Manuel wrote: > > Thanks for the suggestion. This is what I found: > > > > - pg_locks shows only one entry for my DB (I filtered by db oid). The > entry is related to the relation "pg_locks" (AccessShareLock). > > Which would be the SELECT you did on pg_locks. > > > - pg_stat_activity shows ~30 connections (since the DB is in use, this > is expected) > > The question then is, are any of those 30 connections holding a > transaction open that needs to see the data in the affected table and is > keeping autovacuum from recycling the tuples? > > You might need to look at the Postgres logs to determine the above. > Logging connections/disconnections helps as well at least 'mod' statement= s. > > See: > > https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTI= ME-CONFIG-LOGGING-WHAT > > for more information. > > > > > Is there anything specific I should further look into in these tables? > > > > Regarding my last post: Did we see a problem in the logs I provided in > my previous post? We have seen that there are 819294 n_live_tup in the > toast-table. Do we know how much space these tuple use? Do we know how > much space one tuple use? > > You will want to read: > > https://www.postgresql.org/docs/current/storage-toast.html > > Also: > > https://www.postgresql.org/docs/current/functions-admin.html > > 9.27.7. Database Object Management Functions > > There are functions there that show table sizes among other things. > > > > > Best regards, > > Manuel > > > > -----Original Message----- > > From: Adrian Klaver > > Sent: 21 June 2024 22:39 > > To: Shenavai, Manuel ; Achilleas Mantzios < > a.mantzios@cloud.gatewaynet.com>; pgsql-general@lists.postgresql.org > > Subject: Re: Autovacuum, dead tuples and bloat > > > > On 6/21/24 12:31, Shenavai, Manuel wrote: > >> Hi, > >> > >> Thanks for the suggestions. I found the following details to our > >> autovacuum (see below). The related toast-table of my table shows some > >> logs related the vacuum. This toast seems to consume all the data > >> (27544451 pages * 8kb =E2=89=88 210GB ) > > > > Those tuples(pages) are still live per the pg_stat entry in your second > > post: > > > > "n_dead_tup": 12, > > "n_live_tup": 819294 > > > > So they are needed. > > > > Now the question is why are they needed? > > > > 1) All transactions that touch that table are done and that is the data > > that is left. > > > > 2) There are open transactions that still need to 'see' that data and > > autovacuum cannot remove them yet. Take a look at: > > > > pg_stat_activity: > > > > > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-= PG-STAT-ACTIVITY-VIEW > > > > and > > > > pg_locks > > > > https://www.postgresql.org/docs/current/view-pg-locks.html > > > > to see if there is a process holding that data open. > > > >> > >> Any thoughts on this? > >> > >> Best regards, > >> Manuel > >> > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000a1986a061bcacfc7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jun 26, 2024 at 3:03=E2=80=AFAM S= henavai, Manuel <manuel.shena= vai@sap.com> wrote:
Thanks for the suggestions.
I checked pg_locks=C2=A0 shows and pg_stat_activity but I could not find a = LOCK or an transaction on this (at this point in time).

I assume that this problem may relate to long running transactions which wr= ite a lot of data. Is there already something in place that would help me t= o:
1) identify long running transactions

<= a href=3D"https://www.postgresql.org/docs/current/monitoring-stats.html#MON= ITORING-PG-STAT-ACTIVITY-VIEW">https://www.postgresql.org/docs/current/moni= toring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
= =C2=A0
2) get an idea of the data-volume a single transaction writes?

I tested the log_statement=3D'mod' but this writes too much data (i= ncluding all payloads). I rather would like to get a summary entry of each = transaction like:
"Tx 4752 run for 1hour and 1GB data was written."

Is there something like this already available in postgres?

Maybe=C2=A0you can interpolate that by seeing h= ow much wal activity is written during the transaction, but I'm dubious= .
=C2=A0

Best regards,
Manuel

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: 22 June 2024 23:17
To: Shenavai, Manuel <manuel.shenavai@sap.com>; Achilleas Mantzios <a.mantzios@cl= oud.gatewaynet.com>; pgsql-general@lists.postgresql.org
Subject: Re: Autovacuum, dead tuples and bloat

On 6/22/24 13:13, Shenavai, Manuel wrote:
> Thanks for the suggestion. This is what I found:
>
> - pg_locks=C2=A0 shows only one entry for my DB (I filtered by db oid)= . The entry is related to the relation "pg_locks" (AccessShareLoc= k).

Which would be the SELECT you did on pg_locks.

> - pg_stat_activity shows ~30 connections (since the DB is in use, this= is expected)

The question then is, are any of those 30 connections holding a
transaction open that needs to see the data in the affected table and is keeping autovacuum from recycling the tuples?

You might need to look at the Postgres logs to determine the above.
Logging connections/disconnections helps as well at least 'mod' sta= tements.

See:
https= ://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONF= IG-LOGGING-WHAT

for more information.

>
> Is there anything specific I should further look into in these tables?=
>
> Regarding my last post: Did we see a problem in the logs I provided in= my previous post? We have seen that there are 819294 n_live_tup in the toa= st-table. Do we know how much space these tuple use?=C2=A0 Do we know how m= uch space one tuple use?

You will want to read:

https://www.postgresql.org/docs/current/s= torage-toast.html

Also:

https://www.postgresql.org/docs/current/= functions-admin.html

9.27.7. Database Object Management Functions

There are functions there that show table sizes among other things.

>
> Best regards,
> Manuel
>
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: 21 June 2024 22:39
> To: Shenavai, Manuel <manuel.shenavai@sap.com>; Achilleas Mantzios <a.mantzio= s@cloud.gatewaynet.com>; pgsql-general@lists.postgresql.org
> Subject: Re: Autovacuum, dead tuples and bloat
>
> On 6/21/24 12:31, Shenavai, Manuel wrote:
>> Hi,
>>
>> Thanks for the suggestions. I found the following details to our >> autovacuum (see below). The related toast-table of my table shows = some
>> logs related the vacuum. This toast seems to consume all the data<= br> >> (27544451 pages * 8kb =E2=89=88 210GB )
>
> Those tuples(pages) are still live per the pg_stat entry in your secon= d
> post:
>
> "n_dead_tup": 12,
> "n_live_tup": 819294
>
> So they are needed.
>
> Now the question is why are they needed?
>
> 1) All transactions that touch that table are done and that is the dat= a
> that is left.
>
> 2) There are open transactions that still need to 'see' that d= ata and
> autovacuum cannot remove them yet. Take a look at:
>
> pg_stat_activity:
>
> h= ttps://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-= STAT-ACTIVITY-VIEW
>
> and
>
> pg_locks
>
> https://www.postgresql.org/docs/curre= nt/view-pg-locks.html
>
> to see if there is a process holding that data open.
>
>>
>> Any thoughts on this?
>>
>> Best regards,
>> Manuel
>>
>
>
> --
> Adrian Klaver
> adrian.= klaver@aklaver.com
>

--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000a1986a061bcacfc7--