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. It graphically displays database statistics and can then automatically make intelligent suggestions about improving database performance and database maintenance.
How IBAnalyst can help find problems in your Firebird or InterBase database
When you look at your database statistics in IBAnalyst first time, things can be not clear, at least if IBAnalyst shows lot of warnings by colored cells at Summary, Table and Index views. Here are few tips, by real statistics examples.
Summary page may show lot of information, but most valuable is transactions state (description of possible states is at F1 Help, included into IBAnalyst installation). Here you see that some transation is active for a long time, "60% of daily avegage". That transaction may prevent accumulated versions to be considered as garbage by server, and so, garbage collected. The more versions for some record, the more time it takes to read it. You can find are there tables with lot of versions at Table view.
Here you see tables with lot of records, and some tables have many record versions. Row Versions show total versions count for particulat 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 show 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 databases at production use can have indices with only one 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
optimizer may use this index if you specify "where field = ...". Since field contains only one value, using index will cause useless reading of index pages from disk to memory, and consume memory (and time) when server will prepare which rows to show for that query.
creating indices is the part of restore process. Extra indices adds extra time.
Of course, that is not all that you can find about your database in IBAnalyst. You can also find
average number of transactions per day
was there rollbacks or lost connections, and when
how big (in megabytes) each table and index
tables that have records interchanged by blobs, and thus reading only records is slower
empty tables - just forgotten, or empty at the time when statistics was taken
indices with lot of duplicate keys (you can consider about column value distribution)
indices with depth 4 and greater - maybe you need to increase page size to speed up
If you confused by reading colored cell warnings, just open View recommendations - everything sufficient for database performance is gathered here.
Please feel free to ask any questions (firstname.lastname@example.org)
Others tools to resolve Firebird's performance problems
IBSurgeon has other tools which work great with IBAnalyst:
FBScanner - to log, audit and profile Firebird and InterBase applications.
IBTM - IBSurgeon Transaction Monitor, to monitor and analyze transactions' behaviour in dynamics