Firebird.conf optimisation, slow response from both server and client

Question

I'm experiencing high latency (about 10s for each process) when using sql account software where my database is from a server.

Server spec: Processor: Xeon E5-2630 v3 Ram: 8GB OS: Window Server 2019 Firebird superserver 3.0 Speedtest: 100 download, 50upload ping from client to this server, 80ms I have 5 databases, 1-2 user each database

Client software: SQL Account https://www.sql.com.my/

Firebird.config

# Configuration file for Firebird 3.0 64-bit SuperServer, optimized for best performance

ServerMode = Super
DefaultDbCachePages = 131072  # Increased page buffer for better performance
FileSystemCacheThreshold = 4M  # Increased file system cache threshold
TempBlockSize = 15M  # Further increased temp block size for better performance
TempCacheLimit = 4096M  # Further increased temp cache limit for better performance
AuthServer = Srp, Win_Sspi
AuthClient = Srp, Win_Sspi
UserManager = Legacy_UserManager, Srp
WireCrypt = Enabled
RemoteServicePort = 3050
LockMemSize = 99999999
LockHashSlots = 30011

DatabaseAccess = Full
RemoteAccess = true
ExternalFileAccess = None
UdfAccess = Restrict UDF
DatabaseGrowthIncrement = 256M  # Increased database growth increment
FileSystemCacheSize = 600M  # Increased file system cache size
TcpRemoteBufferSize = 60480k  # Increased TCP remote buffer size
TcpNoNagle = 1 
CpuAffinityMask = 0xFF
ProcessPriorityLevel = -15  # Further decreased process priority level for higher priority
DummyPacketInterval = 500  # Set a non-zero dummy packet interval for better TCP keep-alive

# Dedicated Temporary Directories for Each Database
main.fdb = C:\Databases\main.fdb
{
    TempDirectories = C:\Temp\main
}

second.fdb = C:\Databases\second.fdb
{
    TempDirectories = C:\Temp\second
}

third.fdb = C:\Databases\third.fdb
{
    TempDirectories = C:\Temp\third
}

employee.fdb = $(dir_sampleDb)/employee.fdb
employee = $(dir_sampleDb)/employee.fdb
{
    TempDirectories = C:\Temp\employee
}

Database.config

# Configuration file for Firebird databases, optimized by IBSurgeon, released 23 March 2020
# It is optimized for 8-12Gb RAM. Apply firebird_30_superserver_64bit_ram8gb.conf with this configuration!

## In this example, we assume you have 1 big and loaded database main.fdb, 
## and 2 less loaded second.fdb and third.fdb 

## Rename aliases and change paths according to your databases' names

## The recommended total page buffers size is 25-30% for all databases!
## If you have more databases on the same server, increase RAM, or reduce page buffers even more,
## or consider using SuperClassic/Classic (use appropriate conf files)
## If you have only 1 database, you may increase DefaultDbCachePages up to 185K.

########## Any questions? Contact us: support@ib-aid.com

main.fdb=C:\Databases\main.fdb
{
    DefaultDbCachePages = 300K  # 1Gb of RAM for page buffers for 8K pagesize, 2Gb for 16k pagesize 
}

second.fdb=C:\Databases\second.fdb
{
    DefaultDbCachePages = 30K # 240Mb of RAM for page buffers for 8k pagesize, 480Mb for 16k pagesize
}

third.fdb=C:\Databases\third.fdb
{
    DefaultDbCachePages = 30K # 240Mb of RAM for page buffers for 8k pagesize, 480Mb for 16k pagesize
}

### from the original databases.conf

employee.fdb = $(dir_sampleDb)/employee.fdb
employee = $(dir_sampleDb)/employee.fdb

# Master security database specific setup.
# Do not remove it until you understand well what you are doing!
security.db = $(dir_secDb)/security3.fdb
{
    RemoteAccess = false
    DefaultDbCachePages = 20k  # Increased buffer size for the security database
}

Does anyone know how to further optimise the configuration and reduce the latency? I can see that server and client not utilising alot of resources and bandwidth.Thanks

Answer 1

It looks like someone has generated configuration from IBSurgeon's Configuration Calculator website for Firebird, and then this configuration was modified without understanding how parameters work.

The first recommendation is to use the original configurations generated by Firebird Configuration Calculator web-site.

Then, do the Simple Insert/Update/Delete test for Firebird, to see what it is the baseline for the general performance. If results of sIUD test will be less than average, consider to use better hardware or VM. If results are higher than average, collect trace and analyze longest SQL queries.