Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 59A57635DF3
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 16:24:23 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 06072-03 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 19:24:12 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.int.journyx.com (mail.journyx.com [66.219.41.227])
	by mail.postgresql.org (Postfix) with ESMTP id 95138643DC4
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 16:24:12 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mail.int.journyx.com (Postfix) with ESMTP id 978A2CC004
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 14:24:11 -0500 (CDT)
X-Virus-Scanned: amavisd-new at zimbra.int.journyx.com
Received: from mail.int.journyx.com ([127.0.0.1])
	by localhost (mail.int.journyx.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id QVkHVBt++xv9 for <pgsql-admin@postgresql.org>;
	Mon, 15 Mar 2010 14:24:06 -0500 (CDT)
Received: from mail.int.journyx.com (mail.int.journyx.com [192.168.100.5])
	by mail.int.journyx.com (Postfix) with ESMTP id 6B09BCC002
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 14:24:06 -0500 (CDT)
Date: Mon, 15 Mar 2010 14:24:06 -0500 (CDT)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: pgsql-admin@postgresql.org
Message-ID: <896915.170461268681046161.JavaMail.root@zimbra.int.journyx.com>
In-Reply-To: 
 <18735708.169861268677235843.JavaMail.root@zimbra.int.journyx.com>
Subject: Re: Autovac vs manual with analyze
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_8441_9732775.1268681046157"
X-Originating-IP: [192.168.100.188]
X-Mailer: Zimbra 5.0.18_GA_3012.RHEL4 (ZimbraWebClient - FF3.0
	(Win)/5.0.18_GA_3012.RHEL4)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201003/96
X-Sequence-Number: 33895

------=_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>&nbsp;<br>My auto-vac parameters a=
re:<br>autovacuum =3D on&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # enable autovacuum subproces=
s?<br>autovacuum_naptime =3D 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp; # time between autovacuum runs, in secs<br>autovacuum_vacuum_thre=
shold =3D 400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min # of tuple updates =
before vacuum<br>autovacuum_analyze_threshold =3D 200&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; # min # of tuple updates before analyze<br>autovacuum_vacuum_scale=
_factor =3D 0.2&nbsp;&nbsp;&nbsp; # fraction of rel size before<br>autovacu=
um_analyze_scale_factor =3D 0.1&nbsp;&nbsp; # fraction of rel size before<b=
r>#autovacuum_vacuum_cost_delay =3D -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # defa=
ult vacuum cost delay for<br>#autovacuum_vacuum_cost_limit =3D -1&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp; # default vacuum cost limit for autovac<br><br>and aut=
o-vacuum is running.<br>&nbsp;<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--