23 More Ways To Speed Up Firebird

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

Translations: Portuguese

Why "23 more"?

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

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+).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

3. Beware: Domain Controller

4. Increase «max open files» limit on Linux

cat /proc//limits

Max open files 4096 4096 files

5. Use modern Linux

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.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.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: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: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».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:

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

8. Use irqbalance on Linux

9. For Virtual Machines – beware Memory Overcommit

10. For Virtual Machines – check VM limits

11. Clean Firebird temporary files

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

DefaultDBCachePages = X

FileSystemCacheThreshold = X+ N, N>1

13. Speed up security database

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)

14. Try SuperClassic on Firebird 3

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

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

15. Big database? Increase page size

2.5 – 4096 bytes

3.0 - 8192 bytes

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).

16. Do not use no_reserve flag

gstat –h database

gfix -use reserve database

17. Set high initial size for Firebird lock table

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

19. Avoid unnecessary LEFT JOINs

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.irqblalnce often improves Firebird performance and CPU load balancing on the servers with the high number of cores.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.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.Firebird creates many temporary files for various operations: sorting, BLOB processing, tracing. These files are stored in the following locations: on Windows, on LinuxNormally 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.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:There are rare cases when disabling file cache improve the performance – if you have such example, please contact me - [email protected] 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: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.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 SuperClassicTo revert to SuperServerPlease write to me ( [email protected] ) about the results of the experiment, I am interested to see the results.By default, Firebird databases have the following page sizes: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: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.The flagmakes 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 removingflag.How to check is it set or now – see line Attributes in the output ofHow to disable: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.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.For your reference: LockMemSize on high load systems with ~1000 users is usually below 200Mb.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:Runfb_lock_print –d database_name | aliasand check Owners value – it will show the current number of connections to the database.Often I see queries with the construction like this:

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



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. 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: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:Better use this construction insteadIf 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.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.Check your PSQL code for similar situations and remove useless ORDER BY (as well as distinct and UNION).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).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]

Subscribe to our newsletter with Firebird news: