Hey
Where I work we run a website with at growing number of users, over a period
of two months we have seen Firebird slow down at peek hours, where the number
of concurrent website users is about 6.000. Usually there is about 250
attachments, however when the slowdown occurs the number rises to 800-1000 in
about 5-10 seconds. This is what to be expected if query speed slows down.
During my investigation I found out that there is a lot writing to the root
partition(sda) where /tmp is located. both under normal load and more so when
slowdown occurs. Queue size for sda rises above 1 during slowdown. Read/write
operations to sdb where the database is located seems normal and is a fraction
of operations on sda.
Is this high number of write operations normal for Firebird, or do I need to
tune some Firebird or OS settings?
Is it perhaps because TempCacheLimit is too low, and Firebird uses disk for
sorting, and OS is forced to flush this data to disk because almost all memory
is used for filecaching?
System information:
CentOs 7
16 core virtual machine with 128Gb of Ram
3Par 8200 SAN (6 SSD about 75.000 IOPS)
Server is dedicated to one database.
Firebird 2.5.8 (superclassic)
TempCacheLimit = 4294967296
DefaultDbCachePages = 2048
LockMemSize = 5048576
LockHashSlots = 30011
Database size: 155Gb
[user@dbserver]$ free -m
total used free shared buff/cache available
Mem: 128765 3727 912 4147 124125 120569
Swap: 0 0 0
fb_lock_print - not under load:
LOCK_HEADER BLOCK
Version: 145, Active owner: 0, Length: 116117248, Used: 111204848
Flags: 0x0001
Enqs: 17690670118, Converts: 74244796, Rejects: 20098430, Blocks:
413686610
Deadlock scans: 0, Deadlocks: 0, Scan interval: 10
Acquires: 20215919905, Acquire blocks: 1290646628, Spin count: 0
Mutex wait: 6.4%
Hash slots: 30011, Hash lengths (min/avg/max): 0/ 0/ 9
Remove node: 0, Insert queue: 0, Insert prior: 0
Owners (96): forward: 26814936, backward: 24959608
Free owners (1183): forward: 61820848, backward: 88775232
Free locks (148866): forward: 71783848, backward: 1184592
Free requests (1442650): forward: 11030120, backward: 30750136
Lock Ordering: Enabled
Best regards
Thomas Kragh
