IBSurgeon Free Tools are included into HQbird Standard.

IBSurgeon Free Tools is a pack of 3 tools for Firebird and InterBase administrators and developers, designed to facilitate several typical tasks:

1. measure response time of specific SQL query to find time when query runs slower than usual (“Response Checker”),
2. view how much data, indices, blobs, etc, are stored in your Firebird or InterBase database (“DBInfo”)
3. investigate Firebird log for errors and warnings, (“Log Viewer”).

These tools are simple to use, below you can find their short overview and frequently asked questions.

IBSurgeon Response Checker

1. Why do you need Response Checker?

If you are trying to improve Firebird database performance, the first thing you need to define is criteria of performance optimization. And, Firebird database performance has such criteria:

• Average response time
• Maximum response time

Response time should be measured with your real-world application and with your production database (or in test environment which simulates production close enough).

There is no reason to measure average response time out of the scope of your real application, until you want some abstract «optimization» - or you are using test environment which is proven to be very close or even identical to your production database.

Response time is what your end users will refer as «slow», «very slow» or «good enough» (end users never say that your software is «fast»).

How to measure the average and maximum response times in real-world production environments running Firebird? For the basic response time measurement you can use IBSurgeon Response Checker.

Response Checker allows you to specify an SQL query (some kind of heavy query), run it with the specific period and measure its response time. As a result, you’ll be able to recognize time periods when database works slow.

For professional Firebird database optimization service please look here.

2. How to setup Response Checker to measure response time?

Run Response Checker from Start menu “IBSurgeon\FreeTools\IBSurgeon Response Checker”, and insert text of your test SQL query into the appropriate field (see below).

Please note: this SQL must be SELECT query!

Try to put there some “heavy” query, which is used in real application and make sense for end users.

Then, specify the interval for query execution – by default it runs each 10 seconds, and number of stored measurement points. Default values (10 second and 17280 points) are set to store 48 hours.

Also, we recommend to enable checkmark “Disconnect after each check”, to make each query in the separate connect – it will better simulate real-life behavior of your end users.

After that you can click “Start Check” and select database to run your query. As a result, you’ll have graph with measurement points:

At this graph you will be able to see periods when database works slower, and you can get advanced statistics with IBSurgeon optimization tools (IBAnalyst, IBTM or FBScanner) to determine the reason of the slowness.

Please note that Response Checker does not store gathered results, and once you will click “Stop check” and “Start check” again, existing statistics will disappear and new session will start.

IBSurgeon DBInfo

IBSurgeon DBInfo is designed to visualize internal structure's statistics, so administrators or developers can view how much data, index, blob or other kind of information your Firebird or InterBase database stores.

1. What is the purpose of DBInfo?

This tool allows you to

• view how much data, index, blob or other kind of information your InterBase or Firebird database stores
• detect some database corruptions
• look at database page size, overall file size, database format
• have some fun watching dynamically changing % indicators.

2. What InterBase and Firebird versions are supported?

Any from InterBase 4.0 to the latest InterBase and Firebird versions. DBInfo supports the following database on-disk structure formats:

• 8.x - InterBase 4.x
• 9.x - InterBase 5.x
• 10.0 - InterBase 6.x, Firebird 1.0
• 10.1 - Firebird 1.5.x
• 11.x - InterBase 7.0/7.1/7.5
• 11.x - Firebird 2.0/2.1/2.5
• 11.x - Firebird 2.x
• 12.x - InterBase 2007
• 13.x - InterBase 2009
• 15.x - InterBase XE-XE7

3. Do I need to have InterBase or Firebird server running?

No. DBInfo can check database file by itself, without server, and it does not need client library also.

4. Can I check for database contents on remote server?

A. Yes and no. Since DBInfo opens database file by itself, i.e. use direct access to database, you can check for database contents of database on remote server only if this database file is being shared on network.

5. What I can do if DBInfo found that my database is corrupted?

The better way is to immediately check the database by gfix and IBFirstAid Diagnostitian, and send full log to support@ib-aid.com. We will be able to help with Database Repair Services or you can buy full version of FirstAID.

6. How can I see how much space is occupied by specific table and/or index in my database?

In this case we suggest you to use our IBAnalyst tool, that not only can show you detailed information about your database, but also can give some notes and suggestions about performance optimizations.

7. May I use DBInfo if my database is on Linux (or other Unix)?

DBInfo is a Windows Win32 tool, so you may only check database files that can be accessed as files from Windows - locally or at shared folders. Or, you may try to run DBInfo using Wine.

8. Can DBInfo corrupt my database?

Never. DBInfo only reads the database file, and never writes to it. The open mode of the database file is read-only in any case.

9. May I check database contents while InterBase or Firebird is working with it?

Yes. DBInfo can open the database file even when InterBase or Firebird works with it. But, since reading the whole database file affects disk performance, be sure that at the moment your database is not at high load by applications.
Also, if you will check the database when the database is being modified by InterBase or Firebird, DBInfo may show warning that database is corrupted, which may be wrong.

10. What is the purpose of the option "Do not use Windows file cache"?

DBInfo can scan database file with two modes - using Windows cache or not using it. Usually when you copy file on Windows, it goes to the file cache. And, file cache is being allocated from physical memory. So, the bigger is file, the more amount of file cache is used by Windows. This can cause applications and other data to being moved to virtual memory, and slowdown computer performance.
For example, for database files larger than 500mb you could notice slowdown of DBInfo while scanning database file. Thus, we decided to turn off using cache in DBInfo by default. This allows to scan 1-10-100 gb databases without performance loss for DBInfo and operating system itself.

11. Why percentage of blob pages is marked by that color?

The color used to paint percentage bars does not have any meanings, except for bad pages found that is marked as red. This is the only case when we pays your attention to the damaged pages found in database.

IBSurgeon Log Viewer

IBSurgeon Log Viewer is designed to facilitate viewing and analyzing long Firebird and InterBase logs. Many Firebird and Interbase developers and administrators do not pay enough attention to the contents of firebird.log (interbase.log), though there is a lot of useful information, which can help to find and resolve various issues with Firebird/InterBase databases.

Originally Log Viewer was IBSurgeon's internal application, which helped to quickly navigate through very long Firebird and InterBase logs, and in version 2.0 it became mature enough to be released for the community.

Install it and open server’s log (usually it's in C:\Program Files\Firebird\Firebird_n_n\ folder). There are also descriptions for log entries shipped with IBSurgeon Log Viewer:

IBSurgeon Log Viewer is a FREEWARE (see license.txt for details).

Feel free to contact IBSurgeon Support (support@ib-aid.com) if you see suspicious messages in your logs. Don't forget to attach log to the message!

Suspicious symptoms

Where to look while reviewing the log? First of all, pay attention to the number of log entries per day – any significant peak could be an indication of some problem. Then, check unusual entries, especially about critical errors (such entries contain “internal gds consistency check” or “internal Firebird consistency check” error messages).