HQbird 2024 User Guide

HQbird includes native replication to create fault-tolerant systems based on Firebird databases:

Native replication is configured through the special plugin, with the ability to exclude records without PK/UK at the plugin level.

HQbird has complete transport to arrange transfer of segments for asynchronous replication for 1-to-1 or 1-to-many schemas, with automatic setup, transfer and validation of replication segments via sockets or FTP. HQbird has command-line commands to set up databases for replication in bulk, to choose databases in the folder, or in nested folders.

If you have an application with inaccessible or missing sources, HQbird can help you change texts of incompatible or most resource-consuming SQL queries “on the fly”, and therefore help to optimize the performance or migrate an application without SQL queries sources. The replacement is easy configurable, it is implemented by pairs of files which contains text of original and replaced queries.

With Advanced Monitoring, you can find SQL queries that cause issues and then configure the substitution for them, even without access to the application’s source code. The replaced query will occur in trace and MON$ tables with the new text.

HQbird has External Datasource plugins for ODBC and MySQL. Using these plugins, it is possible to execute commands EXECUTE STATEMENT ON EXTERNAL with queries to MySQL or ODBC data source, in order to read data from external datasources, or to write data to external datasources.

Plugins support input parameters and correct mapping of data types (however, in case of ODBC it depends on the specific driver implementation).

See example of an external connection below:

See more Working with external data sources (other DBMS)

HQbird can cache BLOBs in temp space, in order to speed up BLOBs operations (+15%-200% faster than in vanilla Firebird), and to prevent growth of the database file in case of mistaken BLOB operations.

HQbird uses an extra firebird.conf parameter BlobTempSpace to control this feature.

The caching option can be:

This feature can improve the performance of repeated queries, especially when using a connection pool (PHP, etc).

Cache keeps a certain number of prepared queries in each connection’s memory. HQbird has this cache in versions 3.0 and 4.0, and you can adjust it with the DSQLCacheSize setting (default is 0, i.e., disabled).

In vanilla version 5.0, there is a comparable feature, regulated by the MaxCompiledCache option, which is measured in Megabytes, the default is 2Mb.

Firebird Streaming is a technology that tracks changes in the database and sends them to another system, such as Kafka, JSON files, RabbitMQ, full text search plugin, etc.

HQbird offers a replication-based Change Data Capture plugin. The plugin creates a change flow that reflects transaction commits/rollbacks.

HQbird provides ready-made plugins for Kafka, RabbitMQ, JSON files, and also supports their configuration for any destination. CDC is useful for processing queues, sending alerts asynchronously, and copying changes to other systems (such as business intelligence or data science pipelines).

CDC plugin available upon request. For more information, contact IBSurgeon support ([email protected]).

See more Firebird Streaming

Full-text search is a technique that allows you to search for any word or phrase within a large collection of documents or data. Full-text search is different from searching based on metadata or partial text, which may not capture the full meaning or context of the query. Full-text search uses a full-text engine, such as Lucene, to perform the search and return the results.

IBSurgeon Full Text Search UDR is a user-defined routine (UDR) that integrates Lucene with Firebird. A UDR is a custom function that can be called from SQL statements. IBSurgeon Full Text Search UDR allows you to perform full-text search on Firebird tables in varchar and BLOB fields using Lucene engine.

This UDR is available in open source, but HQbird, provides a customizable plugin based on streaming for operational update.

More details: https://www.firebirdsql.org/en/full-text-search-udr/

HQbird implements multi-thread maintenance (sweep), backup, restore, and create index operations. Firebird 2.5, 3.0 and 4.0 are supported, and this functionality also appeared in Firebird vanilla version 5.0.

The format of backup files is the same as in the vanilla Firebird. On the test server with CPU with 8 cores and SSD, we have the following results (compared with 1 thread);

The actual acceleration depends on CPU, disk subsystem of the server, and structure of the database. Install HQbird in the trial mode (up to 30 days) and check what results will be on your server!

More details and test results can be found here: https://ib-aid.com/articles/firebird-gbak-backuptips-and-tricks#110hqbirdbackup

HQbird, starting from version 2.5, supports two important features:

These features help to achieve parallel reading of large data sets, and to accelerate 2-10x times export operations (such as for BI exports or data pipeline). These features are also available in Firebird vanilla, from version 4.0.4 onwards.

HQbird has a pool of external connections for Firebird 2.5, 3.0, and this pool is also available in vanilla version since 4.0.

An external connection pool allows you to execute EXECUTE STATEMENT ON EXTERNAL statements with less overhead in reconnecting to the external database.

The feature is controlled in the firebird.conf with ExtConnPoolSize and ExtConnPoolLifeTime parameters.

From the application perspective, no extra steps are needed to use or not use — it is switched on or off in the server configuration, and completely transparent for the applications. It is also possible to disable garbage collection for queries executed in external connections. It is regulated through configuration parameter ExtConnNoGarbageCollect.

See details: Pool of external connections

HQbird supports encryption with Encryption Framework’s Plugin. The main features are:

We can offer examples of client applications in various languages, such as Delphi, NET, Java, PHP, C++, etc., upon request.

Incorrect configuration of DefaultDbCachePages in firebird.conf, databases.conf or in database header is a common configuration mistake, which often happens during the migration between versions. For instance, it can be too large values of Page Buffers in database header for Classic or SuperClassic, or too low for SuperServer.

HQbird will automatically fix the wrong setting in firebird.conf and databases.conf and it will overwrite, if the configuration is unsuitable for a selected architecture.

Advanced Monitoring of Performance in HQbird is a feature that allows you to monitor and analyze the performance of your Firebird databases (version 5.0, 4.0, 3.0, 2.5) in real time. It collects data from various sources, such as Trace API, MON$ tables, lock table, transactions, CPU and RAM usage, and displays them in graphical and tabular forms. You can see the overall performance trends, as well as drill down to the details of each minute, query, or transaction.

You can also identify performance problems, such as slow and frequent queries, long-running transactions, lock table spikes, etc., and view their plans and statistics.

This feature helps to troubleshoot queries that produce large reports, where many records need to be sorted. HQbird can track queries and operations that create sorting files larger than a given size. When such a query is detected, its text is recorded to firebird.log

Configured as a TempSpaceLogThreshold parameter in firebird.conf, which defines the size of the sorting file for monitoring.

If you have many databases stored in the folder, and want to register all of them in HQbird to setup replication, in HQbird v2024 there is new command-line command to generate JSON file from the folder (recursive or not) with the registration information, which can be used for mass registration.

From replica side, there is special version of HQBird Central for Replicas, which allows to store hundreds of replicas (from different servers) on the single server. HQbird Central for Replicas is shipped by request.

With HQbird, you can always keep track of your backups and avoid losing them, no matter how many databases you have or where they are.

HQbird can transfer backups (or other files by mask) via FTP, sockets, or to Amazon S3 (needs plugin which is available on demand).

HQbird also has built-in FTP server and sockets server with easy setup.

Excessive record versions, also known as garbage versions, slow down Firebird databases significantly. HQbird implements the proper combination of sweep operations and “soft” shutdown of long running writeable transactions, and allows to avoid frequent database backups/restores. With HQbird it is recommended to do backup/restore no more than once per year.

Maintenance can also include the recalculation of indices statistics and the verification of indices health, as well as the examination of metadata health.

HQbird allows installation of multiple Firebird instances of different versions on the same server. It makes migration from one version to another easier. HQbird for Windows installs all supported Firebird versions (5.0, 4.0, 3.0, 2.5) by default, each instance with a different port. You can choose to install only one version, or several versions, during the installation.

