Fragmentation in Data Warehouse

Hi, A simple question, but one (I guess) with a potentially long and complex answer. We have a small Data Warehouse (c. 50Gbyte) running on a 4 cpu HP K-series server (HP-UX 10.20). We're about to put IDS 7.30 UC6 into production. The database resides on an EMC raid-0 disk array, with 8 (+8 mirrors) disks. Currently we fragment our 'medium' sized tables 3 - way with the indexes on a 4th disk, and 'large' tables 7 - way, with indexes on the 8th disk. We have 8 temporary dbspaces, one per disk. The disk with the indexes on, varies from table to table so no individual disk has all of the indexes. Most of the tables are rebuilt each month from scratch using the HPL. Most of our important queries join several tables. Response time is frequently measured in hours rather than minutes. From xtree, it appears that most problems tend to occurr when indexed are used to return data from large tables. Large table scans (using light scans, PDQ and hashing their results into temporary tables) seem to fly. Performance is pretty good (I think), for load and query times, but we'd like to squeeze as much out as possible. The IDS manuals detail the various fragmentation options, but rarely suggest the best options in different circustances. We use round robin fragmentation, because it was easiest to set up. However, I'm happy to go for key range fragmentation, if this is likely to be beneficial. Does anyone have any experience of different fragmentation strategies for this type of system? Martyn Hodgson martyn.hodgson@eaglestar.co.uk

Martyn Hodgson wrote: > > Hi, > > A simple question, but one (I guess) with a potentially long and complex > answer. We have a small Data Warehouse (c. 50Gbyte) running on a 4 cpu HP > K-series server (HP-UX 10.20). We're about to put IDS 7.30 UC6 into > production. The database resides on an EMC raid-0 disk array, with 8 (+8 > mirrors) disks. Currently we fragment our 'medium' sized tables 3 - way with > the indexes on a 4th disk, and 'large' tables 7 - way, with indexes on the > 8th disk. We have 8 temporary dbspaces, one per disk. The disk with the > indexes on, varies from table to table so no individual disk has all of the > indexes. Most of the tables are rebuilt each month from scratch using the > HPL. > > Most of our important queries join several tables. Response time is > frequently measured in hours rather than minutes. From xtree, it appears > that most problems tend to occurr when indexed are used to return data from > large tables. Large table scans (using light scans, PDQ and hashing their > results into temporary tables) seem to fly. So leave the tables alone since table scans are fast enough. Try fragmenting the indexed according to their own key ranges. This will help greatly if the queries contain good filter criteria. An option if the filter criteria are not as good is to use HASHED fragmentation. Either way you will parallelize your index accesses and may be able to eliminate index fragments from individual queries. > Performance is pretty good (I think), for load and query times, but we'd > like to squeeze as much out as possible. The IDS manuals detail the various > fragmentation options, but rarely suggest the best options in different > circustances. We use round robin fragmentation, because it was easiest to > set up. However, I'm happy to go for key range fragmentation, if this is > likely to be beneficial. Does anyone have any experience of different > fragmentation strategies for this type of system? Art S. Kagel