------=_Part_8441_9732775.1268681046157
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_8441_9732775.1268681046157
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><style type=3D'text/css'>p { margin: 0; }</style></head><body><=
div style=3D'font-family: Times New Roman; font-size: 12pt; color: #000000'=
><div style=3D"font-family: Times New Roman; font-size: 12pt; color: rgb(0,=
0, 0);"><div style=3D"font-family: Times New Roman; font-size: 12pt; color=
: rgb(0, 0, 0);"><style>p { margin: 0; }</style><div style=3D"font-family: =
Times New Roman; font-size: 12pt; color: rgb(0, 0, 0);">I've got about 44GB=
of data in a few hundred production databases. I'm using PG 8.1.4, but upg=
rading <span class=3D"Object" id=3D"OBJ_PREFIX_DWT4181">today</span>
(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.<br> <br>My auto-vac parameters a=
re:<br>autovacuum =3D on &nb=
sp; # enable autovacuum subproces=
s?<br>autovacuum_naptime =3D 3 &nb=
sp; # time between autovacuum runs, in secs<br>autovacuum_vacuum_thre=
shold =3D 400 # min # of tuple updates =
before vacuum<br>autovacuum_analyze_threshold =3D 200 &nbs=
p; # min # of tuple updates before analyze<br>autovacuum_vacuum_scale=
_factor =3D 0.2 # fraction of rel size before<br>autovacu=
um_analyze_scale_factor =3D 0.1 # fraction of rel size before<b=
r>#autovacuum_vacuum_cost_delay =3D -1 # defa=
ult vacuum cost delay for<br>#autovacuum_vacuum_cost_limit =3D -1 &nbs=
p; # default vacuum cost limit for autovac<br><br>and aut=
o-vacuum is running.<br> <br>My problem is that each <span class=3D"Ob=
ject" id=3D"OBJ_PREFIX_DWT4182">Saturday</span> at midnight, I have to star=
t a vacuumdb -f -z -a or my pg_clog dir never clears out.<br><br>The manual=
vacuum takes quite some time and impacts weekend customers.<br><br>So, my =
questions are:<br><br>a) Is the manual vacuum needed for performance reason=
s, or is auto-vac sufficient?<br>b) How do my settings look?<br>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?<br><br></div>=
</div></div></div></body></html>
------=_Part_8441_9732775.1268681046157--
