Hi Olaf!

The difference between OAT (Oldest active) and next transaction is ridiculous. What it means is that someone started a transaction long ago (probably five days ago) and hasn't committed or rolled it back yet. Every time Firebird updates a record, it creates another version of the existing record. In the background old record versions are removed, but it cannot remove versions that may still be of interest - in your case that means that it cannot remove old record versions from the last 238000 transactions. The reason it cannot remove these old versions, is that they may still be the versions that are visible to transactions that started long ago and haven't finished yet. The result for the users is that the database file grows and things become slower and slower.

I don't think there is an easy way to find the offending transaction (it should be easier with the monitoring tables in Fb 2.1), but you're probably looking for something that started five days ago, so computers that have been turned off/logged out the last few days aren't the offender (well, we only know that there is at least one very old transaction that is still active, there may be thousands). Checking your code is the only reliable way to ascertain that things like this doesn't happen again.

Though the offending transaction may also be a transaction that you started for some monitoring five days ago and that hasn't modified anything.

Your other, SS 1.5 version, has a much smaller gap, probably small enough for you not to notice any problems.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Olaf Kluge
Sent: 11. juni 2008 14:41
To: firebird-support@yahoogroups.com
Subject: AW: [firebird-support] Problem Firebird Classic Server

Hi,

wie use the gemini odbc driver 2.1 in our front end (Microsoft access 2002)
- one connection to link the tables, one connection with odbc direct to
execute some procedures.

Some procedure-executions over odbcdirect uses some more time (within 5
seconds)

How can I understand these values, how were they okay?

Actualy the values are:

Oldest transact. 833

Oldest active 961

Next transaction 239191 - compared to the previous value 229603

For example the values from our other database with the same connection (ms
access Gemini)

(this database runs with superserver, the version is 1.5)

Oldest trans. 421146

Oldest active 435318

Oldest snapsh.435318

Next transact. 435682

Back to my problem with the other database.

How can I find the client or connection, which makes me some trouble? (In
can't change the lower value in the firebird.conf not until this evening)
The ms access-connection is the same like the other database without these
trouble.

How can I clear this problem? I can disconnect every clients this evening
and connect one for one again. Perhaps this helps to find the problem.

Thank you very much for your helping hand.

And thanks for more ideas, maybe the reason can be found.

Best regards

Olaf

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Im Auftrag von Thomas Steinmaurer
Gesendet: Mittwoch, 11. Juni 2008 13:46
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Problem Firebird Classic Server

>>> First my gstat -h statistics:
>>> Oldest transaction 833
>>> Oldest active 961
>>> Next transaction 229603
>> First of all, you have a transaction management problem, because during
>> 5 days of operation, the gap between OIT/OAT and next transaction grow
>> and will probably grow further.
>
> I just wanted to emphasize what Thomas said here, with this big a gap

after five days, I'm surprised that you actually manage to run several
months before the problems gets too big. At the time you got these
statistics, there were still an open transaction that were amongst the first
1000 transactions started. To me, that indicates something very wrong in how
you handle your transactions (theoretically, that could mean 228000 versions
of each record in your database - in reality it will be a lot less, but
still too many). Transactions should be short, how short depends on your
particular situation, but five days is far too long in all cases.

>
> Do you use CommitRetaining - that at least wasn't a proper commit and

could hold up OIT/OAT (I don't know whether Fb 2.0 can have improved that or
not) or open one or more transactions that you do not close (that may even
be a transaction that doesn't update anything)?

Good one. CommitRetaining (aka soft commit) might be used from the
access components behind the scene, when using some sort of auto commit
mode. Or even worse, e.g. ZEOS does not support hard commits at all.
With Zeos, you have to disconnect from the database to get a hard
commit. I know there are quite some people out there who are using Zeos
without knowing the impact. ;-)

Any chance that the original poster is using the Zeos access components? ;-)

--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com