Re: Strange problem with autovacuum

Hello.

Checkpoint settings:

checkpoint_segments = 128
checkpoint_timeout = 1h

It doesnt seem that the workers are being cancelled (I do not have any
indication of this on the logs).

Thanks

El 14/11/2017 a las 10:15 a. m., Stephen Froehlich escribió:
>
> 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.
>
> 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/
> <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
>