0

Trying to get Postgresql to keep tables a lot cleaner however even after tweaking resource limits it doesn't seem to be keeping up hardly at all.

Even after setting 

ALTER TABLE veryactivetable SET (autovacuum_vacuum_threshold = 10000);

the pg_stat_user_tables for veryactivetable returns 63356 n_dead_tup and a last_autoanalyze & last_autovacuum is over 24 hours old

posgresql.conf settings : 

shared_buffers = 7680MB
work_mem = 39321kB

maintenance_work_mem = 1920MB

vacuum_cost_delay = 0
vacuum_cost_page_hit = 1000
vacuum_cost_page_miss = 1000
vacuum_cost_page_dirty = 2000
vacuum_cost_limit = 7000


autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 10
autovacuum_naptime = 10s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = 50ms
autovacuum_vacuum_cost_limit = 7000
PrasadK
  • 778
  • 6
  • 17
Toomuchcode
  • 1
  • 1

1 Answers1

0

Set autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor to a higher value, probably best back to the default. No need to let autovacuum run more often than necessary, particularly in your situation. The idea is to make it finish fast once it starts.

Set autovacuum_naptime higher, closer to the original default of one minute.

Restore autovacuum_max_workers to 3 unless you have a lot of databases or a lot of tables.

What you should do to make autovacuum finish as fast as possible (which is the goal) is to set autovacuum_vacuum_cost_delay to 0.

If you have just a few very busy tables, it is best to set it on those tables like you show in your question.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263