From scott@journyx.com Mon Mar  15 18:45:40 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 8C64E659EF7
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 15:45:40 -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 98575-01-6
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 18:45:29 +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 2796B65AFD3
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 15:20:41 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mail.int.journyx.com (Postfix) with ESMTP id 6EE617E4025
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 13:20:40 -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 eDXRcoYz6lEX for <pgsql-admin@postgresql.org>;
	Mon, 15 Mar 2010 13:20:36 -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 26F4F7E4021
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 13:20:36 -0500 (CDT)
Date: Mon, 15 Mar 2010 13:20:35 -0500 (CDT)
From: Scott Whitney <scott@journyx.com>
Reply-To: Scott Whitney <swhitney@journyx.com>
To: pgsql-admin@postgresql.org
Message-ID: <18735708.169861268677235843.JavaMail.root@zimbra.int.journyx.com>
In-Reply-To: 
 <31967793.169841268677217557.JavaMail.root@zimbra.int.journyx.com>
Subject: Autovac vs manual with analyze
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_8405_12620711.1268677235839"
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/100
X-Sequence-Number: 33899

------=_Part_8405_12620711.1268677235839
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_8405_12620711.1268677235839
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'=
><style>p { margin: 0; }</style><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 upgrading <span class=3D"Object" id=3D"OBJ_PREFIX_DWT41=
81">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 are:<br=
>autovacuum =3D on&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # enable autovacuum subprocess?<br>=
autovacuum_naptime =3D 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp; # time between autovacuum runs, in secs<br>autovacuum_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;&nbs=
p; # min # of tuple updates before analyze<br>autovacuum_vacuum_scale_facto=
r =3D 0.2&nbsp;&nbsp;&nbsp; # fraction of rel size before<br>autovacuum_ana=
lyze_scale_factor =3D 0.1&nbsp;&nbsp; # fraction of rel size before<br>#aut=
ovacuum_vacuum_cost_delay =3D -1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # default va=
cuum cost delay for<br>#autovacuum_vacuum_cost_limit =3D -1&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp; # default vacuum cost limit for autovac<br><br>and auto-vacu=
um is running.<br><br>My problem is that each <span class=3D"Object" id=3D"=
OBJ_PREFIX_DWT4182">Saturday</span> at midnight, I have to start a vacuumdb=
 -f -z -a or my pg_clog dir never clears out.<br><br>The manual vacuum take=
s quite some time and impacts weekend customers.<br><br>So, my questions ar=
e:<br><br>a) Is the manual vacuum needed for performance reasons, or is aut=
o-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><br></=
div></div></div></body></html>
------=_Part_8405_12620711.1268677235839--



From scott@journyx.com Mon Mar  15 19:24:23 2010
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--



From alvherre@commandprompt.com Mon Mar  15 22:35:57 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 9797765A01A
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 19:35:57 -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 41804-06 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Mon, 15 Mar 2010 22:35:46 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from spinlock.commandprompt.com (host-215.commandprompt.net
	[207.173.203.215])
	by mail.postgresql.org (Postfix) with ESMTP id 29613659FA6
	for <pgsql-admin@postgresql.org>; Mon, 15 Mar 2010 19:35:46 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by spinlock.commandprompt.com (Postfix) with ESMTP id 7DFCB34803D;
	Mon, 15 Mar 2010 15:35:45 -0700 (PDT)
