On Fri, 2023-05-19 at 15:35 +0000, Murthy Nunna wrote:
> Following are the settings I have. I am wondering if there is a way autovacuum can keep the above
> “datfrozenxid” low and not keep increasing. Thank you ahead time for reading my post.
>
> postgres=# select name, setting, unit from pg_settings where name like '%autovacuum%';
> name | setting | unit
> ---------------------------------------+------------+------
> autovacuum | on |
> autovacuum_analyze_scale_factor | 0.1 |
> autovacuum_analyze_threshold | 5000 |
> autovacuum_freeze_max_age | 1500000000 |
> autovacuum_max_workers | 5 |
> autovacuum_multixact_freeze_max_age | 400000000 |
> autovacuum_naptime | 60 | s
> autovacuum_vacuum_cost_delay | 2 | ms
> autovacuum_vacuum_cost_limit | -1 |
> autovacuum_vacuum_insert_scale_factor | 0 |
> autovacuum_vacuum_insert_threshold | 5000 |
> autovacuum_vacuum_scale_factor | 0 |
> autovacuum_vacuum_threshold | 5000 |
> autovacuum_work_mem | -1 | kB
> log_autovacuum_min_duration | 0 | ms
> (15 rows)
There is no need to keep "age(datfrozenxid)" low, but if you want to do that, the correct way
is to reduce "autovacuum_multixact_freeze_max_age".
Your settings are pretty crazy. You won't succeed in anything but having autovacuum run
all the time. You should reset the thresholds and scale_factors to the default values.
To make autovacuum more aggressive (if that is indeed necessary), reduce
"autovacuum_vacuum_cost_delay". I wouldn't do that globally, but only on the tables that
receive lots of updates and deletes.
Yours,
Laurenz Albe