> Hi Patrick, > > If you say that problem occurred recently, I would suggest you to check > SAN disks health. > > However, these values > >>Average system IOPS under load read: 100 > >Average system IOPS under load write: 550 > >Backup Restore IOPS read: 1700 > >Backup Restore 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.
I doubt, Firebird is IO-bound (limited by disk IO). -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. >> 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 >> >> > >
