At 12:31 AM 14/06/2008, you wrote:

>Can somebody tell me the meaning of the follow terms of gstat?
>
>I checkout when the follow counters how they changed during database use and
>I couldn't figure out what is happening and what they really mean.

You REALLY do need to understand, as you have major problems in your application code. You have all the same symptoms of dysfunction that you started posting about in January 2007.

> Oldest transaction

Oldest interesting transaction (OIT). It is not an active transaction but all of the record versions that were affected by that transaction are still needed by the server because other transactions that were uncommitted at the time this transaction ended were interested in one or more of those records.

No transactions that are started after the OIT will get garbage collected.

> Oldest active

Oldest transaction that is still uncommitted (OAT).

> Oldest snapshot

This was the OAT last time the engine tried to perform a garbage collection.

> Next transaction

This is the number of the next transaction that will be started. Subtract 1 and you have the number of the newest transaction that was started.

>I did this test, I worked with ibexpert a database, I get the stat and then
>with my application and I compare the differences of these counters. You can
>see them on the attached xls.

This list does not take attachments.

>I couldn't discriminate how the counters
>should be. Both applications, more or less modified the counters with the
>same way.

Applications do not modify the counters. Applications are responsible for starting transactions and for ending them. A transaction does not end until it is either committed or rolled back. The database engine keeps an inventory of all transactions, including information about whether or not they are interesting. Once a transaction stops being interesting, it is flagged and all of the obsolete record versions affected by that transaction will be cleared out by the next garbage collection or sweep.

> Do they both manage the transactions wrong?

We know that your applications are managing transactions wrongly. With a tool such as IBExpert, you still need to commit transactions. Look for a button.

But we don't know what statistics you are talking about. Perhaps you are looking at index statistics....one cannot tell. Sure, poor index statistics need attention to optimize performance. But your problem (which you have been reporting for many months, in one form or another) is due to lack of transaction management by your application code.

>Please provide values for these counters when an application handles correct
>the transactions for long time.

You are missing the point of what people are trying to tell you. One important number is the "gap" between the Oldest snapshot and the Next transaction, because it tells you about the number of transactions for which no garbage collection has been possible. No garbage collection means that old record versions will build up and build up to the point where it takes the engine an impossibly long time to evaluate the latest committed version of any requested record. Slow....slower....s l o w e r.......crash!

>Also please explain this gstat's counters:
>
> Oldest transaction 1007
>
> Oldest active 1008
>
> Oldest snapshot 1008
>
> Next transaction 18504685
>
>What really means this extreme difference?

Broadly, it means that your applications are not committing anything. However, it is quite possible that your application *does* commit work, but is using Commit Retaining (also known as "soft commit"). From the point of view of garbage collection, it causes every transaction to appear as though it were still active. The last transaction that ever got a hard commit was number 1006.

You have a serious problem here. You have built up the garbage from more than 18.5 million transactions in your database. It is no surprise at all that the database is slow or that you earlier reported "inexplicable" server crashes.

The immediate fix for your sick database is to get everyone - including yourself - to close down all applications. Then, as SYSDBA, put the database offline with gfix, with only SYSDBA allowed to log in. (You are stuffed if your application is written so that all users log in as SYSDBA. In this case, you should rename the database temporarily while you are doing this fix.)

Back it up **using the -g switch to disable garbage collection**; and then restore it using the -c switch and a different name. Use the -v switch in both cases so that you can watch what is happening and can also get a visual verification that the backup and the restore, respectively, actually finished.

As an important side note, if you have not done anything about increasing the page size of your database from 1024 to 4096 or 8192 then DO SO AS PART OF THE RESTORE.

Connect to the restored database using IBExpert or whatever. If all is well, exit from your application. Rename the old database and move it somewhere else. Rename the new database to the correct name and put it online using gfix.

However, it's a temporary fix. Very soon, you will again arrive at the same position you are in now. You will continue to amass new garbage. Nothing is going to improve until you rewrite your application code to make it so that work is explicitly committed (using hard COMMITs) in a timely fashion.

If you are still using the ADO .Net drivers as your interface then go to the firebird-net-provider list and ask about the transaction management provisions it has. Once you know and understand what is available, you can try to figure out how you are going to fix your applications. If you really don't understand transactions at all, then NOW would not be too soon to start getting acquainted....

./heLen