RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
> > OWNER BLOCK 732776 > > Owner id: 114332029419524, type: 1, pending: 0 > > Process id: 26620 (Alive), thread id: 2348 > > Flags: 0x08 wake > > Requests (431): forward: 732888, backward: 10111616 > > Blocks: *empty* > > 732776 waits on nothing. > > > > Most of the entries show: > >Flags: 0x08 wake > > > > Oddly, all of the entries show: > >Blocks: *empty* > > Nobody is waiting at the moment. This is why I suggested getting it > periodically, hoping that some of the runs could step on somebody's wait. Actually, I have a job running every minute. What would be the signature of something that is/was waiting... Perhaps as a REGEX that I can use ;-) -- they are not my strong suit. Sean
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
14.03.2017 20:54, 'Leyne, Sean' wrote: > > The -o -w switches generated details such as: > > OWNER BLOCK 732776 > Owner id: 114332029419524, type: 1, pending: 0 > Process id: 26620 (Alive), thread id: 2348 > Flags: 0x08 wake > Requests (431): forward: 732888, backward: 10111616 > Blocks: *empty* > 732776 waits on nothing. > > Most of the entries show: >Flags: 0x08 wake > > Oddly, all of the entries show: >Blocks: *empty* Nobody is waiting at the moment. This is why I suggested getting it periodically, hoping that some of the runs could step on somebody's wait. Dmitry ++ 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/
RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
Dmitry, > > Am I right to think that I need to create a process to run the command on a > regular basis (every 5 secs?) to find what objects locks are waiting for? > > Yes. But we're still guessing in the dark, the reason of the slowdown could be > completely unrelated to the lock manager (e.g. bad plans, undesired access > to MON$ tables inside triggers, whatever else). Here are some more recent results: LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 67108864, Used: 22695696 Flags: 0x0001 Enqs: 232862988, Converts: 2119345, Rejects: 251137, Blocks: 2802169 Deadlock scans: 20, Deadlocks: 0, Scan interval: 10 Acquires: 393659923, Acquire blocks: 185848452, Spin count: 0 Mutex wait: 47.2% Hash slots: 90001, Hash lengths (min/avg/max):0/ 0/ 7 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (498): forward: 732776, backward: 22433480 Free owners (8):forward: 22510432, backward: 12606880 Free locks (3604): forward: 734616, backward: 5104832 Free requests (4539): forward: 19996544, backward: 11915160 Lock Ordering: Enabled The -o -w switches generated details such as: OWNER BLOCK 732776 Owner id: 114332029419524, type: 1, pending: 0 Process id: 26620 (Alive), thread id: 2348 Flags: 0x08 wake Requests (431): forward: 732888, backward: 10111616 Blocks: *empty* 732776 waits on nothing. Most of the entries show: Flags: 0x08 wake Oddly, all of the entries show: Blocks: *empty* I would have expected at least some of the entries to have values associated with "Blocks". What am I missing? Sean
RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
> > Am I right to think that I need to create a process to run the command on a > regular basis (every 5 secs?) to find what objects locks are waiting for? > > Yes. But we're still guessing in the dark That's fine, that is at least a path to investigate... >, the reason of the slowdown could be completely unrelated to the lock manager >(e.g. bad plans, > undesired access to MON$ tables inside triggers, whatever else). We only have 1 process which access MON$ tables, it is a job that runs every 5 minutes to report on long running SQL statements, none of our triggers reference MON$ structures. The only thing that we use often is GET/SET RDB$Context variables... Sean
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
12.03.2017 00:12, 'Leyne, Sean' wrote: > > Am I right to think that I need to create a process to run the command on a > regular basis (every 5 secs?) to find what objects locks are waiting for? Yes. But we're still guessing in the dark, the reason of the slowdown could be completely unrelated to the lock manager (e.g. bad plans, undesired access to MON$ tables inside triggers, whatever else). Dmitry ++ 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/
RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
> > I asked for -w, not -iw. > > Or maybe -o -w, if just -w prints nothing. FYI, -w prints the same basic details -w -o prints some details -- reading Helen's book to understand what it is reporting. Am I right to think that I need to create a process to run the command on a regular basis (every 5 secs?) to find what objects locks are waiting for? Sean
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
11.03.2017 23:56, Dmitry Yemanov wrote: > > I asked for -w, not -iw. Or maybe -o -w, if just -w prints nothing. Dmitry ++ 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: High "Mutex wait" value, after increase in "Hash Slots" to 90001
11.03.2017 22:40, 'Leyne, Sean' wrote: > > Here are some initial results > > C:\FIREBIRD\bin>fb_lock_print.exe -d x -iw 1 5000 I asked for -w, not -iw. Dmitry ++ 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/
RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
> Too many deadlock scans, it means long (> 10 sec) waiting for some locks. > Regular (every few minutes) fb_lock_print -w output could probably shed > some light... Here are some initial results C:\FIREBIRD\bin>fb_lock_print.exe -d x -iw 1 5000 01:08:29wait/s reject/s timeout/s blckast/s wakeup/s dlkscan/s deadlck/s 01:08:30 0 0 0 0 0 0 0 01:08:31 0 0 0 0 0 0 0 01:08:32 0 0 0 0 0 0 0 01:08:33 0 0 0 0 0 0 0 01:08:3465 5 06666 0 0 01:08:35 10825 0 108 108 0 0 01:08:36 0 0 0 0 0 0 0 01:08:37 0 0 0 0 0 0 0 01:08:3858 2 05858 0 0 01:08:397120 07171 0 0 01:08:40 0 0 0 0 0 0 0 01:08:41 0 0 0 0 0 0 0 01:08:42 0 0 0 0 0 0 0 01:08:43 0 0 0 0 0 0 0 01:08:4425 3 02125 0 0 01:08:4524 8 02324 0 0 01:08:46 0 0 0 0 0 0 0 Sean
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
11.03.2017 21:03, 'Leyne, Sean' wrote: > >>> We have a client with 320GB database (running FB CS v2.5) >> >> Is it really so? FB does not support LockHashSlots more than 64K, it would >> truncate your 90001 down to 65521. > > Is the engine that smart to trim to an exact prime number? Nope, 65521 is just hardcoded as the max supported value, everything bigger gets silently reset to 65521. > Curious, how would the number of owners (even free owners) impact the lock > manager? It depends on what those owners (read: connections) do inside the database. > Today's numbers are much worse, again. But the activity load is > substantially lower (based on the "Enqs" and "Acquires" values). > > LOCK_HEADER BLOCK > Version: 145, Active owner: 0, Length: 67108864, Used: 5269256 > Flags: 0x0001 > Enqs: 202113295, Converts: 934229, Rejects: 96674, Blocks: 615455 > Deadlock scans:103, Deadlocks: 0, Scan interval: 10 > Acquires: 326947224, Acquire blocks: 186433223, Spin count: 0 > Mutex wait: 57.0% Too many deadlock scans, it means long (> 10 sec) waiting for some locks. Regular (every few minutes) fb_lock_print -w output could probably shed some light... Dmitry ++ 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/
RE: [firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
> > We have a client with 320GB database (running FB CS v2.5) > > Is it really so? FB does not support LockHashSlots more than 64K, it would > truncate your 90001 down to 65521. Is the engine that smart to trim to an exact prime number? > > Now, today, I checked the lock print values again, and got even worse > > numbers!!! > > The change didn't help, the reason is elsewhere. 21% vs 23% could be > explained by e.g. printing the stats at 3PM and 5PM. The stats were printed at 3am. > And we don't know what was the mutex wait ratio before they reported the > performance issues. True, not something that came to mind for us to be monitoring. > 688 peak connections. Is this expected? Could the load be growing recently? Yes, that seems a reasonable value. Growth? Can't really speak to that, the client system is accessible by a large number of users, their work activity has day of the week and seasonal variation. Curious, how would the number of owners (even free owners) impact the lock manager? Today's numbers are much worse, again. But the activity load is substantially lower (based on the "Enqs" and "Acquires" values). LOCK_HEADER BLOCK Version: 145, Active owner: 0, Length: 67108864, Used: 5269256 Flags: 0x0001 Enqs: 202113295, Converts: 934229, Rejects: 96674, Blocks: 615455 Deadlock scans:103, Deadlocks: 0, Scan interval: 10 Acquires: 326947224, Acquire blocks: 186433223, Spin count: 0 Mutex wait: 57.0% Hash slots: 90001, Hash lengths (min/avg/max):0/ 0/ 4 Remove node: 0, Insert queue: 0, Insert prior: 0 Owners (47):forward: 732776, backward: 2105384 Free owners (54): forward: 3893360, backward: 4041872 Free locks (6339): forward: 734616, backward: 2979632 Free requests (31952): forward: 2283760, backward: 1299072 Lock Ordering: Enabled > > Any suggestions on how I can improve the numbers? Would a larger/smaller page cache size have any impact? Currently = 400 Could reducing the cached pages increase lock operations performance? Sean
[firebird-support] Re: High "Mutex wait" value, after increase in "Hash Slots" to 90001
10.03.2017 23:58, 'Leyne, Sean' wrote: > > We have a client with 320GB database (running FB CS v2.5) Is it really so? FB does not support LockHashSlots more than 64K, it would truncate your 90001 down to 65521. > Yesterday, I ran fb_lock_print to check on the database and found a > “Mutex wait” value of 20.9%, which I knew that a “bad thing”. So, I > increased (by 50%) the “Hash slots” value from 60011 to 90001. These value are not directly related. Most probably, you didn't need to change that setting. > Now, today, I checked the lock print values again, and got even worse > numbers!!! The change didn't help, the reason is elsewhere. 21% vs 23% could be explained by e.g. printing the stats at 3PM and 5PM. And we don't know what was the mutex wait ratio before they reported the performance issues. > Hash slots: 90001, Hash lengths (min/avg/max):0/ > 0/ 5 Hash stats looks good. > Owners (288): forward: 732776, backward: 29775680 > Free owners (400): forward: 9817904, backward: 9548560 688 peak connections. Is this expected? Could the load be growing recently? > Any suggestions on how I can improve the numbers? Nothing based on this lock print. Dmitry ++ 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: High "Mutex wait" value, after increase in "Hash Slots" to 90001
I forgot to add, the disk subsystem that the database running on supports 200,000+ 8KB IOPS, so slow disk performance is not likely. > We have a client with 320GB database (running FB CS v2.5) reporting > performance issues, while we are investigating possible application sources, I > have been reviewing their Firebird config. > > Yesterday, I ran fb_lock_print to check on the database and found a "Mutex > wait" value of 20.9%, which I knew that a "bad thing". So, I increased (by > 50%) the "Hash slots" value from 60011 to 90001. > > Now, today, I checked the lock print values again, and got even worse > numbers!!! > > LOCK_HEADER BLOCK > Version: 145, Active owner: 0, Length: 67108864, Used: > 32723368 > Flags: 0x0001 > Enqs: 712175161, Converts: 9327661, Rejects: 1440802, Blocks: > 15963765 > Deadlock scans: 9, Deadlocks: 0, Scan interval: 10 > Acquires: 1133127873, Acquire blocks: 256464312, Spin count: > 0 > Mutex wait: 22.6% > Hash slots: 90001, Hash lengths (min/avg/max): 0/ 0/ 5 > Remove node: 0, Insert queue: 0, Insert prior: > 0 > Owners (288): forward: 732776, backward: 29775680 > Free owners (400): forward: 9817904, backward: 9548560 > Free locks (28162): forward: 20344528, backward: 18378944 > Free requests (293517): forward: 7727280, backward: 23127520 > Lock Ordering: Enabled > > > Any suggestions on how I can improve the numbers? > > Thanks in advance > > Sean >
