The size of the database and the number of active users in such systems are usually quite large, so in this article I will talk about the experience of optimizing Firebird and Linux settings based on specific examples of large Firebird databases at BeZdorov (Ingosstrakh), AlfaZdrav , and I will touch upon the experience of other optimization companies Firebird + Linux.
Let's take a closer look at the subject of optimization - Firebird 3.0.5 DBMS (with HQbird extensions), serves a 691GB database (currently) with 1000-1100 daily users, runs on Linux CentOS 7, the server uses an HP DL380 iron. Replication to the backup server is configured for the database (the question of replication is outside the scope of this article).
The DBMS serves the medical information system Infoklinika (produced by the Russian company Smart Delta Systems ), which is one of the most popular medical information systems in Russia.
Let's take a look at the details (the screenshots are taken from HQbird later) of the operation of this database:
Data is intensively inserted into the database - a daily gain of about 0.4 - 0.5 GB
1000-1100 connections is the usual daily load:
Despite intensive growth and active work, the database does not contain any significant amount of garbage versions of records - both thanks to applications written with a good knowledge of multi-version architecture, and due to properly configured garbage collection:
Transaction Markers in the Green Zone:
By the way, note that the data in the database is string, blobs are present, but there are few of them - only 10 GB out of 690 GB of the total size.
The nature of the database load is mixed, 75% of read operations and 25% of write:
Iron
The server that serves this system is not bad, but far from top-end:
HP ProLiant DL380p Gen8, Gen8 2x Xeon(R) CPU E5v2 @ 2.60GHz, 24 logical cores with HT 320Gb RAM, 4 network cards
The disk subsystem consists of two parts: a local 745Gb disk and a dual 2 fiber-channel connection to the SAN, with a 1.8Tb partition.
operating system
Using CentOS 7, kernel version:
Linux version 3.10.0-957.21.3.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC) ) #1 SMP Tue Jun 18 16:35:19 UTC 2019
To the logical question why the most modern kernel and CentOS 8 are not used, the answer is also logical - the migration of critical systems occurs only after the release of several minor releases and thorough testing - this is one of those cases when a known bug is better than two unknowns.
However, it should be noted that until 2017 the system worked on CentOS 6.x, and after the migration, a significant improvement in performance indicators was noted.
Linux setup
An Absolutely Essential Linux Customization for Firebird
There are 2 parameters that must be increased on all Linux servers where Firebird runs - the number of virtual memory areas (VMA) and the number of open files for the Firebird process.
1. VMA
The default VMA number is 64K, it must be increased by 4 times, for this, add the line in sysctl.conf
vm.max_map_count=262144
To check the current value, use the command
sysctl vm.max_map_count
2. Max Open Files
Firebird opens up to 20 descriptors (file handles) for each connection to the database (considering the fact that in Linux all resources look like files), so the maximum number of open files by default (usually 4096) can be very quickly exhausted.
To check the available number of files for Firebird, it is best to look at the limits of the Firebird executable file:
cat /proc/$(pgrep firebird)/limits
where to check the value for Max Open Files and increase them if necessary.
To increase the Max Open Files parameter for Firebird, we added the line to the firebird-superserver.service file in the [service] section:
LimitNOFILE=49999
Optional Linux setup
On this server we also made the following settings
1. Reduced swapiness
Since the server is dedicated exclusively for Firebird DBMSs, and we want to use RAM efficiently under DBMS cache and operating system file cache, we reduce swapiness from default 60% to 10%, for this we added sysctl.conf
vm.swappiness=10
2. Increased the minimum reserved memory size for special OS processes
vm.min_free_kbytes=1048576
i.e. 1GB of memory. This setting indirectly affects memory defragmentation.
Please note that this setting is individual - given that we have 320GB of RAM, 1GB is not so much, but in case of a small amount of memory (for example, 32GB), 1GB could be too much.
3. Reduced keepalive
Firebird relies on keepalive intervals to check connection status, and the default value of keepalive can be very large. Limiting it to 300 seconds, we get rid of hanging connections
net.ipv4.tcp_keepalive_time=300 net.ipv4.tcp_keepalive_probes=5 net.ipv4.tcp_keepalive_intvl=15
More Linux Tuning
Why are we limited to such a small number of Linux settings?
First, we take a conservative approach to server tuning with Linux (which is getting better and better with each new version), and secondly, this Firebird database is neither extremely large nor the most loaded, and Linux copes with the specified settings with your tasks.
Of course, there are a few more settings that can be used to optimize the work of Firebird on Linux - for example, a number of interesting things were presented in the presentation about the 3 TB Firebird database (RedBaza) in the Federal Bailiff Service.
Configure Firebird
The configuration files of the Firebird community with firebirdsql.org are configured very conservatively, and on a more or less powerful server you need to change the configuration files and carefully select the architecture used (in Firebird 3.0 there are 2 types of connections: Embedded and NetworkServer, and 3 types of architectures: SuperServer , SuperClassic, Classic).
Also, you must use the settings for each database - i.e. put critical settings in databases.conf, with reference to a specific database.
In our case, we chose the SuperServer architecture, the most popular in version 3.0, because it efficiently uses multi-core processors and has a cache shared for all connections (but separate for each database).
The following are the configuration files (performance related values only):
firebird.conf - configuration file for all databases on the server
DefaultDbCachePages = 50K # pages FileSystemCacheThreshold = 300K # pages TempBlockSize = 2M # bytes TempCacheLimit = 20480M # bytes LockMemSize = 30M # bytes LockHashSlots = 30011 WireCompression = true ServerMode = Super ExtConnPoolSize = 500 # HQBird ExtConnPoolLifeTime = 14200 # HQBird SortDataStorageThreshold = 8192 #HQbird reports queries
In terms of performance, the key parameters here are:
1. DefaultDbCachePages = 50K # measured in pages, on the database, at page 16K = 0.8Gb
The size of the DefaultDbCachePages page cache in firebird.conf is specially set by default to 800Mb so that the test database accidentally cluttered on the server does not try to take up a huge amount of RAM.
2. FileSystemCacheThreshold = 300K # pages
It is important to set this parameter to a value greater than DefaultDBCachePages in order to enable the use of the OS file cache.
3. TempCacheLimit = 20480M # bytes
This parameter sets the memory size for queries with sorts (and some other operations), and is individual for each system.
As a result of monitoring the sizes of sorts, we found out that 20GB is optimal for this database.
4. LockMemSize and LockHashSlots - parameters of the lock table
These parameters determine the initial size of the lock table and the number of slots for calculating the hash function.
5. WireCompression = True
Configuring compresses traffic between clients and servers, this is especially useful with intensive Execute Statement On External, which runs the client application.
The remaining parameters are described in firebird.conf itself and the related documentation of Firebird and HQbird.
We made the most important settings in databases.conf , with the exact database
database1 = /data/database1.fdb { DefaultDbCachePages = 14080K # 16K pages, 220 GB FileSystemCacheThreshold = 15361K TempSpaceCacheThreshold=2G #HQbird only, track big sorts LockHashSlots = 40099 LockMemSize = 50M }
As you can guess, the main difficulty in this case is how to correctly calculate the size of the memory allocated by our large database.
For Firebird 3.0 with SuperServer architecture, there are two approaches - conservative, which is used on servers with a small amount of RAM (up to 32GB inclusive), which consists in allocating no more than 25% RAM for the page cache, and otherwise relying on the file operating system systems, and fine-tuning, when we precisely determine the optimal size for sorts, allocate a certain memory size for the OS kernel, reserve a certain amount of memory for massive file operations (for example, backup), the remainder allocated for the page cache.
In the second case, it is not possible to immediately obtain the optimal cache size, since the nature of the load can differ greatly for different types of databases, but after several iterations you can achieve an excellent result.
Common mistakes in configuring Firebird
Typical errors include the following:
1) The size of the page cache explicitly specified on the DB header page, which overrides the values specified in firebird.conf and databases.conf.
Especially often this error occurs when changing the architecture from Classic / SuperClassic to SuperServer - if the cache size for the Classic / SuperClassic connection works fine with a clearly indicated small (500-2000 pages) size, then a much larger cache is needed for SuperServer.
To check, run the command
gstat -h databasepathname
and check the
Page Buffers
value - it should be 0, then Firebird will use the values from databases.conf or firebird.conf.
To reset the page cache setting on the header page, run the command
gfix -buff 0 databasepathname
2) When increasing the page cache, they forget to increase the FileSystemCacheThreshold, which leads to the shutdown of the file cache, which reduces performance.
3) Using the default database page size (4096 or 8192), while for large databases you need to use 16K.
Conclusion
In general, 1000+ Firebird users on iron, corresponding to the load configured by Linux and configured by Firebird, work without problems. There is a margin on this server in terms of performance, which was tested at peak loads for up to 1500-1800 users.
Among Linux distributions for the Firebird database with a similar load, the most popular is CentOS 7, the recommended version is Firebird 3.0.
If you have questions, I will be happy to answer in the comments, or by email ak@ibase.ru.