IDS Forum

Locks required - Resource overhead..

Posted By: CESAR MARTINS
Date: Wednesday, 30 September 2009, at 9:57 a.m.

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

Messages In This Thread

• Locks required - Resource overhead..
  CESAR MARTINS -- Wednesday, 30 September 2009, at 9:57 a.m.
  • Re: Locks required - Resource overhead..
    Art Kagel -- Wednesday, 30 September 2009, at 10:09 p.m.
    • Re: Locks required - Resource overhead..
      CESAR MARTINS -- Thursday, 1 October 2009, at 7:52 a.m.
      • Re: Locks required - Resource overhead..
        John Miller iii -- Thursday, 1 October 2009, at 10:53 a.m.
        • Re: Locks required - Resource overhead..
          CESAR MARTINS -- Thursday, 1 October 2009, at 1:28 p.m.
          • Re: Locks required - Resource overhead..
            John Miller iii -- Thursday, 1 October 2009, at 2:20 p.m.
      • Re: Locks required - Resource overhead..
        Art Kagel -- Thursday, 1 October 2009, at 11:14 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.