Re: Fragments or RAID 5?

Chuck Ludwigsen wrote: [SNIP] > Bashar Chalabi <bashar@ctl.com> wrote > > Hi everybody, > > > > Anybody out there knows which provides the best performance: fragmenting > > tables (round robin) on multiple hard drives or letting a RAID5 > controller > > do the work? > > > > Our hardware is a quad-processor Pentium Pro under SCO Unix, with 5 x 2GB > > disks. Informix is ODS 7.22 I would recommend using hardware striping, if your RAID controller supports it, to improve throughput and Informix mirroring for safety. I have found RAID5 for databases to be less than advertised and a performance bottleneck. If you controller and system software can handle both striping and mirroring (ie stripe a set of n mirrored pairs), this hardware mirroring is usually slightly faster than Informix mirroring (which is darn fast in its own right!). If not use hardware striping (RAID0?, RAID1?, RAID2?) and Informix mirroring. Mirroring costs you 50% overhead as compared with 20% overhead but the extra cost is ALWAYS worth the added performance and safety. Also woth Informix mirroring you can stop mirroring to back up one of the mirror sides and restart the mirror afterwards. As far as round robin fragmentation, this will only improve performance if you use a detached non-fragmented index. This is because ODS versions prior to 7.3 cannot perform a merge of sorted sinks so that even if your ORDER BY columns match a fragmented index the engine will gather all of the returned rows (seeking through each fragment sequentially if PDQ is off and in parallel if on) and then re-sort the rows. This is because of a poor merge implementation in 7.1 which was removed in all later versions. Version 7.3 will have merge of sorted sinks code reimplemented, BTW. Also if the index you need to search is attached the engine has to search every fragment just to locate the row(s) that you want, detaching the index and either making the index non-fragmented or fragmented by expression according to its key solves this problem with round robin also. The bigest advantage of round robin fragmentation is that you are not constantly adding space to one fragment after another as the table skews back and forth. If this skewing is not predictable (ex: I have a set of tables fragmented by month so in September I add to the September fragment, etc) it is an administration nightmare. Art S. Kagel