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
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 |