To install HQbird for Linux with multiple instances, please use united installer (it is a new feature of HQbird v2024), and indicate what versions you want.

The fastest way to install HQbird is to use the silent installation in the command line.

In the example below we will install HQbird with Firebird 3.0 into c:\HQbird, the configuration will be c:\HQbirdData\config, output in c:\HQbirdData\output.

See also:

HQbird’s Admin package (it runs on Windows), includes Database Analyst, a tool that assists a user to analyze in detail Firebird database statistics and identify possible problems with database performance, maintenance and how an application interacts with the database. IBAnalyst graphically displays Firebird database statistics in a user-friendly way and highlights the following problems:

More details: Database structure analysis

HQbird MonLogger is a tool to analyze monitoring tables output in Firebird and find problems with slow SQL queries, wrongly designed transactions (long-running transactions, transactions with incorrect isolation level, etc) and identify problematic applications.

MonLogger can connect to Firebird database with performance problems and identify what is the reason of slowness: is it some user attachment, slow SQL query or long-running transaction?

MonLogger supports Firebird 2.1, 2.5, 3.0, 4.0 and 5.0 — for older Firebird versions or InterBase please use FBScanner.

MonLogger can show you:

HQbird includes license of FirstAID, recovery toold for Firebird. IBSurgeon FirstAID is the tool that can automatically diagnose and repair corrupted Firebird or InterBase databases — it can recover corruptions that neither gbak nor gfix can fix. Supported versions: Firebird 1.0, 2.0, 2.1, 2.5, 3.0, 4.0, 5.0, InterBase from 4.0 to 2020.

It uses its layer for low-level database access without using the InterBase or Firebird engine, so it can perform real "surgical" operations and repair your database when all other standard mechanisms (gfix and gbak) cannot.

HQbird comes with the optimized configuration by default to make the best use of resources of powerful servers and Virtual Machines. To improve HQbird configuration, you can use Cofiguration Calculator for Firebird, where you can choose “HQbird”, to obtain the basic optimized configuration for your system here: https://cc.ib-aid.com/democalc.html.

Please note that Calculator produces conservative configurations, and to create customized configuration, you need to monitor and analyze performance logs. IBSurgeon can assist you to create the ideal configuration in the context of Optimization/Configuration/Audit Incident for Firebird: https://ib-aid.com/en/firebird-interbase-performance-optimization-service/

The fastest way to install HQbird is to use the silent installation command.

In the example below we will install HQbird with Firebird 5.0 into c:\HQbird2024, configuration will be c:\HQbirdData\config, output in c:\HQbirdData\output.

The following parameters are mandatory to perform the silent installation:

Optional parameters for the silent installation of HQbird:

Must be set in pairs, both are required!

/REGUIK="z:\HQBird\test\uik" /REGUNLOCK="z:\HQBird\test\unl"

Please note, that in a case of error, for example, if you are trying to run silent installation to install HQbird to the location which is different from the current location, the error message window will popup and installation will be canceled.

The easiest way is to install Firebird bundled with HQbird – just choose the desired version during the installation. However, sometimes it is necessary to use HQbird with a community version of Firebird.

To install Firebird separately, download the Firebird zip archive from www.firebirdsql.org

Unpack the archive file to a suitable location (for instance, C:\Firebird25), after that copy the optimized configuration file firebird.conf (see Optimized Configurations below) to this folder.

Then, go to the Bin folder and then use the Run As Administrator option to run the batch file with the architecture you need.

Of course, you can choose the SuperServer for 2.5 or Classic architecture for 3.0 if you know what you need.

As a result of running the command file, Firebird of the selected architecture will be installed and run as a service.

You can make sure the Firebird service is installed and running in the Services snap-in (services.msc in command prompt):

In this example, Firebird is installed in the folder H:\Firebird\Firebird-2.5.5.26928-0_x64 and running as a service with the SuperClassic architecture.

Next steps:

Prerequisites: make sure you have libtommath, libncurses5-dev and ICU installed (there will be an appropriate error message if they are not installed).

Next steps:

Prerequisites: make sure you have libtommath and ICU installed (there will be an appropriate error message if they are not installed).

Next steps:

Prerequisites: make sure you have libtommath and ICU installed (there will be an appropriate error message if they are not installed).

Next steps:

If you don’t want to change the existing Firebird installation, please run the following command:

It will install HQbird without Firebird binaries.

Make sure these ports are allowed in your firewall configuration:

These ports can be changed in /opt/firebird/firebird.conf (RemoteServicePort), /opt/hqbird/conf/network.properties (server.port) and /opt/hqbird/conf/license.properties (serverlicense.port).

Make sure to allow these ports in your firewall configuration.

To activate HQbird, you can either use a separate utility included in the server and administrator packages for Windows, or use the registration mechanism embedded into the HQBird Firebird DataGuard web interface (for Windows and Linux), or run any tool from the administrator software and use the built-in activation wizard.

The activation wizard looks and works the same in the tools and in the activation tool. It is enough to perform activation once on any computer that can connect to the server where HQbird ServerSide is installed.

You can launch the registration utility from the Start menu (IBSurgeon\HQbird Firebird Admin\HQbird):

If you click the Register button (or Re-Register for repeated registration), you will see the activation wizard:

After that, specify the IP address or the computer name of the server HQbird is installed on in the upper input field and click Connect to HQbird Server. If you started registration utility on the same computer with HQbird Server, it will be “localhost”, otherwise — some remote address.

Then enter your registration data. If you have a license, enter your e-mail address and password that you used to register with the IBSurgeon Deploy Center and click Activate.

Right after you click Activate, the registration wizard will try to connect to the IBSurgeon Deploy Center () and obtain a license. If it succeeds, you will see the corresponding message. If there are any problems, you will see the error message.

If you forget the password, click the Forgot password…​ button and it will open the browser with the password recovery form.

If you need to purchase a new or additional license or renew your subscription, click Purchase.

Click Close this window after the registration is over.

If the server and all client computers have no access to the Internet, you should use offline activation. To do it, click Offline activation tab and follow instructions there. In case of any troubles please contact.

FBDataGuard web interface works correctly with Firefox, Chrome, Safari, Microsoft Edge and Internet Explorer 11.

If you have configured FBDataGuard to use https, the browser will indicate that this web-site is not safe, and it will recommend leaving web-site. This message is caused by the default security certificate for FBDataGuard web-console.

Please ignore this message and click to open FBDataGuard web-console.

It will ask you for username and password (login dialog can be different for Firefox, Safari or Chrome).

At the first launch FBDataGuard will check computer for installed Firebird servers. FBDataGuard for Windows search registry for Firebird records, FBDataGuard for Linux checks default locations of Firebird installations.

FBDataGuard will show the list of all Firebird copies installed, but only the one instance of Firebird can be monitored by FBDataGuard. Choose it by clicking Add Firebird engine to monitoring >>

If you don’t see Firebird instance in auto discovery list, you can choose Add custom >> and configure instance parameters manually.

FBDataGuard Web-console contains 7 tabs (in the left side of the screen, usually they are collapsed):

Web-console is intended for easy configuration of activities (called “jobs”) which are fulfilled by HQbird FBDataGuard.

Almost all FBDataGuard jobs have 2 purposes: the first is to monitor for some values and to raise alerts if necessary, and the second is to store historical values into logs, so later it’s possible to see the dynamics of important parameters of Firebird server and database.

In this section we will consider general configuration of jobs parameters, but not an analysis of gathered logs.

