IDS 12.10 - Query Slow

Hi, We do migration from IDS 11.10 to IDS 12.10. But some query performance slow when compare with old version. It's not impact to all program but just a few program. I noticed when we used temp table on our program, SQL very slow especially during insert value to temp table. Sample as below, we create a few temp table on our program create temp table tmp_tab_pol(pol_no char(12), clt_cd char(8)) with no log; create index i1_tmp_tab_pol on tmp_tab_pol (pol_no, clt_cd) create temp table tmp_tab_ic(new_ic_no char(18)) with no log; create index i1_tmp_tab_ic on tmp_tab_ic (new_ic_no) create temp table tmp_tab_1(pol_no char(12), clt_cd char(8), new_ic_no char(18), old_ic_no char(18), frst_name char(30), last_name char(30)) with no log; create index i1_tab1 on tmp_tab_1 (new_ic_no) create temp table tmp_tab_2(pol_no char(12), clt_cd char(8), new_ic_no char(18), old_ic_no char(18), frst_name char(30), last_name char(30)) with no log; create index i1_tab2 on tmp_tab_2 (pol_no) create index i2_tab2 on tmp_tab_2 (new_ic_no) create index i3_tab2 on tmp_tab_2 (pol_no, new_ic_no) When program running, SQL very slow during get value from tmp_tab_1 " select *from tmp_tab_1 where new_ic_no = ?? ) and insert to tmp_tab_2. It will take a few hour to complete, before this on IDS 11.10 only take 30 minute, So strange right? Please advice. Config file on IDS 12.10, we follow our old config file on IDS 11.10. Thank You

Did you compare the query plans? Any difference in terms of I/O like a different temporary dbspaces layout of storage system? How are you doing the SELECT / INSERT? It seems the records are being fetch by the client and then sent again through the INSERT on second table. If that's the case can you compare the ping times between client and server on original environment and the new one? Did you visualize the new environment as opposed to physical machines in the 11.10 env? You have to dig a bit to find what's different. The engine version may change a query plan and that maybe the cause. But only you can verify. And an overview of the system architecture is needed even for trying wild guesses. Regards. On Wed, Nov 9, 2016 at 2:04 PM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > We do migration from IDS 11.10 to IDS 12.10. But some query performance > slow > when compare with old version. It's not impact to all program but just a > few > program. > > I noticed when we used temp table on our program, SQL very slow especially > during insert value to temp table. Sample as below, we create a few temp > table > on our program > > create temp table tmp_tab_pol(pol_no char(12),> > clt_cd char(8)) with no log; > > create index i1_tmp_tab_pol on tmp_tab_pol (pol_no, clt_cd)> > create temp table tmp_tab_ic(new_ic_no char(18)) with no log;> > create index i1_tmp_tab_ic on tmp_tab_ic (new_ic_no)> > create temp table tmp_tab_1(pol_no char(12),> > clt_cd char(8), > > new_ic_no char(18), > > old_ic_no char(18), > > frst_name char(30), > > last_name char(30)) with no log; > > create index i1_tab1 on tmp_tab_1 (new_ic_no)> > create temp table tmp_tab_2(pol_no char(12),> > clt_cd char(8), > > new_ic_no char(18), > > old_ic_no char(18), > > frst_name char(30), > > last_name char(30)) with no log; > > create index i1_tab2 on tmp_tab_2 (pol_no)> > create index i2_tab2 on tmp_tab_2 (new_ic_no)> > create index i3_tab2 on tmp_tab_2 (pol_no, new_ic_no)> > When program running, SQL very slow during get value from tmp_tab_1 " > select > *from tmp_tab_1 where new_ic_no = ?? ) and insert to tmp_tab_2. It will > take a > few hour to complete, before this on IDS 11.10 only take 30 minute, So > strange > right? Please advice. Config file on IDS 12.10, we follow our old config > file > on IDS 11.10. > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11446e860455d50540ded92e

Hi, Did you compare the query plans? A - u means using Set explain? How to do it for temp table? Any difference in terms of I/O like a different temporary dbspaces layout of storage system? A- We using same temporary db spaces layout. Need to change? How are you doing the SELECT / INSERT? It seems the records are being fetch by the client and then sent again through the INSERT on second table. If that's the case can you compare the ping times between client and server on original environment and the new one? A- I agree with your statement, Record fetch by client into temp table 1 & after that insert into second temp table. Any related to network? Did you visualize the new environment as opposed to physical machines in the 11.10 env? A- Our IDS 11.10 install on pyhsical machines, but new IDS install on Virtual machine (HP Virtual), Both using OS HPUX. You have to dig a bit to find what's different. The engine version may change a query plan and that maybe the cause. But only you can verify. And an overview of the system architecture is needed even for trying wild guesses. A - How to determined query plan for IDS 12.10, what method we can used it? Thank You

