pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: Milen A. Radev <milen@securax.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: Too frequent warnings for wraparound failure
Date: Fri, 11 Mar 2005 09:46:41 -0700
Message-ID: <20050311164641.GA15210@winnie.fuhr.org> (raw)
In-Reply-To: <42316D98.9030507@securax.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>

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/



view thread (14+ messages)  latest in thread

Message-ID: <20050311164641.GA15210@winnie.fuhr.org>
Permalink:  ../20050311164641.GA15210@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050311164641.GA15210@winnie.fuhr.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mike@fuhr.org, milen@securax.org, tgl@sss.pgh.pa.us
  Subject: Re: Too frequent warnings for wraparound failure
  In-Reply-To: <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> <20050311164641.GA15210@winnie.fuhr.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox