Обсуждение: Re: Autovac vs manual with analyze
I've got about 44GB of data in a few hundred production databases. I'm using PG 8.1.4, but upgrading today (even to the latest 8.1) is not an option. I know, I know. I wish it were, and it's slated here for q2, but I cannot even apply maintenance patches without a full testing cycle.
My auto-vac parameters are:
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 3 # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before vacuum
autovacuum_analyze_threshold = 200 # min # of tuple updates before analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac
and auto-vacuum is running.
My problem is that each Saturday at midnight, I have to start a vacuumdb -f -z -a or my pg_clog dir never clears out.
The manual vacuum takes quite some time and impacts weekend customers.
So, my questions are:
a) Is the manual vacuum needed for performance reasons, or is auto-vac sufficient?
b) How do my settings look?
c) Is there a way that the clogs get cleared via autovac, would a full vac of just template1/template0 (if that last is possible) do it?
My auto-vac parameters are:
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 3 # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400 # min # of tuple updates before vacuum
autovacuum_analyze_threshold = 200 # min # of tuple updates before analyze
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac
and auto-vacuum is running.
My problem is that each Saturday at midnight, I have to start a vacuumdb -f -z -a or my pg_clog dir never clears out.
The manual vacuum takes quite some time and impacts weekend customers.
So, my questions are:
a) Is the manual vacuum needed for performance reasons, or is auto-vac sufficient?
b) How do my settings look?
c) Is there a way that the clogs get cleared via autovac, would a full vac of just template1/template0 (if that last is possible) do it?
Scott Whitney wrote: > So, my questions are: > > a) Is the manual vacuum needed for performance reasons, or is auto-vac sufficient? > b) How do my settings look? > c) Is there a way that the clogs get cleared via autovac, would a full vac of just template1/template0 (if that last ispossible) do it? > Autovacuum should be sufficient, provided that the FSM settings are large enough to hold 20% (plus a bit of slack) of your database total size in dead tuples. If they are not, your database starts to bloat and you need ugly hacks like vacuum full to recover the dead space. In 8.1, clog is only cleared by database-wide vacuums, which IIRC autovac doesn't do unless it does a for-Xid-wraparound run, which is not often. Probably a weekly database-wide vacuum (not full, i.e. vacuumdb without -f) is enough. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.