RE: "lazy"stored procedures-- WARNING: LONG MESSAGE
------ =_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
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
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
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 vproc vp_lock, id = 4
950 2251 2.37 vproc vp_lock, id = 6
1 1 1.00 vproc vp_lock, id = 7
503 7301 14.51 vproc vp_lock, id = 8
329 513 1.56 vproc vp_lock, id = 9
165 1303 7.90 vproc vp_lock, id = 10
3 441 147.00 pool po_lock, name = procpool
Locked mutexes:
mid addr name holder lkcnt waiter waittime
Mutexes with waiters:
mid addr name holder lkcnt waiter waittime
Conditions with waiters:
cid addr name waiter waittime
1019 2d51074 netnorm 187 4011
1040 2e695d4 netnorm 194 32
1204 2d79ca4 netnorm 231 300
1342 2e76f44 netnorm 267 2
Pool Summary:
name class addr totalsize freesize #allocfrag #freefrag
resident R 1002014 6963200 12160 2 2
res-buff R 16a6014 20496384 14208 2