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