Locks required - Resource overhead..

Hi, Monitoring my database (IDS 7.31 FD2 , HPUX 11.0) I get a issues what theoretically isn't a problem. But, I trying discovery how much this can affect the performance and CPU utilization. This environment run with at least 350 concurrent users, where some time of day go to 600 users... The client application mainly work with CURSOR STABILITY + TRANSACTION , even only reading process. This is a problem what unfortunately the developers can't solve this (old system, old languages, exists a code translate in the middle, them write at old unisys mainframe code , convert to COBOL and run with Informix) What I already know : if work with Cursors + C. Stability + begin work, shared locks are used on records,in read process. So, this way a lots of report programs in the system put unnecessarily locks. This is the output of my shell over "onstat -g ppf" ordered by "locks required". Watch the top lines. A lot of lkrqs and NO WRITES (ins, upd, del) , only reads... Observation: this stats are zeroed every days at midnight. I run this commands at 10h30m AM. -------------------------------- >tab.ppf.sh -lr partnum lkrqs lkwts isrd iswrt isrwt isdel bfrd bfwrt seqsc rhitratio st_index1____________(db.......) 46214009 0 1666 0 0 0 47710110 0 0 100 systables________________(db...) 20729802 0 6671383 2 8 2 31423431 105 1 100 st_index2_______(db............) 3425388 0 65855076 0 0 0 67742004 0 0 100 ds_table3______________(db.....) 3310334 0 0 2 568776 0 2275300 568780 0 100 ds_table1__________(db.........) 3175431 0 16 0 0 0 3176768 0 0 100 st_inedex4___________(db.......) 2835404 0 3879103 0 0 0 8045904 2 0 100 -------------------------------- This is my top 5 of "onstat -g spi" ordered by "Num Waits" -------------------------------- Num Waits Num Loops Avg Loop/Wait Name 77049 3611594 46.87 mutex lock, name = pt_16200002 56535 98547669 1743.13 shmcb sh_lock 52933 1465100 27.68 mutex lock, name = pt_11200002 21345 10650523 498.97 vproc vp_lock, id = 1 18833 3238677 171.97 mutex lock, name = timestmp -------------------------------- This is my top 5 of "onstat -g spi" ordered by "Num Loops" -------------------------------- Num Waits Num Loops Avg Loop/Wait Name 61967 109661290 1769.67 shmcb sh_lock 23898 12373384 517.76 vproc vp_lock, id = 1 3623 10390468 2867.92 pool po_lock, name = global 16122 9871776 612.32 vproc vp_lock, id = 5 20483 8093892 395.15 vproc vp_lock, id = 3 -------------------------------- This is my top 5 of "onstat -g spi" ordered by "Avg Loop/Wait" -------------------------------- Num Waits Num Loops Avg Loop/Wait Name 5 201380 40276.00 fast mutex, lockhash[4576] 1 40002 40002.00 mutex lock, name = netnorm 7 240012 34287.43 tcb lock, tid = 6 2 60004 30002.00 mutex lock, name = netnorm 1 22763 22763.00 mutex lock, name = netnorm -------------------------------- There is any way to discover if this locks on reads overhead my CPU utilization?? Thanks Cesar

You just proved it. Spin locks spin the CPU in a tight loop of a few instructions rather than sleep, so the proof is below, yes the lock contention is costing you CPU cycles. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Sep 30, 2009 at 9:57 AM, CESAR MARTINS < cesar_inacio_martins@yahoo.com.br> wrote: > Hi, > > Monitoring my database (IDS 7.31 FD2 , HPUX 11.0) I get a issues what > theoretically isn't a problem. > But, I trying discovery how much this can affect the performance and CPU > utilization. > This environment run with at least 350 concurrent users, where some time of > day go to 600 users... > > The client application mainly work with CURSOR STABILITY + TRANSACTION , > even > only reading process. > This is a problem what unfortunately the developers can't solve this (old > system, old languages, > exists a code translate in the middle, them write at old unisys mainframe > code > , > convert to COBOL and run with Informix) > > What I already know : if work with Cursors + C. Stability + begin work, > shared > locks are used on records,in read process. > So, this way a lots of report programs in the system put unnecessarily > locks. > > This is the output of my shell over "onstat -g ppf" ordered by "locks > required". Watch the top lines. > A lot of lkrqs and NO WRITES (ins, upd, del) , only reads... > Observation: this stats are zeroed every days at midnight. I run this > commands > at 10h30m AM. > -------------------------------- > >tab.ppf.sh -lr > > partnum lkrqs lkwts isrd iswrt isrwt isdel bfrd bfwrt seqsc rhitratio > st_index1____________(db.......) 46214009 0 1666 0 0 0 47710110 0 0 100 > systables________________(db...) 20729802 0 6671383 2 8 2 31423431 105 1 > 100 > st_index2_______(db............) 3425388 0 65855076 0 0 0 67742004 0 0 100 > ds_table3______________(db.....) 3310334 0 0 2 568776 0 2275300 568780 0 > 100 > ds_table1__________(db.........) 3175431 0 16 0 0 0 3176768 0 0 100 > st_inedex4___________(db.......) 2835404 0 3879103 0 0 0 8045904 2 0 100 > -------------------------------- > > This is my top 5 of "onstat -g spi" ordered by "Num Waits" > -------------------------------- > Num Waits Num Loops Avg Loop/Wait Name > 77049 3611594 46.87 mutex lock, name = pt_16200002 > 56535 98547669 1743.13 shmcb sh_lock > 52933 1465100 27.68 mutex lock, name = pt_11200002 > 21345 10650523 498.97 vproc vp_lock, id = 1 > 18833 3238677 171.97 mutex lock, name = timestmp > -------------------------------- > > This is my top 5 of "onstat -g spi" ordered by "Num Loops" > -------------------------------- > Num Waits Num Loops Avg Loop/Wait Name > 61967 109661290 1769.67 shmcb sh_lock > 23898 12373384 517.76 vproc vp_lock, id = 1 > 3623 10390468 2867.92 pool po_lock, name = global > 16122 9871776 612.32 vproc vp_lock, id = 5 > 20483 8093892 395.15 vproc vp_lock, id = 3 > -------------------------------- > > This is my top 5 of "onstat -g spi" ordered by "Avg Loop/Wait" > -------------------------------- > Num Waits Num Loops Avg Loop/Wait Name > 5 201380 40276.00 fast mutex, lockhash[4576] > 1 40002 40002.00 mutex lock, name = netnorm > 7 240012 34287.43 tcb lock, tid = 6 > 2 60004 30002.00 mutex lock, name = netnorm > 1 22763 22763.00 mutex lock, name = netnorm > -------------------------------- > > There is any way to discover if this locks on reads overhead my CPU > utilization?? > > Thanks > Cesar > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747365298dc180474d622eb

