|
Jeff, Your following statement made me understand this parameter better… Thank you! “If you want autovacuum_freeze_max_age to be lower just make it lower. You are basically saying you want it to behave as if it were lower, but without actually making it lower. You don't want the ratio
to be much more than 100%, but expecting it to never even get close to 100% doesn't make any sense. On a busy system, it will likely approach the value you told it to, that is what the setting is for.” At this point I am trying to comprehend
https://www.postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND I understand default for autovacuum_freeze_max_age is 200 million. I currently have this setting at 1.5 billion. I am planning on lowering this to 1 billion in the next server restart and expect the ratio to get close to 100%. With 1 billion
setting, if I base my ratio with max allowed 2 billion, the ratio will be 50%. That makes me feel better because the autovacuum will attempt to prevent wraparound when it gets close to 1 billion setting. If my understanding is wrong and if I start seeing dreaded
warning such as “HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.”
then I will have another billion leg room of transactions to set
autovacuum_freeze_max_age to while I work on manual vacuuming. But only disadvantage is I have to restart. This is that I currently have. Please comment when you are able to. Thanks again! select name, setting, unit, context, source::char(8) from pg_settings where name like '%vacuum%'; name | setting | unit | context | source ---------------------------------------+------------+------+------------+---------- autovacuum | on | | sighup | configur autovacuum_analyze_scale_factor | 0.05 | | sighup | configur autovacuum_analyze_threshold | 0 | | sighup | configur autovacuum_freeze_max_age | 1500000000 | | postmaster | configur autovacuum_max_workers | 5 | | postmaster | configur autovacuum_multixact_freeze_max_age | 400000000 | | postmaster | default autovacuum_naptime | 60 | s | sighup | configur autovacuum_vacuum_cost_delay | -1 | ms | sighup | configur autovacuum_vacuum_cost_limit | -1 | | sighup | configur autovacuum_vacuum_insert_scale_factor | 0.1 | | sighup | configur autovacuum_vacuum_insert_threshold | 0 | | sighup | configur autovacuum_vacuum_scale_factor | 0.1 | | sighup | configur autovacuum_vacuum_threshold | 0 | | sighup | configur autovacuum_work_mem | -1 | kB | sighup | default log_autovacuum_min_duration | 0 | ms | sighup | configur vacuum_cost_delay | 2 | ms | user | configur vacuum_cost_limit | 200 | | user | configur vacuum_cost_page_dirty | 20 | | user | default vacuum_cost_page_hit | 1 | | user | default vacuum_cost_page_miss | 2 | | user | default vacuum_defer_cleanup_age | 0 | | sighup | default vacuum_failsafe_age | 1600000000 | | user | default vacuum_freeze_min_age | 50000000 | | user | default vacuum_freeze_table_age | 150000000 | | user | default vacuum_multixact_failsafe_age | 1600000000 | | user | default vacuum_multixact_freeze_min_age | 5000000 | | user | default vacuum_multixact_freeze_table_age | 150000000 | | user | default (27 rows) From: Jeff Janes <jeff.janes@xxxxxxxxx> On Fri, May 19, 2023 at 11:36 AM Murthy Nunna <mnunna@xxxxxxxx> wrote:
Why? The age of datfrozenxid needs to be kept below 2 billion, but you should already be staying way below that, without needing to do anything. What benefit do you think you will accrue from keeping it even more lower? > ...So, eventually I am afraid I have to vacuum the tables manually Autovacuum to prevent wrap around will automatically kick in at autovacuum_freeze_max_age (if nothing makes it happen sooner). There is no need to do a manual vacuum to accomplish that, so nothing needs to be done to avoid it.
While manual vacuums are unthrottled by default, you can change vacuum_cost_delay to be the same as autovacuum_vacuum_cost_delay so they will be throttled in the same way as autovac is. So if you were to have a good reason to do regular
manual vacuums (which I don't think you do), this would not be much of a counterargument. (There are other counterarguments which are better, like autocancelling upon lock conflicts, or just not wanting to write your own scheduling code when autovacuum already
exists.)
If you want autovacuum_freeze_max_age to be lower just make it lower. You are basically saying you want it to behave as if it were lower, but without actually making it lower. You don't want the ratio to be much more than 100%, but expecting
it to never even get close to 100% doesn't make any sense. On a busy system, it will likely approach the value you told it to, that is what the setting is for. Cheers, Jeff |
