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