Recent Performance And Administration Features in Firebird that 'Just Work'!

Major

True SMP support for Super Server.

This is the number one reason to upgrade to Firebird 3.0 if performance is the main bottleneck.

Further improvements were made to Firebird's network protocol

• CORE-2530. This provides a denser data stream and better prefetch logic. Or in other words performance improvements for data sent across the network between the client and server.

Minor performance enhancements available in Fb 3.0.0

• CORE-4528. Hash/merge joins for non-field (DBKEY or derived expression) equalities are now allowed.
• CORE-1482. The optimizer now considers the ORDER BY optimization when making its decision about join order.
• CORE-4556. Data pages are now allocated as a group of sequential ordered pages (extents).
• CORE-4445. The main database file extends faster when physical backup state changes from _stalled_ to _merge_.
• CORE-4443. Linux systems that support "fast file growth" can now use it.
• CORE-4432. Attachments no longer block others when the allocation table is read for the first time.
• CORE-4431. Contention has been reduced for the allocation table lock while database is in _stalled_ physical backup state.

Minor performance enhancements added via maintenance releases

• #8181 -- Ensure the standalone CS listener on Linux uses the _SO_REUSEADDR_ socket option (v3.0.12)
• #8030 -- Better cardinality estimation when empty data pages exist. (v3.0.12)
• #7494 -- Firebird performance issue - unnecessary index reads. (v3.0.11)
• #7093 -- Improve indexed lookup speed of strings when the last keys characters are part of collated contractions. (v3.0.9)
• #6872 -- Faster execution of indexed STARTING WITH with UNICODE collation. (v3.0.9)
• #6769 (CORE-6542) -- More efficient implementation of SUBSTRING for UTF8 character set. (v3.0.8)
• CORE-6274 -- Increased parsing speed of long queries. (v3.0.6)
• CORE-6237 -- Improved performance when using SRP plugin. (v3.0.6)
• CORE-5660 -- Flushing a large number of dirty pages has been made faster. (v3.0.3)
• CORE-5648 -- Measures have been taken to avoid serialization of isc_attach_database calls issued by EXECUTE STATEMENT. (v3.0.3)
• CORE-5614 -- The _merge_ stage of a physical backup stage could run too long, especially with huge page cache. Changes have been made to reduce it. (v3.0.3)
• CORE-5602 -- Improvement in performance of ALTER DOMAIN when the domain has many dependencies. (v3.0.3)
• CORE-4913 -- Speed of backup with _nBackup_ when directed to NAS over SMB protocol has been improved. (v3.0.3)
• CORE-3295 -- The optimizer can now estimate the actual record compression ratio. (v3.0.3)
• CORE-5441 -- The physical numbers of frequently used data pages are now cached to reduce the number of fetches of pointer pages. (v3.0.2)
• CORE-5434 -- A read-only transaction will no longer force write the Header/TIP page content to disk immediately after a change. This improvement gives a significant performance gain where there are numerous light read-only transactions. At this stage, it affects only servers in SS mode. For CS and SC it is more complex to implement and should appear in Firebird 4.0. (v3.0.2)

Major

(These enhancements _DO_ require a small amount of planning and intervention.)

• Maximum Page Size Increased To 32KB

For the cost of a backup and restore this can reduce index depth on tables with millions of rows. If you have indexes with a depth of 4 you probably need this.

• Pooling of external connections

  This allows existing connections to be re-used thus reducing connect times.

• Enhanced Restore Performance via gbak

  (Actually this requires no effort on your part - it is a result of changes to the underlying api).

• Idle Session Timeouts

  Configurable by the config file to automatically kill idle sessions.

• Statement Timeouts

  Configurable by the config file to automatically kill sql statements that exceed a certain time threshold. This is especially handy if you have users who can freely create their own sql queries.

Minor

All of these minor enhancements just work.

Minor performance enhancements available in Fb 4.0.0

