RE: Performance Problems after Upgrade to 7.23UC6

Here's a cut and pasted from a message I sent to the list a few weeks ago: "One interesting thing that came up on this particular machine is that in 7.23+, on an HP/UX 10.20 box, with an ANSI database, performance dies, by a factor of many THOUSANDS, when you have more than about 4 million records in the database. Apparently, Informix loses all grip of query paths, and starts doing sequential scans all over the place." It's worth noting that we eventually got reasonable performance by adding new indexes and intensive tuning of the box and Informix (e.g. make sure your Kernel template is set appropriately). Logically, the indexes were not needed, but they were suggested by Informix and... Informix have said that the problems we are suffering from will be fixed in 7.24.uc5 (We have gone from 7.23.uc1 to 7.24.uc4 in the search for a solution). Unfortunately, I don't have a bug number. Kevin Crisp -----Original Message----- From: John DeSilva [mailto:jads@mcs.bm] Sent: Tuesday, March 31, 1998 2:46 PM To: 'informix-list@iiug.org' Subject: Performance Problems after Upgrade to 7.23UC6 ??????????? All suggestions greatly appreciated ??????????????? We have just recently upgrade a client machine from HPUX 9.04 / Online 7.14UD1 to HPUX 10.20 Online 7.23UC6 and have suffered an incredible performance drop in the process. The machine is a uniprocessor HP9000 G50 with 384 MB Ram. We were able to run 40 - 44 users at very acceptable speeds under 7.14 but at around 30 users under 7.23 the machine takes a serious nose dive ( 5 -10 times slower ). We have contacted infomix tech support who had us dbexport/dbimport the entire DB and run an update statistics. We also contacted HP who had us change the DBC_MAX_PCT kernal parameter from 50% to 15% ( something to do with pseudoswap ). None of these have had any significant impact although the kernal parameter did appear to have some affect. The primary bottleneck apears to be the CPU. Under sar -u the system and user cpu usage are about equal at 50% with 0% appearing in wio and idle. Swapinfo shows us no swapping to the two swap devices that we have but a new memory line showing 50% swapped ( again something to do with pseudoswap according to HP ). All Online parameters were left the same as under 7.14. I have check those parameters which the 7.2 performance tuning guide say affect CPU usage and all appear to be correct. The client has a new D370 2-way on order but it is not expected to arrive for another 3 weeks with installation not due till the first week of May. Has anyone had similar experiences with Online 7.23 and HPUX 10.20 ??? Any suggestions on what might be causing this and any possible solutions ??? John DeSilva MCS Limited jads@mcs.bm