Re: Informix 5 Performance Issues

Phantom173 <loki173@postoffice.worldnet.att.net> wrote: :My company is having performance problems on an application, and I would :like to check out some of the "solutions" that have been given to us by :various sources. : :The client is running Informix 5.2 on a Sequent 790 with 16-586 (60 MHZ) :processors and 760 Meg of memory. According to the Informix monitoring :tools, 90-95% of the reads are being serviced from cache and there are :few page faults, lock waits, etc. In addition, we are doing tremendous :numbers of reads (sometimes 100 million per day). : :Unix monitoring tools show the machine as 100% CPU utilized, with low :levels of page faults, swaps, etc., acceptable disk utilization (e.g., :10-30%, depending on the disk), load factors of 2.0-3.0 (i.e., 2-3 :processes waiting for each processor), and 100+ Meg of memory as "free :memory". : :There are 12 or 15 application processes that account for 80%+ of the :CPU utilization. Each of these elephants uses 120+ CPU seconds per run :(many use 500+ CPU seconds per run) and are run many times per day :(e.g., one of the processes, which averages 500+ CPU seconds per run, is :run 20-40 times per day). These elephants are SQLTurbo's that have been :kicked off by users from on-line screens and run as on-line processes :(yes, we know, that wasn't a very good design and we're in the process :of trying to correct that problem). When we examine the ratio between :CPU time and real (elapsed) time, we find that these elephants tend to :grab a processor and run with it (i.e., the CPU time goes up in lock :step with the real time). Where this is not the case, they are still :relatively close (e.g., CPU time goes up 40-50 seconds for every 60 :second increase in real time). From profiling, we do know that as much :as 50% of the CPU time spent by these programs is in areas of I/O :(mainly reads). : :We have been told by a consultant that a move to Informix 7 would :benefit us because (a) Informix 7 would reduce the number of connects :(and therefore processes) associated with the elephants, and (b) it :would allow multi-threading in the database. Our DBA agrees that this :might provide some benefit, but doubts very strongly that this would be :significant given the facts stated above. He is arguing for an increase :in the number of processors (plus some dramatic redesign of the database :and the application). : :In fact, the DBA is arguing that we remove the 16-586 processors and :replace them with 32-486 processors (50MHz). His logic is that, while :each individual process will run 17% slower, in total things will run :faster because the extra processors means we won't have processes :waiting for 6-15 minutes before they get to run. We have maxed out the :Sequent machine on 586 processors, so we can't add more of those. :The questions I have are: : :1. Forgetting all the other benefits of Informix 7, will we get any :significant benefit from v7 due to reducing the number of connects and :multithreading. Probably. Any single process you now run will run on one CPU only. You seem to have more processes running simultaneously than you have CPU's. When this is the case the CPU utilization should be ok. Still the design of the v7 engine is much better in this area, so I would expect it to be able to run faster. Particularly if you can also utilize parallel scans. :2. Is the DBA's logic sound? Will we get an overall improvement from :having more processors even though they are individually slower. Probably no. Assuming you by 586 realy mean Pentium processors, a 60 MHz Pentium should execute programs perhaps 50 to 100% faster than a 50MHz 486. (I no longer remember the exact number, and can't find them at the Intel Web site any more, but I believe they where in this balpark.) You can't compare clock frequencies only. The Pentium also executes more instructions per clock tick. If the Pentiums are 100% faster 32 486s will obviously slow you down. Even if they are only 50% faster I doubt somewhat that even the Sequent technology will give you a significant performance boost. Isn't it an option to replace the current Pentiums with faster processors? If Sequent is any good at hardware designs you should be able to put in 16 200MHz Pentium Pro processors. That's the way deasent hardware should be designed. :3. If the DBA is correct, will going to 32 processors overwhelm :Informix 5? Can it handle all the requests that will come from these :processors, or will it become the bottleneck. :4. If we go with the 32 processors and Informix becomes a bottleneck, :will v7 help because of its multi-threading. : :Sorry for the long-winded explanation and multi-part question, but I'm :really getting desperate and the CFO is on my back for answers. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org