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 CAHOFxGpzWt1eqDXvHdFCWodhSC-dGhQsSXsN=uMzH3hUd5tsqg@mail.gmail.com
Whole thread Raw
In response to RE: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked  ("Jim Hurne" <jhurne@us.ibm.com>)
Responses RE: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked  ("Jim Hurne" <jhurne@us.ibm.com>)
Re: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked  (Michael Lewis <mlewis@entrata.com>)
List pgsql-general
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. Still, if you run a manual vacuum analyze verbose, what sort of output do you get? What is maintenance_work_mem set to? Are there indexes on this table that perhaps are very large and needing to be rescanned many times because maintenance_work_mem isn't high enough to handle in a single pass? You might try "create index concurrently, drop index concurrently, & rename index" (reindex concurrently if you were on PG 12) as a sort of online 'vacuum full' on the index(es).

By the way, the best practices for these mailing list suggest partial quoting and responding in-line or below, not "top posting" with the entire conversation below.

pgsql-general by date:

Previous
From: "Jim Hurne"
Date:
Subject: RE: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked
Next
From: Andreas Joseph Krogh
Date:
Subject: Sv: autovacuum failing on pg_largeobject and disk usage of thepg_largeobject growing unchecked