Re: Fwd: PG12 autovac issues

On 3/18/20 6:57 AM, Justin King wrote:
Please reply to list also
Ccing list

>>> Here are the settings, these are the only ones that are not set to
>>> default with the exception of a few tables that have been overridden
>>> with a different value due to lots of updates and few rows:
>>
>> And those values are?
>
> Thanks for the response, hopefully this will help:

The below is helpful, but what I was referring to above was the settings
for the overridden tables.

>
> postgres=# select name,setting from pg_settings where name like '%vacuum%';
> name = setting
> autovacuum = on
> autovacuum_analyze_scale_factor = 0.1
> autovacuum_analyze_threshold = 2500
> autovacuum_freeze_max_age = 200000000
> autovacuum_max_workers = 8
> autovacuum_multixact_freeze_max_age = 400000000
> autovacuum_naptime = 15
> autovacuum_vacuum_cost_delay = 20
> autovacuum_vacuum_cost_limit = -1
> autovacuum_vacuum_scale_factor = 0.2
> autovacuum_vacuum_threshold = 500
> autovacuum_work_mem = -1
> log_autovacuum_min_duration = 0
> vacuum_cleanup_index_scale_factor = 0.1
> vacuum_cost_delay = 0
> vacuum_cost_limit = 1000
> vacuum_cost_page_dirty = 20
> vacuum_cost_page_hit = 1
> vacuum_cost_page_miss = 10
> vacuum_defer_cleanup_age = 0
> vacuum_freeze_min_age = 50000000
> vacuum_freeze_table_age = 150000000
> vacuum_multixact_freeze_min_age = 5000000
> vacuum_multixact_freeze_table_age = 150000000
>
>>
>> More below.
>>
>>>
>>> autovacuum = on
>>> log_autovacuum_min_duration = 0
>>> autovacuum_max_workers = 8
>>> autovacuum_naptime = 15s
>>> autovacuum_vacuum_threshold = 500
>>> autovacuum_analyze_threshold = 2500
>>> vacuum_cost_limit = 1000
>>
>> Are either of the below set > 0?:
>>
>> vacuum_cost_delay
>>
>> autovacuum_vacuum_cost_delay
>>
>>>
>>> We want fairly aggressive autovacs to keep table bloat limited -- the
>>> application latency suffers if it has to wade through dead tuples and
>>> staying near realtime is important in our environment.
>>>
>>> ** Also, it should be noted that the autovacuum_analyze_threshold is
>>> probably an incorrect value, we likely intended that to be 250 and
>>> just have now realized it after poking more at the configuration.
>>>
>>>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com