Articles

IBAnalyst: How to get statistics from InterBase/Firebird database in right way

Dmitry Kuzmenko, last update 31-03-2014

Abstract

This document is devoted to tips and tricks of gathering and analyzing statistics from InterBase/Firebird databases with or without IBAnalyst.

Right time, right place

It sounds strange, but just taking statistics via gstat or Services API is not enough. Statistics must be taken at the right moment to show how applications affect data and transactions in database. Worst time to take statistics is
  • Right after restore
  • After backup (gbak –b db.gdb) without –g switch is made
  • After manual sweep (gfix –sweep)
It is also correct that during work there can be moments where database is in correct state, for example, when applications make less database load than usual (users at launch, dinner or its by specific business process times).
 
How to catch when there is something wrong in database?

Yes, your applications can be designed so perfect that they will always work with transactions and data correctly, not making sweep gaps, lot of active transactions, long running snapshots and so on. Usually it does not happen. At least because some developers test their applications running 2-3 simultaneous users at the same time, not more. Thus, when they set up written applications for 15 and more simultaneous users, database can behave unpredictably. Of course, multiuser mode can work Ok, because most of multiuser conflicts can be tested with 2-3 concurrently running  applications. But, next, when more concurrent applications will run,  garbage collection problems can came (at least). And this can be caught if you take statistics at the correct moments.
 

If you does not experience periodical performance problems

This can happen when your applications are designed correctly, there is low database load, or your hardware is modern and very powerful (enough to handle well current user count and data).

The most valuable information is transactions load and version accumulation. This can be seen only if you setup regular statistics saving.

InterBase does not have internal task scheduler, so you are free to use any external, like standard Task Scheduler (Windows) or cron (Unix).
The best setup is to get hourly transaction statistics. This can be done by running

gstat –h db.gdb >db_stat_

Subscribe to IBSurgeon news

Subscribe