5 First Steps To Better Firebird Performance

In general, Windows users need to do the following 3 steps:

On Linux, there are 2 mandatory steps for Firebird servers:

Are you using the default firebird.conf file that comes with the regular (vanilla) Firebird, installed from the standard installer or Linux repo? If so, we have some bad news for you. Your firebird.conf file is not the best one for your Firebird database.

The default firebird.conf file is made to work on the smallest and simplest hardware or VM. But that’s not what most production servers need.

If you are using HQbird with Firebird, you are doing better. HQbird gives you a firebird.conf file that is better for the average server.

Do you want to make your firebird.conf (and databases.conf) files better for your hardware or VM? That’s a smart idea. We have a free service that can help you with that. It’s called “Configuration Calculator for Firebird”, and it can make your Firebird database use your resources better. The service will make the better than standard configuration for you, and. more important, help you avoid common mistakes in Firebird configuration.

If you want to make your configuration even better, we can do more things for you in the frames of our Optimization Services, like analyzing your queries, your database structure, and more.

To try the Configuration Calculator, just click this link: https://cc.ib-aid.com/democalc.html or, better, register in https://cc.ib-aid.com, to access other tools for Firebird.

Select Firebird version, architecture, and then details of your hardware/VM (RAM/CPU cores), and characteristics of database and load: database size, database page size, and click “Create”:

Next, you need to copy and paste the results into your firebird.conf and databases.conf files.

But before you do that, make sure you delete everything that’s already in those files, even the comments. This way, you won’t have any problems with the old and new settings.

Make sure you don’t have value for Page Buffers line in the header page of your database!

If you have to ensure it, run in terminal (command prompt):

or (on Windows)

The output will look like this:

Check that line with parameter Page Buffers is 0.

If it is set to other value, it will override the settings in firebird.conf and databases.conf.

To use values from configuration, set Page Buffers in header to 0 with command

To apply new configuration, restart all instances of Firebird.

As a result, you will see improved performance of your Firebird instance.