You can certainly use SET EXPLAIN for the queries against temp tables, that works just fine. Can you run the queries under SET EXPLAIN on both the old and new servers to get both query plans? I don't know the HPUX Virtualization platform, but in general virtualized environments exhibit poorer IO performance than bare iron physical systems. I would zero stats on both systems before running the queries and then run onstat -g iof afterwards. The details of this report are not as good onv11.10 as they are on v12.10 but the output from v12 may give us some clues. Also run onstat -g ioh (new to v12 it doesn't exist in v11.xx). Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Nov 9, 2016 at 9:28 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Did you compare the query plans? > A - u means using Set explain? How to do it for temp table? > > Any difference in terms of I/O like a different temporary dbspaces layout > of > storage system? > A- We using same temporary db spaces layout. Need to change? > > How are you doing the SELECT / INSERT? It seems the records are being > fetch by > the client and then sent again through the INSERT on second table. If > that's > the case can you compare the ping times between client and server on > original > environment and the new one? > > A- I agree with your statement, Record fetch by client into temp table 1 & > after that insert into second temp table. Any related to network? > > Did you visualize the new environment as opposed to physical machines in > the > 11.10 env? > A- Our IDS 11.10 install on pyhsical machines, but new IDS install on > Virtual > machine (HP Virtual), Both using OS HPUX. > > You have to dig a bit to find what's different. The engine version may > change > a query plan and that maybe the cause. But only you can verify. > And an overview of the system architecture is needed even for trying wild > guesses. > A - How to determined query plan for IDS 12.10, what method we can used it? > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114687504a9b130540df30c0

Hello. A good point is not to keep the old onconfig file. You can use the new onconfig.std, using onconfig_diff you will be able to see the differences and apply your old configs to the new standard. A rebuild of your statistics is highly recommended, if you still not did it. Hope it helps. Best regards. Em 9 de nov de 2016 12:13 PM, Fernando Nunes <domusonline@gmail.com> escreveu: Did you compare the query plans? Any difference in terms of I/O like a different temporary dbspaces layout of storage system? How are you doing the SELECT / INSERT? It seems the records are being fetch by the client and then sent again through the INSERT on second table. If that's the case can you compare the ping times between client and server on original environment and the new one? Did you visualize the new environment as opposed to physical machines in the 11.10 env? You have to dig a bit to find what's different. The engine version may change a query plan and that maybe the cause. But only you can verify. And an overview of the system architecture is needed even for trying wild guesses. Regards. On Wed, Nov 9, 2016 at 2:04 PM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > We do migration from IDS 11.10 to IDS 12.10. But some query performance > slow > when compare with old version. It's not impact to all program but just a > few > program. > > I noticed when we used temp table on our program, SQL very slow especially > during insert value to temp table. Sample as below, we create a few temp > table > on our program > > create temp table tmp_tab_pol(pol_no char(12),> > clt_cd char(8)) with no log; > > create index i1_tmp_tab_pol on tmp_tab_pol (pol_no, clt_cd)> > create temp table tmp_tab_ic(new_ic_no char(18)) with no log;> > create index i1_tmp_tab_ic on tmp_tab_ic (new_ic_no)> > create temp table tmp_tab_1(pol_no char(12),> > clt_cd char(8), > > new_ic_no char(18), > > old_ic_no char(18), > > frst_name char(30), > > last_name char(30)) with no log; > > create index i1_tab1 on tmp_tab_1 (new_ic_no)> > create temp table tmp_tab_2(pol_no char(12),> > clt_cd char(8), > > new_ic_no char(18), > > old_ic_no char(18), > > frst_name char(30), > > last_name char(30)) with no log; > > create index i1_tab2 on tmp_tab_2 (pol_no)> > create index i2_tab2 on tmp_tab_2 (new_ic_no)> > create index i3_tab2 on tmp_tab_2 (pol_no, new_ic_no)> > When program running, SQL very slow during get value from tmp_tab_1 " > select > *from tmp_tab_1 where new_ic_no = ?? ) and insert to tmp_tab_2. It will > take a > few hour to complete, before this on IDS 11.10 only take 30 minute, So > strange > right? Please advice. Config file on IDS 12.10, we follow our old config > file > on IDS 11.10. > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11446e860455d50540ded92e ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

