Re: "lazy"stored procedures-- WARNING: LONG MESSAGE

In article <5mmpum$2jq@cssun.mathcs.emory.edu>, Oscar Goldes <ogolde@impsat1.com.ar> writes > >------ =_NextPart_000_01BC6CEB.02CFABD0 >Content-Type: text/plain; charset="us-ascii" >Content-Transfer-Encoding: 7bit > > > >-----Mensaje original----- >De: Denham.M@amstr.com [SMTP:Denham.M@amstr.com] >Enviado el: Wednesday, May 28, 1997 9:00 PM >Para: ogolde@impsat1.com.ar >CC: informix-list@rmy.emory.edu >Asunto: RE: "lazy"stored procedures > >Oscar > >High bufwaits may well be one of the sources of your problems. > >I would work on reducing these by increasing the number of buffers available to >online, >more frequent cleaning of the buffers, more LRU queues. > >You need to provide more info on the status of your system. Sounds a little sick >to me. > > >Here it goes: >picture taken with onstat BEFORE update statistics for procedure >Picture taken AFTER update statistics for procedure >output of ps -fea > >In this case, the "lazy" procedure name is sp_c_busca_doc > > > > >Thanks in advance again and my apologies for sending such big messages, but I do >not know which part may be relevant > > >------ =_NextPart_000_01BC6CEB.02CFABD0 >Content-Type: text/plain; name="foto.txt" >Content-Transfer-Encoding: 7bit > > >********************************************************* >BEFORE update statistics for procedure sp_c_busca_doc >********************************************************* > > >INFORMIX-OnLine Version 7.20.UC2 -- On-Line -- Up 09:37:03 -- 76856 Kbytes > >Userthreads >address flags sessid user tty wait tout locks nreads nwrites >2c38014 ---P--D 1 informix - 0 0 0 208 2166 >2c38448 ---P--F 0 informix - 0 0 0 0 35694 >2c3887c ---P--B 9 informix - 0 0 0 217 224 >2c38cb0 ---P--D 11 informix - 0 0 0 0 0 Here are the problem sessions:- >2c390e4 Y--P--- 173 siged - 2e695d4 0 1 182083 511 >2c39518 Y--P--- 246 siged - 2e76f44 0 1 105856 72 >2c3994c Y--P--- 166 siged - 2d51074 0 1 382 10 >2c3cbbc Y--P--- 210 siged ttyp1 2d79ca4 0 1 5849 4118 > 8 active, 128 total, 30 maximum concurrent > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >2329703 2456372 59143963 96.06 162585 191939 949011 82.87 > Way too many bufreads. >isamtot open start read write rewrite delete commit rollbk >24067053 214085 1769489 16255484 121769 144534 25834 126275 52 > >ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >0 0 0 12600.72 1212.57 116 232 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans >184350 78 40016091 0 0 37 6611 28601 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits >1661884 34018 522775 2155857 76543 > >********************** > >INFORMIX-OnLine Version 7.20.UC2 -- On-Line -- Up 09:37:03 -- 76856 Kbytes > >MT global info: >sessions threads vps lngspins >4 20 10 1 > > sched calls thread switches yield 0 yield n yield forever >total: 3357506 2782362 530495 87970 1117555 >per sec: 10 5 0 2 0 > >Virtual processor summary: >class vps usercpu syscpu total > cpu 2 12357.52 618.52 12976.04 Much too much buffers reads hence high CPU usage. > aio 4 223.08 532.40 755.48 > lio 1 18.93 57.81 76.74 > pio 1 0.07 0.40 0.47 > adm 1 0.04 0.19 0.23 > msc 1 1.08 3.25 4.33 > total 10 12600.72 1212.57 13813.29 > >Individual virtual processors: > vp pid class usercpu syscpu total > 1 761 cpu 4876.38 163.46 5039.84 > 2 762 adm 0.04 0.19 0.23 > 3 768 cpu 7481.14 455.06 7936.20 > 4 769 lio 18.93 57.81 76.74 > 6 770 aio 60.73 154.31 215.04 > 7 771 msc 1.08 3.25 4.33 > 8 772 aio 59.78 140.73 200.51 > 9 773 aio 55.05 124.71 179.76 > 10 774 aio 47.52 112.65 160.17 > 5 775 pio 0.07 0.40 0.47 > tot 12600.72 1212.57 13813.29 > >Queue statistics not enabled > >Wait statistics not enabled > >Threads: >tid tcb rstcb prty status vp-class name > >2 2c9dc88 0 2 sleeping(Forever) 4lio lio vp 0 >3 2cb1028 0 2 sleeping(Forever) 5pio pio vp 0 >4 2cb12b8 0 2 sleeping(Forever) 6aio aio vp 0 >5 2cb155c 0 2 sleeping(Forever) 7msc msc vp 0 >6 2cb1c68 0 2 sleeping(Forever) 8aio aio vp 1 >7 2cb1e74 0 2 sleeping(Forever) 9aio aio vp 2 >8 2cc50c0 0 2 sleeping(Forever) 10aio aio vp 3 >9 2cc554c 2c38014 4 sleeping(secs: 1) 3cpu main_loop() >10 2cc5c2c 0 2 running 1cpu sm_poll >11 2d1906c 0 2 running 3cpu tlitcppoll >12 2d19464 0 2 sleeping(Forever) 1cpu sm_listen >13 2d5d3ec 0 2 sleeping(secs: 2) 3cpu sm_discon >14 2d5d7f4 0 3 sleeping(Forever) 1cpu tlitcplst >15 2d6b1f0 2c38448 2 sleeping(Forever) 3cpu flush_sub(0) >16 2d6bb6c 2c3887c 2 sleeping(secs: 1) 3cpu btclean >31 2e8c220 2c38cb0 4 sleeping(secs: 1) 1cpu onmode_mon >187 2e60728 2c3994c 2 cond wait(netnorm) 3cpu sqlexec >194 2da5438 2c390e4 2 cond wait(netnorm) 3cpu sqlexec >231 2d8b2a4 2c3cbbc 2 cond wait(netnorm) 3cpu sqlexec >267 41ed028 2c39518 2 cond wait(netnorm) 3cpu sqlexec > > >Spin locks with waits: > >Num Waits Num Loops Avg Loop/Wait Name >1 29500 29500.00 mtcb vproc_sync_lock >919 2609 2.84 mtcb sleeping_lock >11 2152 195.64 mtcb mutex_list_lock >4 4 1.00 mtcb notify_lock >18821 138493 7.36 vproc vp_lock, id = 1 >19458 103810 5.34 vproc vp_lock, id = 3 >4 4 1.00 vpro