Re: autovacuum failing on pg_largeobject and disk usage of the pg_largeobject growing unchecked

On Tue, Jun 16, 2020 at 2:41 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne <jhurne(at)us(dot)ibm(dot)com> wrote:
>
>> Thanks Michael,
>>
>> Here are our current autovacuum settings:
>>
>> autovacuum | on
>> autovacuum_analyze_scale_factor | 0.1
>> autovacuum_analyze_threshold | 50
>> autovacuum_freeze_max_age | 200000000
>> autovacuum_max_workers | 3
>> autovacuum_multixact_freeze_max_age | 400000000
>> autovacuum_naptime | 1min
>> autovacuum_vacuum_cost_delay | 20ms
>> autovacuum_vacuum_cost_limit | -1
>> autovacuum_vacuum_scale_factor | 0.2
>> autovacuum_vacuum_threshold | 50
>> autovacuum_work_mem | -1
>> log_autovacuum_min_duration | 1s
>>
>
> With no autovacuum_vacuum_cost_limit and autovacuum_work_mem set the same
> as maintenance_work_mem, I wouldn't expect any difference between the
> performance of manual vs auto vacuum.
>

This was flawed. -1 for autovacuum_vacuum_cost_limit doesn't mean
unlimited, it means inherit the manual vacuum cost limit which seems likely
to be the default 200 units. Now that you have the manual vacuum done, it
might be prudent to set the autovacuum_vacuum_cost_delay to 1 or 2ms
globally, and watch to see if it stays caught up.