Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 580619F9CA1 for ; Fri, 31 Aug 2007 15:04:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 76719-02 for ; Fri, 31 Aug 2007 15:04:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 6B5CD9F9CB3 for ; Fri, 31 Aug 2007 15:04:06 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 323EF3143C; Fri, 31 Aug 2007 20:04:03 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.performance Subject: Re: 8.2 Autovacuum BUG ? Date: Fri, 31 Aug 2007 13:46:09 -0400 Organization: cbbrowne Computing Inc Lines: 51 Message-ID: <60642vegwu.fsf@dba2.int.libertyrms.com> References: <46D83427.60001@livedatagroup.com> <25038.1188574775@sss.pgh.pa.us> <46D83C50.3040409@livedatagroup.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1008 (Gnus v5.10.8) XEmacs/21.4.20 (linux) Cancel-Lock: sha1:Iv0SEElrtYUmIIvV9ENY+WGJc84= To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.303 tagged_above=0 required=5 tests=AWL=0.302, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200708/402 X-Sequence-Number: 26532 pkalva@livedatagroup.com (Pallav Kalva) writes: > Tom Lane wrote: >> Pallav Kalva writes: >> >>> We turned on autovacuums on 8.2 and we have a database which is >>> read only , it is basically a USPS database used only for address >>> lookups (only SELECTS, no updates/deletes/inserts). >>> >> >> >>> This database has about 10gig data and yesterday autovacuum >>> started on this database and all of a sudden I see lot of archive >>> logs generated during this time, I guess it might have generated >>> close to 3-4gig data during this period. >>> >> >> Probably represents freezing of old tuples, which is a WAL-logged >> operation as of 8.2. Is it likely that the data is 200M transactions >> old? >> > If nothing changed on these tables how can it freeze old tuples ? It does so very easily, by changing the XID from whatever it was to 2 (which indicates that a tuple has been "frozen.") I don't imagine you were wondering how it is done - more likely you were wondering why. "Why" is to prevent transaction ID wraparound failures. > Does it mean that once it reaches 200M transactions it will do the > same thing all over again ? It won't freeze those same tuples again, as they're obviously already frozen, but a vacuum next week may be expected to freeze tuples that are roughly a week newer. > If I am doing just SELECTS on these tables ? how can there be any > transactions ? or SELECTS considered transactions too ? Every query submitted comes in the context of a transaction. If there wasn't a BEGIN submitted somewhere, then yes, every SELECT could potentially invoke a transaction, irrespective of whether it writes data or not. If you submit a million SELECT statements, yes, that could, indeed, indicate a million transactions. -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://cbbrowne.com/info/nonrdbms.html How much deeper would the ocean be if sponges didn't live there?