• More efficient implementation of SUBSTRING for UTF-8 character set
• Improved sorting performance for cases when long VARCHARs are involved.
• Efficient table scans for DBKEY-based range conditions
• Increased parsing speed of long queries
• Improve performance when using SRP plugin for authentication
• Optimizer Improvement for GROUP BY on descending indexes
• Misc config params that can improve performance:
  • TipCacheBlockSize
  • SnapshotsMemSize
  • TempCacheLimit at database level
  • InlineSortThreshold

Minor enhancements added via maintenance releases

• #8598 — Don’t fire referential integrity triggers if primary or unique keys haven’t changed. (v4.0.6)
• #8161 — Cardinality estimation should use primary record versions only. (v4.0.6)
• #8104 -- More efficient evaluation of expressions like +RDB$DB_KEY <= ?+ after mass delete. (v4.0.5)
• #8030 -- Better cardinality estimation when empty data pages exist (v4.0.5)
• #7854 -- Performance issue with time zones. (v4.0.5)
• #7494 -- Avoid unnecessary index reads. (v4.0.3)
• #7093 -- Improve indexed lookup speed of strings when the last keys characters are part of collated contractions. (v4.0.2)
• #7092 -- Improve performance of CURRENT_TIME. (v4.0.2)
• #7038 -- Improve performance of STARTING WITH with insensitive collations. (v4.0.2)
• #6872 -- Faster indexed STARTING WITH execution with UNICODE collations. (v4.0.1)

Major

Parallel (multi-threaded) operation for backup/restore, sweep and index creation

Arguably the reason for jumping straight to Firebird 5. Almost no work required beyond deciding the number of parallel threads to use. And absolutely essential for anyone managing 100Gb+ size databases.

Support for Parallel Operations

You can now parallelize Sweep and Create Index just by setting the new ParallelWorkers and MaxParallelWorkers options in firebird.conf or databases.conf.

Partial indices

An index may now declare a condition that defines the subset of records to be indexed. This is useful when a column largely contains a default value that is duplicated.

Compiled statement cache

A per-attachment cache of compiled SQL statements has been implemented.

OuterJoinConversion

Defines whether OUTER joins can be converted into INNER joins by the optimizer, provided that such a transformation is possible from the query result perspective.

Enabled by default.

SubQueryConversion

Defines whether IN/ANY/EXISTS sub-queries can be unnested and merged with the outer query using the semi-join algorithm, provided that such a transformation is possible. (this is still experimental and disabled by default.)

OptimizeForFirstRows

This feature allows the optimizer to consider another (hopefully better) plan if only a subset of rows is fetched initially by the user application (with the remaining rows being fetched on demand), thus improving the response time. See the documentation for more info.

Minor performance enhancements available in Fb 5.0.0

• Denser record-level compression
• Do not update database-level statistics on every page cache operation
• Better processing and optimization of IN <list> predicates
• Include Performance Cores only in default affinity mask
• Reduce/avoid unnecessary index reads for partial lookups of compound indexes.

Minor enhancements added via maintenance releases

• #8598 — Don’t fire referential integrity triggers if primary or unique keys haven’t changed (v5.0.3)
• #8447 — Avoid index scan for lower/upper bounds containing NULL keys
• #8318 — Wire protocol improvement: send small blobs inline (v5.0.3)
• #8278 — Avoid index lookup for a NULL key if the condition is known to always be FALSE in this case (v5.0.3)
• #6413 — Data pages of newly gbak restored databases should be marked as "swept" (v5.0.3)
• #8307 — Wire protocol improvement: prefetch blob info and some data when opening a blob (v5.0.2)
• #8161 — Cardinality estimation should use primary record versions only. (v5.0.2)
• #8181 -- Ensure the standalone CS listener on Linux uses the _SO_REUSEADDR_ socket option (v5.0.1)
• #8104 -- More efficient evaluation of expressions like +RDB$DB_KEY <= ?+ after mass delete (v5.0.1)
• #8061 -- Unnest IN/ANY/EXISTS sub-queries and optimize them using semi-join algorithm. (v5.0.1)
• #8030 -- Better cardinality estimation when empty data pages exist. (v5.0.1)
• #7928 --  Make _TempCacheLimit_ setting to be per-database (not per-attachment) for _SuperClassic_. (v5.0.1)

Firebird 3 Admin improvements

