MIME-Version: 1.0
References: 
 <CAABECY33tbZr3fNvSTM6oA7W9G_PTOLrMqM1hJ1UwP20pRc8MA@mail.gmail.com>
In-Reply-To: 
 <CAABECY33tbZr3fNvSTM6oA7W9G_PTOLrMqM1hJ1UwP20pRc8MA@mail.gmail.com>
From: Slava Mudry <slava44@gmail.com>
Date: Mon, 6 Jan 2025 15:18:28 -0800
Message-ID: 
 <CAEFxPe0Rf798WEer6P+QU78sGB87cxUQ03SHjmx6FW_mP72msg@mail.gmail.com>
Subject: Re: Aggressive vacuum
To: Graham Hay <grahamrhay@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000442042062b11d93b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEFxPe0Rf798WEer6P%2BQU78sGB87cxUQ03SHjmx6FW_mP72msg%40mail.gmail.com>
Precedence: bulk

--000000000000442042062b11d93b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello Graham,
I had to deal with "vacuum to prevent wraparound" a few times and it's not
fun :(
Postgres doc explains it very well.. if you see that nasty thing running on
your db, your best options are:
1. manually vacuum freeze the table
2. examine vacuum costs and lower them to make sure the autovacuum can do
it's job. Allow more vacuum processes by tuning *autovacuum_max_workers*
3. tune and set autovacuum settings for your table to make sure autovacuum
picks it up sooner than defaults allow it so.

Depending on your usage, there are some other good optimizations you can
try.. The best in my opinion is to partition the large table and isolate
old rows from frequently updated rows and manually vacuum freeze rows that
you know wan't change. Also newer postgres versions have improved the
autovacuuming, if you're on ver 15 (based on your link to docs), you should
consider upgrading to more recent version.
Good luck,
-Slava

On Thu, Dec 19, 2024 at 5:37=E2=80=AFAM Graham Hay <grahamrhay@gmail.com> w=
rote:

> Can anyone enlighten me on exactly what triggers an "aggressive"
> vacuum? I have read the docs multiple times (and watched several
> videos!), but the changes I make do not have the expected outcomes; so
> I'm clearly missing something.
>
>
> https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAP=
AROUND
>
> I have a fairly large table, that has outgrown the vacuum defaults. We
> started by setting:
>
>         - autovacuum_vacuum_scale_factor=3D0
>         - autovacuum_vacuum_threshold=3D100000 # 100K
>
> (10% was a lot of tuples). This was definitely an improvement, but
> when the big vacuum (freeze) kicked off there was a noticeable
> io/latency spike. We then tried setting:
>
>       - autovacuum_freeze_table_age=3D100000000 # 100M
>
> which seemed to make it happen more frequently (and one would hope,
> with less work to do). We reduced this to 50M, and again it was more
> frequent (but not the 2x I expected). And when I dropped it to 10M,
> nothing changed.
>
> After re-reading the docs, I got the impression I also needed to set:
>
>         - autovacuum_freeze_min_age=3D10000000 # 10M
>
> As the system default was 50M. And again, this had an effect, but not
> the 5x I was expecting. The docs say:
>
> > all-visible but not all-frozen pages are scanned if the number of
> transactions that have passed since the last such scan is greater than
> vacuum_freeze_table_age minus vacuum_freeze_min_age
>
> but wouldn't that have been 10M - 50M? i.e. -40M. Is there some other
> setting I'm missing?
>
> All suggestions welcome!
>
> Thanks,
>
> Graham
>
>
>

--=20
-slava

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

<div dir=3D"ltr"><div>Hello Graham,</div><div>I had to deal with &quot;vacu=
um to prevent wraparound&quot; a few times and it&#39;s not fun :(<br></div=
><div>Postgres doc explains it very well.. if you see that nasty thing runn=
ing on your db, your best options are:<br></div><div>1. manually vacuum fre=
eze the table</div><div>2. examine vacuum costs and lower them to make sure=
 the autovacuum can do it&#39;s job. Allow more vacuum processes by tuning=
=20
<span><em>autovacuum_max_workers</em> </span>

</div><div>3. tune and set autovacuum settings for your table to make sure =
autovacuum picks it up sooner than defaults allow it so.</div><div><br></di=
v><div>Depending on your usage, there are some other good optimizations you=
 can try.. The best in my opinion is to partition the large table and isola=
te old rows from frequently updated rows and manually vacuum freeze rows th=
at you know wan&#39;t change. Also newer postgres versions have improved th=
e autovacuuming, if you&#39;re on ver 15 (based on your link to docs), you =
should consider upgrading to more recent version.</div><div>Good luck,</div=
><div>-Slava<br></div></div><br><div class=3D"gmail_quote gmail_quote_conta=
iner"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Dec 19, 2024 at 5:37=E2=
=80=AFAM Graham Hay &lt;<a href=3D"mailto:grahamrhay@gmail.com">grahamrhay@=
gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">Can anyone enlighten me on exactly what triggers an &quot;aggres=
sive&quot;<br>
vacuum? I have read the docs multiple times (and watched several<br>
videos!), but the changes I make do not have the expected outcomes; so<br>
I&#39;m clearly missing something.<br>
<br>
<a href=3D"https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM=
-FOR-WRAPAROUND" rel=3D"noreferrer" target=3D"_blank">https://www.postgresq=
l.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND</a><br>
<br>
I have a fairly large table, that has outgrown the vacuum defaults. We<br>
started by setting:<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 - autovacuum_vacuum_scale_factor=3D0<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 - autovacuum_vacuum_threshold=3D100000 # 100K<b=
r>
<br>
(10% was a lot of tuples). This was definitely an improvement, but<br>
when the big vacuum (freeze) kicked off there was a noticeable<br>
io/latency spike. We then tried setting:<br>
<br>
=C2=A0 =C2=A0 =C2=A0 - autovacuum_freeze_table_age=3D100000000 # 100M<br>
<br>
which seemed to make it happen more frequently (and one would hope,<br>
with less work to do). We reduced this to 50M, and again it was more<br>
frequent (but not the 2x I expected). And when I dropped it to 10M,<br>
nothing changed.<br>
<br>
After re-reading the docs, I got the impression I also needed to set:<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 - autovacuum_freeze_min_age=3D10000000 # 10M<br=
>
<br>
As the system default was 50M. And again, this had an effect, but not<br>
the 5x I was expecting. The docs say:<br>
<br>
&gt; all-visible but not all-frozen pages are scanned if the number of tran=
sactions that have passed since the last such scan is greater than vacuum_f=
reeze_table_age minus vacuum_freeze_min_age<br>
<br>
but wouldn&#39;t that have been 10M - 50M? i.e. -40M. Is there some other<b=
r>
setting I&#39;m missing?<br>
<br>
All suggestions welcome!<br>
<br>
Thanks,<br>
<br>
Graham<br>
<br>
<br>
</blockquote></div><div><br clear=3D"all"></div><br><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr">-slava</div></div>

--000000000000442042062b11d93b--