Thread: Re: Autovac vs manual with analyze

Re: Autovac vs manual with analyze

From
Scott Whitney
Date:
p { margin: 0; }
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?

Re: Autovac vs manual with analyze

From
Alvaro Herrera
Date:
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.