public inbox for [email protected]  

help / color / mirror / Atom feed

autovacuum freeze recommendations at table level
2+ messages / 2 participants
[nested] [flat]

* autovacuum freeze recommendations at table level
@ 2024-08-11 06:13 Durgamahesh Manne <[email protected]>
  2024-08-12 16:37 ` Re: autovacuum freeze recommendations at table level semab tariq <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Durgamahesh Manne @ 2024-08-11 06:13 UTC (permalink / raw)
  To: [email protected]; [email protected]; Christoph Berg <[email protected]>

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 = 2,10,000 as per the formula autovacuum
triggers
Options: fillfactor=85, autovacuum_vacuum_cost_delay=0,
autovacuum_vacuum_cost_limit=3000, parallel_workers=6,
autovacuum_vacuum_scale_factor=0.1, autovacuum_vacuum_threshold=1000,
autovacuum_freeze_max_age=20000000,
autovacuum_multixact_freeze_max_age=20000000, autovacuum_freeze_min_age=0

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 = 20000000,(2 crores)
   autovacuum_multixact_freeze_max_age = 20000000,(2 crores)
   autovacuum_freeze_min_age = 0
);
Regards,
Durga Mahesh


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: autovacuum freeze recommendations at table level
  2024-08-11 06:13 autovacuum freeze recommendations at table level Durgamahesh Manne <[email protected]>
@ 2024-08-12 16:37 ` semab tariq <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: semab tariq @ 2024-08-12 16:37 UTC (permalink / raw)
  To: Durgamahesh Manne <[email protected]>; +Cc: [email protected]; [email protected]; Christoph Berg <[email protected]>

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 = 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 = 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 = 0 -> 50000000*

Thanks, Semab

On Sun, Aug 11, 2024 at 11:12 AM Durgamahesh Manne <
[email protected]> 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 = 2,10,000 as per the formula autovacuum
> triggers
> Options: fillfactor=85, autovacuum_vacuum_cost_delay=0,
> autovacuum_vacuum_cost_limit=3000, parallel_workers=6,
> autovacuum_vacuum_scale_factor=0.1, autovacuum_vacuum_threshold=1000,
> autovacuum_freeze_max_age=20000000,
> autovacuum_multixact_freeze_max_age=20000000, autovacuum_freeze_min_age=0
>
> 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 = 20000000,(2 crores)
>    autovacuum_multixact_freeze_max_age = 20000000,(2 crores)
>    autovacuum_freeze_min_age = 0
> );
> Regards,
> Durga Mahesh
>


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-08-12 16:37 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-11 06:13 autovacuum freeze recommendations at table level Durgamahesh Manne <[email protected]>
2024-08-12 16:37 ` semab tariq <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox