Biblioteca

FirebirdSQL: internal files, temporary files, and environment variables

by Vasily Sidorov, Sep 2, 2022, IBSurgeon

Experienced Firebird DBA know that Firebird may require rather large amount of disk space to store internal and temporary files. The recommended size of disk space for temp files the production system is 30-40 Gb, but for high-load systems it can be more.

In this article we will describe the types of Firebird internal and temp files, and also consider how they are stored and managed through configuration parameters and environment variable of OS. The article continues series of articles and materials devoted to the mastering of installation and configuration of Firebird.

Important notice from Firebird core developers:
Never, never, NEVER set\change FIREBIRD_LOCK env variable - unless you VERY GOOD know what you are doing.

Explicit suggestion from IBSurgeon team:
Don't change FIREBIRD_LOCK.
For other temporary files - see this.

Internal files in Firebird

Internal files are files used by Firebird to perform its internal operations. These files should not be changed or touched by the users or third-party software. The information below is for the reference only.

The common naming pattern of internal files

Firebird internal use prefix fbNN_, where NN could be

major ODS (ON-Disk Structure) number (ODS 12 for Firebird 3.0, ODS 13 for Firebird 4.0), i.e., file starts with fb13.
NN could be the shortened version number – e.g., file can start with fb50.

Also, there could be prefix fb_, which is also used for temporary files.

Types of internal files in Firebird

There are the following main types of internal files in Firebird:

fb_lock_ lock files – files used by Firebird lock manager
fbNN_monitor_ files with data of monitoring tables
fbNN_trace_ file with data for trace sessions
fb_event_ file with stored buffers of events
fb_user_mapping files with users mappings data
files with internal data for trace sessions
flag files (zero size)
fb_repl_ replication files

Let’s consider Firebird internal files in details.

Lock files

Lock files have the prefix fb_lock_. They are used by the lock manager of Firebird to synchronize access to the objects inside Firebird database and between Firebird instance(s).

Lock files are created on the opening of a database file and deleted when closed.

By default, database file is closed when the last connection is disconnected. Starting from Firebird 3.0, it is possible to keep a database file open in Firebird after the disconnect of the last connection using the LINGER option.

The starting size of lock file is set by parameter LockMemSize in firebird.conf. The working size differ depending the architecture: SuperServer uses lock files less intensively than Classic and SuperClassic. Typical size lock-table size is about 30-50Mb, but for the servers with thousands of connections in Classic/SuperClassic the size of lock files can grow up to 2Gb. 2Gb is the implementation limit for lock files in vanilla Firebird.

Files with data from monitoring tables

Starting with version 2.1, Firebird has monitoring tables. These tables are not stored inside the database, monitoring data are collected by request and kept in the internal files with the name pattern fbNN_monitor_.

The size of files with monitoring tables data depends on the number of connections and their activity (transactions, statements, stored procedures, etc). Also, quantity and size of fb_NN_monitor_ files depends on the number of requests to monitoring tables from different connections (it is one of the reasons why you should not use MON$ tables for non-monitoring tasks).

Files with data for trace sessions

File name pattern is fbNN_trace_ keeps the link to the active trace session file. Files with name pattern fb_trace_ contain information about trace session. It is kind of buffer for tracing data. The maximum size of fb_trace file is limited by the parameter MaxUserTraceLogSize in firebird.conf. If this buffer will be overfilled, the trace will automatically suspend.

File for events buffers

Files with name pattern fb_event_ are buffers where Firebird stores undelivered events to the client applications.

Files with users mapping data

Files fb_user_mapping are used to cache users mappings in Firebird instances.

Flag files

In POSIX, Firebird uses flag files with zero size – fb_guard, fb_init, fb_port_NUMBER. These files are used on the startup of Firebird.

Replication files

In Firebird 4 and HQbird files fb_repl_ contain the internal data for the replication processes.

Location of internal files

By default, all Firebird internal files are stored in the same folder: on Windows it is C:\ProgramData\firebird, on Linux - /tmp/firebird. Please note that folder should have the sufficient permissions, so Firebird instance could create and work with files there!

It is possible to change the location of the folder with internal Firebird files by defining the environment variable FIREBIRD_LOCK, but we do not recommend it.

PLEASE NOTE: to avoid database corruption, all Firebird processes which open the same database file should work with the same FIREBIRD_LOCK. Please don’t create this variable and use the default locations.

Size of internal files

Practically, the total size of internal Firebird files is around 100-200Mb, up to 2Gb as a maximum known.

Temporary files in Firebird

Temporary files are created by Firebird engine to store temporary data which occur as a result of users' operations - queries with DISTINCT, for example. Temporary files can be very large in the high-load production systems, so it is necessary for experienced DBA to be able to recognize the types of temporary files and, if necessary, tune Firebird configuration or allocate disk space to store temp files.

Location of temp files in Firebird

Firebird creates temp files in the folder, specified by the environment variables FIREBIRD_TMP, TEMP, TMP (in this order). If there is no such variables, temp files will be created in C:\Temp on Windows or /tmp on Linux.

Practically, on Windows we can see Firebird temp files in C:\Windows\Temp (corresponds to the environment variable TEMP), on Linux in /tmp.

Types of temp files in Firebird

There are the following temporary files (all use prefix fb_):

fb_table_ table objects, including, but not limited to, temporary tables
fb_blob_ blobs and autonomous transactions
fb_undo_ undo data
fb_recbuf_ used to keep intermediate data in active queries, virtual tables, etc
fb_merge_ data for hash joins
fb_sort_ sortings (for queries with DISTINCT/ORDER BY/GROUP BY and for creating/recreating indices)
fb_tpc_ fb_tpc_ data - from v4, list of all known transactions with associated commit numbers
fb_snap_ data for - from v4, list of all active database snapshots

How to change the location of temporary files?

For Firebird versions before 4, it is possible to define the specific folder to store temporary files of types 4-8 with the Firebird configuration parameter TempDirectories.

For Firebird 4 and upper, the new conf parameter TempTableDirectory allows to specify the folder to store temporary tables of types 1-3. It can be set in firebird.conf and in databases.conf.

Environment variables for Firebird

Firebird can use the following OS environment variables (if they are defined):

FIREBIRD folder with Firebird configuration files
FIREBIRD_LOCK folder with internal files
FIREBIRD_MSG folder with message file (firebird.msg)
FIREBIRD_TMP folder for temp files

Practically, FIREBIRD_TMP is the only variable which make sense to define if you have a big reason to store temp files in the specific non-default location.

Please note that if the path in FIREBIRD_TMP will be invalid (not exists or not enough permissions), it will lead to critical errors which prevent Firebird to work.

Important notice from Firebird core developers:
Never, never, NEVER set\change FIREBIRD_LOCK env variable - unless you VERY GOOD know what you are doing.

Explicit suggestion from IBSurgeon team:
Never change FIREBIRD_LOCK. This information is for reference only.
For other temporary files - see this.

Instead of Summary: answer for the very often question

Many DBA ask – should we use RAM-drive or tmpfs to store Firebird internal and/or temporary files?

The short answer is: No, better tune the Firebird configuration. If you have a lot of memory, you can increase value for DefaultDBCachePages (for SuperServer only) and increase TempCacheLimit (in versions 3+ Firebird allows to use TempCacheLimit more than 2Gb).

If you are not sure what values should be used, please use the Firebird Configuration Calculator, which creates the optimal and safe configurations for specific hardware/VM parameters.

The longer answer: Firebird is designed to keep the major part of temporary and internal files in memory, but, since practically there are situations when temporary objects become very large, it also has the mechanism to store them on the disk. Firebird effectively uses OS mechanisms (mmap, file system cache, temporary files, etc) to cache temporary and internal files in memory when it is possible and reasonable. The tuning of Firebird should be done though the Firebird configuration first, and then OS (set location of temp files, various options for file cache tuning, etc).