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=QTJ97hxdO6dRhugJ6tbIoIYLEV5c82WFBrUaUKHwE5BPcc4g1w9BWnFF4/RDSTJ1W11S9oVRgGLz2LT4aRiF4XEpEZwhEshrqcoh0u4x+g6yCXkgx4ER1pXqDVH9tq/sm12m5nMvITPZjvqMvq1xIulpQwrl+pDr+aE0pouNv/Y=;
References: <1340391499.61601.YahooMailNeo@web46101.mail.sp1.yahoo.com>
	<4FE47DA402000025000489E3@gw.wicourts.gov>
Message-ID: <1340394089.27581.YahooMailNeo@web46103.mail.sp1.yahoo.com>
Date: Fri, 22 Jun 2012 12:41:29 -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: <4FE47DA402000025000489E3@gw.wicourts.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Kevin --=0A=0A<...>=0A=0A>> =A0  a) quickly relieve the immediate pain=0A> =
=0A> Set autovacuum_cost_limit to a smaller value.=A0 (Change the=0A> postg=
resql.conf file and do a reload.)=A0 I would start by cutting the=0A> curre=
nt value in half.=0A> =0A=0AThanks -- would not have known how to start. Bu=
t for now, in=0Aobservance of your idea on XID wraparound, I am letting=0Ae=
verything run (this is actually a low point in regular traffic for this=0As=
ite and I am not seeing firect proof that autovac is in fact doing=0Aanythi=
ng out of the ordinary).=0A=0A>>  and=0A>> =0A>> =A0  b) prevent such issue=
s in the future (other than going to manual=0A>> =A0 =A0 =A0 vacuuming 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 transaction wraparou=
nd=0A> protection threshold.=A0 Because the vacuum necessary for this can b=
e=0A> painful, and they tend to come at the worst possible time (the more=
=0A> your workload looks like a really heavy OLTP workload at any given=0A>=
 moment, the higher the probability that this is about to happen), I=0A> al=
ways follow a bulk load (like from restoring pg_dump output) with=0A> a VAC=
UUM FREEZE ANALYZE.=0A> =0A> You might also want to consider running off-ho=
urs vacuums to=0A> supplement autovacuum.=A0 Upgrading to a more recent ver=
sion of=0A> PostgreSQL is likely to help some, too.=0A> =0ADuly noted. =0A=
=0AThanks!=0A=0AGreg=0A