Library

23 More Ways To Speed Up Firebird

Alexey Kovyazin, IBSurgeon, [email protected], 08-Jan-2019
Translations: Portuguese

Why "23 more"?

Some of you remember the article «45 Ways To Speed Up Firebird», which was published in May 2016. Now it is time to publish the next series of tips and tricks, mostly based on the experience of optimization and maintenance of the Firebird databases and servers with a high number of connections (1000+).

1. Set Power Options to High Performance in Windows Server 2016 and 2019

By default, Windows Server has a power plan set to "Balanced", which is not suitable for database servers. Set it to "High Performance" and gain approximately +20% of the performance for CPU-intensive operations. It can be set online, without restart or reboot. The picture below shows the CPU graph, which demonstrates the advantage of "High-performance" power plan:

More details about our tests with Windows power-plans can be found here.

2. Enable «Interact with desktop» on Classic for Windows

If you are using Firebird with Classic architecture on Windows, enable checkmark «Allow service to interact with desktop». Without this setting, the resource «desktop heap» is limited by Windows, and Firebird cannot open more than 250-300 connections (depends on the metadata of the database and related memory consumption) – there will Out Of Memory error.

3. Beware: Domain Controller

The problem that Windows with Domain Controller role disables the write cache on the disk with Active Directory database.
It affects Firebird in various ways (as well as other applications, of course), and it demonstrates significantly worse performance than on the servers without Active Directory roles.
Please note, that this problem affects such popular Windows version as Windows Small Business Server 2011, as well as other versions with DC.

4. Increase «max open files» limit on Linux

If you are using Linux as a database server, don't forget to tune the limits for Firebird. Check limits for your Firebird process (SuperServer or SuperClassic) with the following command:

cat /proc//limits

and pay attention to the line with the max number of open files.
Firebird can use up to 4 handles per connection, and if you something like this:

Max open files 4096 4096 files

it means that the total number of connections served by the Firebird process will be limited to something around 1000.
Please note - if you have 4 databases on the server, the connection for every database is counted.
Set more – I recommend 65535.
Don't forget to check again after restarting of Firebird process: was it applied or not.
For Classic architecture, it is necessary to check and increase limits for the user «firebird».

5. Use modern Linux

Yes, I understand that this advice is trivial, but I have seen many times the good performance improvement after the migration from CentOS 6 to 7, Ubuntu 12 to 16 (on the same hardware!), so now it is the must-have recommendation for database servers with more than 250-300 connections. The modern Linux is a prerequisite before further optimization steps.

Recommended versions of Linux: CentOS 7.x and Ubuntu 16, 18.

6. Reserve 40% RAM for file cache at Windows

OS Memory Manager has implications regarding the memory allocation, and, by default, Windows requires 40% of RAM for file cache.

Unfortunately, the poor tool Windows Task Manager shows memory, which is used for file cache as «free», and some administrators try to make Firebird consume all this free memory, so they set DefaultDBCachePage parameter in firebird.conf to very high values, and it usually leads to swapping.

Always use RAMMap tool to see the actual memory usage on Windows.

The empirical rule for Windows Server (dedicated for use as Firebird server) is the following: Firebird memory (Working Set) should be less than 40% of total RAM. If the total size of working sets for all processes is more than 50%, swap can be started by Windows.

Please note: "reserve" here means not only "do not set too many page buffers in Firebird" but, also important, restrict memory usage of other software. For example, if you have MS Exchange or MSSQL on the same server with Firebird, make sure to restrict their memory appetites.

If you are interested to know details about, I've recorded the webinar devoted to memory management in Firebird:

Part 1. https://www.youtube.com/watch?v=ZBmLgbYt4aM
Part 2, https://www.youtube.com/watch?v=-1_DF2FnU-A

7. Reserve 30% of RAM for file cache at Linux

Linux works with file cache in another way than Windows, and, in general, the amount of RAM used for file cache can be significantly less, than on Windows, without noticeable performance Firebird degradation. However, to guarantee high performance of the system with the high number of connections, especially on Classic and SuperClassic, the good idea will be to reserve 30% of RAM for file cache.

8. Use irqbalance on Linux

irqblalnce often improves Firebird performance and CPU load balancing on the servers with the high number of cores.

9. For Virtual Machines – beware Memory Overcommit

