I’m having some trouble figuring out what was going on that led up to this, but after neglecting maintenance for some
time,I noticed that even though autovacuum was not disabled, it never seemed to be getting around to vacuuming.
Some basics - PG 9.2, about 150 databases, autovacuum default settings
After an initial read of some vacuuming tips, I came up with the following settings:
autovacuum = on # Enable autovacuum subprocess? 'on'
log_autovacuum_min_duration = 100 # -1 disables, 0 logs all actions and
autovacuum_max_workers = 8 # max number of autovacuum subprocesses
autovacuum_naptime = 40min # time between autovacuum runs
autovacuum_vacuum_scale_factor = 0.1 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
autovacuum_vacuum_cost_delay = 10ms # default vacuum cost delay for
# autovacuum, in milliseconds;
autovacuum_vacuum_cost_limit = 1000 # default vacuum cost limit for
# autovacuum, -1 means use
My main interest there actually was getting logs, but something obviously kicked in as I started seeing a number of
databases/tablesgetting vacuumed that had not been vacuumed in over a year. Some of these tables have at least a few
thousandinsert/delete/updates per day, some much more than that.
Was the combination of the large(?) number of databases and default settings effectively preventing autovacuum from
doingits thing?
Thanks,
Charles
