Re: Overall unstableness of Informix

Michael Silver wrote: > > We have had an Informix (7.12.UC2) database running on an HP for over a > year. We have been utterly disappointed with it. It crashes about > once-a-week. We run a lot of batch work against it using New Era for > Motif, which is responsible for many of the crashes. How a programming > language can crash the database engine is beyond me. Often times a > small batch job will hit the engine so hard, that everyone else in the > database times out. We can't use PDQ, because it crashes the database, > or causes strange things to happen. We have a dual processor HP Model > K. Our database fits into RAM (we have 512MB). We have dual port fast > and wide SCSI, with about 6 GB storage. A relatively small database, on > a fast machine, but we get very poor performance. We have had Informix > out here at least every 6 months, and nothing gets fixed, which makes > the $5000 bill hard to swallow. Even HP doesn't understand he > performance > > Is this typical? Does everyone have these problems? Perhaps we are on > the wrong platform? Is Informix just this bad? Should we switch to > Oracle or another database? We went to a user group meeting and laughed > when Informix told us about the datablades. We can't even get the > normal version to work. We feel we can get better performance from an > NT quad Pentium Pro, running SQL server. This is not practical for as > large as we expect to grow, but the fact that they can honestly be > compared is scary. What to do. These are some pointers I can think of real quick. I'm sure the Informix people covered most if not all of them but I'll take a shot just in case. I have On-line 7.11UC1 so somethings I'm about to write might be different but I'm sure most things are the same. 1.- Make sure that MULTIPROCESSOR is set to 0. Informix recommends setting it to 1 only if you have at least 4 processors. 2.- Try using 2 cpu vps, it helped in my case. If you are only going to use 1 cpu vp (NUMCPUVPs) make sure that SINGLE_CPU_VP is not equal to 0. 3.- Does HP support KAIO? If it does make sure you're using it. 4.- Maybe you've used to much memory for caching? There comes a point where setting too many buffers doesn't give you better performance. Actually it might make your machine to start swapping (now that I mention it, check to see if it's swapping). 5.- Checkpoints: I believe that the default of 300 is OK. What I did change was the LRU_MAX_DIRTY AND LRU_MIN_DIRTY (7 and 5 respectively) and now my checkpoints don't take as long. Do an "onstat -F", you shouldn't have any foreground writes (if you, do this is something that you really have to get rid of). Of chunk writes and LRU writes we want LRU writes. 6.- SHMADD shouldn't be a small number (I have 8MB, I had 2 when I first set up On-line) because of the overhead it creates to use it. 7.- I personally don't use PDQ, tried it, it slowed down my response. I think (and I might be wrong on this)that PDQ is for servers that have at least 4 processors with a lot of RAM and a huge database. Hopefully somebody that has set this up and is happy with it, will put there 2 cents in here. 8.- Are you using dbtemp spaces? Are they defined as dbtemp spaces? Are they working correctly? I'm asking because when I first setup my dbtemp spaces Informix wouldn't recognize them. I had to play around with the onconfig file a few times. I hope this helps and if anybody has any pointers please send them! My users are always complaining!