Postgres Pro
Downloads
Home   >   mailing lists

Re: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked - Mailing list pgsql-general

From Michael Lewis
Subject Re: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked
Date
Msg-id CAHOFxGpeZOpKQakmpY1oQeXk4zGiZtBhKMrqRjJLd+RESfTKYw@mail.gmail.com
Whole thread Raw
In response to Re: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked  (Michael Lewis <mlewis@entrata.com>)
List pgsql-general
Tree view
On Tue, Jun 16, 2020 at 2:41 PM Michael Lewis <mlewis@entrata.com> wrote:
On Tue, Jun 16, 2020 at 1:45 PM Jim Hurne <jhurne@us.ibm.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.

pgsql-general by date:

Previous
From: AC Gomez
Date:
Subject: Can the current session be notified and refreshed with a newcredentials context?
Next
From: Ron
Date:
Subject: Re: Can the current session be notified and refreshed with a newcredentials context?