How did you do your migration? If you did a restore on the new machine and then an in place upgrade, did you UPDATE STATISTICS DROP DISTRIBUTIONS and rebuild them? You can also try adding the directive SET OPTIMIZATION LOW and see if that helps. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Wednesday, November 09, 2016 08:37 AM > To: ids@iiug.org > Subject: Re: IDS 12.10 - Query Slow [38101] > > You can certainly use SET EXPLAIN for the queries against temp tables, > that works just fine. Can you run the queries under SET EXPLAIN on both > the old and new servers to get both query plans? > > I don't know the HPUX Virtualization platform, but in general > virtualized environments exhibit poorer IO performance than bare iron > physical systems. > I would zero stats on both systems before running the queries and then > run onstat -g iof afterwards. The details of this report are not as > good on > v11.10 as they are on v12.10 but the output from v12 may give us some > clues. Also run onstat -g ioh (new to v12 it doesn't exist in v11.xx). > > Art > > Art S. Kagel, President and Principal Consultant ASK Database > Management www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on the IIUG, nor any other organization > with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Nov 9, 2016 at 9:28 AM, MOHD FADZIL JUSOH > <fadzil@isianpadu.com> > wrote: > > > Hi, > > > > Did you compare the query plans? > > A - u means using Set explain? How to do it for temp table? > > > > Any difference in terms of I/O like a different temporary dbspaces > > layout of storage system? > > A- We using same temporary db spaces layout. Need to change? > > > > How are you doing the SELECT / INSERT? It seems the records are being > > fetch by the client and then sent again through the INSERT on second > > table. If that's the case can you compare the ping times between > > client and server on original environment and the new one? > > > > A- I agree with your statement, Record fetch by client into temp > table > > 1 & after that insert into second temp table. Any related to network? > > > > Did you visualize the new environment as opposed to physical machines > > in the > > 11.10 env? > > A- Our IDS 11.10 install on pyhsical machines, but new IDS install on > > Virtual machine (HP Virtual), Both using OS HPUX. > > > > You have to dig a bit to find what's different. The engine version > may > > change a query plan and that maybe the cause. But only you can > verify. > > And an overview of the system architecture is needed even for trying > > wild guesses. > > A - How to determined query plan for IDS 12.10, what method we can > used it? > > > > Thank You > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a114687504a9b130540df30c0 > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, Set ouput from onstat -g iof, Any clue can get from here. Our informix data stored on HP3par storage (raid 10). IBM Informix Dynamic Server Version 12.10.FC6AEE -- On-Line -- Up 12:10:44 --12568132 Kbytes AIO global files: gfd pathname bytes read page reads bytes write page writes io/s 3 rootdb 66381824 32413 4335616 2116 1000.4 op type count avg. time seeks 6781 0.0000 reads 6172 0.0010 writes 609 0.0006 kaio_reads 0 N/A kaio_writes 0 N/A 4 phydbs 374784 183 1275959296 623027 372.9 op type count avg. time seeks 2465 0.0000 reads 16 0.0019 writes 2449 0.0027 kaio_reads 0 N/A kaio_writes 0 N/A 5 logdbs 3657684992 1785979 3601643520 1758615 2973.4 op type count avg. time seeks 750658 0.0000 reads 55794 0.0005 writes 694866 0.0003 kaio_reads 0 N/A kaio_writes 0 N/A 6 tempdbs1 135933952 66374 390934528 190886 1952.7 op type count avg. time seeks 16698 0.0000 reads 4490 0.0006 writes 12208 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 7 tempdbs2 236722176 115587 236109824 115288 1848.3 op type count avg. time seeks 12136 0.0000 reads 4498 0.0006 writes 7638 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 8 tempdbs3 129783808 63371 130551808 63746 1839.3 op type count avg. time seeks 7991 0.0000 reads 3986 0.0006 writes 4005 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 9 tempdbs4 675790848 329976 178810880 87310 1607.3 op type count avg. time seeks 10662 0.0000 reads 5057 0.0007 writes 5605 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 10 datadb1 22864973824 11164492 4096 2 646.0 op type count avg. time seeks 525362 0.0000 reads 525360 0.0015 writes 2 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 11 datadb2 11901730816 5811392 1329152 649 1874.2 op type count avg. time seeks 184598 0.0000 reads 184551 0.0005 writes 47 0.0006 kaio_reads 0 N/A kaio_writes 0 N/A 12 datadb3 13818204160 6747033 213063680 104009 981.1 op type count avg. time seeks 2864471 0.0000 reads 2839332 0.0010 writes 25119 0.0004 kaio_reads 0 N/A kaio_writes 0 N/A 13 datadb4 21385355264 10442061 33404928 16311 1347.8 op type count avg. time seeks 723595 0.0000 reads 721981 0.0007 writes 1616 0.0006 kaio_reads 0 N/A kaio_writes 0 N/A 14 datadb5 11906574336 5813757 1355776 662 1858.9 op type count avg. time seeks 184673 0.0000 reads 184625 0.0005 writes 48 0.0009 kaio_reads 0 N/A kaio_writes 0 N/A 15 datadb6 86321807360 42149296 2887680 1410 714.2 op type count avg. time seeks 643764 0.0000 reads 643179 0.0014 writes 559 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 16 datadb7 35387031552 17278800 224139264 109432 2439.5 op type count avg. time seeks 1133251 0.0000 reads 1123923 0.0004 writes 9326 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 17 datadb8 32665298944 15949693 1609101312 785694 2187.9 op type count avg. time seeks 1079128 0.0000 reads 1029676 0.0005 writes 49455 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 18 idxdbs1 5917722624 2889513 21497856 10497 1040.9 op type count avg. time seeks 115022 0.0000 reads 111386 0.0010 writes 3636 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 19 idxdbs2 17926295552 8753073 16064512 7843 1398.3 op type count avg. time seeks 563489 0.0000 reads 558448 0.0007 writes 5038 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 20 idxdbs3 4928724992 2406603 2983936 1456 689.9 op type count avg. time seeks 261116 0.0000 reads 260011 0.0015 writes 1121 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 21 idxdbs4 11074490368 5407466 151756800 74095 911.6 op type count avg. time seeks 357505 0.0000 reads 347656 0.0011 writes 9849 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 22 idxdbs21 4671485952 2280999 141312 69 1852.5 op type count avg. time seeks 72570 0.0000 reads 72521 0.0005 writes 49 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 23 datadb12 4573970432 2233384 5255168 2566 1045.0 op type count avg. time seeks 78913 0.0000 reads 78749 0.0010 writes 164 0.0009 kaio_reads 0 N/A kaio_writes 0 N/A 24 idxdbs22 9899546624 4833762 9295872 4539 1888.2 op type count avg. time seeks 678854 0.0000 reads 676487 0.0005 writes 2366 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 25 datadb61 4554119168 2223686 1101824 538 716.8 op type count avg. time seeks 83238 0.0000 reads 82905 0.0014 writes 338 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 26 idxdbs31 1090973696 532700 3360768 1641 559.2 op type count avg. time seeks 62903 0.0000 reads 61986 0.0018 writes 919 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 27 datadb62 25801230336 12598233 101709824 49645 2159.7 op type count avg. time seeks 2571658 0.0000 reads 2550258 0.0005 writes 21398 0.0004 kaio_reads 0 N/A kaio_writes 0 N/A 28 datadb71 16124940288 7873490 15937536 7778 2394.5 op type count avg. time seeks 475715 0.0000 reads 473587 0.0004 writes 2131 0.0004 kaio_reads 0 N/A kaio_writes 0 N/A 29 datadb9 13065885696 6379825 4251648 2076 1059.3 op type count avg. time seeks 309285 0.0000 reads 308292 0.0009 writes 996 0.0006 kaio_reads 0 N/A kaio_writes 0 N/A 30 idxdbs9 15179673600 7411950 22431744 10945 851.4 op type count avg. time seeks 353725 0.0000 reads 346917 0.0012 writes 6808 0.0005 kaio_reads 0 N/A kaio_writes 0 N/A 31 datadb22 6144 3 0 0 196.5 op type count avg. time seeks 3 0.0000 reads 3 0.0051 writes 0 N/A kaio_reads 0 N/A kaio_writes 0 N/A 32 datadb52 6144 3 0 0 157.6 op type count avg. time seeks 3 0.0000 reads 3 0.0063 writes 0 N/A kaio_reads 0 N/A kaio_writes 0 N/A 33 datadb63 6144 3 0 0 284.3 op type count avg. time seeks 3 0.0000 reads 3 0.0035 writes 0 N/A kaio_reads 0 N/A kaio_writes 0 N/A 34 life.1553 Please advice. Can we improve IO? Which configurati

