Thanks David for your replies.
On Thu, May 7, 2020 at 11:01 PM David Rowley <dgrowleyml@xxxxxxxxx> wrote:
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
- 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
