Firebird.conf配置优化及服务器客户端高延迟问题求助

使用SQL Account软件时，连接服务器数据库出现高延迟问题，每个进程耗时约10秒。

服务器配置 #

• 处理器：Xeon E5-2630 v3
• 内存：8GB
• 操作系统：Windows Server 2019
• Firebird版本：3.0 SuperServer
• 网络测速：下载100Mbps，上传50Mbps
• 客户端到服务器Ping值：80ms
• 部署情况：共5个数据库，每个对应1-2个用户

当前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
}

目前服务器和客户端的资源、带宽利用率均较低，求进一步优化配置以降低延迟。

优化建议 #

1. 修正内存配置过载问题 #

当前Database.config中主库DefaultDbCachePages=300K，按8K页计算占用约2.3GB，加上其他库缓存（30K+30K+20K=80K，约625MB），总缓存已接近3GB，再加上Firebird自身进程、系统占用，8GB内存已接近饱和，易触发内存交换导致延迟。

• 调整主库DefaultDbCachePages为180K（约1.4GB），其他库保持30K，总缓存控制在2GB左右，占8GB内存的25%，符合推荐比例。
• 将Firebird.config中的DefaultDbCachePages改为65536（512MB），作为全局默认值，避免单个库未配置时占用过多内存。

2. 调整TCP参数适配网络延迟 #

客户端Ping值80ms属于中等延迟，当前TcpRemoteBufferSize=60480k过大，会导致TCP窗口调整不灵活，增加传输延迟：

• 将TcpRemoteBufferSize改为8192k（8MB），平衡传输效率和延迟。
• 保持TcpNoNagle=1（禁用Nagle算法），适合交互式业务场景减少小包延迟。

3. 优化加密设置减少CPU开销 #

当前WireCrypt=Enabled会对所有连接加密，增加CPU消耗，若客户端与服务器在可信网络内：

• 将WireCrypt改为Disabled直接关闭加密；若需部分加密，可设置为Required仅强制加密必要连接。

4. 调整进程优先级与CPU亲和性 #

• ProcessPriorityLevel=-15是Windows最高优先级，可能抢占系统关键进程资源，改为-2（高于正常）即可，避免系统不稳定。
• CpuAffinityMask=0xFF绑定所有8核（Xeon E5-2630 v3为8核16线程），改为0x0F绑定前4核，留4核给系统和其他进程，减少上下文切换频率。

5. 检查临时目录与磁盘性能 #

确认C:\Temp所在磁盘为SSD且剩余空间充足（至少10GB以上），若为机械硬盘，建议迁移到SSD，降低临时文件读写延迟。

6. 优化客户端连接参数 #

在SQL Account客户端连接配置中，增加ConnectionTimeout=5和PacketSize=8192，强制使用合适的数据包大小，减少连接等待时间。

7. 统计分析与监控 #

• 执行gstat -h main.fdb分析数据库索引碎片，若碎片率过高，执行ALTER INDEX ... REBUILD重建索引。
• 在Firebird.config中开启SlowQueryThreshold=1000，生成slow_query.log定位耗时较长的SQL语句，针对性优化。

内容的提问来源于stack exchange，提问作者Zhi Zheng