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 <semabtar...@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 = 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 < > maheshpostgr...@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 = 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 >> >
