Re: striping versus round robin

On Dec 19, 1:01pm, rtt wrote: > Subject: striping versus round robin > hi > I am about to upgrade from online 5 to 7 and am sitting with a query > This is to be done on an HP K200 dual process unix box. > Do I use HP's disk striping to create a raw logical volume accross > my six disks or do I round robin my large tables across these disks ??? > The large tables are 2,5 million rows and larger and the rowsize is > extremely large. +- 300k per row. These tables are also hard hit with > access. They also have +- 12 indexes on each one. > Can anybody help ????? >-- End of excerpt from rtt I assume you mean 300bytes/per row not 300,000. You couldn't fit that many on 6 2Gb drives and it depends on the pattern of access that you have to the table and other factors such as recoverability. I would suggest using HP disk striping rather than round robin fragmentation. This way you don't have to hassle with creating detached indexes in seperate dbspaces (See below). Also you will get good random access distribution across your drives for data and indexes. Where most of your access is via a particular index using a specific key value the best performance may be achieved by fragmenting by expression based on the index key (possibly along with disk striping to spread the disk usage patterns, depending on fragment access patterns). A couple of warnings here though: You have to make sure each fragment is going to be large enough to contain any data that it may receive. This generally means that you have to provide more growth space overall as you have to provide extra space in each fragment rather than growth space for the entire table. Data is almost always skewed in some fashion so some fragments will fill up more quickly than others. A full fragment acts the same way as a full table. There is no way of specifying an overflow dbspace (A remainder fragment is not an overflow fragment a mistake I made before I read the manuals more carefully). Informix - How about overflow dbspaces? You need to detach all indexes that are not ordered in the same way as the fragment expression (this means all indexes for round robin). If you don't do this performance on these indexes will be terrible as the optimiser will always have to build a temporary table to sort/merge the results from its index scan of each fragment. Fragmenting these indexes by thier key value can also be done but your tables really aren't large enough to make that worth while. It would probably cost more than it saves. Also watch the rules for unique and constraint indexes when fragmenting tables. Merry Christmas everybody. I'll be back in the New Year. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!