[firebird-support] Re: [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Hey Thomas, thanks for your extensive reply. Unfortunatly we'r still bound to some old 32bit UDF functionality which we can't get in 64bit. I think you know about the use of SuperClassic with 32bit Server - 2GB RAM Limit :) It's not impossible, but also not really a fast route we can go. But for sure again a reason to talk about moving the switch to 2.5. We did ran some some disk IO benchmarks (with AS SSD) today, and in times of SSD kinda depressing :D The thing is, sure this numbers look really low. But the system never uses it. The monitoring of the SAN show's that this load's are never used. The Single-4k-read is worring me, but i lean towards that our 500 proceses are more like the 64-thread test. But even then, we only messured 100 Iops reading on livesystem. Sequential Read speed: ~ 450 MB / s Sequential Write speed: ~500 MB / s 4k read: 196 Iops 4k write: 1376 Iops 4k-64 thread read: 15945 Iops 4k-64 thread write: 7361 Iops Garbage Info still needs to be collected. But first signs show that this indeed could be a potential problem. From Sintatica, every 20 Minutes a Peak in GC for ~15.000 transactions. This get's fixed by the server in the relative small amount of time (i think < 1 minute), since it's really only a single peak in the graph everytime. When the GC stop increasing and the server starts to collect it, we see an increase of concurrent running transactions (= transactions are longer open and processed slower). We don't have data from the live system yet to see if this behaviour kind of "snowballs" when there is really high load on the server. Best Regards, ---In firebird-support@yahoogroups.com,wrote : Hi Patrick, > Hi Thomas, nice to get a response from you. We already met in ~2010 in Linz > at > your office :) > (ex. SEM GmbH, later Playmonitor GmbH) I know. XING (Big Brother) is watching you. Nice to see that you are still running with Firebird. ;-) > First, sorry for posting a mixed state of informations. The config settings i > postet are the current settings. > But the Lock-Table-Header was from last saturday (day of total system crash) > - > we changed Hash Slot Value since than, but it didn't work. New Table looks > like: > > > LOCK_HEADER BLOCK > Version: 16, Active owner: 0, Length: 134247728, Used: 55790260 > Semmask: 0x0, Flags: 0x0001 > Enqs: 1806423519, Converts: 4553851, Rejects: 5134185, Blocks: 56585419 > Deadlock scans: 82, Deadlocks: 0, Scan interval: 10 > Acquires: 2058846891, Acquire blocks: 321584126, Spin count: 0 > Mutex wait: 15.6% > Hash slots: 20011, Hash lengths (min/avg/max): 0/ 7/ 18 > Remove node: 0, Insert queue: 0, Insert prior: 0 > Owners (297): forward: 385160, backward: 38086352 > Free owners (43): forward: 52978748, backward: 20505128 > Free locks (41802): forward: 180712, backward: 3620136 > Free requests (-1097572396): forward: 46948676, backward: 13681252 > Lock Ordering: Enabled > > > The Min/Avg/Max hash lengths look better now, but as you mentioned the Mutex > wait is worring us too. > We have 2 direct questions about that. > > > 1) What are the negative effects of increasing Hash-Slots (too high)? It somehow defines the initial size of a hash table which is used for lock(ed) object lookup by a key (= hash value), ideally with constant O(1) run-time complexity. If the hash table is too small, due to a too small value for hash slots, it starts to degenerate into a linked/linear list per hash slot. Worst case resulting in O(n) complexity for lookups. The above 20011 setting shows an AVG hash length which looks fine. As you might know, Classic having a dedicated process per connection model somehow needs a (global) mechanism to synchronize/protect shared data structures across these processes via IPC. This is what the lock manager and the lock table is used for. > 2) As far as we know, we can't influence Mutex wait directly (it's just > informational). But do you think that's the reason the underlying hardware is > not utilized? I don't think you are disk IO bound. Means, I'm not convinced that faster IO will help. Somehow backed by the high mutex wait. Under normal operations you see 100-500 IOPS with some room for further increase as shown in the 1700 IOPS backup use case. Don't know how random disk IO is in this two scenarios. Any chance to run some sort of disk IO benchmarks or do you already know your upper limits for your SAN IOPS wise? > > > We do consider to upgrade to 2.5, but had our eyes on FB 3 over the last > year, > waiting for it to get ready. > With 2.5.x we tested around a long time now, but never found a real reason to > upgrade - since it's a reasonable amount of work for us. When you say it > improves the lock contention, this sound pretty good. But again the question, > do you think lock contention is limiting our system? Dmitry, Vlad etc. will correct me (in case he is following the thread), but I
[firebird-support] Re: [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Hey Alexey, 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,wrote : 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. Regards, Alexey Kovyazin IBSurgeon HQbird www.ib-aid.com http://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
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Hey, not sure how you can survive with superserver :) I can't see that working with our kind of load (realtime-data-processing, reports, mostly write IOPS) It's a long time ago (Fb 1.5) since we used superserver but we didn't have the best time with it back then. But currently this is not about switching the server-version. More about undestanding why the server is not using the provided hardware :) Also it reads kinda strange if you talk about high load, but always have low cpu usage :) ---In firebird-support@yahoogroups.com,wrote : Hi, just curious – why not superserver? I do not know what your system do and if it is cpu intensive – but i always use superserver because of big cache and this is for me biggest speed up. I use superserver in environment with ~400 connections (near constant) and high load without problem. PS. i wait for FB3.0 and all my servers will gain another speed up because of multicore use. But i have always low cpu usage also in high load system – and i do not know why you can got 80% CPU load – maybe this is some issue or some “weekness” of classic? regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Sent: Monday, April 11, 2016 2:55 PM To: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Subject: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware 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. < p>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, A cquire 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
Re: [firebird-support] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
> There is 77GB free out of 148GB at the minute and it has restored 9GB so > far, final size ~33GB so on the surface looks adequate unless very large > temp files are created when the indexes are reactivated? Yes. This may happen, depending on the index size. Are you sure that temp space is actually pointing to the disk with 77G free space? * What is the value for the TEMP/TMP environment variable (in case you are on Windows)? * Any manual changes for TempDirectories in firebird.conf? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
There is 77GB free out of 148GB at the minute and it has restored 9GB so far, final size ~33GB so on the surface looks adequate unless very large temp files are created when the indexes are reactivated?
Re: [firebird-support] Re: Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
12.04.2016 23:04, tomconl...@gmail.com [firebird-support] wrote: > It's a production database but this restore is for a side-project (email > logging). I > clearly need to get to the root of the problem. You run out of temp space. Buy new HDD. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Re: Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
I'm now trying to restore it with "ignore validity constraints" off in flamerobin. It's a production database but this restore is for a side-project (email logging). I clearly need to get to the root of the problem. The offending index is added to using a generated integer within a trigger so it is very odd to get this error. The system has been up for 6-7 years now, 20-25 users.
Re: [firebird-support] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
Hi! > gbak:activating and creating deferred index K_MERCURY_EMAIL > > gbak:cannot commit index K_MERCURY_EMAIL > > gbak: ERROR:operating system directive CreateFile failed Any chance that you are running out of disk where your TEMP directory is located? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > gbak: ERROR:The system cannot find the path specified. > > Database restore canceled 21:17:19 due to IBPP exception: > > > *** IBPP::SQLException *** > > Context: ServiceImpl::Wait > > Message: isc_service_query failed > > > SQL Message : -901 > > Unsuccessful execution caused by system error that does not preclude > successful execution of subsequent statements > > > Engine Code: 335544342 > > Engine Message : > > action cancelled by trigger (3) to preserve data integrity > > Cannot deactivate index used by a PRIMARY/UNIQUE constraint > > > > >
[firebird-support] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
gbak:activating and creating deferred index K_MERCURY_EMAIL gbak:cannot commit index K_MERCURY_EMAIL gbak: ERROR:operating system directive CreateFile failed gbak: ERROR:The system cannot find the path specified. Database restore canceled 21:17:19 due to IBPP exception: *** IBPP::SQLException *** Context: ServiceImpl::Wait Message: isc_service_query failed SQL Message : -901 Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements Engine Code: 335544342 Engine Message : action cancelled by trigger (3) to preserve data integrity Cannot deactivate index used by a PRIMARY/UNIQUE constraint
Re: [firebird-support] Re: Composite index - issue or not existing feature?
Hi Ann, This is the crux of the matter “Index entries for the old and new versions of a record all contain the DBKey of the primary (newest) record version. When doing an indexed lookup, Firebird walks backward through the record versions until it finds one that the current transaction can see, then check whether the key values in that record version match the lookup values.” thanks you again now i am ready to digg into FB source code – sadly i do not like C++ regards, Karol Bieniaszewski [Non-text portions of this message have been removed]
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Hi, just curious – why not superserver? I do not know what your system do and if it is cpu intensive – but i always use superserver because of big cache and this is for me biggest speed up. I use superserver in environment with ~400 connections (near constant) and high load without problem. PS. i wait for FB3.0 and all my servers will gain another speed up because of multicore use. But i have always low cpu usage also in high load system – and i do not know why you can got 80% CPU load – maybe this is some issue or some “weekness” of classic? regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Monday, April 11, 2016 2:55 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware 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. < p>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, A cquire 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
[firebird-support] as I can install firebird 3RC2 superclasic in linux?
As I can install firebird 3 RC2 superclasic in linux? I want to try it on devuan, I do not know as installing superclasic. Best Regards | ISMAEL |
Re: [firebird-support] String function extract last word from a string
12.04.2016 17:00, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > is it possible to extract the last complete word from a string until the last > blank? For > example “I would like to eat a bacon” In this case I would like to have the > “bacon” as result. RIGHT(string, POSITION(' ' in REVERSE(string))) -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
AW: [firebird-support] String function extract last word from a string
Thanks, I had a more complicated version tested now for select arbeitsgang from tarbg into :agbefore do begin agafter = reverse(agbefore); agafter = left(agafter, position(' ',agafter)); agafter = reverse(agafter); suspend; end -Ursprüngliche Nachricht- Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Dienstag, 12. April 2016 17:08 An: firebird-support@yahoogroups.com Betreff: Re: [firebird-support] String function extract last word from a string 12.04.2016 17:00, 'Checkmail' check_m...@satron.de [firebird-support] wrote: > is it possible to extract the last complete word from a string until > the last blank? For example I would like to eat a bacon In this case I would like to have the bacon as result. RIGHT(string, POSITION(' ' in REVERSE(string))) -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
[firebird-support] String function extract last word from a string
Hello, is it possible to extract the last complete word from a string until the last blank? For example "I would like to eat a bacon" In this case I would like to have the "bacon" as result. Thank you.
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Hi Patrick, > Hi Thomas, nice to get a response from you. We already met in ~2010 in Linz at > your office :) > (ex. SEM GmbH, later Playmonitor GmbH) I know. XING (Big Brother) is watching you. Nice to see that you are still running with Firebird. ;-) > First, sorry for posting a mixed state of informations. The config settings i > postet are the current settings. > But the Lock-Table-Header was from last saturday (day of total system crash) - > we changed Hash Slot Value since than, but it didn't work. New Table looks > like: > > > LOCK_HEADER BLOCK > Version: 16, Active owner: 0, Length: 134247728, Used: 55790260 > Semmask: 0x0, Flags: 0x0001 > Enqs: 1806423519, Converts: 4553851, Rejects: 5134185, Blocks: 56585419 > Deadlock scans: 82, Deadlocks: 0, Scan interval: 10 > Acquires: 2058846891, Acquire blocks: 321584126, Spin count: 0 > Mutex wait: 15.6% > Hash slots: 20011, Hash lengths (min/avg/max):0/ 7/ 18 > Remove node: 0, Insert queue: 0, Insert prior: 0 > Owners (297): forward: 385160, backward: 38086352 > Free owners (43): forward: 52978748, backward: 20505128 > Free locks (41802): forward: 180712, backward: 3620136 > Free requests (-1097572396): forward: 46948676, backward: 13681252 > Lock Ordering: Enabled > > > The Min/Avg/Max hash lengths look better now, but as you mentioned the Mutex > wait is worring us too. > We have 2 direct questions about that. > > > 1) What are the negative effects of increasing Hash-Slots (too high)? It somehow defines the initial size of a hash table which is used for lock(ed) object lookup by a key (= hash value), ideally with constant O(1) run-time complexity. If the hash table is too small, due to a too small value for hash slots, it starts to degenerate into a linked/linear list per hash slot. Worst case resulting in O(n) complexity for lookups. The above 20011 setting shows an AVG hash length which looks fine. As you might know, Classic having a dedicated process per connection model somehow needs a (global) mechanism to synchronize/protect shared data structures across these processes via IPC. This is what the lock manager and the lock table is used for. > 2) As far as we know, we can't influence Mutex wait directly (it's just > informational). But do you think that's the reason the underlying hardware is > not utilized? I don't think you are disk IO bound. Means, I'm not convinced that faster IO will help. Somehow backed by the high mutex wait. Under normal operations you see 100-500 IOPS with some room for further increase as shown in the 1700 IOPS backup use case. Don't know how random disk IO is in this two scenarios. Any chance to run some sort of disk IO benchmarks or do you already know your upper limits for your SAN IOPS wise? > > > We do consider to upgrade to 2.5, but had our eyes on FB 3 over the last year, > waiting for it to get ready. > With 2.5.x we tested around a long time now, but never found a real reason to > upgrade - since it's a reasonable amount of work for us. When you say it > improves the lock contention, this sound pretty good. But again the question, > do you think lock contention is limiting our system? Dmitry, Vlad etc. will correct me (in case he is following the thread), but I recall that in 2.5, especially in SuperClassic being multi-threaded per worker process compared to Classic, now also allows specific(?) lock manager operations in parallel to regular request processing. In general I remember a mentioned improvement of ~25% in a TPC-C style workload with SuperClassic compared to Classic. > > > First and foremost, we would really like to find the bottleneck. We just don't > have the know-how to imagine something like "Fb 2.1 Engine is limiting us > because of ..." and without that knowledge it's hard to take actions like > upgrading to 2.5. > > > We'll try to collect information about the garbage we create :) We do run > "Sinatica Monitoring" on the server, which shows us "Awaiting Gargabe > Collection" Transactions. Is that the information you'r looking for? I'm not familiar with Sinatica. Perhaps the periodic MON$ queries (how frequent are they executed by Sinatica?) also produce some sort of overhead, cause each MON$ table query in context of a new physical transaction results in a stable view of current activity. Possibly not neglectable with > 400 connections. The most easiest way to get insights on your record garbage is, e.g.: * Run gstat -r * Run a tool from IBSurgeon (can't recall the name, Alexey?) * Run a tool from Upscene (FB TraceManager) > > Maybe to avoid confusion, we don't have normal "Spikes" .. the system just > starts to slow down and this state remains until the server-load is gone > (after > midnight, when software is not used anymore). -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
> Thomas, >> >> I doubt, Firebird is IO-bound (limited by disk IO). >> > > Sorry, I don't understand your comment, can you please clarify what you > mean? I think, disk IO isn't the limiting factor in that environment. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
> 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 >> >> > >
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
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. 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
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Hi Thomas, nice to get a response from you. We already met in ~2010 in Linz at your office :) (ex. SEM GmbH, later Playmonitor GmbH) First, sorry for posting a mixed state of informations. The config settings i postet are the current settings. But the Lock-Table-Header was from last saturday (day of total system crash) - we changed Hash Slot Value since than, but it didn't work. New Table looks like: LOCK_HEADER BLOCK Version: 16, Active owner: 0, Length: 134247728, Used: 55790260 Semmask: 0x0, Flags: 0x0001 Enqs: 1806423519, Converts: 4553851, Rejects: 5134185, Blocks: 56585419 Deadlock scans: 82, Deadlocks: 0, Scan interval: 10 Acquires: 2058846891, Acquire blocks: 321584126, Spin count: 0 Mutex wait: 15.6% Hash slots: 20011, Hash lengths (min/avg/max):0/ 7/ 18 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (297): forward: 385160, backward: 38086352 Free owners (43): forward: 52978748, backward: 20505128 Free locks (41802): forward: 180712, backward: 3620136 Free requests (-1097572396): forward: 46948676, backward: 13681252 Lock Ordering: Enabled The Min/Avg/Max hash lengths look better now, but as you mentioned the Mutex wait is worring us too. We have 2 direct questions about that. 1) What are the negative effects of increasing Hash-Slots (too high)? 2) As far as we know, we can't influence Mutex wait directly (it's just informational). But do you think that's the reason the underlying hardware is not utilized? We do consider to upgrade to 2.5, but had our eyes on FB 3 over the last year, waiting for it to get ready. With 2.5.x we tested around a long time now, but never found a real reason to upgrade - since it's a reasonable amount of work for us. When you say it improves the lock contention, this sound pretty good. But again the question, do you think lock contention is limiting our system? First and foremost, we would really like to find the bottleneck. We just don't have the know-how to imagine something like "Fb 2.1 Engine is limiting us because of ..." and without that knowledge it's hard to take actions like upgrading to 2.5. We'll try to collect information about the garbage we create :) We do run "Sinatica Monitoring" on the server, which shows us "Awaiting Gargabe Collection" Transactions. Is that the information you'r looking for? Maybe to avoid confusion, we don't have normal "Spikes" .. the system just starts to slow down and this state remains until the server-load is gone (after midnight, when software is not used anymore). Best Regards, Patrick Friessnegg Synesc GmbH
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
On 2016-04-11 13:55, thetr...@yahoo.com [firebird-support] wrote: > hi, > > recently we had some strange performance issues with our Firebird DB > server. > > 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 At minimum update to Firebird 2.1.7, several bugs including security issues were fixed in 2.1.6 and 2.1.7. Also consider to investigate upgrading to Firebird 2.5.x. Mark