From: | Durgamahesh Manne <maheshpostgres9(at)gmail(dot)com> |
---|---|
To: | semab tariq <semabtariq1(at)gmail(dot)com>, laurenz(dot)albe(at)cybertec(dot)at |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org, pgsql-in-general(at)postgresql(dot)org, Christoph Berg <myon(at)debian(dot)org> |
Subject: | Re: autovacuum freeze recommendations at table level |
Date: | 2024-08-13 19:38:53 |
Message-ID: | CAJCZkoK_p63AW9_yVaxomo0hWNvwrgaR0b7qGMAU84VxuJxmkA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-in-general |
Hi Semab
ALTER TABLE table SET (
autovacuum_freeze_max_age = 60000000,(6 crores)
autovacuum_multixact_freeze_max_age = 60000000,(6 crores)
autovacuum_freeze_min_age = 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 PM semab tariq <semabtariq1(at)gmail(dot)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 = 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 <
> maheshpostgres9(at)gmail(dot)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 = 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
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Lok P | 2024-08-13 19:56:36 | Re: Column type modification in big tables |
Previous Message | Ron Johnson | 2024-08-13 18:32:50 | Re: PG Dump on 11 - Restore on 16 - is possible? |
From | Date | Subject | |
---|---|---|---|
Previous Message | semab tariq | 2024-08-12 16:37:17 | Re: autovacuum freeze recommendations at table level |