Set energy-saving plan to high-performance (+20% on Windows Server 2016+)?
Set Write-Caching Policy for drives to Enable Write-Caching
In the case of RAID, check that the modern driver is installed, and RAID is configured properly.
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
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
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.
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.
Set FileSystemCacheThreshold =100M in order to use file cache of OS
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
For SuperServer and SuperClassic, set TempCacheLimit to 25% of RAM, for Classic – 64M.
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
Set LockHashSlots=20011, it is a good initial value, monitor Hash Lengths in lockprint
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
Do not use flag no_reserve for non-read-only databases. Remove it with gfix -use reserve
Monitor the difference between transactions' markers (OIT, OST, OAT, Next) from gstat -h.
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
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.
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?
Try IBSurgeon's Firebird optimization service www.ib-aid.com/perf: we know how to make your Firebird work faster.