Autovac versus manual vac with analyze

------=_Part_8393_3009547.1268672276712
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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?




------=_Part_8393_3009547.1268672276712
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<=
div style=3D'font-family: Times New Roman; font-size: 12pt; color: #000000'=
>I've got about 44GB of data in a few hundred production databases. I'm usi=
ng 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 =3D on     &nbs=
p;           # enable aut=
ovacuum subprocess?
autovacuum_naptime =3D 3    &nbs=
p;     # time between autovacuum runs, in secs
autov=
acuum_vacuum_threshold =3D 400       # min # =
of tuple updates before vacuum
autovacuum_analyze_threshold =3D 200 =
;     # min # of tuple updates before analyze
autova=
cuum_vacuum_scale_factor =3D 0.2    # fraction of rel size b=
efore
autovacuum_analyze_scale_factor =3D 0.1   # fraction of =
rel size before
#autovacuum_vacuum_cost_delay =3D -1   &n=
bsp;  # default vacuum cost delay for
#autovacuum_vacuum_cost_limit=
=3D -1      # default vacuum cost limit for autov=
ac

and auto-vacuum is running.

My problem is that each Saturd=
ay 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 wee=
kend 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) d=
o it?




------=_Part_8393_3009547.1268672276712--