autovacuum was not vacuuming

From: Charles Sprickman <spork(at)biglist(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: autovacuum was not vacuuming
Date: 2015-05-27 05:02:24
Message-ID: 1E6196B0-6B63-4BC9-8E14-DF54AC2A1F34@biglist.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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/tables getting vacuumed that had not been vacuumed in over a year. Some of these tables have at least a few thousand insert/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 doing its thing?

Thanks,

Charles

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Pascal Laporte (plaporte) 2015-05-27 08:21:01 monitoring TPS and IOPS
Previous Message Rossi, Maria 2015-05-27 00:44:32 Re: refreshing a database with pg_dump and psql