DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=P5QuH7TKDRJhY+k97WC4IxDRO/5J+iT9EQZo8dcodb7MvViZWgsEntv/Qn7KzkIlPMsuilGN2ckVaGjFWjC+I7lBiL1MMD52kx2vqZXRwirSrkbNVf5eByQpio09SDQ+i+vBvaJ7pyuQj6eWbeZGFmw1iNd70rux8cpE9GLj15Y=;
References: <1340391499.61601.YahooMailNeo@web46101.mail.sp1.yahoo.com>
	<4FE47DA402000025000489E3@gw.wicourts.gov>
	<1340394089.27581.YahooMailNeo@web46103.mail.sp1.yahoo.com>
Message-ID: <1340492965.4094.YahooMailNeo@web46114.mail.sp1.yahoo.com>
Date: Sat, 23 Jun 2012 16:09:25 -0700 (PDT)
From: Greg Williamson <gwilliamson39@yahoo.com>
Reply-To: Greg Williamson <gwilliamson39@yahoo.com>
Subject: Re: autovac hitting too many tables at once
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <1340394089.27581.YahooMailNeo@web46103.mail.sp1.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

A follow up --=0A=0A=0A<...>=0A>>> =A0=A0 a) quickly relieve the immediate =
pain=0A>> =0A>> Set autovacuum_cost_limit to a smaller value.=A0 (Change th=
e=0A>> postgresql.conf file and do a reload.)=A0 I would start by cutting t=
he=0A>> current value in half.=0A>>=A0>=0A=0A>Thanks -- would not have know=
n how to start. But for now, in=0A>observance of your idea on XID wraparoun=
d, I am letting=0A>everything run (this is actually a low point in regular =
traffic for this=0A>site and I am not seeing direct proof that autovac is i=
n fact doing=0A>anything out of the ordinary).=0A>=0A=0A=0AWhile sleuthing =
logs for some data these 3 processes began to finish=0Aso the immediate iss=
ue went away. We'll make these changes next=0Aweek I suspect.=A0 Lig search=
es showed that the vast majority of our=0Aautovacs were on replication rela=
ted tables in ordinary processing=0Aso reducing the max number autovacs was=
 not to be contemplated.=0A=0A>>>=A0 and=0A>>> =0A>>> =A0=A0 b) prevent suc=
h issues in the future (other than going to manual=0A>>> =A0 =A0 =A0 vacuum=
ing on a schedule).=0A>> =0A>> If it is suddenly doing this on lots of big =
tables at once, it seems=0A>> somewhat likely that you've hit the transacti=
on wraparound=0A>> protection threshold.=A0 Because the vacuum necessary fo=
r this can be=0A>> painful, and they tend to come at the worst possible tim=
e (the more=0A>> your workload looks like a really heavy OLTP workload at a=
ny given=0A>> moment, the higher the probability that this is about to happ=
en), I=0A>> always follow a bulk load (like from restoring pg_dump output) =
with=0A>> a VACUUM FREEZE ANALYZE.=0A>> =0A>> You might also want to consid=
er running off-hours vacuums to=0A>> supplement autovacuum.=A0 Upgrading to=
 a more recent version of=0A>> PostgreSQL is likely to help some, too.=0A>>=
 =0A>Duly noted. =0A=0A=0AThe autovac processes were not the core problem, =
natch. Someone deleted=0A50000 rows from one table, generating larger delet=
es in the two other tables,=0Awhich in turn provoked replication issues, al=
l of which got autovac's attention.=0A=0AGiven time everything calmed down =
and a lesson learned has been inscribed.=0A=0ADon't do mass deletes -- thro=
ttle them back, one way or another. If you must=0Atread this path make some=
 adjustments to relevant tables and values first.=0A=0AThanks, Kevin, for t=
he quick response. =0A=0A=0AGreg W.