FirebirdSQL logo

Support for parallel operations

Vlad Khorsun

Tracker ticket: #7447

The Firebird engine can now execute some tasks using multiple threads in parallel.Currently, parallel execution is implemented for the sweep and the index creation tasks.Parallel execution is supported for both automatic and manual sweep.

To handle a task with multiple threads, the engine runs additional worker threads and creates internal worker attachments.By default, parallel execution is not enabled.There are two ways to enable parallelism in a user attachment:

  1. set the number of parallel workers in DPB using new tag isc_dpb_parallel_workers,

  2. set the default number of parallel workers using new setting ParallelWorkers in firebird.conf.

The gfix utility has a new command-line switch, -parallel, that allows to set the number of parallel workers for the sweep task.

For example, the following will run sweep on the given database and asks the engine to use 4 workers:

gfix -sweep -parallel 4 <database>

gfix uses DPB tag isc_dpb_parallel_workers when attaches to <database>, if switch -parallel is present.

A similar option was also added to gbak.

The new firebird.conf setting ParallelWorkers sets the default number of parallel workers that can be used by any user attachment running parallelizable task.The default value is 1 and means no use of additional parallel workers.The value in the DPB has a higher priority than the setting in firebird.conf.

To control the number of additional workers that can be created by the engine, there are two new settings in firebird.conf:

ParallelWorkers

Sets the default number of parallel workers used by a user attachments.Can be overridden by attachment using tag isc_dpb_parallel_workers in DPB.

MaxParallelWorkers

Limits the maximum number of simultaneously used workers for the given database and Firebird process.

Internal worker attachments are created and managed by the engine itself.The engine maintains per-database pools of worker attachments.The number of threads in each pool is limited by the value of the MaxParallelWorkers setting.The pools are created by each Firebird process independently.

In SuperServer architecture worker attachments are implemented as light-weight system attachments, while in Classic and SuperClassic they look like usual user attachments.All worker attachments are embedded into the creating server process.Thus, in Classic architectures there are no additional server processes.Worker attachments are present in monitoring tables.Idle worker attachments are destroyed after 60 seconds of inactivity.Also, in Classic architectures, worker attachments are destroyed immediately after the last user connection detaches from the database.

Examples:

Set in firebird.conf ParallelWorkers = 4, MaxParallelWorkers = 8 and restart Firebird server.

  1. Connect to test database not using isc_dpb_parallel_workers in DPB and execute a CREATE INDEX …​ SQL statement.On commit, the index will be created and the engine will use three additional worker attachments.In total, four attachments in four threads will work on index creation.

  2. Ensure auto-sweep is enabled for test database.When auto-sweep runs on that database, it will also use three additional workers (and run within four threads).

  3. More than one task at a time can be parallelized: make two attachments and execute a CREATE INDEX …​ in each of them (of course indices to be built should be different).Each index will be created using four attachments (one user and three worker) and four threads.

  4. Run gfix -sweep <database> without specifying switch -parallel: sweep will run using four attachments in four threads.

  5. Run gfix -sweep -parallel 2 <database>: sweep will run using two attachments in two threads.This shows that value in DPB tag isc_dpb_parallel_workers overrides value of setting ParallelWorkers.

Inline minor ODS upgrade

Dmitry Yemanov

Tracker ticket: #7397

This feature allows to upgrade the existing database to the newest ODS version without backup/restore, provided that the database belongs to the same major ODS version.

For example, a database created by Firebird 4.0 uses ODS 13.0 and thus can be upgraded to the ODS 13.1 used by Firebird 5.0.

Notes
  • The upgrade must be done manually, using gfix -upgrade command

  • It requires exclusive access to the database, an error is thrown otherwise

  • The system privilege USE_GFIX_UTILITY is required

  • An upgrade is transactional, all changes are reverted if any error happens

  • After the upgrade, Firebird 4.0 can no longer open the database

Usage
gfix -upgrade <database>
Caution

This is a one-way modification, downgrading backward is impossible.So please make a database copy before upgrading, just to have a recovery point if something goes wrong during the process.

More cursor-related details in the plan output

Dmitry Yemanov

Tracker ticket: #7441

Detailed plan output now distinguishes between user-specified SELECT statements (reported as select expressions), PSQL declared cursors and sub-queries.Both legacy and detailed plans now also include information about cursor’s position (line/column) inside their PSQL module.

Examples:

Legacy plan
-- line 23, column 2
PLAN (DISTRICT INDEX (DISTRICT_PK))
-- line 28, column 2
PLAN JOIN (CUSTOMER INDEX (CUSTOMER_PK), WAREHOUSE INDEX(WAREHOUSE_PK))
Detailed plan
Select Expression (line 23, column 2)
    -> Singularity Check
        -> Filter
            -> Table "DISTRICT" Access By ID
                -> Bitmap
                    -> Index "DISTRICT_PK" Unique Scan
Select Expression (line 28, column 2)
    -> Singularity Check
        -> Nested Loop Join (inner)
            -> Filter
                -> Table "CUSTOMER" Access By ID
                    -> Bitmap
                        -> Index "CUSTOMER_PK" Unique Scan
            -> Filter
                -> Table "WAREHOUSE" Access By ID
                    -> Bitmap
                        -> Index "WAREHOUSE_PK" Unique Scan

Line/column numbers (as well as PSQL declared cursors) cannot be seen directly in the plan for user-specified SQL queries, except if the query is EXECUTE BLOCK.However, they are accessible in the MON$EXPLAINED_PLAN column in either MON$STATEMENTS or MON$COMPILED_STATEMENTS tables.