pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Milen A. Radev <milen@securax.org>
To: Michael Fuhr <mike@fuhr.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: Too frequent warnings for wraparound failure
Date: Sat, 12 Mar 2005 18:34:19 +0200
Message-ID: <42331A0B.2020804@securax.org> (raw)
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>

-----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-----



view thread (14+ messages)  latest in thread

Message-ID: <42331A0B.2020804@securax.org>
Permalink:  ../42331A0B.2020804@securax.org/
Also on:    postgresql.org/message-id/42331A0B.2020804@securax.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: milen@securax.org, mike@fuhr.org, tgl@sss.pgh.pa.us
  Subject: Re: Too frequent warnings for wraparound failure
  In-Reply-To: <42331A0B.2020804@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> <20050311164641.GA15210@winnie.fuhr.org> <42331A0B.2020804@securax.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