There are several universal tools to measure the disk performance in the common way, they give you the estimation in MB/sec or in IOPS. The most popular is CrystalDiskMark (https://crystalmark.info/).

In our experience, CrystalDiskMark gives adequate estimation of disk speed and can be used as a starting point. The example of CrystalDiskMark output in the text format:

However, it is not enough to have a warranty that disk is really fast for Firebird, because the test is very synthetic.

In order to measure the performance of disk from the Firebird, IBSurgeon has developed the script, which creates the database, inserts 1 million of records, then updates these records, and then deletes them.

The script is executed by isql.exe standard tool, with the actual configuration of Firebird.

Essentially, it is single-thread test which measures Firebird performance for the case of a “peak” processing operation — the result of the test is a database file with size 3.6 Gb.

The result is presented in the form or number of milliseconds to perform Insert 1 Mln, Update 1 Mln, Delete 1 Mln. records separately for the operation and for the commit time.

Since the creation of the script, people have run it on the hundreds of servers (many people from the community have run the test on various servers and sent its results), and now there is the table and the diagram with results.

To facilitate comparison, the result is presented in the format of Inserts/seconds, Updates/seconds, Deletes/second.

Firebird database indices can exist in one of three states: active, inactive, or non-activated.

To inspect the state of indices in a Firebird database, execute the following query:

If the result is 0, all indices are active. If results is not 0, do the following query to see the list of problematic indices:

and check the states of listed indices. Value 1 means that index is inactive, and value 3 means that index is non-active state.

To activate index, do the command for each index:

The optimizer in Firebird (and in InterBase) uses 2 main criteria when analyzing a query and choosing an execution plan — the size of the tables (cardinality) and the selectivity of the indices (selectivity).

The selectivity of the indices is calculated as 1 divided by the number of unique values in the indexed column (or columns). For example, if this is an index on a primary key, and there are 10 thousand records in the table, then its selectivity will be equal to 1 / 10000 = 0.0001. And if the same table has a column with 10 different values, then the index on this column will have a selectivity of 1 / 10 = 0.1. For the optimizer, the lower the selectivity of the index, the better.

If the optimizer always determines the size of the table more or less accurately, then the selectivity of the indices is not recalculated automatically. As a result, the optimizer could estimate the selectivity that is far from reality. So, the selectivity needs to be recalculated manually.

The recalculation of the selectivity of the indices occurs when they are created, when ALTER INDEX ACTIVE, and when SET STATISTICS INDEX indexname.

For example, you created a table, created indices, and then filled the table with data. The selectivity of all the indices of this table will remain 0 until you recalculate the selectivity.

Or, you created a table, indices, filled it with data, made a backup-restore (when restoring, the indices are recreated, and the selectivity will be relevant). And then you changed 30-40% of the data in some tables (added, deleted, or modified). And the selectivity will also be irrelevant.

You can view the stored selectivity of the indices with a query (the selectivity of inactive indices will be equal to -1)

Recalculating statistics of indices in Firebird database is a way to improve the performance of your queries. Statistics tell Firebird how selective your indices are, and help it choose the best execution plan. Here are the steps to recalculate statistics of indices in Firebird database:

That’s it. You have recalculated statistics of indices in Firebird database. You can do this periodically, especially after large changes in your data, to keep your indices efficient.

To verify the state of indices with HQbird, follow these steps:

Your Firebird database is made up of pieces that are all the same size. These pieces are called pages. The size of the pages depends on the version of Firebird you use. In Firebird 1, the default size of pages was 1024 bytes. In Firebird 2.5, the default page size was 4096 bytes. And in Firebird 3.0 and up, the databases are created with page size 8192 bytes. For bigger databases, Firebird 2.5 and 3 have options up to 16384, and v4 + has 32768 bytes.

To see what is the page size for your database, please use command:

On Windows

On Linux

and see line “Page size”.

The size of the pages matters a lot for your Firebird database, so you need to pick the right one.

To change page size, you need to use the gbak tool to backup and restore your database, and add the -page <NNN> option. But be careful, this can take a long time if your database is big. So, it’s better to pick the right page size from the start.

How do you pick the right page size for your Firebird database? There are the following things to consider:

Let’s consider all these points:

As you can understand, the page size cannot be less than default on the specific database. If database is created with CREATE DATABASE command without PAGE_SIZE option, the database by default will be created with default page size. If database was created on previous version of Firebird and converted with backup/restore, the page size could be preserved, and it could be less than default, i.e., non-optimal. In this case, you need to change page size to the default or above. Practically, it means 8192 as a minimum.

For databases less than 20Gb in size, or without intention to grow over this size without backup/restore (when there will be a possibility to change the page size), the general recommendation is to use 8192 bytes as page size (and it is a default size for versions 3+).

For bigger databases use 16384 bytes.

What about 32768, will be better to use the biggest available option?

We have made tests with databases up to 1Tb in size, and did not find the direct performance improvement in comparison with 16384. One of our customers with 4Tb database has reported that 32768 page improved performance. So, decision to use 16K or 32K page size will depend on the next steps.

In order to keep performance of indices on the optimal level, they should have depth ⇐3.

How to check depth?

Using only standard tool, it is a bit lengthy process:

In HQbird, you need to use tool HQbird Database Analyst from HQbird Admin package, which is designed to analyze databases statistics.

Open HQbird Database Analyst, retrieve database statistics (or open file with manually collected statistics), go to tab Indices, and sort on Depth:

If you see indices with Depth > 3, it is necessary to increase database page size.

If you already using the maximum possible page size, for example 16384 on v3, consider migration to v4-5, where maximum page size is 32768.

Firebird keeps table’s data in the compressed form. You can see in the statistics of databases from the previous step an information about size of records in the table, something like this: (the output can be different for different versions):

It is necessary to check the size of records for the biggest tables and compare it with the page size.

The idea is to calculate how many records are stored on the single page — if there are records with average length 700 bytes on 4096 bytes pages, Firebird will need to read many data pages.

Of course, it depends on the database structure and the business logic, but the bigger size of records, the bigger page size needed.

In “HQbird Database Analyst” we can see records sizes for tables in the simple table form: open or retrieve statistics, tab “Tables”, and sort on Record Length.

In addition to “Record Length”, “Database Analyst” show parameter “Average Fill” and calculates derived parameter “Real Fill”, to show how efficiently data are stored on database pages.

Remember that Firebird cache size is measured in pages? If page size is changed, the absolute size of cache will be also changed. If you have set 1 million of pages for the page size 8192, the absolute size of the cache is 8Gb, and if page size is increased to 16384 bytes, the absolute cache size will become 16Gb.

In order to avoid problems with too big cache, after the change of page size, recalculate the configuration in Configuration Calculator cc.ib-aid.com.