Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 30CA264FF09 for ; Fri, 5 Sep 2008 15:09:59 -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 06877-02 for ; Fri, 5 Sep 2008 15:09:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc3-s28.bay0.hotmail.com (bay0-omc3-s28.bay0.hotmail.com [65.54.246.228]) by postgresql.org (Postfix) with ESMTP id 6BC8664FD1A for ; Fri, 5 Sep 2008 15:09:55 -0300 (ADT) Received: from BAY126-W7 ([65.55.131.42]) by bay0-omc3-s28.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Fri, 5 Sep 2008 11:08:02 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_1361da31-5e78-4b74-84ac-abba48a69ccb_" X-Originating-IP: [64.235.97.59] From: Arctic Toucan To: Tom Lane CC: Subject: Re: How can I avoid Frozenxid wraparound on failover to a standby(PITR) database? Date: Fri, 5 Sep 2008 14:08:02 -0400 Importance: Normal In-Reply-To: <26690.1220633537@sss.pgh.pa.us> References: <26690.1220633537@sss.pgh.pa.us> MIME-Version: 1.0 X-OriginalArrivalTime: 05 Sep 2008 18:08:02.0574 (UTC) FILETIME=[565B02E0:01C90F82] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200809/47 X-Sequence-Number: 30140 --_1361da31-5e78-4b74-84ac-abba48a69ccb_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable OK=2C I am glad to hear that what I thought might be the problem is fixed i= n PG 8.2. I feel much better about that :-) =20 That must mean there was something unusual about this particular DB. I have= several other "cloned" DB's configured the same way(They haven't failed ov= er yet)=2C and checking the age of frozenXIDs on them show that they are fi= ne. The primary DB box in my problem setup is completely inaccessible=2C so= I have no way to check what the FrozenXID values were there. One thing I c= ould try is forcing a failover on one of the clones and see if it exhibits = the same behaviour. From what you say I'm guessing the answer is that they = will be OK.=20 =20 My standard settings in the config file are: autovacuum_freeze_max_age =3D 2=2C000=2C000=2C000 vacuum_freeze_min_age =3D 100=2C000=2C000 This particular DB=2C has two tables that are quite heavily updated(approx = 1=2C000=2C000 rows/hour)=2C with the rest of the DB load being around 8=2C0= 00=2C000 row inserts/hour. =20 Previously=2C when I was watching pg_activity on this DB=2C the autovacuum = process seemed to be always vacuuming one or the other of those heavily upd= ated tables(typically for an hour or more at a time).=20 =20 Could another explanation for what happened be that the tables being insert= ed to=2C were not getting their XIDs updated quickly enough by the autovacu= um process on the primary DB because it was spending all its time on those = two tables? (ie. the XID wraparound problem would have ocurred even without= the failover) .=20 =20 If thats the case=2C then once I get this standby DB vacuumed and back up= =2C I will set up a cron job to look at how the max_age of the relation XID= s changes over time.=20 =20 Thanks=2C =20 Mark Sherwood > 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 12:52:17 -0400> From: tgl@sss.pgh.p= a.us> > Arctic Toucan writes:> > I next started= looking at the age(refrozenxid) of the tables in my DB=2C and was surprise= d to see that over 4000 of the 5000 tables in this DB had an age over 2Bill= ion. So thats 4000 tables representing over a terabyte of data that need to= be vacuumed! I am now vacuuming those tables one at a time=2C which is tak= ing a long time(This is a scripted process). So there is no way I could hav= e vacuumed the tables quickly enough even given a warning of impending XID = wraparound.> > > Looking through the support mailing lists(Bugs) I see some= discussion about the frozenxid updates on the master not being propogated = to the slave through the WAL logs=2C and comments from Tom=2C Alvaro and He= ikki suggesting that they were looking into a solution for PG 8.3 and neede= d a way around the problem in PG 8.2. > > Hmm ... that did get fixed in 8.2= > http://archives.postgresql.org/pgsql-committers/2006-11/msg00026.php> so = I'm a bit confused about what happened here. What were your> autovacuum set= tings on the old master? Particularly> autovacuum_freeze_max_age?> > regard= s=2C tom lane _________________________________________________________________ --_1361da31-5e78-4b74-84ac-abba48a69ccb_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable OK=2C I am glad to hear that what I thought might= be the =3Bproblem =3Bis fixed in PG 8.2. I feel much better about = that :-)
 =3B
