Informix Online 5.00 and DGUX 5.4.2.10 and RISC 88110

>Readers of comp.databases.informix > >I'd like to recount a tale that is still on going at my site, it >will really be of interest to a small cross section of this readership >tho. > >In late December 93, the firm I work for purchased a Data General >Aviion 9500, with eight processors, 768 megs of ram. It runs >DGUX 5.4.2.10, and has 18 gigs of hard disk, configured primarily >as raid-5. Our informix engine is online 5.00. Our production >environment typically has 250 - 350 data entry persons and 6 - 8 >4gl programmers. > >... The real problem is >the difference in how the Motorola 88100 and 88110 pipelines >instructions >... We had a similar problem but we did not suspect MC881xx pipelining to be the cause. This was on a Motorola MPC800 with (4x88k, 18GB HD) updating a 10 million row table. We noticed that every 5 minutes, processing would freeze for 5 sec; every half hour, 1 min; every 2 hours, 5 mins; every 10 hours, 20 mins, and so on. During these periods, Online was doing a checkpoint. We did not know why it was so regular but we assumed that Online was rebalancing the 1st index level every 5 mins, 2nd level every half hour, etc. We tried changing the serial field to an integer and used a "random" number generator to give us unique values which at first seemed to overcome the problem until after 10 hours when the checkpoint lasted one hour! Our only solution was to ease the problem by changing the architecture of the system. To cut a long story short, here's a few suggestions: 1. Use lots of small capacity disks instead of a few large ones. Disks, whether large or small, all have similar service times so have lots of them to reduce contention. This is the most important of the points I have here. 2. Don't use raid-5 because the penalties of a disk write are too high. You are effectively reducing the number of disk spindles. Use Informix or OS disk mirroring which I think is adequate. (Version 6 uses split reads on mirrored partitions which effectively doubles throughput). 3. Keep number of indexes on heavily updated tables low. Online transactions shouldn't delete and insert into the same table because this forces index rebalancing sooner. Run deletes in an off-peak batch process. 4. If you ever use dbimport, dbload, or load, make sure the indexes are created BEFORE not after. This will make sure that indexes are fragmented across all disks instead of on the last one. 5. Reduce the threshold for page cleaners. This ensures that there is less work at a checkpoint. I would rather have overworked cleaners than long checkpoint because users notice checkpoints a lot more. 6. Reduce long transaction watermark down to 10%(low) and 15%(high). LTXs on a large database are very painful so catch them early. I hope some of this helps. When the problem is fixed, please tell us what the solution was. Regards David -- David W Chan -- Objective Research Ltd. London E1, England -- Email: dwc@cix.compulink.co.uk