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