General approach is the following: each activity is represented by a “widget”, which has the following parts:

Status — it is indicated with color icon and name. Status of database is a summary of all included server-level jobs and databases' statuses, and, respectively, status of database is a summary of all database-level jobs.

CRITICAL means problems, OK means “Everything is fine”, WARNING means some issues which require attention, MAJOR means major issue, MINOR – minor issue, MALFUNCTION means that jobs was not succeeded (something prevents its execution), NOT_AVAILABLE means that job is not available in this server or database version.

OFF means that job is not active, UNKNOWN means that job is active but was not started yet, so actual results are unknown.

Job name indicates the name of activity.

Last run shows the time after the last run of this job.

Period/Schedule to run shows how often or when the job will be started.

More>> is the link which opens the widget and shows more details and suggested action for administrator to resolve the situation.

All jobs in FBDataGuard have default settings, which are very close to recommended values for the 80% of Firebird installations, so after initial configuration server and database will be protected at pretty good level comparing with default installation, however, we recommend additional configuration and tuning for every job. In the next sections we will consider each job and its configuration.

To register auto-discovered server, you need to click at Add Firebird engine to monitoring>> and then adjust auto-discovered settings.

When installing under Windows, if the option to automatically register the master/replica is selected, the server will be added automatically. In this case, you can skip this step.If the option to automatically register a replica is selected, then the database will be added in addition.

Let’s consider what can you see in the Server dialog (and, normally, you don’t need to change them):

By default “Output directory” for Firebird server is ${agent.default-directory}/${server.id}, it corresponds to C:\HQbirdData in case of a default installation.

It can be not very convenient, so we recommend pointing FBDataGuard output directory to more simple path, usually located at disk where backups are intended to be stored, for example F:\myserverdata.

After clicking “Save” FBDataGuard will populate default configurations files and immediately start analysis of firebird.log. It can take a while (for example, 1 minute for 100Mb firebird.log). After that you will see initial web-console with registered Firebird server:

FBDataGuard shows alerts and statuses of monitored objects: if everything is fine, it shows green signs, otherwise there will be yellow or red notifications.

Below we will consider in details each monitored objects and its settings.

Server: Active server widget shows summarized status of all server-level jobs and statuses of monitored databases.

Server: Active server also indicates Firebird currently running or not and shows detailed version of Firebird and HQbird.

If you click on configure link, you will see the same dialog that we have used to register Firebird instance in FBDataGuard, and now it can be used for changing Firebird instance properties:

In general, there is no need to edit Firebird server details after the registration, until you are not reinstalling Firebird — but in this case we recommend reinstalling HQBird too.

FBDataGuard check replication.log for errors. In case of error it sends an appropriate alert (by email) to the administrator.

To enable this job please check “Enabled”.

“Server log” job periodically checks firebird.log and if it detects that file was changed, log analysis starts. The embedded analytic engine checks each entry in firebird.log and categorizes them into several categories with different levels of a severity. According the severity of messages status of job is assigned and appropriate alerts are generated.

Once administrator has reviewed errors and alerts (and performed necessary actions to solve the reason of error), he need to click on “Resolved” link and FBDataGuard will forget old error messages in firebird.log.

In the configuration dialog of “Server log” you can enable/disable this job and set the check period (in minutes).

Also this job watches for the size of firebird.log and if its size exceeds “Size to roll”, FBDataGuard will split firebird.log and rename it according to the date-time pattern.

“Server: Temp files” job is useful to catch and solve performance problems with Firebird database.

While performing SQL queries Firebird stores intermediate results for sorting and merging data flows in temporary files, which are allocated in specified TEMP locations. FBDataGuard shows at “Server: Temp files” widget information about quantity and size of temporary files.

FBDataGuard recognizes locations of TEMP folders and monitors quantity and size of temporary files. Lack of space can lead to the performance problem or more serious errors, too many (or too large) temporary files can indicate problems with SQL queries quality.

Using configuration dialog you can enable/disable this job, set period and thresholds to the maximum size of temporary files (size of all files) and quantity.

If you see that size of temp files is too high and if there is enough RAM on the server, increase TempCacheLimit parameter in firebird.conf to fit all temporary tables into RAM.

Also, HQbird checks other temp files used by Firebird — if you see extreme values (several Gb) for trace or monitor, the good idea will be check the FIREBIRD_TMP folder for outdated files (with old modification timestamps). Please note — the screenshot below is not a real alert (i.e., values are Ok), it was created to demonstrate the output in case of large temporary files.

“Firebird server folder” jobs monitors size, occupied by Firebird installation. It’s enabled by default.

There are several threats prevented by this job: maladministration issues when database volumes or external tables are being created in %Firebird%\Bin folder, very big firebird.log which can exhaust all places at drive with Firebird installation, and some other problems.

Also this job monitors and analyses information, gathered by all space-related jobs (including database-level jobs). At the picture below you can see quick representation of space analysis for all drives where Firebird, databases and backups are stored.

Using configuration dialog you can enable/disable this job, set period of checking and thresholds for server folder size.

By default, we use 1 Gb is a standard setting for Firebird installation.

If the size of your Firebird is larger, please consider clean-up of old logs and other unwanted artifacts, or increase parameter Max occupied (in bytes) to prevent false alerts.

Note for Linux users: if you see red warning regarding the inconsistent space information, add locations with database and backups to Disk Space widget:

You can get idea where is your database and backup is actually located with command df -h.

“HQbird output folder” monitoring is intended to watch space occupied by reports, logs, stats, metadata repository and other data, gathered and generated by HQbird — this folder by default is C:\HQbirdData\output.

For databases unattended for a long time (1-2 years) it is possible that FBDataGuard logs will occupy too much space and lack of space can lead to database outage. To prevent it for sure, “HQbird output folder” is watching for occupied space.

By default, “HQbird output folder” job is enabled.

Also, if someone has ignored recommendations to put backups’ folders to the explicit locations, it is possible that database backup will be created inside Agent folder. In this case you’ll see CRITICAL status immediately — FBDataGuard will recognize and warn you regarding wrong configuration.

And, this job is useful for bundles of FBDataGuard and third-party applications.

In the configuration dialog you can enable/disable this job, set check period (by default it is 10 minutes), and set thresholds for alerts.

Thresholds can be set in % of max size occupied by log or using the explicit size in bytes.

FBDataGuard checks both values and raises alert for the first threshold. If you wish to set % only, you need to set -1 as value to “Max occupied”.

The list of databases monitored by FBDataGuard is in the “Databases” section.

To register database in FBDataGuard, you need to click at the symbol “Plus” in the right corner of “Databases” (there will be a hint “Add database”) and fill the following form:

You can see the list of databases available for registration or their aliases by clicking on the link View database aliases.

After registration, FBDataGuard will populate database configuration with default values and then show web-console with registered database:

You can adjust database settings later; now let’s proceed with alerts setup.

FBDataGuard can monitor several databases at the single server (up to 80 databases). For each database the separate widget is created. At the top widget database status is shown, database nickname (it’s specified during database adding and can be changed). Also database widget shows the full path to the database, its size, status of backups and the number of currently connected users.

Using configuration dialog you can set database nickname, path to database and output folder for the database (to store logs and jobs results).

FBDataGuard checks the validity of path to database and it does not allow specifying the wrong path.

Also, for HQbird Enterprise, in the database widget you can see status of the replication and configure replication by clicking on the icon. Please read more details in the replication configuration section.

Since HQbird 2020, the database widget in HQbird also shows the encryption status of the database.