Virtual Machine can be configured to have more memory than physically exists on the host machine – with a feature known Memory Overcommit (the name can be different on the different virtualization systems). It means that in the case of a peak of memory consumption (on VM with the database server or on the neighbor VM) swap can start, which will lead to significant delays. For high-performance VM intended for a database server, all memory should be static.

10. For Virtual Machines – check VM limits

Often VMs are created with default CPU and IO limits, which can be very low, like 50 IOPS and 10% CPU. Check your server VM settings and remove any limits - high-performance database server should have all possible CPU, bandwidth, and IO.

11. Clean Firebird temporary files

Firebird creates many temporary files for various operations: sorting, BLOB processing, tracing. These files are stored in the following locations: on Windows C:\ProgramData\firebird, on Linux /tmp/firebird
Normally these files should be cleaned automatically, however, sometimes it does not happen (for example, in the case of server reboot).
Check these folders periodically and clean old files – there could be many GBs of outdated files fb_NNN, and cleaning them will free space on the system drive.

12. Don't forget to enable file cache with big Firebird cache

As you know, Firebird cache (also called «page buffers») is specified by the parameter DefaultDBCachePages in firebird.conf/databases.conf, or directly in the header of the database.
In Firebird 3 SuperServer the size of this cache can be set very high, but it is important to remember about another parameter: FileSystemCacheThreshold.
If FileSystemCacheThreshold is less than DefaultDBCachePages or page buffers, the operating system's file cache will be not used, which can lead to performance problems.
In 99% of cases, it is better to have file cache enabled.
To ensure this, always set parameters according to the following rule:

DefaultDBCachePages = X
FileSystemCacheThreshold = X+ N, N>1

There are rare cases when disabling file cache improve the performance – if you have such example, please contact me - [email protected]!

13. Speed up security database

Every connection to the Firebird database establishes the connection to the security database (security3.fdb in case of Firebird 3), and performs several reads and writes (transaction pages, header page). If you have frequent connections, the performance of your security database can become a problem.
As a minimum, you can do the following:

Increase page buffers for securityN.fdb (empirical optimum is 256 buffers)
Move security3.fdb to the fast drive (it is a standard feature in Firebird 3, in 2.5 it will require reinstallation)

Then, you can set Forced Writes OFF for the security database – the small chance of corruption is not a problem in this case.
The most radical way is to make security database read-only – it will eliminate all writes to it.
If you do not often change users in the security database, it is the best solution.

14. Try SuperClassic on Firebird 3

In Firebird 3 the SuperServer architecture was heavily advertised as the ultimate performance solution, but there are some load types which demonstrates better performance with SuperClassic (but not Classic – it always works slower than SuperServer/SuperClassic).

How to carry out this experiment in a safe way? Follow the steps below:
To try SuperClassic

Set in firebird.conf
1. ServerMode=SuperClassic
2. DefaultDbCachePages=1024
gfix –buff 0
Restart Firebird

To revert to SuperServer

Set in firebird.conf
1. ServerMode=SuperServer
2. DefaultDbCachePages=N # N*page size*databases_count < 25% RAM
3. FileSystemCacheThreshold = N+1
gfix –buff 0
Restart Firebird

Please write to me ([email protected]) about the results of the experiment, I am interested to see the results.

15. Big database? Increase page size

By default, Firebird databases have the following page sizes:

2.5 – 4096 bytes
3.0 - 8192 bytes

However, the maximum page size is 16K (in 4.0 – 32K).
For databases more than 100Gb in 95% cases, it is better to have the highest available page size, in order to:

Decrease depth of indices. It is recommended to have indices with depth less or equal 3. Indices with depth 4 and 5 will be much slower
Increase utilization of RAM. Firebird cache is specified in pages, 1000 pages with 8K page size will be 8Mb of actual memory, and with 16K – 16Mb.
Decrease number of system pages. It will speed up the access to the records of the large tables (less jumps pointer-pointer-data page), and helps with the preparation of the large SQL queries. To increase the page size of the database, the database should be back up with gbak tool and then restore with parameter –page (gbak –c –page 16384).

Please note: if you have a database with many small blobs, increasing the page size can either decrease fragmentation or increase fragmentation, and it is difficult to predict will it increase or decrease performance.

16. Do not use no_reserve flag

