Re: Re: PG12 autovac issues

On Wed, Mar 18, 2020 at 10:13 AM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
>
> 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.

Ah, apologies, I missed that.  They are:

alter table production.tita set
(autovacuum_analyze_scale_factor = 0, autovacuum_vacuum_scale_factor =
0, autovacuum_vacuum_threshold = 100000, autovacuum_analyze_threshold
= 50000);

>
> >
> > 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.klaver@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com