Re: AutoVacuum and growing transaction XID's

Thanks David for your replies.

On Thu, May 7, 2020 at 11:01 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Fri, 8 May 2020 at 09:18, github kran <githubkran(at)gmail(dot)com> 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
>

- Yes I said it was originally 3 but I noticed the work_mem parameter
was changed few weeks back to 4 GB and then from that day onwards there is
an increasing trend of the MaxUsedTransactionIds from 200 Million to 347
million ( It's growing day by day from last 2 -3 weeks)
- Do you think there could be a formula on how the workers could have
increased based on this increase in WORK_MEM controlled by database ?.

> 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%';
>
Output of vacuum

name setting min_val max_val boot_val reset_val
autovacuum on null null on on
autovacuum_analyze_scale_factor 0.02 0 100 0.1 0.02
autovacuum_analyze_threshold 50 0 2147483647 50 50
autovacuum_freeze_max_age 200000000 100000 2000000000 200000000 200000000
autovacuum_max_workers 8 1 262143 3 8
autovacuum_multixact_freeze_max_age 400000000 10000 2000000000 400000000
400000000
autovacuum_naptime 5 1 2147483 60 5
autovacuum_vacuum_cost_delay 5 -1 100 20 5
autovacuum_vacuum_cost_limit -1 -1 10000 -1 -1
autovacuum_vacuum_scale_factor 0.05 0 100 0.2 0.05
autovacuum_vacuum_threshold 50 0 2147483647 50 50
autovacuum_work_mem -1 -1 2147483647 -1 -1

>
> 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.
>

The current situation I have is the auto vacuum kicked with 8 tables with
each of those tied to each worker and it's running very slow in 9.6 as you
mentioned
i observed VACUUM on those 8 tables is running from last 15 hrs and
other process are running for 1 hr+ and others for few minutes for
different tables.

Finally I would wait for your reply to see what could be done for this
VACUUM and growing TXIDs values.

- Do you think I should consider changing back the work_mem back to 4
MB what it was originally ?
- Can I apply your recommendations on a production instance directly
or you prefer me to apply initially in other environment before applying on
Prod ?
- Also like I said I want to clean up few unused tables OR MANUAL
VACUUM but current system doesn't allow me to do it considering these
factors.
- I will try to run VACUUM Manually during off peak hrs , Can I STOP
the Manual VACUUM process if its take more than 10 minutes or what is the
allowed time in mins I can have it running ?.

David
>