RE: Performance problems

There are a very large number of sequential scans (onstat -p)!! Have you run update statistics medium as described in the release notes? Can you really run the application on 2000 buffers? There is a HUGE number of buffer waits - the obvious answer to the question is no! You will not be able to use read-aheads with this configuration. Turn both parameters to 0 until you have the application running properly - you need to put numbers here, not use defaults. How much RAM should you have allocated to Informix? More than 67Mb! Spare memory on the server? Is 8Mb SHMVIRTSIZE sufficient for your application / users? see onstat -g seg when running and configure accordingly. HTH Murray Wood -----Original Message----- From: Willaert Doris [SMTP:doris.willaert@suzuki.be] Sent: Thursday, June 03, 1999 9:41 PM To: Informix-list (E-mail) Subject: Performance problems System : IBM RS6000 model suz580 AIX v 4.2.1 (Single processor) (73.3 Specint92 134.6 Specfp92) Informix : Version 7.3 UD6 We upgraded 2 weeks ago from Online Version 5.05 to version 7.3. With version 5.05 the performance was ok, but since the upgrade we have big performance problems. We see that the CPU is heavely overloaded. (onstat -p) When we run onstat -g rea we almost always see jobs waiting with vp-class cpu. After reading the performance manual, we adjusted some of the ONCONFIG parameters. We had some approvement, but we still have serious performance problems. Our question now is if our system too small for IDS version 7.3 or do we overlook something in the ONCONFIG parameters. Onstat -p Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 1 days 14:19:47 -- 67456 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 5284657 3186561 203702086 97.41 128083 203994 6211519 97.94 isamtot open start read write rewrite delete commit rollbk 180211944 19139044 35140907 61139383 2250509 168109 48442 97411 14 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 8 0 0 43747.70 13794.77 219 462 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 1669976 943 69761100 0 0 140 11110 1406739 ixda-RA idx-RA da-RA RA-pgsused lchwaits 2265627 48551 271394 2492301 2997 onstat -u Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 1 days 14:20:40 -- 67456 Kbytes Userthreads address flags sessid user tty wait tout locks nreads nwrites 40040014 ---P--D 1 informix - 0 0 0 4221 1355 400404c8 ---P--F 0 informix - 0 0 0 0 14585 4004097c ---P--F 0 informix - 0 0 0 0 19228 40040e30 ---P--F 0 informix - 0 0 0 0 13714 400412e4 ---P--F 0 informix - 0 0 0 0 11120 40041798 ---P--F 0 informix - 0 0 0 0 9653 40041c4c ---P--F 0 informix - 0 0 0 0 10612 40042100 ---P--F 0 informix - 0 0 0 0 10908 400425b4 ---P--F 0 informix - 0 0 0 0 13402 40042a68 ---P--- 9 informix - 0 0 0 0 1224 40042f1c ---P--B 10 informix - 0 0 0 560 16 400433d0 ---P--D 12 informix - 0 0 0 0 0 40043d38 Y--P--- 24 marcv - 401ee164 0 3 13793 320 400441ec Y--P--- 25 marcv - 40007188 0 2 187 0 400446a0 Y--P--- 26 marcv - 4023d074 0 3 85540 8095 40044b54 Y--P--- 27 marcv - 4025d0b8 0 3 3144 16 40045008 B--PR-- 2478 nadinew 12 301f0480 0 1 111 0 40045970 Y--P--- 30 marcv - 402b1074 0 2 3275 8 40045e24 Y--P--- 31 marcv - 402db074 0 3 926 105 400462d8 Y--P--- 32 marcv - 40317074 0 3 25792 1916 4004678c Y--P--- 33 marcv - 40173074 0 3 10906 632 40046c40 Y--P--- 34 marcv - 40327074 0 3 18383 754 400470f4 Y--P--- 35 marcv - 40437074 0 3 4910 169 400475a8 Y--P--- 2419 kgstall 30 4045b748 0 3 355 0 40047a5c Y--P--- 166 dorisw - 401ce1b0 0 1 1436 40 40047f10 Y--P--- 41 marcv - 40407918 0 3 1734 324 400483c4 Y-BP--- 2466 kjwiele 22 604c9028 0 847 2700 40 40048878 Y--P--- 164 dorisw - 60255150 0 1 8900 952 40048d2c --BPR-- 167 dorisw - 0 0 23 7524 232 400491e0 Y--P--- 2467 kgstall 30 6078919c 0 4 477 0 40049694 L-BPR-- 168 dorisw - 30063fe4 -1 8 2750 104 40049b48 Y--P--- 169 dorisw - 4023da30 0 1 4108 208 40049ffc Y--P--- 2027 marans 14 902667ec 0 1 203 0 4004a4b0 Y--P--- 175 nadinew - 407439c0 0 4 7069 312 4004a964 Y--P--- 353 kmnauwe - 4025db14 0 1 221 8 4004ae18 Y--P--- 1990 kbsmet 24 a0195074 0 2 10388 120 4004b2cc Y--P--- 2474 kdtroye 16 9000b8c0 0 2 140 8 4004c0e8 Y--P--- 2384 kncreem 11 8003bafc 0 6 4800 80 4004c59c Y--P--- 2047 kcverey 32 80505074 0 2 888 124 4004ca50 Y--P--- 1156 dorisw - 903ed3f4 0 1 3001 32 4004cf04 Y--P--- 2049 marans 14 90189074 0 2 358 0 4004d3b8 Y--P--- 1983 kpsilvr 27 4024bd58 0 2 5490 192 4004d86c Y--P--- 2471 kacalvo 3 90793074 0 3 581 24 4004dd20 ---PR-- 2072 vapaul - 0 0 1 134744 0 4004e1d4 Y--P--- 2105 vsbeert 15 60689c2c 0 1 1737 16 4004e688 Y--P--- 2426 nadinew 49 40231138 0 1 67 0 4004f4a4 Y--P--- 1941 kncreem 13 40619bc8 0 3 1728 0 4004f958 Y--P--- 1521 marcv - 905a3cb0 0 2 30104 8 400502c0 Y--P--- 2483 kmccom 36 90569abc 0 1 87 8 40050774 Y--P--- 1828 vapaul - 4023dfa8 0 1 280 32 40050c28 Y--P--- 2421 kmvanbo 35 a05f9074 0 1 886 16 400510dc ---PR-- 1946 kbsmet 17 0 0 0 18659 200 40051590 Y--P--- 725 ktverhe2 - 60619a0c 0 3 374 8 40051a44 Y--P--- 369 kmnauwe - 4020b29c 0 2 170 8 40051ef8 Y--P--- 2476 ktverhe2 25 90505b98 0 3 185 0 400523ac Y--P--- 2221 kjwiele 20 9026f874 0 2 7077 48 40052860 Y--P--- 2453 kmccom 37 702c81ac 0 1 86 8 400531c8 ---P--- 2485 kmccom 36 0 0 2 466 '"'' 40053b30 Y--P--- 1018 kacalvo - 807cb1a4 0 2 9

Murray Wood wrote: > > There are a very large number of sequential scans (onstat -p)!! > Have you run update statistics medium as described in the release notes? > There is a bug in 7.30 (we found it in UC7) where the number of sequential scans is misrepresented. The bug number is 89287. John Carlson Informix DBA WHSmith USA