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 1tIXUQ-00ExYo-7J for pgsql-general@arkaria.postgresql.org; Tue, 03 Dec 2024 18:19:14 +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 1tIXUN-00BtXc-3H for pgsql-general@arkaria.postgresql.org; Tue, 03 Dec 2024 18:19:12 +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 1tIXUM-00BtXR-LS for pgsql-general@lists.postgresql.org; Tue, 03 Dec 2024 18:19:11 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tIXUK-000s0q-2c for pgsql-general@lists.postgresql.org; Tue, 03 Dec 2024 18:19:11 +0000 Received: by mail-ot1-x32d.google.com with SMTP id 46e09a7af769-71d4fceec9aso2310644a34.3 for ; Tue, 03 Dec 2024 10:19:08 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733249946; x=1733854746; 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=4erAyn9wp9EPgkVz3gz0dFiAf+GPCpcgnPDAmV64ykI=; b=dYWopTkoqob4Sv/+Mok1lI/t47aVlmrHObTZCxZ8LFZUnTS4LR28/nNdHhI5bt/EUH PcNbwHZFRieGtbqvaeOraFWNjznQSpF4rcx2vTNlxqNmWyllRo/A8mDtmdwudsQ9acq9 DiW3bdS5SLGxndF439BYxJTT9f+LOUxfQmoMqTvQBltRsp32QaR/t4mY/3MI5fdHqmJW p7efZAq6U9EVNgGRNM2Awj92QvOHNR+Ts/QCJkdw99Z7jOYhKbZ8YeotH2me7HP2R5bc IEdLYr4iPoDBjh0eaE3x7MLRaWXHfa0W2dyJIkNTm0GID9fI1qBY8uUTNW5dFwK1Xyyh YXzg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733249946; x=1733854746; 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=4erAyn9wp9EPgkVz3gz0dFiAf+GPCpcgnPDAmV64ykI=; b=UlkgUPCDipgXELC0HUhoqWg0n0ZMYhXCEk3qFnBuv2Nej8WSBh7WFQ9G72H/nakGiQ wtLTzZoW/4hf+8WJY3SHfvb5nK7fdmVjRnK/Fzhhw1fNpMEbQKWW2kxMyC3pZsJQaCo1 nH3IH/vkh5E1LZXw+sKP9kIaYsND7xqpc87hr4beXGW8T58tRdpRKEizYqzBDf2oLkDD 5p+mbbxmKUsknBMW11bqOttdDa0oBfsyJhbLKQPLlYR865sl1HWyvJdaFh9sRJzIBrvQ WkHp2Ly6ajUfyEyd6d9C/liJC/+FutBtfAw+oT0SH080hFM+gER15xclUYFBQAP1ioVi 1oYA== X-Gm-Message-State: AOJu0YwA45O32mynuPNHKdpp13ML0R4SxSBBNYLIvEpm9WkSaGEo53A5 TpHhQHu3EOaFATIhLxY1QMf9DVcxzvxkgZVk1vIhBKGC+xuiG+/8UlOe8WuHqEQw5XEGSQg9YiJ s8Kbaz+H4EUHjfqcUkWr7CoStZgAwrQ== X-Gm-Gg: ASbGncsCu3jhJdufTKVhQJK+R5BKca9PZAr1LuUrjsZZkDsRdmqYVQCwJw9I2z4AfUD I6GwEt0lJ/XiWdTFIyzRC2UlesYkq1q9is3FsHRMbX3sbKSShTlyrtyuVIVg/QtN3vw== X-Google-Smtp-Source: AGHT+IGDzkAFsRSARqZdXQ5zBnNx3N1MLcAOJ+jfizYcxc3/Kqu9DAL7tb2N0uSzd6grupvSSNLT8nY2cnJhQkp0u3I= X-Received: by 2002:a05:6830:6619:b0:71d:4bd9:8b98 with SMTP id 46e09a7af769-71dad6e6e85mr4782795a34.25.1733249946177; Tue, 03 Dec 2024 10:19:06 -0800 (PST) MIME-Version: 1.0 References: <341dd184-9096-4d1f-a74c-7db02cd1004c@aklaver.com> In-Reply-To: From: Ron Johnson Date: Tue, 3 Dec 2024 13:18:55 -0500 Message-ID: Subject: Re: Autovacuum and visibility maps To: "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000059581f062861b352" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000059581f062861b352 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable When in doubt, "manually" vacuum and/or analyze. Maybe even disable autovacuum on that table before the TRUNCATE + INSERT, do the "manual" vacuum-analyze and then re-enable autovacuum. Bonus points for programmatically determining which partitions you're going to insert into, so that you only manually maintain those partitions. On Tue, Dec 3, 2024 at 1:11=E2=80=AFPM Tefft, Michael J wrote: > Thanks for the point about truncates versus deletes. > > But most of these partitions have over 100k rows, all inserted at once. W= e > have the default setting: > > #autovacuum_vacuum_insert_threshold =3D 1000 # min number of row ins= erts > > > > So I thought we should be triggering by inserts. > > > > Mike > > > > *From:* Adrian Klaver > *Sent:* Tuesday, December 3, 2024 11:57 AM > *To:* Tefft, Michael J ; > pgsql-general@lists.postgresql.org > *Subject:* Re: Autovacuum and visibility maps > > > > On 12/3/24 08: 32, Tefft, Michael J wrote: > We have some batch queries > that had occasionally having degraded > runtimes: from 2 hours degrading = to > 16 hours, etc. > > Comparing plans from good and bad runs, we saw that th= e > good plans > > On 12/3/24 08:32, Tefft, Michael J wrote: > > > We have some batch queries that had occasionally having degraded > > > runtimes: from 2 hours degrading to 16 hours, etc. > > > > > > Comparing plans from good and bad runs, we saw that the good plans used > > > index-only scans on table =E2=80=9Cx=E2=80=9D, while the bad plans used= index scans. > > > > > > Using the pg_visibility utility, we found that all of the 83 partitions > > > of table =E2=80=9Cx=E2=80=9D were showing zero blocks where all tuples = were visible. We > > > ran a VACUUM on the table; the visibility maps are now clean and the > > > good plans came back. > > > > > > Our question is: why did autovacuum not spare us from this? > > > > > > We are using default autovacuum parameters for all except > > > log_autovacuum_min_duration=3D5000. These partitions are populated by > > > processes that do a truncate + a single insert-select. > > > > > > We see autovacuum failure (failed to get lock) messages, followed by a > > > success message, in the log for one of these partitions (the biggest > > > one) but even that partition showed zero blocks with all tuples visible= . > > > > > > Are we wrong to expect autovacuum to clean up the visibility map? > > > > I have to believe it is due to this: > > > > https://urldefense.com/v3/__https://www.postgresql.org/docs/current/routi= ne-vacuuming.html*VACUUM-FOR-SPACE-RECOVERY__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdX= SpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOracZS= xzvbw$ > > > > "If you have a table whose entire contents are deleted on a periodic > > basis, consider doing it with TRUNCATE rather than using DELETE followed > > by VACUUM. TRUNCATE removes the entire content of the table immediately, > > without requiring a subsequent VACUUM or VACUUM FULL to reclaim the > > now-unused disk space. The disadvantage is that strict MVCC semantics > > are violated." > > > > Combined with this: > > > > https://urldefense.com/v3/__https://www.postgresql.org/docs/current/runti= me-config-autovacuum.html*GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9= VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-= _jyxJnNx3YBvbxQOraeerEd0yw$ > > > > "autovacuum_vacuum_threshold > > > > Specifies the minimum number of updated or deleted tuples needed to > > trigger a VACUUM in any one table. ... > > > > " > > > > I'm going to say the TRUNCATE itself does not trigger an autovacuum. I > > would suggest throwing a manual VACUUM in the table population script. > > > > > > > > postgres=3D# select version(); > > > > > > version > > > > > > -----------------------------------------------------------------------= ----------------------------------- > > > > > > PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 > > > 20210514 (Red Hat 8.5.0-22), 64-bit > > > > > > Thank you, > > > > > > Mike Tefft > > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --00000000000059581f062861b352 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
When in doubt, "manually" vacuum and/or ana= lyze.