Hi, I means value on onconfig 12.10 same or better than value onconfig IDS 11.10. i don't copy onconfig file and put on IDS 12.10. I already rebuild of your statistics is highly. We don't have performance issue for overall our system, only a few program, especially program running using tempdbs. Please advice. Thank You

These numbers look OK. I do notice that you are not using DIRECT_IO which might improve performance. How many AIO VPs do you have? Post the output from onstat -g iov so we can see how the AIO VPs are performing. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 10, 2016 at 9:49 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Set ouput from onstat -g iof, Any clue can get from here. Our informix data > stored on HP3par storage (raid 10). > > IBM Informix Dynamic Server Version 12.10.FC6AEE -- On-Line -- Up 12:10:44 > --> 12568132 Kbytes > > AIO global files: > gfd pathname bytes read page reads bytes write page writes io/s > 3 rootdb 66381824 32413 4335616 2116 1000.4 > > op type count avg. time > > seeks 6781 0.0000 > > reads 6172 0.0010 > > writes 609 0.0006 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 4 phydbs 374784 183 1275959296 623027 372.9 > > op type count avg. time > > seeks 2465 0.0000 > > reads 16 0.0019 > > writes 2449 0.0027 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 5 logdbs 3657684992 1785979 3601643520 1758615 2973.4 > > op type count avg. time > > seeks 750658 0.0000 > > reads 55794 0.0005 > > writes 694866 0.0003 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 6 tempdbs1 135933952 66374 390934528 190886 1952.7 > > op type count avg. time > > seeks 16698 0.0000 > > reads 4490 0.0006 > > writes 12208 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 7 tempdbs2 236722176 115587 236109824 115288 1848.3 > > op type count avg. time > > seeks 12136 0.0000 > > reads 4498 0.0006 > > writes 7638 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 8 tempdbs3 129783808 63371 130551808 63746 1839.3 > > op type count avg. time > > seeks 7991 0.0000 > > reads 3986 0.0006 > > writes 4005 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 9 tempdbs4 675790848 329976 178810880 87310 1607.3 > > op type count avg. time > > seeks 10662 0.0000 > > reads 5057 0.0007 > > writes 5605 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 10 datadb1 22864973824 11164492 4096 2 646.0 > > op type count avg. time > > seeks 525362 0.0000 > > reads 525360 0.0015 > > writes 2 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 11 datadb2 11901730816 5811392 1329152 649 1874.2 > > op type count avg. time > > seeks 184598 0.0000 > > reads 184551 0.0005 > > writes 47 0.0006 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 12 datadb3 13818204160 6747033 213063680 104009 981.1 > > op type count avg. time > > seeks 2864471 0.0000 > > reads 2839332 0.0010 > > writes 25119 0.0004 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 13 datadb4 21385355264 10442061 33404928 16311 1347.8 > > op type count avg. time > > seeks 723595 0.0000 > > reads 721981 0.0007 > > writes 1616 0.0006 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 14 datadb5 11906574336 5813757 1355776 662 1858.9 > > op type count avg. time > > seeks 184673 0.0000 > > reads 184625 0.0005 > > writes 48 0.0009 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 15 datadb6 86321807360 42149296 2887680 1410 714.2 > > op type count avg. time > > seeks 643764 0.0000 > > reads 643179 0.0014 > > writes 559 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 16 datadb7 35387031552 17278800 224139264 109432 2439.5 > > op type count avg. time > > seeks 1133251 0.0000 > > reads 1123923 0.0004 > > writes 9326 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 17 datadb8 32665298944 15949693 1609101312 785694 2187.9 > > op type count avg. time > > seeks 1079128 0.0000 > > reads 1029676 0.0005 > > writes 49455 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 18 idxdbs1 5917722624 2889513 21497856 10497 1040.9 > > op type count avg. time > > seeks 115022 0.0000 > > reads 111386 0.0010 > > writes 3636 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 19 idxdbs2 17926295552 8753073 16064512 7843 1398.3 > > op type count avg. time > > seeks 563489 0.0000 > > reads 558448 0.0007 > > writes 5038 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 20 idxdbs3 4928724992 2406603 2983936 1456 689.9 > > op type count avg. time > > seeks 261116 0.0000 > > reads 260011 0.0015 > > writes 1121 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 21 idxdbs4 11074490368 5407466 151756800 74095 911.6 > > op type count avg. time > > seeks 357505 0.0000 > > reads 347656 0.0011 > > writes 9849 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 22 idxdbs21 4671485952 2280999 141312 69 1852.5 > > op type count avg. time > > seeks 72570 0.0000 > > reads 72521 0.0005 > > writes 49 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 23 datadb12 4573970432 2233384 5255168 2566 1045.0 > > op type count avg. time > > seeks 78913 0.0000 > > reads 78749 0.0010 > > writes 164 0.0009 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 24 idxdbs22 9899546624 4833762 9295872 4539 1888.2 > > op type count avg. time > > seeks 678854 0.0000 > > reads 676487 0.0005 > > writes 2366 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 25 datadb61 4554119168 2223686 1101824 538 716.8 > > op type count avg. time > > seeks 83238 0.0000 > > reads 82905 0.0014 > > writes 338 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 26 idxdbs31 1090973696 532700 3360768 1641 559.2 > > op type count avg. time > > seeks 62903 0.0000 > > reads 61986 0.0018 > > writes 919 0.0005 > > kaio_reads 0 N/A > > kaio_writes 0 N/A > > 27 datadb62 25801230336 12598233 101709824 49645 2159.7 > > op type count avg. time > > seeks 2571658 0.0000 > > reads 2550258 0.0005 > > writes 21398 0.0004 > > kaio_reads 0 N/A > > kaio_writes 0 N/A@@

