FBOpt is a command-line tool for optimising Firebird databases. The tool addresses some aspects of the internal structure of databases that the Firebird engine itself cannot rectify, and some issues that survive even a backup and restore with gbak.
Beside optimization, FBOpt can be also used to upgrade or downgrade databases between Firebird versions (ODS structures), convert databases to new default character set, or to create regular gbak database backups with simultaneous restore.
FBOpt can run on 64-bit versions of Windows and Linux, and requires at least the client part of 64-bit version of Firebird 3 or newer to be installed on machine where you want to run it.
Internal database structure
Efficiency of the internal database structure plays an important role in the performance of the Firebird engine. The layout of internal structures has a direct impact on the complexity and speed of operations needed for processing them, which translates to utilisation of CPU and memory. Data distribution has a primary impact on the number of I/O operations and on cache utilisation. While the layout of data structures is fixed in the code, data distribution is variable, depending on the actual operations performed on the database. In current Firebird implementations, the engine pays only minimal attention to the storage and distribution of both user and internal data. The engine’s primary focus is on the speed of the current operation, rather than the overall balanced performance. Over time this can lead to degradation of performance as the internal database layout becomes progressively less efficient.
Eventually the drop in performance requires intervention which, for Firebird, means a rebuild of the database from a logical backup (gbak). However, restore from backup fixes only the following problems, namely:
- Row fragmentation, so each row is stored on single page (if a single row is not bigger than database page)
- Table and index fragmentation, data and index pages are compacted to hold as much data as possible, and all pages that belong to single database table or index are stored consecutively.
It does not address the following issues:
- Index clustering: jumps to different data pages while walking through an index
- Data density on data pages: all data pages could be either highly packed or have space reserved for updates
- Physical location of user data and index structures: all user data is stored first, in creation order, then all the indices are added
- Prevention of data fragmentation
FBOpt was designed and built to solve all these problems.
Physical Order of Rows
Specifying the order of rows can significantly improve performance for queries that read data in a range of key values or in a given key order.
If there is an index with the same key, it has the highest possible “clustering factor” and there are no unnecessary jumps to different data pages while walking through the index. Any potential loss of performance from using such an index for returning data in key order, rather than internal sort order, is eliminated.
With FBOpt, you can specify one such index for each table.
Space Reservation
Specifying the space reservation level for individual tables allows the option to mark tables that do not change for higher storage density while keeping necessary space reserved for tables that are frequently updated. High data density saves disk space and increases query performance by reducing the number of I/O operations.
Physical Order of Tables and Clustering
Specifying the physical order of tables in a database allows storage of tables with static data before tables that are updated or extended, so the volatile part of the database is at the end of the file. Combining this with table clustering makes it possible to keep static data more or less separated from volatile data which, over time, helps reduce the build-up of fragmentation.
Database analysis
Since version 2.0, FBOpt can determine the best clustering and physical ordering of tables, and which tables could use dense storage without reserving space.
To do so, FBOpt analyzes the database and collects information about transactions that saved individual table rows. By comparing two such information sets taken at different time, FBOpt can determine data change patterns for each table, and assign it to appropriate data cluster.
Licensing
On purchase of an appropriate license (a 14-day trial version is also available ) from our store, a download of the software will be made available. A single license will allow you to optimize any number of databases within your organization.