Library

Short Firebird database performance checklist

Windows

  1. Set energy-saving plan to high-performance (+20% on Windows Server 2016+)?

  2. Set Write-Caching Policy for drives to Enable Write-Caching

  3. In the case of RAID, check that the modern driver is installed, and RAID is configured properly.

Linux

  1. Is Max Open Files set to 49999? Check it cat /proc/$(pgrep firebird)/limits, default is 4096, increase it if necessary: set it in Firebird service file LimitNOFILE=49999

  2. Check VMA count cat /proc/sys/vm/max_map_count, default is 64K, should be set to 250K: add to /etc/sysctl.conf the line vm.max_map_count=250000

Firebird Configuration (recommendations for Firebird 3 only)

  1. Make sure you don't use default DefaultDBCacheBuffers, they are very low (256 and 8K)! Set 1024 for SuperClassic and 100K for SuperServer. Don't use Classic in Firebird 3, it is slow.

  2. Make sure that DefaultDBCacheBuffers is not overridden in database header: check it with gstat -h database -> Page Buffers, it should be 0. Set it to 0 with gfix -buffers 0.

  3. Set FileSystemCacheThreshold =100M in order to use file cache of OS

  4. For SuperServer, allocate for DefaultDBCacheBuffers less than 30% of RAM. If you have many databases, count memory for all of them, and use databases.conf to configure them separately. Check the total number of connections and databases with the command:

    fbsvcmgr.exe localhost:service_mgr user sysdba password masterkey info_svr_db_info

  5. For SuperServer and SuperClassic, set TempCacheLimit to 25% of RAM, for Classic – 64M.

  6. Set LockMemSize=30M, it is a good initial value, then monitor the growth of the lock table with command fblockprint -d databasename and increase LockMemSize if necessary

  7. Set LockHashSlots=20011, it is a good initial value, monitor Hash Lengths in lockprint

Database

  1. For databases > 50Gb, use the maximum page size: 16Kb (default 8Kb), to optimize reading IO and depth of indices. Set it on during restore with switch "page": gbak -c -page 16384

  2. Do not use flag no_reserve for non-read-only databases. Remove it with gfix -use reserve

  3. Monitor the difference between transactions' markers (OIT, OST, OAT, Next) from gstat -h.

    1. Big (OST-OIT) means that the database probably contains a lot of garbage versions and requires sweep. Schedule explicit sweep for the night time with gfix -sweep

    2. Big (Next-OAT) means the long active writeable transaction which blocks garbage collection. Check MON$ tables to see what application holds the transaction open and fix it.

5 minutes test to estimate Firebird performance

Use open-source INSERT-UPDATE_DELETE script: www.ib-aid.com/dbtest Currently, the fastest tested server has shown 18140 inserted records per second, what will be your result?

How to improve Firebird performance beyond this simple tuning?

Try IBSurgeon's Firebird optimization service www.ib-aid.com/perf: we know how to make your Firebird work faster.