MIME-Version: 1.0
References: <CAJCZko+yQzSRaHa_U2cQ3aeHtMKcyrgOSnVjoZkJsH2LH3f69g@mail.gmail.com>
 <CAG=z8NRYzV2buVyBz5Rb-SQWt9fU8+H0oioaA2SejPKo=qELOg@mail.gmail.com>
In-Reply-To: <CAG=z8NRYzV2buVyBz5Rb-SQWt9fU8+H0oioaA2SejPKo=qELOg@mail.gmail.com>
From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Date: Wed, 14 Aug 2024 01:08:53 +0530
Message-ID: <CAJCZkoK_p63AW9_yVaxomo0hWNvwrgaR0b7qGMAU84VxuJxmkA@mail.gmail.com>
Subject: Re: autovacuum freeze recommendations at table level
To: semab tariq <semabtariq1@gmail.com>, laurenz.albe@cybertec.at
Cc: pgsql-general@lists.postgresql.org, pgsql-in-general@postgresql.org, 
	Christoph Berg <myon@debian.org>
Content-Type: multipart/alternative; boundary="0000000000002f12e4061f95bc22"
Archived-At: <https://www.postgresql.org/message-id/CAJCZkoK_p63AW9_yVaxomo0hWNvwrgaR0b7qGMAU84VxuJxmkA%40mail.gmail.com>
Precedence: bulk

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

Hi Semab

ALTER TABLE table SET (
   autovacuum_freeze_max_age =3D 60000000,(6 crores)
   autovacuum_multixact_freeze_max_age =3D 60000000,(6 crores)
   autovacuum_freeze_min_age =3D 0
);
I set this but autovacuum to prevent wraparound runs for every 10 minutes
on the table being modified in this case
How to minimize the impact of this ?
There is no option to set naptime at table level
I could see total records around 40 lakhs that are being modified
Toast considers large objects but I use parameters without toast.

Regards,
Durga Mahesh Manne


On Mon, Aug 12, 2024 at 10:07=E2=80=AFPM semab tariq <semabtariq1@gmail.com=
> wrote:

> 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 triggere=
d
> 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 th=
is
> value can cause more frequent freezing, which can increase the overhead o=
f
> 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 autovac=
uum
>> 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_ag=
e=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
>>
>

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

<div dir=3D"ltr"><div>Hi=C2=A0Semab</div><div><br></div><div>ALTER TABLE ta=
ble SET (<br>=C2=A0 =C2=A0autovacuum_freeze_max_age =3D 60000000,(6 crores)=
<br>=C2=A0 =C2=A0autovacuum_multixact_freeze_max_age =3D 60000000,(6 crores=
)<br>=C2=A0 =C2=A0autovacuum_freeze_min_age =3D 0<br>);<br></div><div>I set=
 this but autovacuum to prevent wraparound runs for every 10 minutes on the=
 table being modified in this case=C2=A0</div><div>How to minimize the impa=
ct of this ?</div><div>There is no option to set naptime at table level</di=
v><div>I could see total records around 40 lakhs that are being modified=C2=
=A0</div><div>Toast considers large objects but I use parameters without to=
ast.</div><div><br></div><div>Regards,</div><div>Durga Mahesh Manne</div><d=
iv><br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail=
_attr">On Mon, Aug 12, 2024 at 10:07=E2=80=AFPM semab tariq &lt;<a href=3D"=
mailto:semabtariq1@gmail.com">semabtariq1@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"><div dir=3D"ltr">Hi Durg=
a<div><br><div><b>autovacuum_freeze_max_age</b>=C2=A0specifies the maximum =
age (in transactions) that a table&#39;s tuples can reach before a vacuum i=
s forced to prevent transaction ID wraparound. when the age of the oldest t=
uple in the table exceeds this value, an autovacuum is triggered to freeze =
the tuples.</div><div><b>Recommendation =3D=C2=A020000000 -&gt;=C2=A0150000=
000=C2=A0</b></div><div><br><b>autovacuum_multixact_freeze_max_age </b>It i=
s<b>=C2=A0</b>similar to above, but applies to multi-transaction IDs (used =
for shared row locks). when the age of the oldest multi-transaction ID exce=
eds this value, an autovacuum is triggered to freeze the multi-transaction =
IDs.<br><b>Recommendation =3D=C2=A020000000 -&gt;=C2=A0150000000</b></div><=
div><b><br>autovacuum_freeze_min_age</b> specifies the minimum age (in tran=
sactions) that a tuple must reach before it is considered for freezing. Low=
ering this value can cause more frequent freezing, which can increase the o=
verhead of autovacuum.<br></div><div><b>Recommendation =3D 0 -&gt;=C2=A0<sp=
an style=3D"background-color:transparent;color:inherit;font-family:monospac=
e,monospace;font-size:inherit">50000000</span></b><br></div><div><b><span s=
tyle=3D"background-color:transparent;color:inherit;font-family:monospace,mo=
nospace;font-size:inherit"><br></span></b></div><div><span style=3D"backgro=
und-color:transparent;color:inherit;font-size:inherit"><font face=3D"arial,=
 sans-serif">Thanks, Semab</font></span></div></div></div><br><div class=3D=
"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, Aug 11, 2024 at=
 11:12=E2=80=AFAM Durgamahesh Manne &lt;<a href=3D"mailto:maheshpostgres9@g=
mail.com" target=3D"_blank">maheshpostgres9@gmail.com</a>&gt; wrote:<br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-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 th=
at 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 re=
cords in table is about 20lakhs</div><div>current setting for this=C2=A0tab=
le 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<br>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, a=
utovacuum_multixact_freeze_max_age=3D20000000, autovacuum_freeze_min_age=3D=
0<br></div><div><br></div><div>How autovacuum freeze parameters work.Give m=
e some recommendations to improve the performance better than now=C2=A0</di=
v><div>Ex :ALTER TABLE table SET (<br>=C2=A0 =C2=A0autovacuum_freeze_max_ag=
e =3D 20000000,(2 crores)<br>=C2=A0 =C2=A0autovacuum_multixact_freeze_max_a=
ge =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>
</blockquote></div></div>

--0000000000002f12e4061f95bc22--