Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B9BFB2E00FE for ; Fri, 22 Feb 2008 14:15:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49063-04 for ; Fri, 22 Feb 2008 14:15:33 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.179]) by postgresql.org (Postfix) with ESMTP id 010E82E00F6 for ; Fri, 22 Feb 2008 14:15:32 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so614239waf.5 for ; Fri, 22 Feb 2008 10:15:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=HcsMRKBztcKOk8UqSx5ZEkNSGiwyBzrUt2IptJYbsBk=; b=NTEdvhD9qi/B6dqDTE4WEH7Tub4MC+3CuD56OSoB5MoGi3y68nvoxoo8lwxn3rtq5Efa8WligOYZEC+QcSCjW32itnPij2wYVxK5/A1byrBtWPJkOYosWkuU77amGuN7o8ghvtVTr9OEWoc7FszTSlbwYiz/30iBnLqJ7FCjCKw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=IsTXYy+ZuxQWIXTGoiD/mK6H9OVvsszAvbpVtbDcY9jw7eqotcO5iAhTS1v1FmUwCq619d3DYWfI9mhv6s9Zhc1sGvHoLtJP0oXJOFrfk/lsN2CpFhuCxDaRySYEiFb8r/q2R6qVKTaobPywek8zRrsiuBlvieb9z+BK7zXSJdM= Received: by 10.114.121.1 with SMTP id t1mr316574wac.67.1203704131331; Fri, 22 Feb 2008 10:15:31 -0800 (PST) Received: by 10.114.102.14 with HTTP; Fri, 22 Feb 2008 10:15:31 -0800 (PST) Message-ID: <2ca799770802221015o7d4786f4v46467b379781d7c8@mail.gmail.com> Date: Fri, 22 Feb 2008 20:15:31 +0200 From: "Mikko Partio" To: "Alvaro Herrera" Subject: Re: Stray autovacuum daemon Cc: pgsql-admin@postgresql.org In-Reply-To: <2ca799770802220901r3b4bbe4cnd64b86878336372e@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1945_13563208.1203704131321" References: <2ca799770802220305v38b800dbt909c84e3b3be57cc@mail.gmail.com> <20080222154430.GD9392@alvh.no-ip.org> <2ca799770802220901r3b4bbe4cnd64b86878336372e@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/280 X-Sequence-Number: 28354 ------=_Part_1945_13563208.1203704131321 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Fri, Feb 22, 2008 at 7:01 PM, Mikko Partio wrote: > > > On Fri, Feb 22, 2008 at 5:44 PM, Alvaro Herrera < > alvherre@commandprompt.com> wrote: > > > Mikko Partio escribi=F3: > > > > > Now, the problem is that I cannot turn autovacuum off! I have tried t= o > > set > > > autovacuum =3D off at postgresql.conf with no avail. I have also trie= d > > to > > > disable the vacuum by inserting rows to pg_autovacuum but still the > > vacuum > > > processes just pop up. What's even more weird is that autovacuum is > > > vacuuming the same tables over and over. No sign of xid wraparound > > issues > > > show in the logs. How can I stop autovacuum without restarting the > > database > > > instance? > > > > If your database is in danger of Xid wraparound, autovacuum will run on > > it no matter what you do. Check pg_class.relfrozenxid and > > pg_database.datfrozenxid, and the current Xid counter (from > > pg_controldata I think). Also take a look at the description of the > > autovacuum_min_freeze_age param (or was it max_freeze_age? I forget). > > > > I think I may have found the problem, the import uses a temporary table which I guess autovacuum can not handle? Manually vacuuming it changed the relfrozenxids of other tables dramatically. Regards Mikko ------=_Part_1945_13563208.1203704131321 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On Fri, Feb 22, 2008 at 7:01 PM, Mikko P= artio <mpartio@gmail.com> wr= ote:


On Fri, Feb 22, 20= 08 at 5:44 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Mikko Partio escribi=F3:

> Now, the problem is that I cannot turn autovacuum off! I have tried to= set
> autovacuum =3D off at postgresql.conf with no avail. I have also tried= to
> disable the vacuum by inserting rows to pg_autovacuum but still the va= cuum
> processes just pop up. What's even more weird is that autovacuum i= s
> vacuuming the same tables over and over. No sign of xid wraparound iss= ues
> show in the logs. How can I stop autovacuum without restarting the dat= abase
> instance?

If your database is in danger of Xid wraparound, autovacuum will run = on
it no matter what you do.  Check pg_class.relfrozenxid and
pg_database.datfrozenxid, and the current Xid counter (from
pg_controldata I think).  Also take a look at the description of the autovacuum_min_freeze_age param (or was it max_freeze_age?  I forget).=



I think I may have fou= nd the problem, the import uses a temporary table which I guess autovacuum = can not handle? Manually vacuuming it changed the relfrozenxids of other ta= bles dramatically.

Regards

Mikko

------=_Part_1945_13563208.1203704131321--