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