On Fri, 8 May 2020 at 09:18, github kran <githubkran@xxxxxxxxx> wrote: > 1) We haven't changed anything related to autovacuum except a work_mem parameter which was increased to 4 GB which I believe is not related to autovacuum It might want to look into increasing vacuum_cost_limit to something well above 200 or dropping autovacuum_vacuum_cost_delay down from 20 to something much lower. However, you say you've not changed the autovacuum settings, but you've also said: > 1) I see there are 8 Vacuum workers ( Not sure what changed) running in the background and the concern I have is all of these vacuum processes are running with wrap around and while they are running The default is 3, so if you have 8 then the settings are non-standard. It might be good to supply the output of: SELECT name,setting from pg_Settings where name like '%vacuum%'; You should know that the default speed that autovacuum runs at is quite slow in 9.6. If you end up with all your autovacuum workers tied up with anti-wraparound vacuums then other tables are likely to get neglected and that could lead to stale stats or bloated tables. Best to aim to get auto-vacuum running faster or aim to perform some manual vacuums of tables that are over their max freeze age during an off-peak period to make use of the lower load during those times. Start with tables in pg_class with the largest age(relfrozenxid). You'll still likely want to look at the speed autovacuum runs at either way. Please be aware that the first time a new cluster crosses the autovacuum_freeze_max_age threshold can be a bit of a pain point as it can mean that many tables require auto-vacuum activity all at once. The impact of this is compounded if you have many tables that never receive an UPDATE/DELETE as auto-vacuum, in 9.6, does not visit those tables for any other reason. After the first time, the relfrozenxids of tables tend to be more staggered so their vacuum freeze requirements are also more staggered and that tends to cause fewer problems. David
