Re: Informix 5 Performance Issues

In article <5j4i8s$c7p@cssun.mathcs.emory.edu>, Billy Wheeler <billy@west.co.za> writes >Remember - YMMV, etc: > >On 16 Apr 97 at 17:04, Phantom173 wrote: > >> 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). > >Honestly, I know it's a cop-out, but you really should try it to see >whether it works. I agree with your consultant. I couldn't see how >many processes you were running in total, but context switching can >become a significant overhead. I'm also sure that Sequent supports >processor affinity - this means that you could tie up a CPU just to >run an Online 7 virtual process, with resultant performance benefits. > >A redesign of your application may be important for other reasons, >but I would hesitate to use that as my first step to improve >performance!! > In Informix Online Performance Tning course the first thing is to check the application. 90% of the performance improvement comes from tuning the application code/database indicies. >> 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. > >In fact, you would probably get the best performance if you added >more CPUs *and* switched to 7. 7's internal parallelism really takes >advantage of multiple resources far better than 5.x. > >> 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. > >I believe so, yes. Agreed. > >> 2. Is the DBA's logic sound? Will we get an overall improvement from >> having more processors even though they are individually slower. > >Yes. ??? Depends 1. More processors generally = more cache on the CPU = better 2. More processors = more movement of data from one CPU to any other (cache flushing) = worse. Answer - depends on the app (benchmark). > >> 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. > >The largest benchmark I have ever seen for Informix involved 26 CPUs >- I don't know if they felt that's as far as it would scale or what. >I would upgrade to 7 in either event. It would handle it but v7 would handle it much better - use Parallel Data Query (PDQ) + fragmenting data across many disks. > >> 4. If we go with the 32 processors and Informix becomes a bottleneck, >> will v7 help because of its multi-threading. > >Yes. > >> 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. > Run isql and do a Query -> New update statistics on the database. Get someone to use put "set explain on" in the 4GL program. Then run the program and look at the sqexplain.out file produce in the current directory. If you find sequential scans occuring on a table then run 'select nrow from systables where tabname = <tablename>' in isql. If the answer is >200 then an index is missing. Once you have created a suitable index/indicies then run update statistics for table <tablename> in isql. If the same sql gets prepared many times (sqexplain.out shows sql statements being prepared) then the code is crap and needs to be changed to prepare the sql once and execute it many times. Good luck again! >Good luck. > >-- >Ciao, >Billy > >Nose to the ground and ear to the grindstone in: Johannesburg > > Why is it hard for women to find men who are sensitive, caring and > good looking? Because those men already have boyfriends. -- David Williams