Re: [firebird-support] High write access on disk

2019-11-12 Thread Alexey Kovyazin a...@ib-aid.com [firebird-support]

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=21

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   usedfree  shared  buff/cache   available

Mem:   128765   3727912  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






[firebird-support] High write access on disk

2019-11-12 Thread kragh.tho...@yahoo.com [firebird-support]
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
   totalusedfree  shared  buff/cache   available
 Mem:   128765   3727912  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