Maybe even disable autovacuum on that table = before the TRUNCATE=C2=A0+ INSERT, do the "manual" vacuum-analyze= and then re-enable autovacuum.=C2=A0 Bonus points for programmatically det= ermining which partitions you're going to insert into, so that you only= manually maintain those partitions.

On Tue, Dec 3, 2024 at 1:11=E2=80=AFPM = Tefft, Michael J <Michael.= J.Tefft@snapon.com> wrote:

Thanks for the point = about truncates versus deletes.

But most of these par= titions have over 100k rows, all inserted at once. We have the default sett= ing:

#autovacuum_vacuum_in= sert_threshold =3D 1000=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 # min number of row i= nserts

=C2=A0<= /span>

So I thought we shoul= d be triggering by inserts.

=C2=A0<= /span>

Mike

=C2=A0<= /span>

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Tuesday, December 3, 2024 11:57 AM
To: Tefft, Michael J <Michael.J.Tefft@snapon.com>; pgsql-general@lists.p= ostgresql.org
Subject: Re: Autovacuum and visibility maps

=C2=A0

On 12/3/24= 08:=E2=80=8A32, Tefft,= Michael J wrote: > We have some batch queries that had occasionally having degr= aded > runtimes: from 2 hours degrading to 16 hours, etc. > > Comp= aring plans from good and bad runs, we saw that the good plans

