IBAnalyst is a tool that allows a database administrator to analyze detailed Firebird or InterBase database statistics and then identify possible problems with database performance, maintenance and how an application interacts with the database.
IBAnalyst graphically displays Firebird (or InterBase) database statistics in a user-friendly way and highlights the following problems:
Moreover, IBAnalyst can automatically make intelligent suggestions about improving database performance and database maintenance.
IBAnalyst can get statistics from the live production databases through Services API (recommended), or analyze text output of gstat -a -r ... commands. Statistics from the peak load periods can give a lot of information about actual performance problems in production databases.
Let's walk through the key features of IBAnalyst. When you look at your database statistics in IBAnalyst first time, things can be not clear, especially if IBAnalyst shows lot of warnings by colored red and yellow cells at Summary, Tables and Index views. Let's consider several real statistics examples.
Summary page shows a lot of information, but the most valuable is transactions state (please read description of possible transactions states in IBAnalyst help, it is available by clicking F1 or in menu Help).
At this screenshot you can see that some transaction is active for a long time, "60% of daily average". IBAnalyst marks such transaction's state by red, because this transaction may prevent accumulated versions to be considered as garbage by server, and so, to be garbage collected. This is a possible reason of slowness: the more versions exist for some record, the more time it will takes to read it.
In order to find this long-running transaction you can use MON$Logger module of FBScanner, or perform direct query of MON$ tables. Then, to find out which tables were affected by long running transactions (tables with a lot of record versions) you need to go to "Tables" view of IBAnalyst.
At "Tables" view you can see tables and their important parameters: number of records, number of record versions, record length, maximum number of versions, etc.
You can sort this view to find the largest tables. Especially we are interested tables with many record versions - many record versions will make garbage collection for affected tables longer. Usually it is necessary to change update and delete algorithms to get rid of many record versions.
Row Versions show total versions count for particular table, and row Max Vers shows maximum versions reached by some record. For example, if you look at table NAB, there are 11.9 million records, total versions are 20932, but one record have 176 versions. Reading and parsing such packet from disk takes more time, so, reading this record is slower than reading others.
This picture also shows a lot of tables where data was deleted. But, because of long running transaction, server can't delete these versions, and they still on disk, still indexed, and still being read by server when reading data.
Some production databases can have indices with the only key value indexed. This can happen because database was developed "to be extended in the future", or, someone just experimented with the indices during development or tests. You can see these indices as "Useless" in IBAnalyst:
SKIN04, SKIN05, SKOUT03, etc, built on the column that have only one value for all rows (million rows). These indices are really useless, because
Of course, that is not all that you can find about your database in IBAnalyst. You can also find
If you confused by reading colored cell warnings, just open "Reports\View recommendations" - everything sufficient for database performance is gathered here. Please feel free to ask any questions (firstname.lastname@example.org)