“Database: Transactions” job is intended to log transactions activity. It monitors 2 important intervals: difference between Oldest Active Transaction and Next transaction and gap between Oldest Snapshot and Oldest Interesting.

If these intervals are out of the frames of the specified threshold, it means problem with transactions management.

These logs can be analyzed to get helpful insight regarding database performance and application quality (see more information here http://ib-aid.com/en/articles/ibanalyst-what-you-can-see-at-summary-view/).

This job also monitors the implementation limit in Firebird: maximum transactions number in Firebird versions before 3.0 should be less than 2³¹-1. Near this number database should be backup and restored. It will throw an alert if transaction number will be close to the restrictions.

Also, the transaction dynamics is shown on the tab “Graphs gallery”:

“Lockprint” job monitors the information from the lock table of Firebird. It is very important for architectures Classic/SuperClassic and useful for SuperServer.

The lock table is an internal Firebird mechanism to organize access to the objects inside Firebird engine. HQbird monitors the important parameters of the lock table:

“Database: Index statistics recalculation” is an important job which helps to keep performance of indices at optimal level, and performs additional checking of a database health.

“Database: Index statistics recalculation” allows to run re-computing of indices selectivity values. During this procedure Firebird quickly walks through leaf pages of indices, and renews statistics about selectivity. By visiting these pages Firebird also verifies their integrity and if index is corrupted, the warning will be thrown.

Also, this job verifies that all indices are active in database. Inactive or non-activated indices usually indicate corruption and lead to performance degradation.

By default this job is disabled, but we recommend enabling it after careful selecting of indices for the recalculation.

There are three modes in this job: AUTO, ALL, SELECTED.

ALL is the mode where all indices will be checked.

AUTO is the default mode. It is very similar to ALL, but it also checks the size of database and do not touch indices if database is bigger than 3.6Gb.

SELECTED is the recommended mode. It allows choosing of indices which should be recomputed or those which should be avoided.

To include indices into the list of recomputed, you need to specify indices names (divided by comma), and to exclude – perform the same in the appropriate field.

As you can see at configuration dialog screenshot, there are fields to enable/disable job, to set update mode, and to include or exclude indices. “DB size to switch, bytes” is to set limit where AUTO mode is working. “Check index activity” switch should be always on, until you are not performing special manipulations with inactive indices.

“Database: Verified Backup” is one of the key jobs to guarantee the safety of data stored in the protected database. During the development of HQbird we had in mind certain recovery scenario, and this scenario implies that the key goal of database protection is to minimize potential losses of data. If we have healthy backup, recovery can be concentrated on saving the most recent data (just entered into the database), and it greatly decreases the time of overall outage.

As you will see below, “Database: Verified Backup” is not just a wrapper for standard gbak functionality and scheduler, this is a smart job which has many built-in rules to prevent problems with backups and provide suitable interface for backups management.

Initially “Database: Verified Backup” job is shown as Ok, though backup was not tried. In this case OK means that backup at least scheduled.

Also this job recognizes files according the name pattern (see below information regarding configuration), and shows the totals number of backups.

After the backup will be done, the widget information will be changed: creation time of last successful backup will be shown, and also the time took to actually perform the backup (only 1 minute 12 seconds at the screenshot with example).

Also, the detailed alert will be send to your email and/or HQbird Control Center:

“Database: Verified Backup” checks the free space at the drive with backup destination, and if it detects that there is not enough free disk space, CRITICAL alert will be sent, and current backup will be canceled (if necessary).

Let’s consider the configuration dialog for backup in more details:

If we will click on button More>>, the advanced backup options will appear:

Incremental backup is a job to schedule and manage incremental backups in Firebird.

Please note that we recommend to use incremental backups only in combination with verified backups, since incremental backup performs coping of database pages changed since the last backup (in case of multilevel incremental backup).

HQbird FBDataGuard implements 2 types of multilevel incremental backup: Simple and Advanced incremental backups, and also Dump backup (see Database: Dump backup).

Multilevel backup in Firebird must follow the following steps:

Incremental backup with simple schedule allows planning 3 levels of backups: weekly, daily and hourly.

You can see summary information for such incremental backup configuration at the following screenshot of its widget:

In order to setup Simple incremental backup, click on Settings “gear” of the widget and select “Simple schedule” (selected by default). The following dialog will appear:

There are 4 main areas in this dialog, let’s cover them one by one.

The top area is devoted for general settings of the incremental backup – they are the same for Simple and Advanced schedules:

Max duration, sec — it limits the maximum duration of backup process, by default is 1 day (86400 seconds).

Minimum free disk space (bytes) — minimal size of free disk space to prevent backup to start, by default ~9Mb

Backup folder — where incremental backup for the selected database will be stored. It is necessary to store incremental backups for each database separately from backups of other databases: i.e., the separate folder for each database.

It is necessary to specify backup folder with enough free disk space to store all level of backups!

Journal name — file name details information about incremental backups files, for internal use only.

Path to nBackup — it is possible to specify other nbackup tool than standard nbackup (not recommended).

Backup name pattern — pattern for files of incremental backup (no need to change it).

Options — additional options for nbackup command line tool (no need to change it).

Do not check existence of backup files — this option should be checked if you plan to delete or more incremental backups to another location.

Do not check GUID chain — this option should be checked if you want to skip existence check of previous levels of incremental backups.

Immediately create non-existing low-level backups — by default this option is On. It means that if you have scheduled the initial start moment of level 1 backup earlier than the initial start moment of level 0 backup, DataGuard will automatically fix it and create level 0 backup right before level 1. The following backups of level 0 will be fulfilled according the regular schedule.

Send OK email for levels 0, 1, 2 — enable this option to receive notifications about incremental backups (highly recommended!)

After setting main set of parameters the schedule itself should be set. As you can see on the screenshot below, you need to specify day of the week and time to do level 0 (weekly) backup, days of week and time to start level 1 (daily) backups and hours and minutes of level 3 (hourly backups).

For each backup level you can specify how many files to keep in history.

By default it is set to keep 5 weekly backups, 7 daily and 24 hourly backups.

However, sometimes more flexible schedule is required, for this purpose Incremental Backup widget has Advanced schedule:

As you can see, the upper part of the configuration screen is the same as in Simple schedule, and the difference is in the way how backup levels are scheduled.

Advanced schedule allows to setup up to 5 levels of backup, and plan them with flexible CRON expressions.

For example, you can setup it to create full copy (level 0) backup every 3 months, level 1 copy every month, level 2 — every week, level 3 every day and level 4 — every hour.

This job also utilizes nbackup functionality in Firebird, but unlike multilevel backups, it always performs a full copy (level 0) of the database. Such job is useful to quickly create a copy of working database.

The configuration of “Database: Dump backup” is trivial:

You just need to setup when and where DataGuard should copy a full copy (level 0 incremental backup), and how many copies it should keep.

One of the often tasks of the database administrators is restoring database from the backup. There could be many reason to do restore, the most popular reasons are regular check of the stored backups and necessity to have fresh restored copy for quick rollback. HQbird FBDataGuard can automate restoring of backups (which were created with gbak or “Database: Verified” backup) with Database: RestoreDB job. Let’s consider the options and parameters of this job.

By default, restore is disabled — and, since restoring can be long and resource-consuming job, please plan when to restore carefully.

The database can be restored from different types of backups. To specify which types of backups are used during recovery, use the Restore Source switch.

Below you can see the configuration dialog for Database: RestoreDB in nbackup mode:

In gbak mode, the configuration dialog for Database: RestoreDB looks like this:

The purpose of "Transfer Replication Segments" job is to send replication segments produced by async replication from master to replica server. In the case of distributed environment of the asynchronous replication, when the network connection between master and replica server is unstable, or with high latency, or when servers are in the different geographical regions, the best way to transfer replication segments will be through FTP or FTP over SSH.

Below we will consider how to setup Cloud Backup for this task.

First, the asynchronous replication master should be configured to save replication segments into the some local folder — by default, it will be ${db.path}.LogArch — as it is shown in the example below:

Then we can setup Transfer Replication Segments job to monitor this folder for the new replication segments and upload them to the remote FTP server.

As you can see at the screenshot above, Transfer Replication Segments job checks folder, specified in “Monitor this folder” with an interval, specified in “Check period, seconds”.

Please note — Transfer Replication Segments sends files in the order of their names, not dates.

To check that transferred files are valid replication segments, and to support automatic re-initialization of the replica databases, the checkmark “Enable/disable” must be enabled.

By default, Cloud Backup compresses and encrypts replication segments before send them. The default password is “zipmasterkey” (without quotes), which can be specified in the field next to the “Compress segments” checkbox. FBDataGuard creates the compressed and encrypted copy of the replication segment and upload it to the specified target server.

To disable packing and encryption, uncheck the “Compress segments” checkmark.

Checkbox “Send Ok report” — send email to the specified in Alerts address every time when replication segment is uploaded. By default it is off.

As a result, FBDataGuard will upload encrypted and compressed replication segments to the remote server. To decompress and decrypt them into the regular replication segments, another instance of HQbird FBDataGuard should be installed on the replica server, and Cloud Backup Receiver job should be configured — see more details in the section Database: File Receiver.

Next, let’s look at the settings for each file transfer protocol.

The purpose of "Transfer Files" job is to send backup files from master to replica server. In the case of distributed environment, when the network connection between master and replica server is unstable, or with high latency, or when servers are in the different geographical regions, the best way to transfer files will be through FTP or FTP over SSH.

Below we will consider how to setup "Transfer Files" for this task.

First, the database server should be configured to save backup files into the some local folder — by default, it will be ${db.default-directory}/backup — as it is shown in the example below:

Then we can setup Transfer Files job to monitor this folder for the new backup files and upload them to the remote FTP server.

As you can see at the screenshot above, Transfer Files job checks folder, specified in “Monitor this folder” according to the schedule specified in “Activate cron expression”. Please note — Transfer Files sends files in the order of their names, not dates.

By default, “Transfer Files” compresses and encrypts backup files before send them. The default password is “zipmasterkey” (without quotes), which can be specified in the field “Encrypt when compressing”. FBDataGuard creates the compressed and encrypted copy of the backup and upload it to the specified target server.

To disable encryption, uncheck the “Encrypt when compressing” checkmark.

As a result, FBDataGuard will upload encrypted and compressed files to the remote server. To decompress and decrypt them into the regular files, another instance of HQbird FBDataGuard should be installed on the replica server, and File Receiver job should be configured — see more details in the section Database: File Receiver.

Next, let’s look at the settings for each file transfer protocol.

The purpose of the “Pump Files” task is to transfer files from one directory accessible to the DataGuard to some other location, usually remote, with the possibility of using various methods that can be connected to the DataGuard in the form of plugins and selectable in the task configuration with the ability to set unique for each plugin parameters. HQbird includes two file transfer plugins: fpt and sftp. There are other file transfer plugins. Transfer plugins are jar files and are located in the Firebird DataGuard/plugins folder.

Let’s consider the options and parameters of this job.

The algorithm of this task is as follows:

In general, “File Receiver” is designed to decompress files from zip archives, and the most often it is used in the pair with “Transfer Replication Segments” to transfer archived replication segments.

“File Receiver” checks files in the folder specified in “Watch for incoming files in”, with interval equal to “Check periods, seconds”. Its checks only files with specified mask according “Filename template” (.journal* by default) and specified extension (.replpacked by default). If it encounters such files, it decompresses and decrypts them with the password, specified in “Decrypt password”, and copies to the folder, specified in “Unpack to directory”.

If the Monitor for reinitialization parameter is enabled, then the File Listener will also monitor files intended for replica reinitialization, such files have the prefix specified in “Prefix for incoming reini- files”.

There are the following additional parameters:

After setup of Cloud Backup Receiver, configure the replica to look for replication segments: set in the “Log archive directory” the same path as in “Cloud Backup Receiver” → “Unpack to directory”.

“Database: Low level metadata backup” is one of the key jobs of DataGuard, it ensures database protection at low level.

First of all, this job stores raw metadata in special repository, so in case of heavy corruption (due to hardware failure, for example) of database it is possible to use this repository to recover database.

The second purpose of this job is to constantly check all important system tables for consistency. Every 20 minutes it walks through all important system tables in the database and ensures that there are no errors at metadata level.

The third purpose is to warn administrator about too many formats for each tables.

There is an implementation limit in Firebird to have 256 formats per table, however even several formats can greatly increase a chance of hard corruption and can slow down the performance. It is recommended do not change tables structure at production database and keep only one format per each table. If it’s not possible, administrator should try to perform backup/restore more often to transform all formats into the single one.

Validation of Firebird database requires exclusive access: i.e., no users should be connected during validation. “Database: Validate DB” job shuts down the database and performs validation of database, and then turns it on.

By default, this job is OFF.

Please consider carefully, is it possible to provide exclusive access for database. Validation can also take significant time.

Using configuration dialog, you can enable/disable this job, set time to run, set the shutdown timeout (time to wait before launch validation), and also shutdown mode (FORCE, ATTACH, TRANSNATIONAL). If you have no deep knowledge n what you are doing, it’s better to keep default parameters.

“Database: Validate DB” will send alert with critical status if there will be any errors.

Also, Firebird will write errors into firebird.log, and they will appear in the alerts generated by <<>> job.

FBDataGuard includes special job to run an explicit sweep, in case if automatic sweep was disabled. By default, job is disabled.

The recommendation is to schedule explicit sweep with disconnection of long-running transactions for all databases where such transactions are detected. The recommended period is once per day (usually during the night, after backup’s completing).

By default, sweep is set to 00-15, which can be not a good time, because default verified backup starts at the same time, so better change it.

Please note: by default, check mark “Disconnect connections with long-running active transactions before sweep” is enabled. It means that HQbird will find and disconnect long-running transactions (more than 300 minutes) before sweep — in order to make sweep efficient. If long-running active transactions will be not disconnected, sweep cannot clean old records versions.

“Do not disconnect processes with name pattern” — in this parameter specify SIMILAR TO expression for processes names which will be not disconnected. By default, we exclude gfix, gbak, gstat and fbsvcmgr processes.

“Disconnect processes older than (min)” — HQbird will disconnect processes which have long-running active writeable transactions, by default threshold is 300 minutes. The practical upper limit for this parameter is 1440 minutes (it is highly unlikely that transaction does something useful more than 1 day).

“Use NN CPU cores to sweep” — HQbird Enterprise can use multiple cores to perform sweep operation, in order to make sweep 4-6 times faster. We recommend to specify no more than 1/2 CPU cores in case of the single database on the server, or 1/4 of CPU cores if there are several databases. For example, if you have 16 cores and 1 big database, set this parameter to 8, if there are several big databases, set 4.

This job watches for all objects related with database: database files (including volumes of multi-volume database), delta-files, backup files and so on.

“Database: Disk space” job analyzes the growth of database and estimate will there be enough free space for the next operation like backup (including test restore) on the specific hard drive.

It generates several types of alerts. Problems with disk space are in the top list of corruption reasons, so please pay attention to the alerts from this job.

This job also contributes data to the server space analysis graph.

By default, this job is enabled.

Using configuration dialog, you can specify check period and thresholds for free space. The first reached threshold will be alerted. To set threshold only in % of disk space, you need to set explicit space in bytes to 0.

If you are using incremental backups (or Dump backup), this job is critically important. It watches for delta-files lifetime and size, and warns if something goes wrong. Forgotten delta-files are the often reason of corruptions and significant losses of data.

This jobs finds all delta files associated with database and check their age and size. If one of these parameters exceeds thresholds “Maximum delta size” or “Maximum delta age”, administrator will receive the alert and database status will be set to CRITICAL.

This job is very useful to capture performance problems and perform overall check of database at low-level without making backup.

We recommend running this job every day and storing a history of statistics report.

Then, with HQbird Database IBAnalyst it is possible to find problems with database performance and get useful recommendations how to fix them.

This task allows you to check the availability of the replica database. After a specified period, it changes the value of the specified generator and compares the value of the generator on the replica side and the master database.

Min diff to alert — the difference between the values of the generator on the master and replica side, after which alter are sent.

Many administrators use the following pattern to solve performance problems — backup-restore-replace. This is usually necessary if the application does not manage transactions well, which leads to garbage accumulation. With proper application design, there is no need to constantly perform backup and restore with replacement. Nevertheless, HQbird provides the ability to automate this process and run it both manually (via the Web console) and automatically (according to a schedule).

The backup-restore-replace pattern consists of the following steps:

However, there are many pitfalls in this seemingly simple process:

In case of any of the above problems, it is necessary to cancel the backup-restore process, that is, a rollback to the original state of the database is required.

HQbird fully automates the process and avoids most problems. It monitors disk space, disconnects active users before starting backup, and monitors the performance and integrity of the backup and restored database.

During the start FBDataGuard looks for in registry for configuration and output paths:

These values specify the paths to FBDataGuard configuration and output folder — these values are chosen during installation.

One of the most frequently asked questions is how to adjust port for web-console application (by default it is 8082). It can be done by changing port setting in file %config%\agent\agent.properties (%config% is C:\HQbirdData\config or /opt/hqbird/conf).

%config% — folder to store configuration information, it is specified in .

You can specify its password in the file access.properties (in C:\HQbirdData\config or /opt/hqbird/conf)

After setting the password, restart FBDataGuard, and new password will be encrypted and applied.

There is read-only user to access HQbird FBDataGuard, with the name guest.

To setup replication, open HQbird FBDataGuard: run modern browser (Chrome, Firefox, etc) and open this local URL: http://127.0.0.1:8082 (port if configurable in HQBird ini files)

Enter default name and password: admin/strong password.

Register Firebird server, and the following picture will appear:

Check that you are actually connected to the correct Firebird version — in the upper left corner in “Active server” widget should be version “…​ Firebird 2.5 HQbird” or “…​ Firebird 3.0 HQbird” or “…​ Firebird 4.0 HQbird”.

After that click “Add database” in the right bottom corner and configure nick name and path to the database which will be master:

After the successful registration of the database click on the icon in the header of database to setup replication:

After that the main configuration dialog for master and replica databases will appear.

When replication is not configured, this dialog is almost empty:

To start replication we need to create an initial copy of the database file, which will be used as a target for the replication process. Let’s refer to such database file as “replica”.

Starting with HQbird 2018 R2, the replica will be created automatically in the folder which will you specify in the dialog after clicking on “Reinitialize replica database”.

If you have enough space in the folder with the database, just leave the path empty, and click Submit, and replica will be created near the database. Or, you can specify other destination on the local drives with enough free space.

If you click Submit, HQbird will start the process of replica creation. There will be an appropriate message about it:

In case of default action, the resulted database will be in the same folder with the database. The name of the replica will be DATABASE_NAME.EXT.DD-MMM-YYYY_NNNN.4replica — for example, employee30.fdb.17-Apr-2018_142507.4replica

All stages of replica creation are listed as alerts in HQbird (also sent by email):

After completing the configuration of asynchronous replication on the master server, we need to configure it for the replica database at the replica server instance.

First of all, we assume that you have successfully installed HQbird on the replica server. We recommend to use on replica server SuperClassic for Firebird 2.5 and SuperServer for Firebird 3.0 (these are default configurations of HQbird).

Firebird Classic Linux users: If you run Firebird on replica server in Classic mode on Linux, you need to run additional Firebird replicator process with the command fb_smp_server -r.

Second, the replica database should be registered in HQbird FBDataGuard. If you intend to use automatic re-initialization, you can register some small database (employee.fdb) with the required name, and the do re-initialization: as a result, replica database will be automatically transferred from the master server.

Third, we assume that you have managed to setup transfer of logs with Database: Transfer Replication Segments/Database: File Receiver, or with network share.

Then complete the replication setup — the only required parameter is a path to the folder with archived replication segments, and by default it is already set — HQbird will create folder with logs near the database:

So, no need to change anything here, just click Save.

Assuming the replica database is configured in D:\DATABASE\DBWREPLICA.FDB, the HQBird will create folder D:\DATABASE\DBWREPLICA.FDB.LogArch, and replica will import replication segment files from it.

Click Save and restart Firebird service (to ensure that replication parameters were applied).

After restart, the replica server will start to consume the replication segments from the folder — please note, after the import all processed segments will be deleted. Also, it will create file with the name {DATABASE-GUIDE} — Firebird stores there some internal information about replication progress.

If Database: Transfer Replication Segments/Database: File Receiver are configured, it is possible to perform the complete re-initialization with 1 click to “Reinitialize replica database”.

Once clicked, the master HQbird will do the following:

Next steps will be done by replica HQbird instance:

Replica is back to the normal mode.

If you have setup asynchronous replication, but it does not work, the first thing is to enable job Server: Replication Log on the master and on the replica. This job parses replication.log files, and if there are errors, creates the appropriate alert.

Also, the good thing is to enable “Verbose” option on the replica, and restart Firebird. Verbose will make Firebird to write a lot of details about replication into the replication.log file (near firebird.log).

Usually the text of the error is self-explanatory, but since there are some popular questions which occur regularly, we decide to create the table with the list of main problems with asynchronous replication and ways to resolve it.

As you can see, the downtime required for initialization the synchronous replication is bigger than downtime to configure asynchronous replication, because replica database must be online before master’s start.

Synchronous replication is designed to write changes from the master database directly to the replica database. The big advantage of synchronous replication that replication delay can be very small, but the disadvantage is that in the case of the lost connection between master and replica servers there will be gaps in transmitted data.

In this example, the synchronous replica database is on the remote server with IP address replica server and path /data/test2.fdb.

No setup is necessary for synchronous replication on the replica server, except gfix –replica <master-guid> for the replica database to switch it to the replica mode.

In the case of testing synchronous replication of HQbird on the production system, we recommend setting parameter disable_on_error to true.

It will switch off replication in case of replication error, and the master server will continue to work without replication.

To reinitialize replication the replication log should be analyzed and all initialization steps should be done again.

Also, please enable job “Replication log” in HQbird FBDataGuard to monitor replication log for errors and warnings:

Let’s consider how to create replica for asynchronous replication using nbackup:

Database GUID is the unique identifier of a master database.

To find out {DATABASEGUIDE}, run command gstat –h:

To switch database to the replica mode run the following command:

To switch database to the normal (master) mode run the same command with the empty {} instead of database GUID:

for Firebird 2.5 and 3.0

for Firebird 4.0

If you run gstat –h database_name, the output will contain the keyword “replica” in Attributes section for database configured as replica:

For master database there is no special marks in Attributes.

In Firebird 2.5 and 3.0, there is a context variable REPLICA in the SYSTEM area that contains information about database status:

In Firebird 4.0 use another context variable REPLICA_MODE:

Also in Firebird 4.0 you can use the MON$DATABASE monitoring table:

Database replica mode:

This feature can be enabled in firebird.conf with the parameter DSQLCacheSize:

The number specifies how many recent queries for each database connection to cache.

To apply the new value, Firebird restart is required.

By default, cache of prepared statements is 0, it means OFF. We recommend to carefully enable it: start with values like 4, 8, 16, to find the best performance effect.

In Firebird 5.0, the prepared statement cache was redesigned and included in the "vanilla" build.

The cache of prepared statements is controlled by the MaxStatementCacheSize parameter in firebird.conf.

By default, the prepared statement cache is enabled. To disable it, you need to set the MaxStatementCacheSize parameter to 0.

HQbird monitors all aspects of Firebird server and database functioning, and includes continuous monitoring and detailed monitoring.

The continuous monitoring is performed by HQbird FBDataGuard. It is low-invasive, but very effective monitoring which can help to find and resolve the majority of issues with databases performance and stability.

FBDataGuard performs the following monitoring activities:

FBDataGuard can graphically represent information gathered during the monitoring, for example, transactions and number of users:

In the version 2020, HQBird introduces the new approach for the automatic performance monitoring with Firebird MON$ tables and TraceAPI.

Now it is possible to schedule the regular check of database performance in less than 1 minute.

For this just open tab Performance and setup monitoring for transactions and queries:

In order to setup Performance monitoring, specify its mandatory parameters in the dialog:

The first mandatory parameter is “Enable performance monitoring” — it must be enabled to run traces by schedule.

The next important parameters are “Start trace session at” and “Stop trace session”. They contain CRON expressions which specify when tracing starts and stops.

By default, trace is set to start at 10-30 and to end at 11-00. It is recommended to adopt tracing schedule for your needs. Below you can see the table with some popular options.

The next important parameter is time threshold for the slow queries, it is set in the field “Log SQLs with execution time more than”. In this field you need to set time threshold (in milliseconds), after exceeding it logs will be stored and analyzed.

By default, the time is set to 1000 milliseconds, or 1 second. It means, that only queries which take more than 1 seconds, will be logged and analyzed.

We recommend keeping 1000 ms as a basic value, until your database is very slow: in this case 3000-5000 ms can be a good start.

“Send email” check mark indicates if there necessity to send the performance report. The email settings from Alerts configuration will be used to send performance report.

For more advanced settings, “Performance Monitoring” dialog has additional parameters (normally, you don’t need to adjust them).

As a result of this job, HQbird will generate the performance report, which will be stored in the Output folder as a file with the extension html, and it will be sent by email (in case if “Send email” is enabled). Also the most recent performance is available for review and download in the HQBird interface.

The HQbird FBDataGuard performance analysis provides 3 types of reports:

In the beginning of the report you will see the graphical representation of most problematic SQL queries:

When you click “Sort by duration” (it is a default option), you will see SQL queries and stored procedures which took the longest time to execute first.

Normally there will be long-running reports and other big SQLs.

When you click on “Sort by frequency” link in the header of the report, you will see most frequent queries: i.e., those queries which started frequently (among logged queries).

For example, in this case the statement SP_GET_INVOICE_REPORT was run 46 times. It means that this query heavily affects the overall performance, and it should be optimized first.

When you click on “Sort by summary”, you will see the queries which took the most part of the time (among logged queries). These queries usually are the best candidates for the optimization.

FBDataGuard is the first line of a defense for Firebird database; once FBDataGuard encounters something suspicious inside the monitored areas, it sends an alert with description of the issue.

After receiving such alert from FBDataGuard the database administrator should proceed with detailed investigation of the problem.

The choice of tool for detailed monitoring depends on the type of detected problem.

If FBDataGuard reports long-running active transaction (Next-OAT), it is necessary to use HQbird Mon$Logger to detect the source of currently running active transaction.

If stuck of oldest interesting transaction is reported, database administrator must plan an explicit sweep to clean uncollected garbage with FBDataGuard sweep job (if it is necessary) and then plan tracking of forced rollbacks with Performance Monitoring in FBDataGuard.

If users report slowness problem with some queries, Perfusion or FBScanner should be used.

If there is unusual spikes in transaction behavior, IBTransactionMonitor can be a good addition to HQbird FBDataGuard to clarify the situation.

The problems with general database performance and occasional or periodic slowness require an analysis of database structure, which can be done only with HQbird Database Analyst.

Below we will consider how to work with HQbird monitoring tools in more details.

At the first screen we can see aggregated statistics for database connections, and identify connections with the biggest problems:

At the second tab you can find aggregated performance statistics for statements.

This statistics better reflects the momentary situation in the database — since monitoring tables collect information since the beginning of each object life, statements you can see here are those which were running during the moment when snapshot was taken.

The third tab is “Attachments”. You can open this tab directly to jump there by clicking one of the records at “Aggregated performance statistics”.

“Attachments” shows the list of users connected to the Firebird database, with many useful details: USER and ROLE of attachment, start time and ID of attachment, is there garbage collection enabled for the attachment, name of remote process which established an attachment, and several accumulated performance counters for the attachment: number of sequential reads (done by attachment since its start), number of indexed reads, number of inserts, updates and deletes, as well records backouts, purges and expunges.

By default, some of columns of attachment are switched off, to show only most important information.

Of course, every time you click on attachment, you can jump to transactions running inside it, and then to statements. There is a checkbox in the left upper corner of “Transactions” and “Statements” tabs, which controls the behavior — when checked, only transactions and statements marked by selected attachment ID, will be shown.

Tab “Transactions” shows active transactions at the moment when snapshot was taken.

If checkbox “Link to selected attachment” is enabled, only transactions for selected attachment will be shown, otherwise all transactions are shown.

One of the most important characteristics is a lifetime of transactions: since Firebird is designed to work with short write transactions, it is important to keep them as short as possible. MonLogger highlights transactions with isolation modes and read-write settings which hold Oldest Active transaction and therefore provoke excessive record versions to be not cleared. If you see such transaction and it started a while ago, it means that it can be responsible for excessive records versions.

Sort on “started at” column and look for old transactions, marked in red: all writeable transactions and read only snapshots stuck Oldest Active Transaction and provoke excessive record versions to be hold. Identify where these transactions started (right-click and select “View parent attachment”) and fix your code to commit this transaction earlier.

Tab “Statements” shows statements active at the moment of snapshot: if you need to catch all statements FBPerfMon or FBScanner should be used (all these tools are part of IBSurgeon Optimization Pack).

If “Link to selected attachment” is enabled, only statements for specific attachment will be shown, otherwise all active statements are in the list.

Some statements have no associated transaction id (=0): these queries are prepared, but not executed.

The graph displays the performance counters Fetches, Reads, Writes, Marks based on monitoring tables. You can drill down to each time point by clicking on it or selecting "Data for time" from the list.

The graph displays the number of active users and requests, as well as the ping time. You can drill down to each time point by clicking on it or selecting "Data for time" from the list.

The graph displays the performance counters Fetches, Reads, Writes, Marks and statement execute time based on data from trace logs. You can drill down to each time point by clicking on it or selecting "Data for time" from the list.

The graph displays the consumed memory, as well as the processor load based on tracking by the wmic utility.

The same as "RAM and CPU Windows", only in Linux.

The graph displays the number of active transactions and the gap between the counters OST-OIT, Next-OAT.

The graph displays data to the load on the lock manager (relevant in Classic and SuperClassic).

FBScanner (Firebird Scanner) is a tool included in HQbird advanced distribution of Firebird, which can monitor and view all traffic between Firebird and InterBase servers and their client applications.

It shows the real-time activity of connected clients:

FBScanner can log all SQL traffic to text files and external Firebird database, it includes FBScanner LogAnalyzer module to analyze SQL performance.

FBScanner can be used to profile database applications, monitor user activity, and manage database connections (including client disconnects on Classic, SuperClassic and SuperServer architectures). It’s also ideal for troubleshooting INET errors, as well as auditing existing applications and performance tuning.

FBScanner supports Firebird (V1.x, V2.0, V2.1 and V2.5), InterBase (V4.0 to 2009/XE3). It is a useful tool for analyzing production databases, especially if the application has been developed by third-party and there is no source code available.

FBScanner is transparent as far as the database application is concerned and does not require any changes in application or database source code, logic or configuration.

FBScanner does not change anything in transferred SQL traffic and works simply like a transparent proxy, so all applications will work normally.

FBScanner consume approximately 50-150Mb of memory (for 30-100 active clients), it is known that FBScanner adds approximately 150ms for every SQL statement.

To configure FBScanner start “FBScanner Service Settings” from Start menu (IBSurgeon\HQbird Server Side\Firebird SQL Scanner\).This tool will help you to setup both basic and advanced configuration parameters for FBScanner.

The basic configuration parameters are shown at the main screen of “FBScanner Configuration”. It scans Windows registry for installed Firebird services and show them in the grid.

By default Firebird uses port 3050 for network connections.

FBScanner works as a transparent TCP proxy — it redirects all SQL traffic from and to Firebird clients to another.

FBScanner offers to change Firebird port to 3053, in order to start its own instance at 3050. FBScanner checks for the port usage and if either 3050 or 3053 are used by other software (not Firebird), it will warn you with red caption “Port used” near new “Port” text box.

The green figure in the center of “FBScanner Configuration” main screen briefly shows how client applications SQL traffic will be passed.

At the figure below you can see that FBScanner found Firebird 1.5 instance, and offers to change its port to 3053, in order to set own instance to listen at 3050.

Such default scenario will give the maximum compatibility with existing Firebird clients (i.e., end-user applications).

To approve the changes, click “Ok”, otherwise “Cancel”.

FBScanner can route SQL traffic not only as local proxy, but from another computer too. To understand the difference and discover consequences, let’s walk though details.

The basic (and default) configuration of FBScanner implies that it works on the same computer where Firebird is working, and process all SQL traffic from Firebird clients (i.e., end-user applications) which use default connection string (and, therefore, port 3050).

Sometimes it’s not convenient to setup FBScanner to process all requests, for example, in case of:

In these cases the good idea is to setup FBScanner at the remote computer and pass only part of SQL traffic through it. It also makes possible to perform necessary analysis of SQL without changing ports or other configuration at server — the only needed adjustment will be change host name in client applications' connections strings.

One of the frequent use cases for setting up FBScanner in remote configuration is using it as debug console for developer computer, so developer can see in real-time (with FBScanner LogViewer) or afterwards (with FBScanner LogAnalyzer) all SQLs from own computer to the Firebird server.

At the figure below you can see how it can look like:

Now let’s back to the configuration and see how easy to setup FBScanner to route SQL traffic at the remote computer.

At the bottom of the main screen of “FBScanner Configuration” you can see the following default settings (for Firebird 2.5 example we considered above):

In order to setup FBScanner to route SQL traffic to the remote Firebird, we need to change “Server Type” from “Local…​” to “Remote”. It will change the main screen of the configuration tool.

First of all, we need to specify network name (or IP) of the computer with Firebird instance and port where it will be used — it should be entered into “Interface” text box.

Then we need to specify Firebird version — in our example it’s Firebird 1.5.

FBScanner instance also has “Interface” — it’s the list of network adapters found at the computer. If you need to bind FBScanner to one of them and disable connections from other network adapters, choose one of the adapters from the drop-down list. By default FBScanner will accept Firebird clients’ requests from all network adapters.

Below you can see the example of FBScanner configuration to route SQL traffic to remote Firebird instance, which resides on myserver1 computer and works on default port 3050.

Click “Ok” to confirm new settings, and FBScanner will route SQL requests to the remote Firebird.

From Start menu run “Firebird Scanner\FBScanner Settings”, then click button “Advanced options” (in the right bottom of the main screen).

At the dialog click tab “SQL log”.

By default logging is disabled.

There are 2 options for logging — to file and to Firebird log database.

Many users told us that they did not realize how many queries, transactions and other operations are performed by their software. As you remember, FBScanner stores all information into the single table. It uses self-links to reduce the amount of stored information and it makes raw log hard to read and understand.

To facilitate log analysis we have created new module in FBScanner — LogAnalyzer. It’s available in IBSurgeon Deploy Center for all FBScanner users (inside “Download” section).

LogAnalyzer requires Firebird 2.5 to work with log database. It also creates new indices and runs heavy reporting queries, so it’s recommended the following procedure:

To analyze log database, start LogAnalyzer and click “Connect to FBScanner log base”, then fill out connection parameters and select log database.

At first start LogAnalyzer will create necessary indices, it can take several minutes.

After that LogAnalyzer will show the last available day in the log at the “Server Load” tab:

“Server Load” tab shows how many SQL queries were run per minute, and how much time they took to execute. Effectively it shows server load, i.e., number of queries and their execution times.

Zoom in (button in the top left corner of the tab “Server load”), drag graph by holding right-button of the mouse and select the peak you are interested to investigate — click right-button to show popup-menu

It will show you tab “All statements”, where you can browse SQL queries

Select any query to see its text and, if plan logging feature is enabled, its plan.

To follow the execution flow, you can right-click on the query and look for connection and transactions for this query

LogAnalyzer marks bold queries in the same transaction:

You can sort queries and, for example, find query with the longest execution time:

To know more about this query — double-click on it and see more details

FBScanner automatically logs all 10054 errors, disconnects and failed login attempts with detailed description in the FBScanner.log file, which is in FBScanner main directory.

To perform operations which do not require monitoring or debugging, like backup and restore or mass load of records (in billing systems) we recommend bypassing FBScanner service.

If FBScanner is installed in default recommended configuration, i.e., on port 3050 and Firebird is on port 3053, connection strings should be like this

example of connection string

Example of using backup command

and, of course, using local connection string will always bypass FBScanner:

To monitor connections, queries and transaction in real-time FBScanner includes special tool namely FBScanner Viewer.

FBScanner Viewer shows momentary snapshot of SQL traffic between Firebird and monitored client applications.

In the first column we can see type of record — connection, statements or transaction.

In the table below you can find description of all columns at main page of FBScanner Viewer (some columns are hidden by default, use menu Columns to turn them on/off):

In the following table you can see details for the values appeared in the first column in FBScanner Viewer for SQL statements rows:

Summary contains the most important information extracted from database statistics. Usually full statistics of database contains hundreds of Kbytes and it is not easy to recognize the important information.

Below is the description of database objects and parameters that you may see in Summary. For description of visible problems (marked red or yellow) see column hints or Recommendations output.