Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 24932643669
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 14:25:45 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 80138-04-3
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 17:25:32 +0000 (UTC)
X-Greylist: delayed 00:06:15.300798 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 EDF37643632
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 14:25:31 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mail.int.journyx.com (Postfix) with ESMTP id C32D31BC1EB
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 12:19:14 -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 wY5uEL867cyJ for <pgsql-admin@postgresql.org>;
	Mon, 15 Mar 2010 12:19:10 -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 1FB6A1BC1EA
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 11:57:57 -0500 (CDT)
Date: Mon, 15 Mar 2010 11:57:56 -0500 (CDT)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: pgsql-admin@postgresql.org
Message-ID: <14937670.169651268672276718.JavaMail.root@zimbra.int.journyx.com>
Subject: Autovac versus manual vac with analyze
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_8393_3009547.1268672276712"
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/99
X-Sequence-Number: 33898

------=_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

<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'=
>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.<br><br>My auto=
-vac parameters are:<br>autovacuum =3D on&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # enable aut=
ovacuum subprocess?<br>autovacuum_naptime =3D 3&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp; # time between autovacuum runs, in secs<br>autov=
acuum_vacuum_threshold =3D 400&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # min # =
of tuple updates before vacuum<br>autovacuum_analyze_threshold =3D 200&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp; # min # of tuple updates before analyze<br>autova=
cuum_vacuum_scale_factor =3D 0.2&nbsp;&nbsp;&nbsp; # fraction of rel size b=
efore<br>autovacuum_analyze_scale_factor =3D 0.1&nbsp;&nbsp; # fraction of =
rel size before<br>#autovacuum_vacuum_cost_delay =3D -1&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp; # default vacuum cost delay for<br>#autovacuum_vacuum_cost_limit=
 =3D -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # default vacuum cost limit for autov=
ac<br><br>and auto-vacuum is running.<br><br>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.<br><br>The manual vacuum takes quite some time and impacts wee=
kend customers.<br><br>So, my questions are:<br><br>a) Is the manual vacuum=
 needed for performance reasons, 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) d=
o it?<br><br><br><br></div></body></html>
------=_Part_8393_3009547.1268672276712--