Hi, Ouput from onstat -g iov IBM Informix Dynamic Server Version 12.10.FC6AEE -- On-Line -- Up 12:32:25 --12568132 Kbytes AIO I/O vps: class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors tempops fifo 8 0 i 0.0 0 0 0 0 1 0.0 0 0 adt 7 0 i 0.5 21373 0 21369 0 21374 1.0 0 21373 msc 6 0 i 0.0 1862 0 0 0 1863 1.0 0 1862 aio 5 0 i 236.2 10664359 10595146 69065 0 10590884 1.0 0 3917566 aio 17 1 i 14.9 670442 649341 21094 0 626539 1.1 0 489826 aio 18 2 i 5.4 242692 228560 14129 0 209280 1.2 0 169868 aio 19 3 i 4.3 193499 181993 11504 0 162708 1.2 0 131537 aio 20 4 i 4.0 178895 169059 9834 0 152229 1.2 0 122664 aio 21 5 i 3.7 167562 158986 8574 0 146205 1.1 0 116946 aio 22 6 i 3.4 153786 145870 7914 0 137764 1.1 0 108512 aio 23 7 i 2.9 129978 122585 7392 0 126203 1.0 0 89199 aio 24 8 i 1.6 70116 63250 6866 0 54820 1.3 0 34056 aio 25 9 i 1.2 54348 47991 6357 0 37590 1.4 0 20584 aio 26 10 i 1.1 50593 44584 6009 0 34269 1.5 0 18176 aio 27 11 i 1.0 47305 41835 5470 0 31982 1.5 0 16599 aio 28 12 i 1.0 46159 40874 5285 0 29961 1.5 0 15940 aio 29 13 i 0.9 42658 37980 4676 0 28184 1.5 0 14408 aio 30 14 i 0.9 40736 36367 4367 0 26653 1.5 0 13331 aio 31 15 i 0.9 38725 34770 3953 0 25343 1.5 0 12472 aio 32 16 i 0.8 36849 33308 3540 0 24151 1.5 0 11866 aio 33 17 i 0.8 35733 32394 3337 0 23049 1.6 0 11116 aio 34 18 i 0.8 34251 31197 3053 0 21858 1.6 0 10492 aio 35 19 i 0.7 33118 30187 2929 0 20912 1.6 0 10167 aio 36 20 i 0.7 32141 29346 2794 0 19954 1.6 0 9766 aio 37 21 i 0.7 31054 28414 2639 0 19136 1.6 0 9362 aio 38 22 i 0.7 29791 27216 2575 0 18435 1.6 0 8800 aio 39 23 i 0.6 29151 26705 2446 0 17665 1.7 0 8623 aio 40 24 i 0.6 28214 25832 2382 0 16980 1.7 0 8276 aio 41 25 i 0.6 27507 25248 2259 0 16354 1.7 0 8065 aio 42 26 i 0.6 26307 24152 2155 0 15611 1.7 0 7499 aio 43 27 i 0.6 25616 23566 2050 0 15033 1.7 0 7437 aio 44 28 i 0.6 25167 23125 2042 0 14352 1.8 0 7242 aio 45 29 i 0.5 24480 22530 1950 0 13790 1.8 0 7166 aio 46 30 i 0.5 23756 21796 1960 0 13207 1.8 0 6877 pio 4 0 i 0.1 2390 0 2390 0 2391 1.0 0 2390 lio 3 0 i 17.3 779318 0 779318 0 779319 1.0 0 779318 I do notice that you are not using DIRECT_IO which might improve performance, we need to change using DIRECT_IO on our onconfig file? Please advice. Thank You

