Patrick, > 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
fb_lock_print is reporting a hash slots value of 15077 but you show a setting of 20011. Mutex wait looks high to me. Some ideas: * Increase the hash slots value to 30011 * Get a picture on how many garbage (record versions) you create. AFAIR it is the -r switch of gstat which gives you that information. Sudden spikes in the statement response time could be related to co-operative garbage collection in Classic/SuperClassic, where basically the statement synchronously removes garbage of out-dated record versions * Consider upgrading to 2.5. 2.1.7 is end-of-life and 2.5 improved in the area of lock contention in Classic/SuperClassic substantially. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
