MIME-Version: 1.0
References: <CAJCZko+yQzSRaHa_U2cQ3aeHtMKcyrgOSnVjoZkJsH2LH3f69g@mail.gmail.com>
In-Reply-To: <CAJCZko+yQzSRaHa_U2cQ3aeHtMKcyrgOSnVjoZkJsH2LH3f69g@mail.gmail.com>
From: semab tariq <semabtariq1@gmail.com>
Date: Mon, 12 Aug 2024 21:37:17 +0500
Message-ID: <CAG=z8NRYzV2buVyBz5Rb-SQWt9fU8+H0oioaA2SejPKo=qELOg@mail.gmail.com>
Subject: Re: autovacuum freeze recommendations at table level
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org, 
	Christoph Berg <myon@debian.org>
Content-Type: multipart/alternative; boundary="000000000000d99e2a061f7f1b5b"
Archived-At: <https://www.postgresql.org/message-id/CAG%3Dz8NRYzV2buVyBz5Rb-SQWt9fU8%2BH0oioaA2SejPKo%3DqELOg%40mail.gmail.com>
Precedence: bulk

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

Hi Durga

*autovacuum_freeze_max_age* specifies the maximum age (in transactions)
that a table's tuples can reach before a vacuum is forced to prevent
transaction ID wraparound. when the age of the oldest tuple in the table
exceeds this value, an autovacuum is triggered to freeze the tuples.
*Recommendation =3D 20000000 -> 150000000 *

*autovacuum_multixact_freeze_max_age *It is similar to above, but applies
to multi-transaction IDs (used for shared row locks). when the age of the
oldest multi-transaction ID exceeds this value, an autovacuum is triggered
to freeze the multi-transaction IDs.
*Recommendation =3D 20000000 -> 150000000*

*autovacuum_freeze_min_age* specifies the minimum age (in transactions)
that a tuple must reach before it is considered for freezing. Lowering this
value can cause more frequent freezing, which can increase the overhead of
autovacuum.
*Recommendation =3D 0 -> 50000000*

Thanks, Semab

On Sun, Aug 11, 2024 at 11:12=E2=80=AFAM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:

> Hi  Respected Team,
>
> Could you please let me know that how this freeze parameters work
> Update query runs on table  through which data being modified daily in
> this case
> Total records in table is about 20lakhs
> current setting for this table is
> Access method: heap
> if it reaches > 0.1*2000000+1000 =3D 2,10,000 as per the formula autovacu=
um
> triggers
> Options: fillfactor=3D85, autovacuum_vacuum_cost_delay=3D0,
> autovacuum_vacuum_cost_limit=3D3000, parallel_workers=3D6,
> autovacuum_vacuum_scale_factor=3D0.1, autovacuum_vacuum_threshold=3D1000,
> autovacuum_freeze_max_age=3D20000000,
> autovacuum_multixact_freeze_max_age=3D20000000, autovacuum_freeze_min_age=
=3D0
>
> How autovacuum freeze parameters work.Give me some recommendations to
> improve the performance better than now
> Ex :ALTER TABLE table SET (
>    autovacuum_freeze_max_age =3D 20000000,(2 crores)
>    autovacuum_multixact_freeze_max_age =3D 20000000,(2 crores)
>    autovacuum_freeze_min_age =3D 0
> );
> Regards,
> Durga Mahesh
>

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

<div dir=3D"ltr">Hi Durga<div><br><div><b>autovacuum_freeze_max_age</b>=C2=
=A0specifies the maximum age (in transactions) that a table&#39;s tuples ca=
n reach before a vacuum is forced to prevent transaction ID wraparound. whe=
n the age of the oldest tuple in the table exceeds this value, an autovacuu=
m is triggered to freeze the tuples.</div><div><b>Recommendation =3D=C2=A02=
0000000 -&gt;=C2=A0150000000=C2=A0</b></div><div><br><b>autovacuum_multixac=
t_freeze_max_age </b>It is<b>=C2=A0</b>similar to above, but applies to mul=
ti-transaction IDs (used for shared row locks). when the age of the oldest =
multi-transaction ID exceeds this value, an autovacuum is triggered to free=
ze the multi-transaction IDs.<br><b>Recommendation =3D=C2=A020000000 -&gt;=
=C2=A0150000000</b></div><div><b><br>autovacuum_freeze_min_age</b> specifie=
s the minimum age (in transactions) that a tuple must reach before it is co=
nsidered for freezing. Lowering this value can cause more frequent freezing=
, which can increase the overhead of autovacuum.<br></div><div><b>Recommend=
ation =3D 0 -&gt;=C2=A0<span style=3D"background-color:transparent;color:in=
herit;font-family:monospace,monospace;font-size:inherit">50000000</span></b=
><br></div><div><b><span style=3D"background-color:transparent;color:inheri=
t;font-family:monospace,monospace;font-size:inherit"><br></span></b></div><=
div><span style=3D"background-color:transparent;color:inherit;font-size:inh=
erit"><font face=3D"arial, sans-serif">Thanks, Semab</font></span></div></d=
iv></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_att=
r">On Sun, Aug 11, 2024 at 11:12=E2=80=AFAM Durgamahesh Manne &lt;<a href=
=3D"mailto:maheshpostgres9@gmail.com">maheshpostgres9@gmail.com</a>&gt; wro=
te:<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"><div dir=3D"=
ltr">Hi=C2=A0 Respected Team,<div><br></div><div>Could you please=C2=A0let =
me know that how this freeze parameters work</div><div>Update query runs on=
 table=C2=A0 through which data being modified daily in this case</div><div=
>Total records in table is about 20lakhs</div><div>current setting for this=
=C2=A0table is=C2=A0</div><div>Access method: heap</div><div>if it reaches =
&gt; 0.1*2000000+1000 =3D 2,10,000 as per the formula autovacuum triggers<b=
r>Options: fillfactor=3D85, autovacuum_vacuum_cost_delay=3D0, autovacuum_va=
cuum_cost_limit=3D3000, parallel_workers=3D6, autovacuum_vacuum_scale_facto=
r=3D0.1, autovacuum_vacuum_threshold=3D1000, autovacuum_freeze_max_age=3D20=
000000, autovacuum_multixact_freeze_max_age=3D20000000, autovacuum_freeze_m=
in_age=3D0<br></div><div><br></div><div>How autovacuum freeze parameters wo=
rk.Give me some recommendations to improve the performance better than now=
=C2=A0</div><div>Ex :ALTER TABLE table SET (<br>=C2=A0 =C2=A0autovacuum_fre=
eze_max_age =3D 20000000,(2 crores)<br>=C2=A0 =C2=A0autovacuum_multixact_fr=
eeze_max_age =3D 20000000,(2 crores)<br>=C2=A0 =C2=A0autovacuum_freeze_min_=
age =3D 0<br>);<br>Regards,</div><div>Durga Mahesh=C2=A0</div></div>
</blockquote></div>

--000000000000d99e2a061f7f1b5b--