OK, look at the io/wkup column. If all of the AIO VPs have a value of 1.0 or higher then the engine could improve performance somewhat with more AIO VPs. You want to see one or two AIO VPs with an io/wkup less than 1.0. If you have more than one with an io/wkup of 0.0 then you have more than you can take advantage of and can drop some. In your case you can use more. I would guess at least 5 more in addition to the 31 you have now. Another option would be to enable DIRECT_IO in which case you will only need between 2 and 6 AIO VPs. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Nov 11, 2016 at 3:02 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Ouput from onstat -g iov > > IBM Informix Dynamic Server Version 12.10.FC6AEE -- On-Line -- Up 12:32:25 > --> 12568132 Kbytes > > AIO I/O vps: > class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors > tempops > fifo 8 0 i 0.0 0 0 0 0 1 0.0 0 0 > adt 7 0 i 0.5 21373 0 21369 0 21374 1.0 0 21373 > msc 6 0 i 0.0 1862 0 0 0 1863 1.0 0 1862 > aio 5 0 i 236.2 10664359 10595146 69065 0 10590884 1.0 0 3917566 > aio 17 1 i 14.9 670442 649341 21094 0 626539 1.1 0 489826 > aio 18 2 i 5.4 242692 228560 14129 0 209280 1.2 0 169868 > aio 19 3 i 4.3 193499 181993 11504 0 162708 1.2 0 131537 > aio 20 4 i 4.0 178895 169059 9834 0 152229 1.2 0 122664 > aio 21 5 i 3.7 167562 158986 8574 0 146205 1.1 0 116946 > aio 22 6 i 3.4 153786 145870 7914 0 137764 1.1 0 108512 > aio 23 7 i 2.9 129978 122585 7392 0 126203 1.0 0 89199 > aio 24 8 i 1.6 70116 63250 6866 0 54820 1.3 0 34056 > aio 25 9 i 1.2 54348 47991 6357 0 37590 1.4 0 20584 > aio 26 10 i 1.1 50593 44584 6009 0 34269 1.5 0 18176 > aio 27 11 i 1.0 47305 41835 5470 0 31982 1.5 0 16599 > aio 28 12 i 1.0 46159 40874 5285 0 29961 1.5 0 15940 > aio 29 13 i 0.9 42658 37980 4676 0 28184 1.5 0 14408 > aio 30 14 i 0.9 40736 36367 4367 0 26653 1.5 0 13331 > aio 31 15 i 0.9 38725 34770 3953 0 25343 1.5 0 12472 > aio 32 16 i 0.8 36849 33308 3540 0 24151 1.5 0 11866 > aio 33 17 i 0.8 35733 32394 3337 0 23049 1.6 0 11116 > aio 34 18 i 0.8 34251 31197 3053 0 21858 1.6 0 10492 > aio 35 19 i 0.7 33118 30187 2929 0 20912 1.6 0 10167 > aio 36 20 i 0.7 32141 29346 2794 0 19954 1.6 0 9766 > aio 37 21 i 0.7 31054 28414 2639 0 19136 1.6 0 9362 > aio 38 22 i 0.7 29791 27216 2575 0 18435 1.6 0 8800 > aio 39 23 i 0.6 29151 26705 2446 0 17665 1.7 0 8623 > aio 40 24 i 0.6 28214 25832 2382 0 16980 1.7 0 8276 > aio 41 25 i 0.6 27507 25248 2259 0 16354 1.7 0 8065 > aio 42 26 i 0.6 26307 24152 2155 0 15611 1.7 0 7499 > aio 43 27 i 0.6 25616 23566 2050 0 15033 1.7 0 7437 > aio 44 28 i 0.6 25167 23125 2042 0 14352 1.8 0 7242 > aio 45 29 i 0.5 24480 22530 1950 0 13790 1.8 0 7166 > aio 46 30 i 0.5 23756 21796 1960 0 13207 1.8 0 6877 > pio 4 0 i 0.1 2390 0 2390 0 2391 1.0 0 2390 > lio 3 0 i 17.3 779318 0 779318 0 779319 1.0 0 779318 > > I do notice that you are not using DIRECT_IO which might improve > performance, > we need to change using DIRECT_IO on our onconfig file? > > Please advice. > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114b05fce70bf80541051e1d

