Re: 7.22.UC1X2 Performance tuning questions....

Bill, Thanks for your response, We will be installing the new asyncdsk software later today and relinking the kernel tonight, Once this is done we will turn on KAIO and reduce the number of NUMAIOVPS to two. It will be very interesting to see the impact of the KAIO on the system. Our system is about 60% OLTP and 40% DSS. We set the check point interval to 600 and I have checked the production logs and we are only getting a check point every 10 minutes which means that the PHYSBUFF setting is correct, The duration of the check point is now always between 20-25. We have just set LRU_MAX_DIRTY to 4 to try and reduced the check point duration and we will see how it goes during the day and maybe go down to as low as 2 at a later date (each 1% is 3000 pages of work on the system). We have not implemented any table fragmentation because it's was just to much extra work on top of getting the new system ready to run in the four days. I like your idea about the CPU's and will discuss this option with the team during the day. Once again, Thank you for taking the time to answer my queries. I look forward to your response. Cheers, Tony. ---------- From: Bill Ennis To: tony edwards Cc: informix-list@rmy.emory.edu Subject: Re: 7.22.UC1X2 Performance tuning questions.... Date: Friday, 4 April 1997 03:19 Hi, First question: What does processor affinity have to do with KAIO? KAIO tells informix to use direct kernal access for i/o operation. I have not yet heard that using KAIO depends on the availablity of processor affinity. I do know that KAIO works with HP/UX 10.2 with Informix 7.12 (tried it yesterday with my buddies at the SSA-HP competentcy center). Now, assuming that you do wind up not using KAIO you may want to try setting NUMAIOVPS = number of chunks (43 in your case). But, if you do wind up using KAIO then reduce NUMAIOVPS to a much lower value since only very few disk writes will go outside the realm of Kernal reads/writes (try 2). You didn't classify your application as OLTP or DSS. I'd need this to suggest a setting for OPTCOMPIND (generally 0 for OLTP or 2 for DSS, and you can override with an env setting for the exception). My guess is that your OLTP because of the checkpoint problem you mention and also because you say indexes are not being used correctly. DSS systems are primarily read intensive and subsequentely do not have checkpoint problems. How many CPU's does this system have? I'd guess 8 by the setting of 7 you are using. Try upping to 8. Again, my friends at HP tell me that even if your application is running on the db server they have seen best performance using ALL processors. I'm not sure what to suggest for the checkpoint problem since you already have the LRU_MAX/MIN_DIRTY values pretty low. You may want to increase the interval. You didn't mention PDQ settings. Are you using it? If so this could be some of the problem with all of the ready processes. It is possible to give all system resources to one or a few queries leaving everyone else just sittin around waitin. Send PDQ parameters if you are using. Keep me posted, Bill > > Team, > We have recently upgraded our HP-UX 9.04 / 5.07 to HP-UX 10.20 / > 7.22.UC1X2. Here is some information about the system > configuration...... > > Database size 50 GB, chunks 43, memory 3 GB, disk nike arrays 120GB, > cpus 8 > > NUMCPUS 7 LOCKS 600000 BUFFERS 300000 CKPTINTVL 600 > NUMAIOVPS 20 PHYSBUFF 400 LOGBUFF 400 LOGSMAX 165 > CLEANERS 45 SHMVIRTSIZE 1000000 SHMADD 200000 > LRU_MAX_DIRTY 5 LRU_MIN_DIRTY 1 LRUS 45 > STACKSIZE 32 OPTCOMPIND 2 NOAGE 0> > We don't have KAIO set because processor affinity is not supported on > HP. > > Problems we are having.... > > 1) A checkpoint is taking 16-20 seconds (Stats based on interval of > 300). > 2) Ready to run can be over 60 processes. > 3) The oninit processes never go below 156 for a priority and can be as > high as 268. > 4) System not using indexes correctly > > - Have run update statistics medium for all tables, update statistics > high on first column of > each index and run update statistics low on all other index fields. > > Can any one advise me on their update statistics strategie ? (This > system is 24x7) > > 5) Our LRU writes are 60% and the chunk writes are 40 % of the onstat -F > command. > 6) If we changed our optcompind to zero would we need to run so many > update stats? > > I look forward to any suggestions you can provide... > > Cheers, Tony. > -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net