Re: Puzzling performance issue?

First of all, thanks for the drops! The comp.databases.informix group has given several noteworthy replies... Normally I tune by the book (Informix) yet with this system it was seemingly a negative impact. My second set of attempts were to see if I could alleviate the issues using Art Kagel's BR and BTR methods. I've found on larger systems this works very well and in some cases, much better than the Informix's standard suggestion. Regardless, I should have displayed my "Informix" tuned onconfig so the responses received would not have pointed to my straw grasping attempts. However, there was one thing I did not realize about this particular system. The box is said to be paging heavily. Now I'm not exactly sure what heavily is relative to on this box, but the AIX research I've done makes me think this is indeed a major issue that needs to be addressed ASAP. Therefore my next step (today) is to go back off the memory intensive areas to make the instance footprint smaller as well as go back to using my "by the book" tuned onconfig. If I can reduce the paging and the impact on performance is positive, then I'll keep traveling down that road until the things balance out. Currently this seems to make the most sense? Also, the point made about going with single cpuvp and affinitizing to the second proc seems logical but I'm not an AIX expert. Are there any other thoughts or experiences on this?

Eric wrote: > First of all, thanks for the drops! The comp.databases.informix group > has given several noteworthy replies... Normally I tune by the book > (Informix) yet with this system it was seemingly a negative impact. > My second set of attempts were to see if I could alleviate the issues > using Art Kagel's BR and BTR methods. I've found on larger systems > this works very well and in some cases, much better than the > Informix's standard suggestion The Informix recommendations have to apply to a vast variety of user configurations. If you have plenty of memory to burn then going for optimal BTRs (and cache percentages) is icing on the cake. A small machine has to be tuned the old-fashioned way. If you haven't got enough memory to virtually eliminate common I/O, then you have to sacrifice BTR and cache percentages. 10 years ago our performance demands were much lighter than today. Nobody would accept the performance of a 50MHz 386 with 64Mb today, but we used to have sites with 50-100 users on machines just like that. Frightening to think about now :-/ > However, there was one thing I did not realize about this particular > system. The box is said to be paging heavily. Now I'm not exactly > sure what heavily is relative to on this box It's relative to no activity which is ideal :-) The engine needs to make large amounts of I/O calls, which means the disks need to be available all the time. Adding more and more buffers follows the law of diminishing returns, but if you add so many that the machine is swapping heavily, then the disk queues are full of paging requests, which dramatically reduces the responsiveness of the engine's I/O. Even worse, if the engine's buffers are being swapped, then what's the point in buffering those engine pages anyway? If they get swapped out, they still need a disk-access to get to them, but now the paging not only delays a genuine I/O request, it also interferes with something as simple as a memory access by the CPU! Swapping and paging is truly evil with a database engine. I strongly recommend setting RESIDENT = -1 in the $ONCONFIG so that nothing in the engine is paged out, and then balance the rest after that. > Also, the point made about going with single cpuvp and affinitizing > to the second proc seems logical but I'm not an AIX expert. Are there > any other thoughts or experiences on this? Not AIX specific, but it applies to many platforms so AIX should be no different. NOAGE, if supported on a platform, tells the engine's CPUVP processes to hog all the CPU time they desire. Therefore it's a disaster on a single CPU machine, and needs to be handled carefully on multi-cpu machines, especially dual. NOAGE is good 'cos, if you buy more CPU's specifically for the engine, you can guarantee that it actually gets full use of them. On multi-cpu machines, the first processor may or may not be the only one that can do hardware and/or some kernel activities, so it makes sense NOT to hog that processor. If the specific platform does not suffer from this effect, then it's immaterial which one you pick; then again, why not get into a good habit and always leave the first processor free? Machines with NUMA architecture (eg many DG AViions and some IBM boxes that I know of) organise the CPU's into "quadrants", and the first CPU on each is charged with communicating with the other quadrants and generally doing the ugly stuff. Therefore it gets ugly on those boxes dealing with the affinity parameters. The best approach on those is to use some operating system commands to pin the processes to processors 2,3,4 on each quadrant, leaving the other cpu to deal with hardware and also to actually do some work for the other engine VP types and even the users With 4 or more processors it's fairly easy to choose to devote the last N-1 or N-2 processes to the engine as needed, but 2 and 3 CPU are a bit of a problem child. If you treat a 3 CPU machine as multi-cpu with some 2-cpu-style reservations, then you can ignore them. As for 2 CPU machines, you've got 3 choices. 1) do not set NOAGE, do not set AFF_ affinity parameters, and allocate 2 or 3 or even 4 CPU vps. The combination of a good handful of CPUVPs adds up to give the engine enough operating system time slices to get some work done despite the lack of NOAGE setting. This style of allocation seemed to work well on HP's back in the early days of the 7 engines, but it seemed to suffer somewhere around the 7.21 engines. I wouldn't recommend this any more, but it might be worth a try. 2) Allocate 2 CPU's, do not set NOAGE. Pin them on each processor using AFF_ parameters - this means that the processes do not drift from processor to processor, which may be expensive on some architectures. If this gives you enough cpu time to get the job done, then great. However, if the CPUVPs end up getting only 30% or less of the time (for example) and if that is not enough to get the work done, then you'd be better off with the 3rd suggestion (or MAYBE the 1st). 3) treat the box as a multi-cpu. Allocate only 1 CPUVP, set NOAGE on, use AFF_ to pin it to the 2nd processor. This way, you are guaranteed that the CPUVP gets upto 50% of all cpu resources, on demand without any arguments from other processes. There is a 4 possibility - get another CPU into the machine. Actually, there's also a 5th - get a modern GHz Pentium with SCSI and 1 or 2 Gb of memory, and you'll whup the ass off any 5yr old 200Mhz AIX or HP or .... machine. And it's as cheap as chips. Anyway, your numbers did show that memory was a real problem, so doubling that will make a lot of people happy.

You haven't told us what any of the SQL is trying to do yet. That's a bit like asking for directions without saying where you're trying to get to. There is no Magic Parameter X in performance tuning. You have to understand what the engine is doing with your SQL. Andy eric.washaliski@mediware.com (Eric) wrote in message news:<614b3eb4.0311200825.43772ed0@posting.google.com>... > First of all, thanks for the drops! The comp.databases.informix group > has given several noteworthy replies... Normally I tune by the book > (Informix) yet with this system it was seemingly a negative impact. > My second set of attempts were to see if I could alleviate the issues > using Art Kagel's BR and BTR methods. I've found on larger systems > this works very well and in some cases, much better than the > Informix's standard suggestion. Regardless, I should have displayed my > "Informix" tuned onconfig so the responses received would not have > pointed to my straw grasping attempts. However, there was one thing I > did not realize about this particular system. The box is said to be > paging heavily. Now I'm not exactly sure what heavily is relative to > on this box, but the AIX research I've done makes me think this is > indeed a major issue that needs to be addressed ASAP. Therefore my > next step (today) is to go back off the memory intensive areas to make > the instance footprint smaller as well as go back to using my "by the > book" tuned onconfig. If I can reduce the paging and the impact on > performance is positive, then I'll keep traveling down that road until > the things balance out. Currently this seems to make the most sense? > Also, the point made about going with single cpuvp and affinitizing > to the second proc seems logical but I'm not an AIX expert. Are there > any other thoughts or experiences on this?