I have found with the 12.10 engines that DIRECT_IO off is often faster - that could just be my world but worth testing Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, November 11, 2016 5:52 AM To: ids@iiug.org Subject: Re: IDS 12.10 - Query Slow [38120] OK, look at the io/wkup column. If all of the AIO VPs have a value of 1.0 or higher then the engine could improve performance somewhat with more AIO VPs. You want to see one or two AIO VPs with an io/wkup less than 1.0. If you have more than one with an io/wkup of 0.0 then you have more than you can take advantage of and can drop some. In your case you can use more. I would guess at least 5 more in addition to the 31 you have now. Another option would be to enable DIRECT_IO in which case you will only need between 2 and 6 AIO VPs. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Nov 11, 2016 at 3:02 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Ouput from onstat -g iov > > IBM Informix Dynamic Server Version 12.10.FC6AEE -- On-Line -- Up 12:32:25 > --> 12568132 Kbytes > > AIO I/O vps: > class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors > tempops > fifo 8 0 i 0.0 0 0 0 0 1 0.0 0 0 > adt 7 0 i 0.5 21373 0 21369 0 21374 1.0 0 21373 > msc 6 0 i 0.0 1862 0 0 0 1863 1.0 0 1862 > aio 5 0 i 236.2 10664359 10595146 69065 0 10590884 1.0 0 3917566 > aio 17 1 i 14.9 670442 649341 21094 0 626539 1.1 0 489826 > aio 18 2 i 5.4 242692 228560 14129 0 209280 1.2 0 169868 > aio 19 3 i 4.3 193499 181993 11504 0 162708 1.2 0 131537 > aio 20 4 i 4.0 178895 169059 9834 0 152229 1.2 0 122664 > aio 21 5 i 3.7 167562 158986 8574 0 146205 1.1 0 116946 > aio 22 6 i 3.4 153786 145870 7914 0 137764 1.1 0 108512 > aio 23 7 i 2.9 129978 122585 7392 0 126203 1.0 0 89199 > aio 24 8 i 1.6 70116 63250 6866 0 54820 1.3 0 34056 > aio 25 9 i 1.2 54348 47991 6357 0 37590 1.4 0 20584 > aio 26 10 i 1.1 50593 44584 6009 0 34269 1.5 0 18176 > aio 27 11 i 1.0 47305 41835 5470 0 31982 1.5 0 16599 > aio 28 12 i 1.0 46159 40874 5285 0 29961 1.5 0 15940 > aio 29 13 i 0.9 42658 37980 4676 0 28184 1.5 0 14408 > aio 30 14 i 0.9 40736 36367 4367 0 26653 1.5 0 13331 > aio 31 15 i 0.9 38725 34770 3953 0 25343 1.5 0 12472 > aio 32 16 i 0.8 36849 33308 3540 0 24151 1.5 0 11866 > aio 33 17 i 0.8 35733 32394 3337 0 23049 1.6 0 11116 > aio 34 18 i 0.8 34251 31197 3053 0 21858 1.6 0 10492 > aio 35 19 i 0.7 33118 30187 2929 0 20912 1.6 0 10167 > aio 36 20 i 0.7 32141 29346 2794 0 19954 1.6 0 9766 > aio 37 21 i 0.7 31054 28414 2639 0 19136 1.6 0 9362 > aio 38 22 i 0.7 29791 27216 2575 0 18435 1.6 0 8800 > aio 39 23 i 0.6 29151 26705 2446 0 17665 1.7 0 8623 > aio 40 24 i 0.6 28214 25832 2382 0 16980 1.7 0 8276 > aio 41 25 i 0.6 27507 25248 2259 0 16354 1.7 0 8065 > aio 42 26 i 0.6 26307 24152 2155 0 15611 1.7 0 7499 > aio 43 27 i 0.6 25616 23566 2050 0 15033 1.7 0 7437 > aio 44 28 i 0.6 25167 23125 2042 0 14352 1.8 0 7242 > aio 45 29 i 0.5 24480 22530 1950 0 13790 1.8 0 7166 > aio 46 30 i 0.5 23756 21796 1960 0 13207 1.8 0 6877 > pio 4 0 i 0.1 2390 0 2390 0 2391 1.0 0 2390 > lio 3 0 i 17.3 779318 0 779318 0 779319 1.0 0 779318 > > I do notice that you are not using DIRECT_IO which might improve > performance, > we need to change using DIRECT_IO on our onconfig file? > > Please advice. > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114b05fce70bf80541051e1d **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

reading/writing/both? Thanks and regards. On Fri, Nov 11, 2016 at 1:44 PM, Paul Watson <paul@oninit.com> wrote: > I have found with the 12.10 engines that DIRECT_IO off is often faster - > that could just be my world but worth testing > > Cheers > Paul > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Friday, November 11, 2016 5:52 AM > To: ids@iiug.org > Subject: Re: IDS 12.10 - Query Slow [38120] > > OK, look at the io/wkup column. If all of the AIO VPs have a value of 1.0 > or higher then the engine could improve performance somewhat with more AIO > VPs. You want to see one or two AIO VPs with an io/wkup less than 1.0. If > you have more than one with an io/wkup of 0.0 then you have more than you > can take advantage of and can drop some. > > In your case you can use more. I would guess at least 5 more in addition to > the 31 you have now. > > Another option would be to enable DIRECT_IO in which case you will only > need between 2 and 6 AIO VPs. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Fri, Nov 11, 2016 at 3:02 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> > wrote: > > > Hi, > > > > Ouput from onstat -g iov > > > > IBM Informix Dynamic Server Version 12.10.FC6AEE -- On-Line -- Up > 12:32:25 > > > --> > 12568132 Kbytes > > > > AIO I/O vps: > > class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup > errors > > > tempops > > fifo 8 0 i 0.0 0 0 0 0 1 0.0 0 0 > > adt 7 0 i 0.5 21373 0 21369 0 21374 1.0 0 21373 > > msc 6 0 i 0.0 1862 0 0 0 1863 1.0 0 1862 > > aio 5 0 i 236.2 10664359 10595146 69065 0 10590884 1.0 0 3917566 > > aio 17 1 i 14.9 670442 649341 21094 0 626539 1.1 0 489826 > > aio 18 2 i 5.4 242692 228560 14129 0 209280 1.2 0 169868 > > aio 19 3 i 4.3 193499 181993 11504 0 162708 1.2 0 131537 > > aio 20 4 i 4.0 178895 169059 9834 0 152229 1.2 0 122664 > > aio 21 5 i 3.7 167562 158986 8574 0 146205 1.1 0 116946 > > aio 22 6 i 3.4 153786 145870 7914 0 137764 1.1 0 108512 > > aio 23 7 i 2.9 129978 122585 7392 0 126203 1.0 0 89199 > > aio 24 8 i 1.6 70116 63250 6866 0 54820 1.3 0 34056 > > aio 25 9 i 1.2 54348 47991 6357 0 37590 1.4 0 20584 > > aio 26 10 i 1.1 50593 44584 6009 0 34269 1.5 0 18176 > > aio 27 11 i 1.0 47305 41835 5470 0 31982 1.5 0 16599 > > aio 28 12 i 1.0 46159 40874 5285 0 29961 1.5 0 15940 > > aio 29 13 i 0.9 42658 37980 4676 0 28184 1.5 0 14408 > > aio 30 14 i 0.9 40736 36367 4367 0 26653 1.5 0 13331 > > aio 31 15 i 0.9 38725 34770 3953 0 25343 1.5 0 12472 > > aio 32 16 i 0.8 36849 33308 3540 0 24151 1.5 0 11866 > > aio 33 17 i 0.8 35733 32394 3337 0 23049 1.6 0 11116 > > aio 34 18 i 0.8 34251 31197 3053 0 21858 1.6 0 10492 > > aio 35 19 i 0.7 33118 30187 2929 0 20912 1.6 0 10167 > > aio 36 20 i 0.7 32141 29346 2794 0 19954 1.6 0 9766 > > aio 37 21 i 0.7 31054 28414 2639 0 19136 1.6 0 9362 > > aio 38 22 i 0.7 29791 27216 2575 0 18435 1.6 0 8800 > > aio 39 23 i 0.6 29151 26705 2446 0 17665 1.7 0 8623 > > aio 40 24 i 0.6 28214 25832 2382 0 16980 1.7 0 8276 > > aio 41 25 i 0.6 27507 25248 2259 0 16354 1.7 0 8065 > > aio 42 26 i 0.6 26307 24152 2155 0 15611 1.7 0 7499 > > aio 43 27 i 0.6 25616 23566 2050 0 15033 1.7 0 7437 > > aio 44 28 i 0.6 25167 23125 2042 0 14352 1.8 0 7242 > > aio 45 29 i 0.5 24480 22530 1950 0 13790 1.8 0 7166 > > aio 46 30 i 0.5 23756 21796 1960 0 13207 1.8 0 6877 > > pio 4 0 i 0.1 2390 0 2390 0 2391 1.0 0 2390 > > lio 3 0 i 17.3 779318 0 779318 0 779319 1.0 0 779318 > > > > I do notice that you are not using DIRECT_IO which might improve > > performance, > > we need to change using DIRECT_IO on our onconfig file? > > > > Please advice. > > > > Thank You > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a114b05fce70bf80541051e1d > > ************************************************************ > **************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a113fbb5a5a71a7054106d662

