Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id AE60C2E00F6 for ; Fri, 22 Feb 2008 13:01:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 55451-04 for ; Fri, 22 Feb 2008 13:01:14 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.234]) by postgresql.org (Postfix) with ESMTP id 116DF2E00E1 for ; Fri, 22 Feb 2008 13:01:14 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 71so861177wri.5 for ; Fri, 22 Feb 2008 09:01:14 -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=vHoQtFzyPmXEoDfDbYn4m8VDiOLfYvbqkL9wSrUNfgQ=; b=NIZlnh9sy1oEPbXnKmUIGo2fBpG3klHbewp+qS34x9CLeIGconSKHBXeWv/NkID3gH2KjECLgzsaxkm6ebDWzwC5YjwPVv7t4+n8HqPhgXTWxn/LWTVk4TlRGjhNeOrHmdEHZIIVu+smF7vlMuFmHTeVAGbgCdGxqX3uN2zt+Iw= 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=PlCxpadN9gXuxGm5ZSUIAVeyLKQWk1zcTNhTXXbEG8691UXNqMzmTHnJQLxClWT4BpbWUgf/TVRiWBt8COaosAMxwzDmNY68MZ97QWacnU9b1SQKCegOb7kXHM/piIBf/kfCPFPa3ofnLylUiPaaYrAj9e3YuaJBq1TJcTDFIYg= Received: by 10.114.169.2 with SMTP id r2mr214234wae.76.1203699672884; Fri, 22 Feb 2008 09:01:12 -0800 (PST) Received: by 10.114.102.14 with HTTP; Fri, 22 Feb 2008 09:01:12 -0800 (PST) Message-ID: <2ca799770802220901r3b4bbe4cnd64b86878336372e@mail.gmail.com> Date: Fri, 22 Feb 2008 19:01:12 +0200 From: "Mikko Partio" To: "Alvaro Herrera" Subject: Re: Stray autovacuum daemon Cc: pgsql-admin@postgresql.org In-Reply-To: <20080222154430.GD9392@alvh.no-ip.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1394_28950535.1203699672878" References: <2ca799770802220305v38b800dbt909c84e3b3be57cc@mail.gmail.com> <20080222154430.GD9392@alvh.no-ip.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/279 X-Sequence-Number: 28353 ------=_Part_1394_28950535.1203699672878 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Fri, Feb 22, 2008 at 5:44 PM, Alvaro Herrera 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 > 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). > But I thought vacuum would send some messages to log if xid wraparound is looming? SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind =3D 'r' order= by 2 desc limit 4; relname | age ---------------+----------- table1 | 2141707556 table2 | 2141707556 table3 | 2141707556 table4 | 2141707556 SELECT datname, age(datfrozenxid) FROM pg_database; datname | age -----------+------------ template1 | 1833384067 template0 | 1833384065 postgres | 1933384063 mydb | 2040873036 Seems ok to me ... ? autovacuum_freeze_max_age and vacuum_freeze_min_age have the default value. Regards Mikko ------=_Part_1394_28950535.1203699672878 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On Fri, Feb 22, 2008 at 5:44 PM, Alvaro = Herrera <alvherre@commandp= rompt.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).=


But I thought vacu= um would send some messages to log if xid wraparound is looming?

SE= LECT relname, age(relfrozenxid) FROM pg_class WHERE relkind =3D 'r'= order by 2 desc limit 4;
    relname    |    age
---= ------------+-----------
 table1 | 2141707556
 table2 | 214= 1707556
 table3  | 2141707556
 table4  | 21417075= 56


SELECT datname, age(datfrozenxid) FROM pg_database;
 = datname  |    age
-----------+------------
 template1 | 1833384067
 template0= | 1833384065
 postgres  | 1933384063
 mydb  = ;   | 2040873036

Seems ok to me ... ?=20 autovacuum_freeze_max_age and vacuum_freeze_min_age have the default value.=

Regards

Mikko
------=_Part_1394_28950535.1203699672878--