Re: Too frequent warnings for wraparound failure - Mailing list pgsql-admin

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