HI, I would guess at least 5 more in addition to the 31 you have now.- What i need to to get more io, what parameter need to insert/change on my onconfig. Paul Watson, I have found with the 12.10 engines that DIRECT_IO off is often faster - that could just be my world but worth testing A- thank for your information about DIRECT_IO. Art S. Kagel, Are you agree with paul watson? means no need to change to enable DIRECT_IO. Please advice. Thank You

My experience with DIRECT_IO has been different from Paul's. That just means that you have to test both in your environment with your hardware and workload to know which is better. Meanwhile, I have given you advice on tuning the AIO VPs while DIRECT_IO is disabled, and a suggestion to try DIRECT_IO. Sounds like you need to set up a test system and give it a try. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Nov 11, 2016 at 9:14 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > HI, > > I would guess at least 5 more in addition to the 31 you have now.- What i > need > to to get more io, what parameter need to insert/change on my onconfig. > > Paul Watson, > > I have found with the 12.10 engines that DIRECT_IO off is often faster - > that could just be my world but worth testing > A- thank for your information about DIRECT_IO. > > Art S. Kagel, > Are you agree with paul watson? means no need to change to enable > DIRECT_IO. > > Please advice. > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec508f4bcb62a770541072e3a

Hi, Look like i need to test it on our environment and determined which one is better, i will do it. I have given you advice on tuning the AIO VPs while DIRECT_IO is disabled. - Let me try this first, tuning on AIO VPs, How to do it? What value i need to set it? I noticed that different value for both version. on version 11.10 #NUMAIOVPS and version 12.10 have AUTO_AIOVPS parameter. # AUTO_AIOVPS - Enables (1) or disables (0) automatic management I just enable or assign value for this parameter (auto)aiovps? Required database restart after change parameter to take effect? Please advice Thank You

OK ... Without a restart you can add AIO VPs with: onmode -p +5 aio (similarly you can drop aio vps with: onmode -p -<N> aio to remove <N> aio vps) In the ONCONFIG, under v11.10 the NUMAIOVPS parameter is still active, so you could change: NUMAIOVPS 31 to NUMAIOVPS 36 On either 11.10 or 12.10, you can use the newer VPCLASS parameter to configure any VP class, so: VPCLASS aio,num=36,noage ## The 'noage' option is important on AIX and HPUX to prevent the aggressive process aging that happens in those OSes. Other UNIXes like Linux & MacOS are less aggressive, but setting noage is still a good idea. The AUTO_AIOVPS parameter controls whether Informix automatically tries to add and drop AIO VPs as needed. You can set this to '1' to enable it. It is harmless, but setting it doesn't mean you do not have to manually monitor and tune the AIO VPs because this autonomic setting is often too slow to respond to peak loads, so having the number of AIO VPs properly set in the first place is important. Then you can rely on AUTO_AIOVPS to adjust their number if you hit an unusual load peak. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Nov 11, 2016 at 9:30 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Look like i need to test it on our environment and determined which one is > better, i will do it. > > I have given you advice on tuning the AIO VPs while DIRECT_IO is disabled. > - Let me try this first, tuning on AIO VPs, How to do it? What value i > need to > set it? > > I noticed that different value for both version. > on version 11.10 > #NUMAIOVPS > and version 12.10 have AUTO_AIOVPS parameter. > # AUTO_AIOVPS - Enables (1) or disables (0) automatic management > > I just enable or assign value for this parameter (auto)aiovps? Required > database restart after change parameter to take effect? > > Please advice > > Thank You > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec508f4bcf3f7c1054108d139