Pls help to tune my informix engine .. thanks

Hi gurus: I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs HP-UX server. There are 57 dbspaces with a total of 85 chunks. The informix engine is running very slow recently. I was not sure whether it is the result of poor tuning or not. Can you please help me to check and give me some recommendations on how to tune it in order to get the best performance? Thanks a lot for your help! Here is my Ifx environment: NUMCPUVPS 5 RESIDENT 0 BUFFERS 500000 NUMAIOVPS 10 PHYSBUFF 64 LOGBUFF 32 CLEANERS 16 LRUS 16 RA_PAGES 128 RA_THRESHOLD 120 MAX_PDQPRIORITY 100 DS_MAX_QUERIES 6 DS_TOTAL_MEMORY 300000 DS_MAX_SCANS 1048576 OPTCOMPIND 1 ------------------------------------------------------------------------------------------------------------------------ AIO I/O vps: class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors msc 0 i 0.1 258826 0 0 0 258285 1.0 0 aio 0 s 139.4 310433198 245194986 65234239 0 274448365 1.1 0 aio 1 s 77.1 171605894 131416316 40188056 0 142615991 1.2 0 aio 2 s 49.5 110226682 85208702 25017447 0 82656571 1.3 0 aio 3 s 36.1 80455402 62701994 17753151 0 53487510 1.5 0 aio 4 i 28.9 64239500 50633111 13606194 0 38153282 1.7 0 aio 5 i 24.4 54432096 43375836 11056130 0 29087844 1.9 0 aio 6 i 21.6 48054335 38586469 9467746 0 22745100 2.1 0 aio 7 i 19.4 43142850 34708630 8434119 0 17944265 2.4 0 aio 8 i 17.8 39632220 31911248 7720896 0 14210005 2.8 0 aio 9 i 16.7 37147633 29952717 7194826 0 11534836 3.2 0 pio 0 i 1.7 3803554 0 3803554 0 3803503 1.0 0 lio 0 i 0.0 81620 0 81620 0 80652 1.0 0 ------------------------------------------------------------------------------------------------------------------------ 16 buffer LRU queue sets priority levels # type set total % of length LOW HIGH 0 F 31229 99.8% 31160 22957 8203 1 m 0.2% 69 37 32 2 f 31250 99.8% 31190 22987 8203 3 m 0.2% 60 36 24 4 f 31230 99.8% 31163 22960 8203 5 m 0.2% 67 36 31 6 f 31271 99.8% 31195 22992 8203 7 m 0.2% 76 43 33 8 f 31247 99.8% 31189 22986 8203 9 m 0.2% 58 36 22 10 f 31238 99.8% 31171 22968 8203 11 m 0.2% 67 39 28 12 f 31271 99.8% 31194 22991 8203 13 m 0.2% 77 33 44 14 f 31254 99.8% 31181 22978 8203 15 m 0.2% 73 40 33 16 f 31245 99.8% 31170 22967 8203 17 m 0.2% 75 39 36 18 f 31245 99.7% 31165 22962 8203 19 m 0.3% 80 51 29 20 f 31243 99.8% 31171 22968 8203 21 m 0.2% 72 44 28 22 f 31265 99.8% 31199 22996 8203 23 m 0.2% 66 40 26 24 f 31241 99.8% 31169 22966 8203 25 m 0.2% 72 34 38 26 f 31264 99.8% 31192 22989 8203 27 m 0.2% 72 44 28 28 f 31237 99.7% 31156 22953 8203 29 m 0.3% 81 37 44 30 f 31269 99.8% 31205 23002 8203 31 m 0.2% 64 39 25 1129 dirty, 499999 queued, 500000 total, 524288 hash buckets, 2048 buffer size start clean at 70.000% (of set total) dirty, or 21875 buffs dirty, stop at 60.000% ------------------------------------------------------------------------------------------------------------------------ IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 25 days 18:43:11 -- 1602308 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 2632097402 4941899304 45398348353 94.20 311280009 1399748080 2450843309 92.79 isamtot open start read write rewrite delete commit rollbk 28871389253 72735451 1442312012 19817366922 589835341 138529174 205634855 294588 0 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 0 0 0 2812432.82 626212.81 7201 14402 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 66194052 2043 3691556477 0 0 13524 49590653 34601364 ixda-RA idx-RA da-RA RA-pgsused lchwaits 402916999 38263349 1719398752 2144011894 26815459 ------------------------------------------------------------------------------------------------------------------------ THanks again for your help!

