MIME-Version: 1.0
References: 
 <CAABECY33tbZr3fNvSTM6oA7W9G_PTOLrMqM1hJ1UwP20pRc8MA@mail.gmail.com>
In-Reply-To: 
 <CAABECY33tbZr3fNvSTM6oA7W9G_PTOLrMqM1hJ1UwP20pRc8MA@mail.gmail.com>
From: Jeff Janes <jeff.janes@gmail.com>
Date: Tue, 7 Jan 2025 14:30:33 -0500
Message-ID: 
 <CAMkU=1zERVLy7nwQ05pQDG-_zx59i1NHM+ciB15ijz9AYypPyA@mail.gmail.com>
Subject: Re: Aggressive vacuum
To: Graham Hay <grahamrhay@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000693cc062b22c877"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMkU%3D1zERVLy7nwQ05pQDG-_zx59i1NHM%2BciB15ijz9AYypPyA%40mail.gmail.com>
Precedence: bulk

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

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

>
>
>
> https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-WRAP=
AROUND
>
>
This link is to v15 docs.  Is that the version you are actually using?  The
behavior of this feature has changed (repeatedly) over the years, so this
is important to know.


>  This was definitely an improvement, but
> when the big vacuum (freeze) kicked off there was a noticeable
> io/latency spike.


Was the change noticeable because it caused degradation in the user
experience (latency of things users actually care about) or because you
have monitoring tools which detected a change even though no one was
complaining?  Can you tell if the cause is driven by sequential reads,
random reads, or writes?

The way to  suppress IO spikes is generally to tweak the vacuum_cost_*
and/or auto_vacuum_vacuum_cost_* settings.  This will make the vacuums take
longer but be less intensive.


> 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.
>

Please be more quantitative.  It wasn't 2x, but then what was it?  And how
long did each one take?

Freezing automatically more often is likely not the answer to IO spikes.
Going from gumming up your system once a week for one hour to gumming it up
3 times a week for 20 minutes each is likely not a real solution.  And it
might not even do that--you could gum it up 3 times a week for 50 minutes
each! Unless you have large swaths of table which become effectively
read-only over time, each aggressive vacuum might need to do the same
amount of IO so doing it more often just makes things worse.

The solution would be to either use the *vacuum_cost_* parameters to
throttle it down to the point where it doesn't cause problems, or
intentionally schedule vacuum freeze during quiet periods (over night, over
weekends) preempting the need for them to happen automatically.

Cheers,

Jeff

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Dec 19, 2024 at 8:37=E2=80=AFAM G=
raham Hay &lt;<a href=3D"mailto:grahamrhay@gmail.com">grahamrhay@gmail.com<=
/a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><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></blockquote><div><br></div><div>This link is to v15 docs.=C2=A0 Is tha=
t the version you are actually using?=C2=A0 The behavior of this feature ha=
s changed (repeatedly) over the years, so this is important to know.</div><=
div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=C2=A0Thi=
s was definitely an improvement, but<br>
when the big vacuum (freeze) kicked off there was a noticeable<br>
io/latency spike. </blockquote><div><br></div><div>Was the change noticeabl=
e=C2=A0because it caused degradation in the user experience (latency of thi=
ngs users actually care about) or because you have monitoring tools which d=
etected a change even though no one was complaining?=C2=A0 Can you tell if =
the cause is driven by sequential reads, random reads, or writes?</div><div=
><br></div><div>The way to=C2=A0 suppress IO spikes is generally to tweak t=
he vacuum_cost_* and/or=C2=A0auto_vacuum_vacuum_cost_* settings.=C2=A0 This=
 will make the vacuums take longer but be less intensive.</div><div>=C2=A0<=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">We then tried settin=
g:<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></blockquote><div><br></div><div>Please be more quantit=
ative.=C2=A0 It wasn&#39;t 2x, but then what was it?=C2=A0 And how long=C2=
=A0did each one take?</div><div><br></div><div>Freezing automatically more =
often is likely not the answer to IO spikes.=C2=A0 Going from gumming up yo=
ur system once a week for one hour to gumming it up 3 times a week for 20 m=
inutes each is likely not a real solution.=C2=A0 And it might not even do t=
hat--you could gum it up 3 times a week for 50 minutes each! Unless you hav=
e large swaths of table which become effectively read-only over time, each =
aggressive vacuum might need to do the same amount of IO so doing it more o=
ften just makes things worse.</div><div><br></div><div>The solution would b=
e to either use the *vacuum_cost_* parameters to throttle it down to the po=
int where it doesn&#39;t cause problems, or intentionally schedule vacuum f=
reeze during quiet periods (over night, over weekends) preempting the need =
for them to happen automatically.</div><div>=C2=A0</div><div>Cheers,</div><=
div><br></div><div>Jeff</div></div></div>

--0000000000000693cc062b22c877--