This is a bunch of stuff that eases the life of the sysadmin. Some work straight out of the box. Some require a little thought prior to use.

Increased Limits in Several Areas

• Transaction IDs, attachment IDs, statement IDs all have increased maximum values.
• Maximum number of page buffers in cache is increased for 64-bit servers.
• Maximum database size is increased.
• #5913 (CORE-5647) -- Increased number of formats/versions of views from 255 to 32K.

The change for the Transaction ID limit is particularly important. Historically, the transaction ID space was limited to 2^31 -1^ and this could overflow in a matter of weeks under conditions of heavy usage. The database would become unavailable until a full backup/restore cycle resets the transaction ID counter. Since v3 it is now 2^48^ bits, or roughly equal to 2.8 * 1014 transactions, increasing the database up-time without backup and restore by a factor 217.

Multiple Security Databases

Firebird now supports user access control via more than one security database on the server.

Minor enhancements available in Fb 3.0.0

• IPv6 Support

• Validation Whilst Database is On-line

  Extremely useful when minor corruption is expected as running validation can take a long time on very large databases.

• Run-time Statistics in gbak Verbose Output

Minor enhancements added in maintenance releases

• #7468 -- Add switch to control in guardian timeout before killing firebird server process (v3.0.11)
• CORE-4462 -- Implemented option to restore compressed .nbk files without explicitly decompressing them. (v3.0.5)
• CORE-5913 -- Context variables WIRE_COMPRESSED and WIRE_ENCRYPTED were added to the SYSTEM namespace to report compression and encryption status, respectively, of the current connection. See Context Variables: v.3.0.4 for details. (v.3.0.4)

Firebird 4 Admin improvements

More features that ease the life of the sysadmin. Again, some work straight out of the box and others require a little thought prior to use.

Database Replication

OK, this does require some work - you need to provision the hardware for the replicant at least. But basic replication from the primary to the replica works more or less out of the box. Replication is perhaps the most compelling reason to upgrade to v4.

UUID-based Backup and In-Place Merge

The nBackup utility in Firebird 4 can perform a physical backup that uses the GUID (UUID) of the most recent backup of a read-only standby database to establish the backup target file. Increments from the source database can be applied continuously to the standby database, eliminating the need to keep and apply all increments since the last full backup.

The new style of "warm" backup and merge to a standby database can be run without affecting an existing multilevel backup scheme on the live database.

Minor enhancements available in Fb 4.0.0

• Engine and database configuration (read-only) exposed through virtual table RDB$CONFIG

  This is a neat feature, giving access to the current db config setting via SQL. Before this feature it was necessary to superimpose the values from the connection params over the database.conf params over the settings in firebird.conf if you wanted to see the active config of a connection.

• Miscellaneous enhancements to the monitoring tables.

• Various improvments to the TRACE api:

  • performance statistics for DDL statements
  • ability to see STATEMENT RESTART events (or their count).
  • report the new category of session management statements, e.g. ALTER SESSION RESET.
  • Extend the trace records for COMMIT/ROLLBACK RETAINING to show old/new transaction IDs
  • Trace other users' attachments

Minor enhancements added in maintenance releases

• #7928 —  Make TempCacheLimit setting to be per-database (not per-attachment) for SuperClassic (v4.0.5)
• #7468 — Add switch to control in guardian timeout before killing firebird server process (v4.0.3)

Firebird 5 Admin improvements

And yet more features that ease the life of the sysadmin. Again, some work straight out of the box and others require a little thought prior to use.

PSQL and SQL profiler

OK, this does require some work. But profiling complex stored procedures and sql blocks has never been easier. And performance problems can often be traced back to poorly optimised stored procedures.

Minor enhancements available in Fb 5.0.0

• Various improvments to the TRACE api:
  • COMPILE events for procedures/functions/triggers

Minor enhancements added in maintenance releases

• #8310 — Collect network statistics and make it available for user applications. This is a neat new feature and you can test it in isql if you have Firebird 5 installed. Just set set wire_stats_on and execute an sql statement.
• #8165 - Added shutdown handler for Classic Server (v5.0.1)