what happened?? reloading data??? update statistics??? define slow. and also supply how busy your disks and cpus are. > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 66194052 2043 3691556477 0 0 13524 49590653 > 34601364 seq scans seems high??? may be > OPTCOMPIND 1 set to 0?? have you run update statistics?? > start clean at 70.000% (of set total) dirty, or 21875 buffs dirty, > stop at > 60.000% LRU_MAX and min dirty set to 60 and 70 %???? what is the reason for that??? may be 6 and 7 or 5 and 10.... do you use KAIO or just iovps??? i suggest KAIO nowadays!!! if you must use iovps i would add at least 10, maybe i would create 58 aivps... hummmm you are better off using kaio. Superboer Henry schreef: > Hi gurus: > > I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs > HP-UX server. > There are 57 dbspaces with a total of 85 chunks. > The informix engine is running very slow recently. > I was not sure whether it is the result of poor tuning or not. > Can you please help me to check and give me some recommendations on how > to tune it in order to get the best performance? > Thanks a lot for your help! > > Here is my Ifx environment: > > NUMCPUVPS 5 > RESIDENT 0 > BUFFERS 500000 > NUMAIOVPS 10 > PHYSBUFF 64 > LOGBUFF 32 > CLEANERS 16 > LRUS 16 > RA_PAGES 128 > RA_THRESHOLD 120 > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES 6 > DS_TOTAL_MEMORY 300000 > DS_MAX_SCANS 1048576 > OPTCOMPIND 1> > ------------------------------------------------------------------------------------------------------------------------ > > AIO I/O vps: > class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup > errors > msc 0 i 0.1 258826 0 0 0 258285 1.0 > 0 > aio 0 s 139.4 310433198 245194986 65234239 0 274448365 1.1 > 0 > aio 1 s 77.1 171605894 131416316 40188056 0 142615991 1.2 > 0 > aio 2 s 49.5 110226682 85208702 25017447 0 82656571 1.3 > 0 > aio 3 s 36.1 80455402 62701994 17753151 0 53487510 1.5 > 0 > aio 4 i 28.9 64239500 50633111 13606194 0 38153282 1.7 > 0 > aio 5 i 24.4 54432096 43375836 11056130 0 29087844 1.9 > 0 > aio 6 i 21.6 48054335 38586469 9467746 0 22745100 2.1 > 0 > aio 7 i 19.4 43142850 34708630 8434119 0 17944265 2.4 > 0 > aio 8 i 17.8 39632220 31911248 7720896 0 14210005 2.8 > 0 > aio 9 i 16.7 37147633 29952717 7194826 0 11534836 3.2 > 0 > pio 0 i 1.7 3803554 0 3803554 0 3803503 1.0 > 0 > lio 0 i 0.0 81620 0 81620 0 80652 1.0 > 0 > > ------------------------------------------------------------------------------------------------------------------------ > > 16 buffer LRU queue sets priority levels > # type set total % of length LOW HIGH > 0 F 31229 99.8% 31160 22957 8203 > 1 m 0.2% 69 37 32 > 2 f 31250 99.8% 31190 22987 8203 > 3 m 0.2% 60 36 24 > 4 f 31230 99.8% 31163 22960 8203 > 5 m 0.2% 67 36 31 > 6 f 31271 99.8% 31195 22992 8203 > 7 m 0.2% 76 43 33 > 8 f 31247 99.8% 31189 22986 8203 > 9 m 0.2% 58 36 22 > 10 f 31238 99.8% 31171 22968 8203 > 11 m 0.2% 67 39 28 > 12 f 31271 99.8% 31194 22991 8203 > 13 m 0.2% 77 33 44 > 14 f 31254 99.8% 31181 22978 8203 > 15 m 0.2% 73 40 33 > 16 f 31245 99.8% 31170 22967 8203 > 17 m 0.2% 75 39 36 > 18 f 31245 99.7% 31165 22962 8203 > 19 m 0.3% 80 51 29 > 20 f 31243 99.8% 31171 22968 8203 > 21 m 0.2% 72 44 28 > 22 f 31265 99.8% 31199 22996 8203 > 23 m 0.2% 66 40 26 > 24 f 31241 99.8% 31169 22966 8203 > 25 m 0.2% 72 34 38 > 26 f 31264 99.8% 31192 22989 8203 > 27 m 0.2% 72 44 28 > 28 f 31237 99.7% 31156 22953 8203 > 29 m 0.3% 81 37 44 > 30 f 31269 99.8% 31205 23002 8203 > 31 m 0.2% 64 39 25 > 1129 dirty, 499999 queued, 500000 total, 524288 hash buckets, 2048 > buffer size > start clean at 70.000% (of set total) dirty, or 21875 buffs dirty, > stop at > 60.000% > > ------------------------------------------------------------------------------------------------------------------------ > > IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 25 > days 18:43:11 -- 1602308 Kbytes> > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 2632097402 4941899304 45398348353 94.20 311280009 1399748080 > 2450843309 92.79 > > isamtot open start read write rewrite delete commit > rollbk > 28871389253 72735451 1442312012 19817366922 589835341 138529174 > 205634855 294588 0 > > 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 > 0 0 0 2812432.82 626212.81 7201 14402 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 66194052 2043 3691556477 0 0 13524 49590653 > 34601364 > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 402916999 38263349 1719398752 2144011894 26815459 > > ------------------------------------------------------------------------------------------------------------------------ > > THanks again for your help!

