Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 345D465011B
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri,  5 Sep 2008 17:07:28 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 40467-06-3 for
	<pgsql-admin-postgresql.org@postgresql.org>;
	Fri,  5 Sep 2008 17:07:22 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from bay0-omc3-s32.bay0.hotmail.com (bay0-omc3-s32.bay0.hotmail.com
	[65.54.246.232])
	by postgresql.org (Postfix) with ESMTP id E39516500FB
	for <pgsql-admin@postgresql.org>; Fri,  5 Sep 2008 17:07:22 -0300 (ADT)
Received: from BAY126-W4 ([65.55.131.39]) by bay0-omc3-s32.bay0.hotmail.com
	with Microsoft SMTPSVC(6.0.3790.3959);
	Fri, 5 Sep 2008 13:07:19 -0700
Message-ID: <BAY126-W494933EEE8D519125B0809C580@phx.gbl>
Content-Type: multipart/alternative;
	boundary="_e0e5797f-b51d-4597-a00e-242be3cab60f_"
X-Originating-IP: [64.235.97.59]
From: Arctic Toucan <arctic_toucan@hotmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: <pgsql-admin@postgresql.org>
Subject: Re: How can I avoid Frozenxid wraparound on failover to a
	standby(PITR) database?
Date: Fri, 5 Sep 2008 16:07:20 -0400
Importance: Normal
In-Reply-To: <29354.1220640349@sss.pgh.pa.us>
References: <BAY126-W41BCC3E747431387BDB0999C580@phx.gbl>
	<26690.1220633537@sss.pgh.pa.us>
	<BAY126-W7398550C91F253B656DCC9C580@phx.gbl>
	<29354.1220640349@sss.pgh.pa.us>
MIME-Version: 1.0
X-OriginalArrivalTime: 05 Sep 2008 20:07:19.0815 (UTC)
	FILETIME=[00675170:01C90F93]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.607 tagged_above=0 required=5
	tests=HTML_FONT_FACE_BAD=0.606, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200809/51
X-Sequence-Number: 30144

--_e0e5797f-b51d-4597-a00e-242be3cab60f_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

If I recall correctly=2C the reason we originally set autovacuum_freeze_max=
_age =3D 2=2C000=2C000=2C000 was that most of the data in the DB is a rolli=
ng window(partitioned tables) and so by having the max age so high most of =
the tables in the DB would never need to be vacuumed at all thus allowing t=
he autovacuum to concentrate on the high update rate of the two tables of c=
oncern.=20
=20
It seems that we may have gotten to the point where our rolling window exce=
eds 2billion transactions and that approach is no longer viable so we need =
to change our max_age as you suggest.
=20
Thanks=2C as usual=2C for the help.Regards...
=20
Mark



> To: arctic_toucan@hotmail.com> CC: pgsql-admin@postgresql.org> Subject: R=
e: [ADMIN] How can I avoid Frozenxid wraparound on failover to a standby(PI=
TR) database? > Date: Fri=2C 5 Sep 2008 14:45:49 -0400> From: tgl@sss.pgh.p=
a.us> > Arctic Toucan <arctic_toucan@hotmail.com> writes:> > My standard se=
ttings in the config file are:> > autovacuum_freeze_max_age =3D 2=2C000=2C0=
00=2C000> > vacuum_freeze_min_age =3D 100=2C000=2C000> > Ah=2C well=2C ther=
e's the issue. It could be expected that no tuple> freezing would happen be=
fore autovacuum_freeze_max_age. I'd take a zero> off that value I think ---=
 you're not leaving yourself a lot of daylight> between freeze_max_age and =
wraparound. You could get away with that> setting in a smaller database tha=
t didn't take so long to vacuum=2C but> in a bigger one you want to freeze =
a bit more aggressively IMHO.> > regards=2C tom lane
_________________________________________________________________


--_e0e5797f-b51d-4597-a00e-242be3cab60f_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style>
.hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt=3B
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class=3D'hmmessage'>If I recall correctly=2C the reason we originally=
 set <FONT face=3D"">autovacuum_freeze_max_age</FONT> =3D 2=2C000=2C000=2C0=
00 was that most of the data in the DB is a rolling window(partitioned tabl=
es) and so by having the max age so high most of the tables in the DB would=
 never need to be vacuumed at all thus&nbsp=3Ballowing the autovacuum to co=
ncentrate on the high update rate of the two tables of concern. <BR>
&nbsp=3B<BR>
It seems that we may have gotten to the point where our rolling window exce=
eds 2billion transactions and that approach is no longer viable&nbsp=3Bso w=
e need to change our max_age as you suggest.<BR>
&nbsp=3B<BR>
Thanks=2C as usual=2C for the help.<BR><BR>Regards...<BR>
&nbsp=3B<BR>
Mark<BR><BR><BR>

<HR id=3DstopSpelling>
<BR>
&gt=3B To: arctic_toucan@hotmail.com<BR>&gt=3B CC: pgsql-admin@postgresql.o=
rg<BR>&gt=3B Subject: Re: [ADMIN] How can I avoid Frozenxid wraparound on f=
ailover to a standby(PITR) database? <BR>&gt=3B Date: Fri=2C 5 Sep 2008 14:=
45:49 -0400<BR>&gt=3B From: tgl@sss.pgh.pa.us<BR>&gt=3B <BR>&gt=3B Arctic T=
oucan &lt=3Barctic_toucan@hotmail.com&gt=3B writes:<BR>&gt=3B &gt=3B My sta=
ndard settings in the config file are:<BR>&gt=3B &gt=3B autovacuum_freeze_m=
ax_age =3D 2=2C000=2C000=2C000<BR>&gt=3B &gt=3B vacuum_freeze_min_age =3D 1=
00=2C000=2C000<BR>&gt=3B <BR>&gt=3B Ah=2C well=2C there's the issue. It cou=
ld be expected that no tuple<BR>&gt=3B freezing would happen before autovac=
uum_freeze_max_age. I'd take a zero<BR>&gt=3B off that value I think --- yo=
u're not leaving yourself a lot of daylight<BR>&gt=3B between freeze_max_ag=
e and wraparound. You could get away with that<BR>&gt=3B setting in a small=
er database that didn't take so long to vacuum=2C but<BR>&gt=3B in a bigger=
 one you want to freeze a bit more aggressively IMHO.<BR>&gt=3B <BR>&gt=3B =
regards=2C tom lane<BR><BR><br /><hr />Upgrade to Hotmail Plus and share mo=
re photos with bigger attachments. Click here to find out how <a href=3D'ht=
tp://get.live.com/en-ca/mailplus/features' target=3D'_new'>Click here to fi=
nd out how</a></body>
</html>=

--_e0e5797f-b51d-4597-a00e-242be3cab60f_--