X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 97446566F4 for ; Fri, 11 Mar 2005 16:46:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94633-07 for ; Fri, 11 Mar 2005 16:46:48 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 81D81566E2 for ; Fri, 11 Mar 2005 16:46:46 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j2BGkgw3015365 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 11 Mar 2005 09:46:45 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j2BGkg9U015395; Fri, 11 Mar 2005 09:46:42 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j2BGkfO2015394; Fri, 11 Mar 2005 09:46:41 -0700 (MST) (envelope-from mfuhr) Date: Fri, 11 Mar 2005 09:46:41 -0700 From: Michael Fuhr To: "Milen A. Radev" Cc: Tom Lane , pgsql-admin@postgresql.org Subject: Re: Too frequent warnings for wraparound failure Message-ID: <20050311164641.GA15210@winnie.fuhr.org> References: <42301107.6010901@securax.org> <20050310155734.GA86004@winnie.fuhr.org> <423079B3.8050601@securax.org> <15917.1110474897@sss.pgh.pa.us> <42316D98.9030507@securax.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <42316D98.9030507@securax.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/183 X-Sequence-Number: 16681 On Fri, Mar 11, 2005 at 12:06:16PM +0200, Milen A. Radev wrote: > Tom Lane wrote: > > > So you managed to execute about 6000 transactions in those two seconds. > > Doing what? > > Well that could be the problem - I'm 99.99% positive we do not execute > so many transactions - may be 30-40 thousand per day and that's only for > one relatively busy DB. One of your earlier messages said 30-40 thousand *inserts*. Is the database also receiving a lot of queries? Selects increment the transaction counter as well if they're not part of an explicit transaction (i.e., one started with BEGIN or its equivalent) -- in other words if you're in the so-called "autocommit" mode. > The example database ("xxx1") you've chosen is not used at all. As > most of the other databases in that cluster. The transaction counter is global, so "the number of transactions from the cutoff XID to the current transaction's XID" increments for every database as the counter increments. > And another observation (closely related IMO): when I login on another > development server/cluster and execute the same query I get an increment > for every database for every query execution. But there the version is > 8.0.1. Right -- see above about selects. If you're in autocommit mode then each statement is a separate transaction. If you really are executing that many transactions, then you might need to vacuum more than once a day to avoid the warnings. Another possibility would be to see if multiple SELECT statements can be wrapped in a single transaction. For example, if one application query requires many database queries, then you might be able to do them all in one transaction. Sometimes it's desirable to do so for other reasons: if you use the SERIALIZABLE isolation level, for example, then all queries in a transaction will see the same snapshot of the database. That is, two SELECTs will see the same thing even if another transaction commits changes in between. That isolation can be an important part of producing output that doesn't contain inconsistencies. -- Michael Fuhr http://www.fuhr.org/~mfuhr/