percentage read cached 94% could do better, can you afford to give more nemory to inormix? seq scans seems high, badly written queries, poor indexes, poor statistics for the optimiser? if you have lots of seq scans this may explain the read cache

Henry said: > Hi gurus: > > I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs > HP-UX server. > There are 57 dbspaces with a total of 85 chunks. > The informix engine is running very slow recently. What changed? Also, you need more buffers. -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

What type of system - DSS or OLTP? What does top, yamm, glance, etc indicate? High CPU on all 6? What are your checkpoints? What's your virtsize? What is the physical size of mem on the box? What type of storage? Do you have a hot disk? Raw or Cooked? onstat -u onstat -m onstat -g seg Have you identified any long running queries? Have you looked at the set explain on any queries? Do you use directives? And yes, have you tried update statistics? No stats-the optimizer has no idea how the data is distributed in your tables and decides to perform scans. Thanks "Superboer" <superboer7@t-onl ine.de> To Sent by: informix-list@iiug.org informix-list-bou cc nces@iiug.org Subject Re: Pls help to tune my informix 12/07/2006 11:55 engine .. thanks AM what happened?? reloading data??? update statistics??? define slow. and also supply how busy your disks and cpus are. > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > 66194052 2043 3691556477 0 0 13524 49590653 > 34601364 seq scans seems high??? may be > OPTCOMPIND 1 set to 0?? have you run update statistics?? > start clean at 70.000% (of set total) dirty, or 21875 buffs dirty, > stop at > 60.000% LRU_MAX and min dirty set to 60 and 70 %???? what is the reason for that??? may be 6 and 7 or 5 and 10.... do you use KAIO or just iovps??? i suggest KAIO nowadays!!! if you must use iovps i would add at least 10, maybe i would create 58 aivps... hummmm you are better off using kaio. Superboer Henry schreef: > Hi gurus: > > I am using IBM Informix Dynamic Server Version 9.40.FC7 under a 6 CPUs > HP-UX server. > There are 57 dbspaces with a total of 85 chunks. > The informix engine is running very slow recently. > I was not sure whether it is the result of poor tuning or not. > Can you please help me to check and give me some recommendations on how > to tune it in order to get the best performance? > Thanks a lot for your help! > > Here is my Ifx environment: > > NUMCPUVPS 5 > RESIDENT 0 > BUFFERS 500000 > NUMAIOVPS 10 > PHYSBUFF 64 > LOGBUFF 32 > CLEANERS 16 > LRUS 16 > RA_PAGES 128 > RA_THRESHOLD 120 > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES 6 > DS_TOTAL_MEMORY 300000 > DS_MAX_SCANS 1048576 > OPTCOMPIND 1> > ------------------------------------------------------------------------------------------------------------------------ > > AIO I/O vps: > class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup > errors > msc 0 i 0.1 258826 0 0 0 258285 1.0 > 0 > aio 0 s 139.4 310433198 245194986 65234239 0 274448365 1.1 > 0 > aio 1 s 77.1 171605894 131416316 40188056 0 142615991 1.2 > 0 > aio 2 s 49.5 110226682 85208702 25017447 0 82656571 1.3 > 0 > aio 3 s 36.1 80455402 62701994 17753151 0 53487510 1.5 > 0 > aio 4 i 28.9 64239500 50633111 13606194 0 38153282 1.7 > 0 > aio 5 i 24.4 54432096 43375836 11056130 0 29087844 1.9 > 0 > aio 6 i 21.6 48054335 38586469 9467746 0 22745100 2.1 > 0 > aio 7 i 19.4 43142850 34708630 8434119 0 17944265 2.4 > 0 > aio 8 i 17.8 39632220 31911248 7720896 0 14210005 2.8 > 0 > aio 9 i 16.7 37147633 29952717 7194826 0 11534836 3.2 > 0 > pio 0 i 1.7 3803554 0 3803554 0 3803503 1.0 > 0 > lio 0 i 0.0 81620 0 81620 0 80652 1.0 > 0 > > ------------------------------------------------------------------------------------------------------------------------ > > 16 buffer LRU queue sets priority levels > # type set total % of length LOW HIGH > 0 F 31229 99.8% 31160 22957 8203 > 1 m 0.2% 69 37 32 > 2 f 31250 99.8% 31190 22987 8203 > 3 m 0.2% 60 36 24 > 4 f 31230 99.8% 31163 22960 8203 > 5 m 0.2% 67 36 31 > 6 f 31271 99.8% 31195 22992 8203 > 7 m 0.2% 76 43 33 > 8 f 31247 99.8% 31189 22986 8203 > 9 m 0.2% 58 36 22 > 10 f 31238 99.8% 31171 22968 8203 > 11 m 0.2% 67 39 28 > 12 f 31271 99.8% 31194 22991 8203 > 13 m 0.2% 77 33 44 > 14 f 31254 99.8% 31181 22978 8203 > 15 m 0.2% 73 40 33 > 16 f 31245 99.8% 31170 22967 8203 > 17 m 0.2% 75 39 36 > 18 f 31245 99.7% 31165 22962 8203 > 19 m 0.3% 80 51 29 > 20 f 31243 99.8% 31171 22968 8203 > 21 m 0.2% 72 44 28 > 22 f 31265 99.8% 31199 22996 8203 > 23 m 0.2% 66 40 26 > 24 f 31241 99.8% 31169 22966 8203 > 25 m 0.2% 72 34 38 > 26 f 31264 99.8% 31192 22989 8203 > 27 m 0.2% 72 44 28 > 28 f 31237 99.7% 31156 22953 8203 > 29 m 0.3% 81 37 44 > 30 f 31269 99.8% 31205 23002 8203 > 31 m 0.2% 64 39 25 > 1129 dirty, 499999 queued, 500000 total, 524288 hash buckets, 2048 > buffer size > start clean at 70.000% (of set total) dirty, or 21875 buffs dirty, > stop at > 60.000% > > ------------------------------------------------------------------------------------------------------------------------ > > IBM Informix Dynamic Server Version 9.40.FC7 -- On-Line -- Up 25 > days 18:43:11 -- 1602308 Kbytes> > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 2632097402 4941899304 45398348353 94.20 311280009 1399748080 > 2450843309 92.79 > > isamtot open start read write rewrite delete commit > rollbk > 28871389253 72735451 1442312012 19817366922 589835341 138529174 > 205634855 294588 0 > > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > 0 0 0 0 0 0 0 > > o