= 

On 12/3/24 08:32, Tefft, Michael J wrote:
> We ha=
ve some batch queries that had occasionally having degraded <=
/span>
> runti=
mes: from 2 hours degrading to 16 hours, etc.
> 
> Compa=
ring plans from good and bad runs, we saw that the good plans used <=
u>
> index=
-only scans on table =E2=80=9Cx=E2=80=9D, while the bad plans used index sc=
ans.
> 
> Using=
 the pg_visibility utility, we found that all of the 83 partitions <=
u>
> of ta=
ble =E2=80=9Cx=E2=80=9D were showing zero blocks where all tuples were visi=
ble. We 
> ran a=
 VACUUM on the table; the visibility maps are now clean and the <=
/u>
> good =
plans came back.
> 
> Our q=
uestion is: why did autovacuum not spare us from this?=

> 
> We ar=
e using default autovacuum parameters for all except <=
/pre>

> log_a=
utovacuum_min_duration=3D5000. These partitions are populated by =



> proce=
sses that do a truncate + a single insert-select.

> 


> We se=
e autovacuum failure (failed to get lock) messages, followed by a 


> succe=
ss message, in the log for one of these partitions (the biggest <=
/u>


> one) =
but even that partition showed zero blocks with all tuples visible.<=
u>


> 


> Are w=
e wrong to expect autovacuum to clean up the visibility map?<=
/span>


=C2=
=A0


I have to =
believe it is due to this:


=C2=
=A0


https://urldefense.com/v3/__https://www.postgresql.=
org/docs/current/routine-vacuuming.html*VACUUM-FOR-SPACE-RECOVERY__;Iw!!Lf_=
9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1=
-_jyxJnNx3YBvbxQOracZSxzvbw$


=C2=
=A0


"If y=
ou have a table whose entire contents are deleted on a periodic <=
/u>


basis, con=
sider doing it with TRUNCATE rather than using DELETE followed 


by VACUUM.=
 TRUNCATE removes the entire content of the table immediately, 


without re=
quiring a subsequent VACUUM or VACUUM FULL to reclaim the 


now-unused=
 disk space. The disadvantage is that strict MVCC semantics <=
/span>


are violat=
ed."


=C2=
=A0


Combined w=
ith this:


=C2=
=A0


https://urldefense.com/v3/__ht=
tps://www.postgresql.org/docs/current/runtime-config-autovacuum.html*GUC-AU=
TOVACUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1p=
e8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOraeerEd0yw$<=
u>


=C2=
=A0


"auto=
vacuum_vacuum_threshold


=C2=
=A0


Specifies =
the minimum number of updated or deleted tuples needed to 


trigger a =
VACUUM in any one table. ...


=C2=
=A0


"<=
/u>


=C2=
=A0


I'm go=
ing to say the TRUNCATE itself does not trigger an autovacuum. I =



would sugg=
est throwing a manual VACUUM in the table population script.<=
/span>


=C2=
=A0


> 


> postg=
res=3D# select version();


> 


>=C2=A0=
 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 version<=
u>


> 


> -----=
---------------------------------------------------------------------------=
--------------------------


> 


> Postg=
reSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 <=
/u>


> 20210=
514 (Red Hat 8.5.0-22), 64-bit


> 


> Thank=
 you,


> 


> Mike =
Tefft


> 


=C2=
=A0


-- =



Adrian Kla=
ver


adrian.klaver@aklaver.=
com


=C2=
=A0


--
Death to <Redacted>, and butter sauce= .
Don't boil me, I'm still alive.
<Redacted>= lobster!
--00000000000059581f062861b352--