Hello Thomas,
Seems like your system generates a lot of sort files - the problem can
be that TempCacheLimit in Firebird 2.5 cannot be more than 2Gb -1.
When you set it to something between 2 and 4Gb, it means 0.
So, set something like
TempCacheLimit=2100000000
Consider to tune other parameters from our optimized configurations
https://ib-aid.com/en/optimized-firebird-configuration/
and worth to look through
https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/
https://ib-aid.com/en/articles/23-more-ways-to-speed-up-firebird/
You provided lockprint from the moment when you had only 96 users, and
peak seems to be ~1300 users, so it is not very useful :)
Regards,
Alexey
On 13.11.2019 0:45, kragh.tho...@yahoo.com [firebird-support] wrote:
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
