How are you returning the connection to the pool?
When you get a Connection from a pool, it's usually a wrapper around a
Connection from the JDBC driver. Closing the Connection will allow it
to be reused, so I don't understand why you're not closing the
Connection.
BTW, I'm sure you're already doing this, but Connections should always
be closed in a finally{} block.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of imoffskating
Sent: Wednesday, February 08, 2006 11:46 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Oldest Snapshot slowing down DB

Thanks once again Helen for your reply. Sorry im such a novice and dont
spot these obvious things.

Hmm well it might have been our code indeed, or perhaps something left
over from a clear of the database?
the cron gstat is reporting the db behaving nicely and oldest snapshot
and oldest transaction are not getting stuck. great! We shall leaving
the server running and see what happens....

To close our connections we first complete all statments, then close all
result sets (res.close()), then close all statements.
Then we return the connection to the pool (ie we dont call con.close).

I shall double check with Roman this is the correct way to close and
return a connection to the pool.

Ha ha Its not ebay we're running :) , it will have quite a lot of
activity on it though.

Thanks for all your help,

Alex.


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 10:29 PM 8/02/2006, you wrote:
> >Thanks again for your kind and detailed reply, it has defintely shed
> >some light on the issue.
> >
> >i've added in some more comments inline below...
> >
> >What i meant by " we are pretty sure its not the program leaving open

> >transactions accidently as Oldest transaction is fine" is that we are

> >pretty certain we are committing and closing all our connections so
> >there are no connections left open to the db accidentty.
>
> A connection and a transaction aren't the same thing. Check with
> Roman on Firebird-java what happens when your app returns a connection

> to the pool without ending any transactions, i.e. does the Jaybird
> interface take care of committing the transaction[s] or should you be
> taking care of that before sending the connection to

the pool?

>
> > > >and we have timers in the code to check for possible leaked
> >transactions.
> > >
> > > What's a "leaked transaction"?
> > >
> >Sorry my bad explanation. i mean we have a timer in our java code
> >which checks to see if we have accidently not closed a connection.
>
> More confusion here between a connection and transactions...ask Roman
> to explain what goes on. I mean, you can call isc_detach_database but

> that doesn't instantly make the transactions evaporate.
>
> >We are running java 1.5 with Jaybird JDBC byu the way.
> >
> >Thanks for the suggestion, i have done this gstat -h on a cron job
> >and it seems oldest snapshot is not been increased, ie garbage
> >collection has not run at all :( .
> >
> >here are two runnings of gstat -h. One at 6pm, the other 15 hours
> >later at 9am. Oldest snapshot is the same on both.
> >
> >
> >6pm:
> >----------------
> >Database header page information:
> > Flags 0
> > Checksum 12345
> > Generation 10076932
> > Page size 4096
> > ODS version 10.1
> > Oldest transaction 10035365
> > Oldest active 10035366
> > Oldest snapshot 10035366
> > Next transaction 10076930
> > Bumped transaction 1
> > Sequence number 0
> > Next attachment ID 0
> > Implementation ID 19
> > Shadow count 0
> > Page buffers 0
> > Next header page 0
> > Database dialect 3
> > Creation date Feb 6, 2006 12:40:09
> > Attributes
> >
> > Variable header data:
> > *END*
> >-----------------
> >
> >9am:
> >----------------
> >Database header page information:
> > Flags 0
> > Checksum 12345
> > Generation 15542154
> > Page size 4096
> > ODS version 10.1
> > Oldest transaction 10035365
> > Oldest active 10035366
> > Oldest snapshot 10035366
> > Next transaction 15542152
> > Bumped transaction 1
> > Sequence number 0
> > Next attachment ID 0
> > Implementation ID 19
> > Shadow count 0
> > Page buffers 0
> > Next header page 0
> > Database dialect 3
> > Creation date Feb 6, 2006 12:40:09
> > Attributes
> >
> > Variable header data:
> > *END*
> >
> >-------------------------
>
> OIT and OAT are stuck for 15 hours. Oldest snapshot of course doesn't

> move, since GC can't get past the OIT because the OAT is running all
> night. Since the OAT got stuck, nearly 5.5 million transactions have
> started. More plottings will help to find out when (or if) eventually

> things start to move again.
>
> >should i have a cron job to force sweep of the database perhaps?
>
> It would be interesting at a point like this just to run *a* manual
> sweep and then look at the stats afterwards. By the look of it,
> you'll want to do this sweep at a quiet time.
>
> >or is there a setting in firebird.conf?
>
> No. Firebird.conf settings can't influence what misbehaving
> applications do. In Fb 2.0, you'll be able to choose an alternative
> method of GC, but, again, if applications aren't managing their
> transactions properly, the huge garbage buildup will still hit, just
> in different places.
>
> >is the best way to force a sweep gstat -sweep?
>
> Call it "force" if you like. But gstat doesn't do sweeps. Gfix does
> sweeps. Should we infer that you never sweep the database?
>
> It might be useful to run regular sweeps in a cron job, *if* you find
> that sweeping improves things. But treating the symptoms doesn't cure

> the disease. Find out what this transaction is that's holding up GC
> downstream.
>
> I'm quite curious about what your server's doing at night that causes
> 5.5 million transactions to start between 6 p.m. and 9 a.m. You're
> not running eBay, by any chance, are you? :-)
>
> ./heLen
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links