That must mean there was something unusual about this particular DB. I have= several other "cloned" =3BDB's configured the same way(They haven't fa= iled over yet)=2C and =3Bchecking the age of frozenXIDs on them show th= at they are fine. The primary DB box in my problem setup is completely inac= cessible=2C so I have no way to check what the FrozenXID values were there.= One thing I could try is forcing a failover on one of the clones and see i= f it exhibits the same behaviour. From what you say I'm guessing the answer= is that they will be OK.
 =3B
My standard settings in the config file =3Bare:
autovacuum_freeze_max_age =3B=3D 2=2C000=2C000=2C000
vacuum_freeze_min_age =3D 100=2C000=2C000

This particular DB=2C has two tables that are =3Bquite heavily updated(= approx 1=2C000=2C000 rows/hour)=2C with the rest of the DB load =3Bbein= g around 8=2C000=2C000 row inserts/hour.
 =3B
Previously=2C when I was watching =3Bpg_activity on this DB=2C the auto= vacuum process seemed to be always vacuuming one or the other of those heav= ily updated tables(typically for an hour or more at a time).
 =3B
Could another explanation for what happened be that the tables being insert= ed to=2C were not getting their XIDs updated quickly enough by the autovacu= um process on the primary DB because it was spending all its time on those = two tables? (ie. the =3BXID wraparound problem would have ocurred even = without the failover) .
 =3B
If thats the case=2C then once I get this standby DB vacuumed and back up= =2C I will set up a cron job to look at how the max_age of the relation XID= s changes over time.
 =3B
Thanks=2C
 =3B
Mark Sherwood


>=3B To: arctic_toucan@hotmail.com
>=3B CC: pgsql-admin@postgresql.o= rg
>=3B Subject: Re: [ADMIN] How can I avoid Frozenxid wraparound on f= ailover to a standby(PITR) database?
>=3B Date: Fri=2C 5 Sep 2008 12:= 52:17 -0400
>=3B From: tgl@sss.pgh.pa.us
>=3B
>=3B Arctic T= oucan <=3Barctic_toucan@hotmail.com>=3B writes:
>=3B >=3B I next= started looking at the age(refrozenxid) of the tables in my DB=2C and was = surprised to see that over 4000 of the 5000 tables in this DB had an age ov= er 2Billion. So thats 4000 tables representing over a terabyte of data that= need to be vacuumed! I am now vacuuming those tables one at a time=2C whic= h is taking a long time(This is a scripted process). So there is no way I c= ould have vacuumed the tables quickly enough even given a warning of impend= ing XID wraparound.
>=3B
>=3B >=3B Looking through the support= mailing lists(Bugs) I see some discussion about the frozenxid updates on t= he master not being propogated to the slave through the WAL logs=2C and com= ments from Tom=2C Alvaro and Heikki suggesting that they were looking into = a solution for PG 8.3 and needed a way around the problem in PG 8.2.
&g= t=3B
>=3B Hmm ... that did get fixed in 8.2
>=3B http://archives= .postgresql.org/pgsql-committers/2006-11/msg00026.php
>=3B so I'm a bi= t confused about what happened here. What were your
>=3B autovacuum se= ttings on the old master? Particularly
>=3B autovacuum_freeze_max_age?=
>=3B
>=3B regards=2C tom lane


Get your infor= mation fix on your phone. With MSN Mobile you get regular news=2C sports an= d =A0finance updates. T= ry it today! = --_1361da31-5e78-4b74-84ac-abba48a69ccb_--