X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 574569FA4D9 for ; Tue, 22 Aug 2006 16:20:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 18176-04 for ; Tue, 22 Aug 2006 16:20:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id DD7DA9FB25D for ; Tue, 22 Aug 2006 16:20:17 -0300 (ADT) Received: from alvh.no-ip.org (201-221-200-187.bk11-dsl.surnet.cl [201.221.200.187]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id k7MJKFFD013763; Tue, 22 Aug 2006 12:20:15 -0700 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 3FA3FC2DC58; Tue, 22 Aug 2006 15:19:53 -0400 (CLT) Date: Tue, 22 Aug 2006 15:19:53 -0400 From: Alvaro Herrera To: Marinos Yannikos Cc: pgsql-performance@postgresql.org Subject: Re: VACUUM FULL needed sometimes to prevent transaction ID wraparound? Message-ID: <20060822191952.GG25475@alvh.no-ip.org> Mail-Followup-To: Marinos Yannikos , pgsql-performance@postgresql.org References: <44EB48A9.7030704@geizhals.at> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <44EB48A9.7030704@geizhals.at> User-Agent: Mutt/1.5.12-2006-07-14 X-Virus-Scanned: ClamAV version 0.88.3, clamav-milter version 0.88.3 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Tue, 22 Aug 2006 12:20:16 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.414 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/353 X-Sequence-Number: 20538 Marinos Yannikos wrote: > Hello, > > we're looking into the reason why we are getting warnings about > transaction ID wraparound despite a daily "vaccumdb -qaz". Someone is > claiming that VACUUM without FULL cannot reassign XIDs properly when > max_fsm_pages was set too low (it says so here too, but this is rather > old: http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp). Is > this true, or do we have a different issue here? We're using 8.1.3 with > a database generated on 8.1.3 (i.e. not migrated from 7.x or anything > like that). It's not true. Having shortage of FSM entries will make you lose space, but it will be able to recycle Xids anyway. I guess your problem is that you're not vacuuming all databases for some reason. I'd advise to lose the -q and make sure you're not redirecting to somewhere you can't read the log; the read the log and make sure everything is going fine. What's the warning anyway? Does it say that wraparound point is nearing, or does it merely say that it is on Xid and you don't know how far that number actually is? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.