The flag no_reserve makes Firebird do not reserve free space (30%) on data pages for the possible record versions, which occur after UPDATE or DELETE. This flag allows the data to be stored in a more compact way (and size of the database is less too), but in case of UPDATE/DELETE all changes go to the new data page. As a result, in the database with no_reserve flag UPDATE/DELETE operations are slower.
So, if your database is not read-only, I recommend removing no_reserve flag.
How to check is it set or now – see line Attributes in the output of

gstat –h database

How to disable:

gfix -use reserve database

After this command, the new data pages will be created with reserved space.
However, to achieve the full effect, it is necessary to back up a database with gbak and then restore, in this case, all data pages will be with reserved space.
Please note: the database size will grow after removing no_reserve flag and backup/restore.

17. Set high initial size for Firebird lock table

Lock table is the mechanism of Firebird, which is used to synchronize access to the internal engine objects.
Firebird lock table can grow automatically, but its increase is a slow operation, which can lead to micro-freezes. Lock table can only grow, starting from the initial size (it is set в firebird.conf).
To prevent multiple rounds of increasing lock table, the good idea will be to watch for the size of lock table at the end of the working period (day, week, etc), and then set it as the initial size in firebird.conf.
LockMemSize=99999999
For your reference: LockMemSize on high load systems with ~1000 users is usually below 200Mb.

18. Use fb_lock_print to count the number of connections to the database

Getting the number of database connections is a frequent task for the database developers: it can be necessary for licensing purposes, for example.
Often developers use query SELECT count(*) FROM MON$ATTACHMENTS to get this value, but it is not the optimal way: frequent queries to MON$ tables can be a burden for the database, so better use the alternative:
Run
fb_lock_print –d database_name | alias
and check Owners value – it will show the current number of connections to the database.

19. Avoid unnecessary LEFT JOINs

Often I see queries with the construction like this:

T1 LEFT JOIN T2 ON (…) WHERE T2.Field_condition

Essentially, the condition on T2 excludes NULLs from the output of LEFT JOIN T2, so it is possible to change LEFT JOIN to INNER JOIN - it will not affect the query's result.
INNER JOIN gives more freedom for Firebird optimizer, and in the modern versions of Firebird, it is optimized much better than LEFT.
Especially it makes sense in the following cases:

No condition for T1 in WHERE clause
T2 is a small table

20. Avoid unnecessary counting of records

Another common mistake in the complex database queries and stored procedures is to use select count() just to check the existence of the record.
The following query will read all records according to condition1:

(select count(*)…. where condition11) >0

Better use this construction instead

Exists(select first 1 id where condition1)

If condition1 returns more than 1 record, the proposed option will be much faster, because it does not read all records, it stops after the first fetched record.

21. Avoid unnecessary sorting in stored procedures

The ordering of the query's results inside the stored procedure should be justified by the business logic.
For example, in the example of the stored procedure below, ORDER BY clause is useless from the business-logic point of view, but it adds unnecessary sorting operation.

create or alter procedure NEW_PROCEDURE
returns (
    SUMX double precision)
as
declare variable _amount double precision;
begin
for select T1.amount from Table1 t1 where ....
    order by id
    into :_amount
    do 
    begin
     sumx=sumx+_amount
    end;
  suspend;
end

Check your PSQL code for similar situations and remove useless ORDER BY (as well as distinct and UNION).

22. Do not keep queries in the prepared state without the necessity

Often we see 500-1000 prepared statements in each connection (it can be checked with MON$ queries).
The vast majority of them runs only once, and then they just sit in the RAM, making Firebird working set bigger, and slow down MON$ queries.
The recommendation is to keep SQL queries in the prepared state only they are intended to be started many times, or if their prepare time is big (can be so for very big queries with a lot of joins and access to the huge tables).

23. Always close queries with large sorting

Until SQL query with sorting (ORDER BY, GROUP BY, UNION, distinct) is not closed, Firebird retains sorted records in the memory. The size of memory allocated for sorting is set by TempCacheLimit parameter in firebird.conf, by default it is 64Mb.
Even with increased TempCacheLimit, the long-running queries with a large number of sorted records will consume all allocated amount eventually, and as a result, sorting will go to the temporary files (i.e., disk). As a result, it can lead to significant slowness.
The recommendation is to close all such queries in a timely manner.

Questions?

Please feel free to contact me with any questions: [email protected]!