Library

What are practical recommendations? Since this feature came from HQbrid (vv2.5-4) where it was used during several years on hundreds of servers, we can use its experience for Firebird 5.0. For SuperServer it is recommended to set parameter MaxParallelWorkers to 64, and ParallelWorkers to 2. For maintenance tasks (backup/restore/sweep) it is better to set individually with switch -parallel, with the value equal to the half of the number of physical cores of your processor (or all processors).

For Classic architecture, MaxParallelWorkers should be set to a number smaller than the number of physical cores of your processor it is important since the MaxParallelWorkers limit is set on each process.

Let’s look at how parallelism affects the execution time of building or rebuilding an index. The effect of parallelism on automatic sweep will not be shown, since it starts automatically without our participation. Impact of concurrency on manual sweep will be demonstrated when examining how Firebird tools perform maintenance tasks.

Let’s compare the speed of creating an index for the WORD_DICTIONARY table for different ParallelWorkers values, containing 4079052 records.

For the purity of the experiment, before the new test, we restart the Firebird service. In addition, in order for the table to be in the page cache, we run the following query:

The query to create an index looks like this:

The execution statistics for this query with ParallelWorkers = 1 are as follows:

Now let’s delete this index, set ParallelWorkers = 4 and MaxParallelWorkers = 4 in the config and restart the server. Statistics for running the same query look like this:

As you can see, the index creation time has decreased by a little over 2 times.

The same thing happens when rebuilding the index with the query:

Let’s see how parallel execution will affect backup’s speed of gbak tool.

We will use the fastest backup option - through the service manager and with garbage collection disabled. In order to be able to track the time of each operation during the backup, we will add the -stat td switch.

First, let’s run the backup without parallelism:

The backup completed in 35.810 seconds.

Now let’s try to run a backup using 4 threads (on the computer which has 8 cores).

The backup completed in 18.267 seconds!

As you can see, as the number of parallel processors increases, the backup speed increases, although not linearly.

Now let’s look at how parallelism affects the speed of restoring from a backup. Restoring from a backup consists of the following steps:

Parallelism will only be involved in the last two stages.

In order to be able to track the time of each operation during restoration from a backup, we will add the -stat td switch.

First, let’s start restoring from a backup without parallelism:

Restore from backup completed in 201.590 seconds. Of these, 70.73 seconds were spent on restoring table data and 121.142 seconds on building indexes.

Now let’s try to start restoring from a backup using 4 threads.

Restore from backup completed in 116.718 seconds. Of these, 26.748 seconds were spent on restoring table data and 86.075 seconds on building indexes.

With the help of 4 parallel workers, we were able to almost double the recovery speed. At the same time, the speed of data recovery has increased by almost 3 times, and the construction of indexes has accelerated by 1.5 times.

Why? The explanation is simple: parallelism is used only when engine builds large indexes. Many tables in the database taken as an example are small, the indexes on them are small too, and the number of such tables is large. Therefore, the numbers in your database may be different.

Sweep (cleaning) is the important maintenance process: Firebird scans specified database, and if there are “garbage” records versions, remove them from data pages and from indices. By default, Firebird database is created with autosweep setting, but for the medium and large databases (30+Gb) with high number of transactions per second it could be necessary to disable automatic sweep use manual (usually, scheduled) sweep instead.

How to test correctly? An idle sweep after restoring from the database did not show any difference in single-threaded and multi-threaded mode. Therefore, I first ran a sweep on the restored database so that the next sweep would not check uncluttered pages, and then I made a request like this:

The purpose of this request was to create 'garbage' in the database. Now you can run the sweep to test its execution speed.

First, let’s run sweep without parallelism:

Now we will update the large table and rollback again, and run a sweep with 4 parallel workers.

As you can see, the speed of sweep execution has increased more than 2 times.

The -icu switch allows you to rebuild the indexes in the database using the new ICU.

The ICU library is used by Firebird to support COLLATION for multibyte encodings like UTF8. On Windows, ICU is always bundled with Firebird. In Linux, ICU is usually a system library and depends on Linux version. When moving a database file from one Linux distribution to another, the ICU installed on the system may have a different version. This may result in a database on an OS running a different version of ICU being binary incompatible for indexes character data types.

Since rebuilding indexes can be done using parallelism, this is also supported for gfix -icu.