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 1CE0F52A71 for ; Sat, 12 Mar 2005 16:34:30 +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 70792-06 for ; Sat, 12 Mar 2005 16:34:24 +0000 (GMT) Received: from mail.securax.org (mail.securax.org [212.123.25.145]) by svr1.postgresql.org (Postfix) with SMTP id 2511D5441E for ; Sat, 12 Mar 2005 16:34:22 +0000 (GMT) Received: (qmail 24431 invoked by uid 2077); 12 Mar 2005 16:30:46 -0000 Received: from scx (HELO ?10.3.3.12?) (milen@securax.org@scx) by mail.securax.org with SMTP; 12 Mar 2005 16:30:46 -0000 Message-ID: <42331A0B.2020804@securax.org> Date: Sat, 12 Mar 2005 18:34:19 +0200 From: "Milen A. Radev" User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Michael Fuhr Cc: Tom Lane , pgsql-admin@postgresql.org Subject: Re: Too frequent warnings for wraparound failure References: <42301107.6010901@securax.org> <20050310155734.GA86004@winnie.fuhr.org> <423079B3.8050601@securax.org> <15917.1110474897@sss.pgh.pa.us> <42316D98.9030507@securax.org> <20050311164641.GA15210@winnie.fuhr.org> In-Reply-To: <20050311164641.GA15210@winnie.fuhr.org> X-Enigmail-Version: 0.90.1.1 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/191 X-Sequence-Number: 16689 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Michael Fuhr wrote: > 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. > Of course you were both right - we found one small brain-dead perl script that "executed an infinite loop in under 2 seconds" and in every loop "pings" the DB using an empty query (well that's the implementation in the perl pgsql driver). The responsible were punished. - -- Milen A. Radev -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCMxoLqGlhYx0/sboRAgLPAJ9/1+ZWhTexWDwvnB1fQe63oczUSQCgnzwQ m6K1nSwKu+c2QfPKRTnccSM= =shk1 -----END PGP SIGNATURE-----