public inbox for [email protected]  

help / color / mirror / Atom feed

From: semab tariq <[email protected]>
To: Durgamahesh Manne <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Christoph Berg <[email protected]>
Subject: Re: autovacuum freeze recommendations at table level
Date: Mon, 12 Aug 2024 21:37:17 +0500
Message-ID: <CAG=z8NRYzV2buVyBz5Rb-SQWt9fU8+H0oioaA2SejPKo=qELOg@mail.gmail.com> (raw)
In-Reply-To: <CAJCZko+yQzSRaHa_U2cQ3aeHtMKcyrgOSnVjoZkJsH2LH3f69g@mail.gmail.com>
References: <CAJCZko+yQzSRaHa_U2cQ3aeHtMKcyrgOSnVjoZkJsH2LH3f69g@mail.gmail.com>

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
>

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: autovacuum freeze recommendations at table level
  In-Reply-To: <CAG=z8NRYzV2buVyBz5Rb-SQWt9fU8+H0oioaA2SejPKo=qELOg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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