From: | Stephen Froehlich <s(dot)froehlich(at)cablelabs(dot)com> |
---|---|
To: | Efraín Déctor <efraindector(at)motumweb(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Strange problem with autovacuum |
Date: | 2017-11-14 16:15:35 |
Message-ID: | CY1PR0601MB192702DC2A8AEDF0B7C499A6E5280@CY1PR0601MB1927.namprd06.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
What are your checkpoint / wal settings?
Are there any log entries at those times about autovacuums being cancelled? The log will typically give a reason.
--Stephen
From: pgsql-novice-owner(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Efraín Déctor
Sent: Tuesday, November 14, 2017 8:46 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Strange problem with autovacuum
CableLabs WARNING: The sender of this email could not be validated and may not match the person in the "From" field.
Hello list.
I have a strange problem with one of our servers (PostgreSQl 9.2.8). Every monday the autovacuum workers seems to stop working (the workers decrease in number) this causes that 5 tables that are constantly updated and deleted to be really slow.
[cid:image001(dot)png(at)01D35D29(dot)1F9C36D0]
I have this settings on postgresql.conf:
maintenance_work_mem = 7936MB
autovacuum = on
#log_autovacuum_min_duration = -1
autovacuum_max_workers = 12
#autovacuum_naptime = 1min
#autovacuum_vacuum_threshold = 50
#autovacuum_analyze_threshold = 50
#autovacuum_vacuum_scale_factor = 0.2
#autovacuum_analyze_scale_factor = 0.1
#autovacuum_freeze_max_age = 200000000
#autovacuum_vacuum_cost_delay = 20ms
#autovacuum_vacuum_cost_limit = -1
Per table I have this settings:
datosr
autovacuum_enabled=true,
autovacuum_vacuum_threshold=50,
autovacuum_vacuum_scale_factor=0.1,
autovacuum_vacuum_cost_limit=1000
datose
autovacuum_enabled=true,
autovacuum_vacuum_threshold=50,
autovacuum_vacuum_scale_factor=0.1,
autovacuum_vacuum_cost_limit=1000
idig_h
autovacuum_enabled=true,
autovacuum_vacuum_threshold=1000,
autovacuum_vacuum_cost_limit=2000
iodig
autovacuum_enabled=true,
autovacuum_vacuum_threshold=1000,
autovacuum_vacuum_cost_limit=2000
ia
autovacuum_vacuum_threshold=50,
autovacuum_vacuum_scale_factor=.1,
autovacuum_vacuum_cost_limit=1000
I bases those settings by reading this blog entry: https://blog.2ndquadrant.com/autovacuum-tuning-basics/
It seems that I am doing something wrong, could you guys please point me in the right direction?
Thank you very much
From | Date | Subject | |
---|---|---|---|
Next Message | Efraín Déctor | 2017-11-14 16:40:08 | Re: Strange problem with autovacuum |
Previous Message | Efraín Déctor | 2017-11-14 15:45:31 | Strange problem with autovacuum |