| Subject | Re: [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware |
|---|---|
| Author | |
| Post date | 2016-04-13T01:47:27Z |
thanks you for our input. I think what you say is correct, and we reviewed our disk setup again.
We are utilizing mechnical discs so it's kinda hard to compare SSD performance to them.
But they should provide enought IOPS for our load.
Unfortunatly we can't just switch to a single SSD, since we would loose replication and failover systems the SAN provides which is a critical demand for us. I'm afraid for now we have to stick with it, until we have some facts to proof that the SAN Setup is our limiting factor. And data is not should that for me currently.
On a sidenode, we do own a server with SSD setup, but in tests we couldn't get a noticable performance gain through increasement of IOs this way. (tests was generic and not real world load unfortunatly)
Best Regards,
Patrick
---In firebird-support@yahoogroups.com, <ak@...> wrote :
If you say that problem occurred recently, I would suggest you to check SAN disks health.
However, these values
system IOPS under load read: 100>Average
>Average system IOPS under load write: 550Restore IOPS read: 1700
>Backup>BackupRestore IOPS write: 250 are really, really low.
1700 IOPS for the database with 4k page means 6.8Mb/sec (in case of random reads).
I suggest to install a single SSD drive and check how it will work.
SSD IOPS looks like
Random Read 4KB (QD=32) : 283.050 MB/s [ 69104.0 IOPS]
Random Write 4KB (QD=32) : 213.837 MB/s [ 52206.2 IOPS]
From our optimization practice we found that if you need to optimize only the single instance of the database, the most cost effective way is to upgrade to SSD first, and only then fix other problems.
Regards,
Alexey Kovyazin
IBSurgeon HQbird www.ib-aid.com
hi,
recently we had some strange performance issues with our Firebird DB server.
On high load, our server started to slow down. Select and update SQL query times did go up by more than 500% on average,
but reaching unreasonable high execution times at worst case. (several minutes instead of < 1sec)
OIT/OAT/Next Transaction statistics was within 1000 the hole time
We were not able to messure any hardware limiting factor. Indeed, this system was running with only 8 cores at about 70% CPU usage on max. load.
We decided that this may be our problem since we experienced a similar problem at about 80% CPU load in the past.
So we upgraded the hardware. As expected, the CPU-load dropped to ~35% usage on max. load scenario.
But this did not solve the problem.
Same story for the harddisk system. The usage is not even near it's max capacity.
We also can't see any impact on the harddisk.
We'r kind of stuck with our ideas, because we have no idea what could be a potential bottleneck to the system.
Since the hardware doesn't show a limit, there have to be anything else - most likely firebird engine related that's limiting our system.
We would be very grateful if anyone can give us hints where we can search further.
Or someone has similar experiences to share with us.
Operating System: Windows Server 2003
Firebird: 2.1.5 Classic
Dedicated database server (VMWare)
CPU: 16 cores, each 2.4 GHz
RAM: 32 GB
About 14GB are used from OS and firebird processes under max load.
HDD: SAN Storage System
Average system IOPS under load read: 100
Average system IOPS under load write: 550
Backup Restore IOPS read: 1700
Backup Restore IOPS write: 250
SAN IPOS Limit (max): 3000
Firebird Config Settings, based on defaults
DefaultDbCachePages = 1024
LockMemSize = 134247728
LockHashSlots = 20011
Database
size: about 45 GB
450 to 550 concurrent connections
Daily average of 65 transactions / second (peak should be higher)
FB_LOCK_PRINT (without any params) while system was slowing down (~4 days uptime).
I have to note, Firebird was not able to print the complete output (stats was not cropped by me)
LOCK_HEADER BLOCK
Version: 16, Active owner: 0, Length: 134247728, Used: 82169316
Semmask: 0x0, Flags: 0x0001
Enqs: 4211018659, Converts: 10050437, Rejects: 9115488, Blocks: 105409192
Deadlock scans: 1049, Deadlocks: 0, Scan interval: 10
Acquires: 4723416170, Acquire blocks: 640857597, Spin count: 0
Mutex wait: 13.6%
Hash slots: 15077, Hash lengths (min/avg/max): 3/ 12/ 25
Remove node: 0, Insert queue: 36, Insert prior: 74815332
Owners (456): forward: 131316, backward: 14899392
Free owners (9): forward: 39711576, backward: 49867232
Free locks (42409): forward: 65924212, backward: 23319052
With best Regards,
Patrick Friessnegg
Synesc GmbH