The Firebird Transaction Statistic Logger (FTSL) utility is now available as Freeware without source code. You can download the utility here.

If you have any feedback, feel free to contact me.

Enjoy,
Thomas

I've continued the work on the Firebird Transaction Statistic Logger (FTSL) and could not resist adding visualization capabilities to this utilitity. It now will be able to show a chart for the OIT/OAT/OST/NT numbers and also for the calculated gap values.

The following chart shows the absolut transaction ID for the transaction counters (click to enlarge):




If you are more interested in getting a chart with the gaps, this one will be useful (gap between OAT and NT):



FTSL now also can be installed as a Windows service with multi-instance capabilities, which allows to install one monitoring service per monitored database. You won't have the GUI gimmicks then of course.

Regards,
Thomas

One of the most important thing when using Firebird is having a proper transaction management concept in your client application in place, so that the transaction numbers oldest interesting transaction (OIT) and oldest active transaction (OAT) can move forward, thus making the gap between the OIT/OAT and the next transaction (NT) not too big, otherwise you may suffer from bad performance.

Reasons for stuck transaction numbers can be rolled back transactions with a lot of undone modifications, long-running transactions (does not apply to read-only read committed transactions), calling commit retaining without doing a "hard" commit from time to time, failed distributed transaction (2PC). It's beyond the scope here to go into this in more detail.

Well, when you are suffering from bad performance, while the performance was ok after a backup/restore cycle, usually your transaction numbers got stuck due to reasons mentioned above. I find a self-written little nifty application called "Transaction Statistic Logger" pretty useful, to gather transaction statistics over time, store the transaction numbers in a log file or another Firebird database and analyze the data and spot timestamps where the gap got bigger and bigger. Here is a screen of the logger application:



I did not add any diagramming facility, because this can be done via Excel and an ODBC connection to the log database pretty easy. For example:




Pretty interesting when doing consulting jobs at sites with poor transaction management or when trying to unterstand how other applications handle transactions. And in combination with the versioning monitoring tables thingy I described here, the results are even more interesting. ;-)


Regards,
Thomas