Re: Performance problems

Willaert Doris wrote: > > 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. OK You have only one CPU VP configured and have MULTIPROCESSOR set to 0. As this is a single CPU system you can improve performance by setting SINGLE_CPU_VP to 1 which enables several optimizations which assume no parallel processing. You have a heavy I/O load but only 2000 BUFFERS I would suggest increasing that to AT LEAST 20,000, more if you have the memory to spare. You are supporting over 40 concurrent sessions with only 8 LRUS I would suggest increasing that to 32 along with CLEANERS to 32. You also only have 4 AIO VPS to support 10 chunks The minimum for a 7.3 install is one per chunk plus and additional one for any busy chunks, with the number of LRUS as a minimum. So if you increase LRUS to 32 use AIOVPS of 32 otherwise at least 14. Monitor onstat -g iov at least one or two of the aio VPs should show <1 IO/wkupindicating that at least some of the time that VP was awakened another AIO VP had freed up to perform the IO before it was fully awake. If not you can take advantage of even more AIO VPs. Also make sure that you have used the new UPDATE STATISTICS MEDIUM/HIGH as recommended in the release notes for 7.2x since the 7.xx optimizer is VERY dependent on Data Distributions which the old 5.xx UPDATE STATISTICS without option does not generate. You can get my dostats.ec utility which generate an optimal set of update statistics statements, and optionally executes them, for your tables. Lastly, V7.30 includes correlated subquery flattening which converts correlated sub-queries to the equivalent join query which is MUCH faster if the indexes needed to perform the join are availab1e, but, which is MUCH slower if the indexes are not there. Check your code for correlated sub-queries and make certain the join indexes are in place. Art S. Kagel