Hi Art, Thanks for your answer! I already thought that, just wanted to be sure with other opinions, just like your. I believed the big problem is this "shmcb sh_lock" (because all my data/st_index1 is in buffer pool) , where I have a big "Num loops". > This is my top 5 of "onstat -g spi" ordered by "Num Loops" > -------------------------------- > Num Waits Num Loops Avg Loop/Wait Name > 61967 109661290 1769.67 shmcb sh_lock What I really wish is something like a calculation saying that SPINs consume N percent of my CPU since the statistics are cleared. Do you think this is possible?

To reduce the spins on the sh_lock you can enable VP_MEMORY_CACHE_KB in your onconfig. This will setup a private cache of free memory on each cpu vp so that many allocations and free's will not go through this spin lock. To monitor the progress run onstat -g vpcache. This feature is available in version 11 (and later version 10). John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/01/2009 04:52:22 AM: > [image removed] > > Re: Locks required - Resource overhead.. [17276] > > CESAR MARTINS > > to: > > ids > > 10/01/2009 04:52 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi Art, > > Thanks for your answer! > > I already thought that, just wanted to be sure with other opinions, just like > your. > I believed the big problem is this "shmcb sh_lock" (because all my > data/st_index1 is in buffer pool) , where I have a big "Num loops". > > > This is my top 5 of "onstat -g spi" ordered by "Num Loops" > > -------------------------------- > > Num Waits Num Loops Avg Loop/Wait Name > > 61967 109661290 1769.67 shmcb sh_lock > > What I really wish is something like a calculation saying that SPINsconsume N > percent of my CPU since the statistics are cleared. > Do you think this is possible? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hard to calculate I'd say. You don't know how many CPU cycles each spin represents. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Oct 1, 2009 at 7:52 AM, CESAR MARTINS < cesar_inacio_martins@yahoo.com.br> wrote: > Hi Art, > > Thanks for your answer! > > I already thought that, just wanted to be sure with other opinions, just > like > your. > I believed the big problem is this "shmcb sh_lock" (because all my > data/st_index1 is in buffer pool) , where I have a big "Num loops". > > > This is my top 5 of "onstat -g spi" ordered by "Num Loops" > > -------------------------------- > > Num Waits Num Loops Avg Loop/Wait Name > > 61967 109661290 1769.67 shmcb sh_lock > > What I really wish is something like a calculation saying that SPINs > consume N > percent of my CPU since the statistics are cleared. > Do you think this is possible? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd644bf5bd10474e118e5

Hi John, Unfortunately today I can't use this resource , IDS 7.31 . Just trying understand what you said,you can provide more details?? I don't see a direct relation between Spin locks and CPU VP CACHE. For me this is like compare apple with orange. I believed this *maybe* reduce the spin *loop* locks, but don't avoid any locks, because they just improve the access to memory reducing the "lock time".... My way of thinking is right? César

What I said was the CPU VP CACHE will help with the sh_lock spin lock. These locks have little to due with application logic, but rather the access = to objects in shared memory. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/01/2009 10:28:02 AM: > [image removed] > > Re: Locks required - Resource overhead.. [17284] > > CESAR MARTINS > > to: > > ids > > 10/01/2009 10:28 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi John, > > Unfortunately today I can't use this resource , IDS 7.31 . > > Just trying understand what you said,you can provide more details?? > > I don't see a direct relation between Spin locks and CPU VP CACHE. > For me this > is like compare apple with orange. > > I believed this *maybe* reduce the spin *loop* locks, but don't avoid= any > locks, because they just improve the access to memory reducing the "l= ock > time".... > > My way of thinking is right? > > C=E9sar > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=