Received: from spinlock.commandprompt.com ([127.0.0.1])
	by localhost (spinlock.commandprompt.com.commandprompt.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id AkyQe4MeCGXs; Mon, 15 Mar 2010 15:35:45 -0700 (PDT)
Received: from perhan.alvh.no-ip.org (200-85-214-83.bk4-dsl.surnet.cl
	[200.85.214.83])
	by spinlock.commandprompt.com (Postfix) with ESMTPSA id 1926C34803A;
	Mon, 15 Mar 2010 15:35:45 -0700 (PDT)
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id 2C7EC6E8D4; Mon, 15 Mar 2010 19:35:43 -0300 (CLST)
Date: Mon, 15 Mar 2010 19:35:43 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Scott Whitney <swhitney@journyx.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Autovac vs manual with analyze
Message-ID: <20100315223543.GH3323@alvh.no-ip.org>
References: <18735708.169861268677235843.JavaMail.root@zimbra.int.journyx.com>
	<896915.170461268681046161.JavaMail.root@zimbra.int.journyx.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <896915.170461268681046161.JavaMail.root@zimbra.int.journyx.com>
User-Agent: Mutt/1.5.20 (2009-06-14)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/104
X-Sequence-Number: 33903

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 is possible) 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.



From simon@2ndQuadrant.com Thu Mar  18 09:02:56 2010
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 3056D632FB3
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 18 Mar 2010 06:02:56 -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 49249-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 18 Mar 2010 09:02:45 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outmail137193.authsmtp.co.uk (outmail137193.authsmtp.co.uk
	[62.13.137.193])
	by mail.postgresql.org (Postfix) with ESMTP id 9D96F63272C
	for <pgsql-admin@postgresql.org>; Thu, 18 Mar 2010 06:02:45 -0300 (ADT)
Received: from mail-c194.authsmtp.com (mail-c194.authsmtp.com [62.13.128.121])
	by punt7.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id
	o2I92SEL058388; Thu, 18 Mar 2010 09:02:28 GMT
Received: from [192.168.0.4] (88-110-151-22.dynamic.dsl.as9105.com
	[88.110.151.22]) (authenticated bits=0)
	by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id o2I92Njs011191;
	Thu, 18 Mar 2010 09:02:23 GMT
Subject: Re: Autovac vs manual with analyze
From: Simon Riggs <simon@2ndQuadrant.com>
To: Scott Whitney <swhitney@journyx.com>
Cc: pgsql-admin@postgresql.org
In-Reply-To: 
 <18735708.169861268677235843.JavaMail.root@zimbra.int.journyx.com>
References: <18735708.169861268677235843.JavaMail.root@zimbra.int.journyx.com>
Content-Type: text/plain
Date: Thu, 18 Mar 2010 09:02:22 +0000
Message-Id: <1268902942.3827.1696.camel@ebony>
Mime-Version: 1.0
X-Mailer: Evolution 2.26.1 
Content-Transfer-Encoding: 7bit
X-Server-Quench: f7a32745-326c-11df-80b9-0022640b883e
X-AuthReport-Spam: If SPAM / abuse - report it at:
	http://www.authsmtp.com/abuse
X-AuthRoute: OCdxZQATClZOTQEd DAteCiN5VAwpPBRK HVkIKg5MJUcNSQVJ
	NksadBtFaQ1bYFhD HGQLWlREUFR7WWF/ aQkfZQ1DY0tOQQRv
	UVZLQE1XHAJ3AVJe BGcXUGcoNAVHeX95 ZAhjV3hZEhJ9JEV/
	RUpRCG4EYzR9aWFL Bl1Qd1FdbQNKfB1D blAtXHsONCtlM3Bw
	LCYfGhEXCH12NThS XAgGZUwJCUMCGCM1 Q1gdHCkvVUQDTiEi PRFO
X-Authentic-SMTP: 61633235383639.1015:706/Kp
X-AuthFastPath: 255
X-Virus-Status: No virus detected - but ensure you scan with your own
	anti-virus system.
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/119
X-Sequence-Number: 33918

On Mon, 2010-03-15 at 13:20 -0500, Scott Whitney wrote:

> 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.

Why is your pg_clog dir a problem?

> So, my questions are:
> 
> a) Is the manual vacuum needed for performance reasons, or is auto-vac
> sufficient?

If you don't know why you are running it, then you should skip it and
measure the difference. 

-- 
 Simon Riggs           www.2ndQuadrant.com