Library

IBAnalyst: what you can see at Summary View

Dmitry Kuzmenko, last update 31-03-2014

Abstract

This document is devoted to explanation of information at IBAnalyst’s “Summary view” page, and how to interpret this information for your own database statistics. Also we added several examples of statistics to installation package to facilitate you study all InterBase statistics’ nuts and bolts. They are in Examples directory of IBAnalyst installation.
If you don't know what is Oldest transaction, Oldest snapshot, active and Next, please read before Craig Stunz's article "Understanding Transactions Lifetime":
http://blogs.teamb.com/craigstuntz/articles/UnderstandingTransactionLifetimes.aspx

Transaction numbers

If you have read Understanding Transaction Lifetimes article, maybe you still have questions about OIT/OST/OAT numbers. Here is short description:

Number	Holds, ...	Moves forward, ...
Oldest transaction	when transaction with this number was rolled back, and there were lot of data changes in it, or when client connection was lost	when automatic or manual sweep succeeds.
Oldest Snapshot	when snapshot (or read committed write prior to IB 7.1) is active for a long time (it remembered oldest active snapshot as it's local OST)	when new transaction starts, if transaction holding OST is ended
Oldest Active	when transaction with this number is active for a long time	when new transaction starts, if transaction holding OAT is ended
Next	never	when new transaction starts

note: Oldest transaction here is the same as Oldest Interesting Transaction (OIT), mentioned in lot of another articles.

Fine statistics with standard settings

Let's start IBAnalyst and open (with Statistics/Load statistics from file menu) file !allok.txt.

IBAnalyst reports not only database creation date, but also recognize current datetime on server if statistics was taken via Services API, or file datetime if it is statistics file loaded.
That's why we suggest not modifying statistics files, because in this case IBAnalyst will calculate average transactions per day incorrectly. Transactions per day row here shows about ~12500 transactions per day and that database "lives" from it's creation or restore for 8 days.
Oldest, Oldest snapshot, Oldest active and Next transactions here in perfect state, wish you always have statistics looks like this.

Many active transactions

Next, open file !lotofactive.txt (you can look back at !allok picture at any moment to compare following examples with it).

Here Active transactions row marked as red, because there is big difference between Oldest active and Next transaction. This means that some transaction at the moment statistics was taken still active, and after it's start 55,000 transactions already started (they can be at any state – active, committed, rolled back). Since the count of average transactions per day is ~12500, IBAnalyst shows you warning that some transaction still lives for 4.4 days. This can happen when:

some application is still running and have at least one transaction open – some user maybe keep application running for a long time
application runs for a long time and looses transaction handles – i.e., your code (or components/libraries that you use) dynamically starts transactions, and under some circumstances "forget" to end them with rollback or commit.
your application does not use explicit transactions (BDE), leaving transaction handling to components being used. As a result transaction lifetimes is not controlled by application, and you can think for sure that most of Next-OAT transactions are really active.
your application uses driver or components that allow "default transaction". If your code does not control this transaction, it can run very long time.

Sad to say, statistics does not show actual number of currently active transactions. This can only be viewed in InterBase 7.x using IBConsole, IB Performance Monitor or via direct query to tmp$transactions temporary system table. In Firebird 1.5 you can call isc_database_info with isc_info_active_transactions parameter.

Sweeping

Now let’s open !needsweep.txt.
Sweep is a housekeeping process inside InterBase. Sweep walks all records in database and tries to cleanup all garbage versions, than tries to move up Oldest transaction number. In newly created database Sweep interval by default is 20000. When difference between transactions (see table below) becomes greater than sweep interval, sweep will run automatically. Thus you can see periodical performance degradation on your database. For example, your applications work OK during Monday and Tuesday, but at Wednesday users report about performance problems for some hours, and then performance becomes OK again.
If you see similar behavior - it is an automatic sweeping.

Server version	When sweep run
InterBase 7.x	(Oldest Active – Oldest) > Sweep interval
InterBase 4.x, 5.x, 6.x, Firebird prior to 1.5.2, Yaffil	(Oldest Snapshot – Oldest) > Sweep interval

Table 1. Conditions when automatic sweep run
note: IBAnalyst automatically shows correct Sweep gap info for all versions. IBAnalyst can detect difference between server implementations only by ODS id, for example, InterBase 7.x has ODS 11.x, other modern servers have ODS 10.x. If you work only with Interbase 7.x databases (ODS 11), you can switch appropriate option in Options dialog.

When your database has sweep interval <> 0, IBAnalyst basically marks this row as yellow (warning you that automatic sweep can start in any unpredictable moment). Generally 60% of all applications have problems with automatic sweeping. Easiest way to avoid this problem is to set sweep interval to 0, which leads to switching off automatic sweeping. But, if some application will make lot of changes and than rollback, Oldest transaction will freeze and won't move up until sweep is run. Since effective transactions state is calculated from Oldest to Next transaction, this distance will grow, and performance will drop. To prevent this, you must run sweep manually (gfix –sweep). At this picture you can see behavior when sweep interval is 0 and there was big transaction rolled back:

Here sweep gap value shows that some big (made lot of changes) transaction was rolled back about 4.5 days ago. We recommend you to run sweep manually each day.
Of course, for that mentioned 60% applications maybe it's better to set sweep interval greater or less than 20000, but it depends on many factors (daily transaction is one of these factors, for example) and could be understood only by experimental way. So, if you will set Sweep interval to 0 you may be sure that sweep won't run automatically in unpredictable moment.
The same picture you can see for !rollback.txt file.
note: If sweep is running automatically, for big database or database with a lot of garbage record versions Snapshot, Active and Next transactions can move forward while sweep works, and sweep can start again at the nearest transaction start.

When sweep can't do its work

There are many cases when sweep can't move Oldest transaction higher. Of course, sweep at first tries to do its work, i.e. check all records in database and collect unneeded record versions. But it will run again and again without success if

there was some problem when sweep run: server was stopped during sweep, or there was error during garbage record version cleaning. In addition, you can see this picture when:

statistics was taken when sweep works
sweep is running and trying to collect garbage for table that is being updated constantly. This can last until updates will end.
sweep is stalled on page locks, because there are lot of users working with data.

In common, sweep does not have chances to finish during high database load. Usually when performance degrades to impossibility to continue normal work, DBA restarts server, and sweep run at first user connect. Since there will be some time while other users connects, sweep will have time to finish its work.
Since InterBase 7.1/7.5 calculates Sweep gap differently from previous versions, the other situation when sweep can't make its work is when some application have long running snapshot transaction:

Here are two warnings – one (yellow) about long running snapshot, and other (red) about Sweep interval and Sweep gap.

Long running snapshot

Previous picture indicates long running snapshot in InterBase 7.1/7.5. If Sweep interval was set to 0, there wouldn't be red warnings, just yellow. Similar picture will indicate long running snapshot transaction in other Interbase, Firebird and Yaffil versions:

As you see here, Sweep gap is calculated by Oldest Snapshot and Oldest transaction difference (for ODS 10, pre-IB7.x versions). So there is no sweep warning (except default sweep interval).
But, not only snapshot transactions affect transactions state this way. All InterBase, Firebird and Yaffil versions except InterBase 7.1/7.5 have the following behavior, that we named "read committed artifact".

Snapshots again and When ReadCommitted freezes Oldest Snapshot

Open !snapshot2.txt. :

Note that Oldest transaction is greater than Oldest snapshot. And Sweep gap has negative value. This can happen in two cases. First case is when there are some snapshot transactions start and commits one after another. I.e. this picture can happen with snapshots as well as previous picture. Next case happens only in servers other than IB 7.1/7.5 with ReadCommitted transactions (or in combination of ReadCommitted and Snapshot transactions). They can lock Oldest Snapshot number the same way as Snapshot transactions do. Current transaction state can be emulated with the following sequence

1.   start transaction 1, snapshot or read_committed
2.   start/commit some read_committed transactions
3.   start transaction 2, snapshot or read_committed
4.   start/commit some read_committed transactions
5.   commit transaction 1
(of course, we talk here about read_committed write transactions, not read-only).
At this point currently active transaction 2 (snapshot or read committed), started after snapshot 1 (mark 3), will hold snapshots number as Oldest Snapshot (if you work with IB 7.1/7.5 this can happen only for concurrent snapshot transactions. read_committed or read_committed+snapshot will not produce this effect). Since there were no big rollbacks, Oldest transaction moves forward, and becomes greater than Oldest Snapshot.
Now, if you have long-running read committed transaction, you will see picture like this. Sad to say, you can't do anything here with your applications (except adding "read" parameter for read-only transactions). And of course, sweep won't run automatically (if it is set <> 0) in this case.
note: this behavior will be fixed in Firebird 2.0

Absolute and relative views

By default IBAnalyst fills transaction rows according to percent of their absolute value. I.e. 100% is from 0 to Next transaction. Sometimes, when database works for a long time you can see transaction info as

While there are lot of transactions, difference between snapshot, active and oldest looks very small (close to 98%). To make situation more clear open Options dialog, Transactions tab, and check "Relative (from oldest) bars %" option (you can't set this checkbox if you use old-style view, without graph bars). After pressing OK button transaction view will change to

Now you see transactions difference in relative view (100% is from Oldest (or snapshot) transaction to Next, not from 0). It is easier to understand current situation and view warnings (if any).
This view will keep on until you uncheck it with Options dialog. You can understand what view you are seeing by Oldest Transaction or Oldest snapshot row – in relative view one of these rows is never filled with green color. In absolute view it is always filled (partially, of course).
Still have questions? Ask us at support@ib-aid.com