Data Warehouse design

I would appreciate any feedback regarding optimal design strategies for our Data Warehouse. Our current situation is this: We are currently building a Data Warehouse using Informix Dynamic Server 7.23 in an AIX 4.1.4 Unix environment. From what I have gathered, our operating environment does not include RAID, disk striping, or mirroring. Our initial deployment will be in the neighborhood of 50 gigs. We have used smit to create 12 4 gig logical volumes (chunks). These logical volumes were created using smit strategies of "center" for disk location and "maximum" for number of physical volumes that each logical volume may be spread across. The logical volumes are spread across up to 20 physical volumes. One dbspace was created to house each logical volume. At rollout, we hope to be supporting 20-30 users, initially with Metacube. Our questions are these: 1. Shoud we deploy an Informix fragmentation strategy on the larger tables, distributing them across several of the dbspaces? 2. Would anything be gained from a table fragmentation strategy since the underlying chunks themselves are already distributed across several physical disks? 3. Should indexes be stored in dbspaces separate from the data? 4. Should the indexes be fragmented? Any comments are welcome. We are new to the design phase and eager to do things right. Right now, we have the luxury of being able to make a change if needed. Thanks -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

gresmi@yahoo.com wrote: > > I would appreciate any feedback regarding optimal design strategies for our > Data Warehouse. Our current situation is this: We are currently building a > Data Warehouse using Informix Dynamic Server 7.23 in an AIX 4.1.4 Unix > environment. From what I have gathered, our operating environment does not > include RAID, disk striping, or mirroring. Our initial deployment will be in > the neighborhood of 50 gigs. We have used smit to create 12 4 gig logical > volumes (chunks). These logical volumes were created using smit strategies of > "center" for disk location and "maximum" for number of physical volumes that > each logical volume may be spread across. The logical volumes are spread > across up to 20 physical volumes. One dbspace was created to house each > logical volume. > I think you will need to resize your chunks into 2 GB disks instead of 4GB. Informix can only use up to 2GB max per drive on 32bit systems. I'd also recommend UNIX mirroring but this will cut your drive count in half. > At rollout, we hope to be supporting 20-30 users, initially with Metacube. > > Our questions are these: > > 1. Shoud we deploy an Informix fragmentation strategy on the larger tables, > distributing them across several of the dbspaces? yes! > 2. Would anything be gained from a table fragmentation strategy since the > underlying chunks themselves are already distributed across several physical > disks? Yes. Chunk allocation is only that. Fragmentation is the distribution of the data. Think of it like a loaf of bread. The loaf is sliced up, now you need to spread something on each slice, such as peanut butter. > 3. Should indexes be stored in dbspaces separate from the data? Yes. This is recommended, and only available in 7.x, not 8.x. > 4. Should the indexes be fragmented? > Depends on the table and the index. Keep in mind that for DW in general indexes should be minimized and added with extreme care. They affect everything about your data warehouse. > Any comments are welcome. We are new to the design phase and eager to do > things right. Right now, we have the luxury of being able to make a change if > needed. > > Thanks > gresmi@yahoo.com, Curious why you're not using XPS ( 8.2x ). 7.x is acceptable but not really designed for DW. You're also on one machine, instead of clustering, so you'll get a one-server performance environment. One benefit to 7.x is that you get to separate your indexes into a separate dbspace from the tables whereas in 8.x you cannot. The indexes must live in the same dbspace as tables in XPS, but in actual practice we've been told by Informix to avoid indexes as a rule in 8.x whenever possible. Any time you get an opportunity to spread your data out across as many drives as possible you should do so. This is crucial so you don't hammer one or two drives to death during queries. Thus the importance of spreading things out. The bummer to 7.x also is that you won't be able to set up dbslices, which are logical groupings of your dbspaces. These are a really great feature of XPS, and make space allocation of dbspaces very easy. What would be optimal is to not only fragment wide, but deep. XPS takes advantage of hybrid fragmentation, I don't know if 7.x does or not, but this is a way to not only force the fragmentation by expression, but also by dbspace at the same time. It requires a bit of architectural design, but well worth it in the long run. If you have lots of disks fragmentation is the way to go. By the way, XPS is doable as a one-server setup, you should consider getting a copy and build your DW right, from the start. You could actually set it up with multiple coservers on one machine, and then as the DW grows migrate it to additional servers. Thanks, Tim -- - -- --- Tim Schaefer ---- tschaefe@mindspring.com --- http://www.inxutil.com -- -

> > I think you will need to resize your chunks into 2 GB disks instead of 4GB. > Informix can only use up to 2GB max per drive on 32bit systems. I'd also > recommend UNIX mirroring but this will cut your drive count in half. > Tim, I think you mean 2 GB for the logical "drives" or file systems. Depending on the system, the simplest trick is to create 2 GB filesystems then not mount them. But you already know this. ;-) As to the mirroring. I concur. It works on all systems and is cheaper than raid to implement. > > 3. Should indexes be stored in dbspaces separate from the data? > > Yes. This is recommended, and only available in 7.x, not 8.x. > Wasn't there a bug that under 7.X if you separate the index space from the table space, you would get too much space allocated for the index. I seem to recall Tony Aduchi giving a talk about his findings at one of our IGLUG meetings. > [SNIP] > One benefit to 7.x is that you get to separate your indexes into a separate > dbspace from the tables whereas in 8.x you cannot. The indexes must live in > the same dbspace as tables in XPS, but in actual practice we've been told by > Informix to avoid indexes as a rule in 8.x whenever possible. > That's weird. And it doesn't make sense. Do you have any reason as to why they would say this? -Mikey

Tim, Thanks much for your reply. If you don't mind, I'd like to extend the forum a bit. Unfortunately, we haven't a choice regarding version 7.23, as to which Informix Data Warehouse engine we use. But, from everything else I've gathered, we can make it work for us. I'm curious about the chunk size change from 4 gigs to 2. Is this a function of Informix 7.23, AIX, or the communication between them? And what is the impact of not making the change? I'm pretty sure we don't have a choice about the RAID or mirroring options either. How crucial are these strategies? Regarding the configuration of 1 logical volume per dbspace, and each logical volume consisting of multiple underlying physical volumes: When we set up the logical volumes using smit, choosing strategies of "center" for disk location and "maximum" for number of physical volumes that each logical volume should be spread across, we were choosing from the the same list of available disks each time. As we progressed through the creation of each logical volume, the number of physical disks allocated to each decreased on each iteration. And, the best available location on disk degraded, as well. My questions about this are: 1) If we fragment large tables across these 12 dbspaces, are we gaining anything or did we defeat the fragmentation strategy by choosing to distribute the logical volumes over as many physical volumes as possible in the first place? 2) Should we have chosen less and different disks up front in smit? About indexes: How does storing them in separate dbspaces increase performance? Is it true that you should you use only non-fragmented indexes on fragmented tables? Is this because they must be reassembled im memory? FYI. I'm pretty sure 7.x allows you to choose dbspaces as you fragment by expression. Thanks again. In article <36CFFC68.15C58C67@mindspring.com>, Tim Schaefer <tschaefe@mindspring.com> wrote: > gresmi@yahoo.com wrote: > > > > I would appreciate any feedback regarding optimal design strategies for our > > Data Warehouse. Our current situation is this: We are currently building a > > Data Warehouse using Informix Dynamic Server 7.23 in an AIX 4.1.4 Unix > > environment. From what I have gathered, our operating environment does not > > include RAID, disk striping, or mirroring. Our initial deployment will be in > > the neighborhood of 50 gigs. We have used smit to create 12 4 gig logical > > volumes (chunks). These logical volumes were created using smit strategies of > > "center" for disk location and "maximum" for number of physical volumes that > > each logical volume may be spread across. The logical volumes are spread > > across up to 20 physical volumes. One dbspace was created to house each > > logical volume. > > > > I think you will need to resize your chunks into 2 GB disks instead of 4GB. > Informix can only use up to 2GB max per drive on 32bit systems. I'd also > recommend UNIX mirroring but this will cut your drive count in half. > > > At rollout, we hope to be supporting 20-30 users, initially with Metacube. > > > > Our questions are these: > > > > 1. Shoud we deploy an Informix fragmentation strategy on the larger tables, > > distributing them across several of the dbspaces? > > yes! > > > 2. Would anything be gained from a table fragmentation strategy since the > > underlying chunks themselves are already distributed across several physical > > disks? > > Yes. Chunk allocation is only that. Fragmentation is the distribution of > the data. Think of it like a loaf of bread. The loaf is sliced up, now > you need to spread something on each slice, such as peanut butter. > > > 3. Should indexes be stored in dbspaces separate from the data? > > Yes. This is recommended, and only available in 7.x, not 8.x. > > > 4. Should the indexes be fragmented? > > > > Depends on the table and the index. Keep in mind that for DW in general > indexes should be minimized and added with extreme care. They affect > everything about your data warehouse. > > > Any comments are welcome. We are new to the design phase and eager to do > > things right. Right now, we have the luxury of being able to make a change if > > needed. > > > > Thanks > > > > gresmi@yahoo.com, > > Curious why you're not using XPS ( 8.2x ). 7.x is acceptable but not really > designed for DW. You're also on one machine, instead of clustering, so you'll > get a one-server performance environment. > > One benefit to 7.x is that you get to separate your indexes into a separate > dbspace from the tables whereas in 8.x you cannot. The indexes must live in > the same dbspace as tables in XPS, but in actual practice we've been told by > Informix to avoid indexes as a rule in 8.x whenever possible. > > Any time you get an opportunity to spread your data out across as many > drives as possible you should do so. This is crucial so you don't hammer > one or two drives to death during queries. Thus the importance of spreading > things out. The bummer to 7.x also is that you won't be able to set up > dbslices, which are logical groupings of your dbspaces. These are a really > great feature of XPS, and make space allocation of dbspaces very easy. > > What would be optimal is to not only fragment wide, but deep. XPS takes > advantage of hybrid fragmentation, I don't know if 7.x does or not, but this > is a way to not only force the fragmentation by expression, but also by dbspace > at the same time. It requires a bit of architectural design, but well worth it > in the long run. If you have lots of disks fragmentation is the way to go. > > By the way, XPS is doable as a one-server setup, you should consider getting > a copy and build your DW right, from the start. You could actually set it > up with multiple coservers on one machine, and then as the DW grows migrate > it to additional servers. > > Thanks, > > Tim > > -- > - > -- > --- Tim Schaefer > ---- tschaefe@mindspring.com > --- http://www.inxutil.com > -- > - > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

gresmi@yahoo.com wrote: > > Tim, > Thanks much for your reply. If you don't mind, I'd like to extend the forum a > bit. > > Unfortunately, we haven't a choice regarding version 7.23, as to which > Informix Data Warehouse engine we use. But, from everything else I've > gathered, we can make it work for us. I'm curious about the chunk size change > from 4 gigs to 2. Is this a function of Informix 7.23, AIX, or the > communication between them? And what is the impact of not making the change? This is a 32-bit UNIX problem, as I understand it. 64-bit UNIX can use disks larger than 2GB. You are certainly welcome to create disks larger than 2 GB, which is not the problem, but you cannot create Informix chunks greater than 2GB on 32-bit UNIX, thus you'll waste disks unless they are 2GB or less in size. Make lots of 2GB disks, and fragment your data across dbspaces. > I'm pretty sure we don't have a choice about the RAID or mirroring options > either. How crucial are these strategies? RAID appears to be desirable in NT, and workable, however, as I recall there are performance problems with RAID on some systems. This may not be the case on your system, but I would think mirroring is enough without the RAID or simply RAID up to mirroring... RAID experts please jump in here. > Regarding the configuration of 1 > logical volume per dbspace, and each logical volume consisting of multiple > underlying physical volumes: When we set up the logical volumes using smit, > choosing strategies of "center" for disk location and "maximum" for number of > physical volumes that each logical volume should be spread across, we were > choosing from the the same list of available disks each time. As we > progressed through the creation of each logical volume, the number of > physical disks allocated to each decreased on each iteration. And, the best > available location on disk degraded, as well. I have not personally set up our disks with smit, relying on our system administrators to do this. We tell them what we require, they do smit. To be clear: 1. Mirror your chunks via UNIX. 2. When you create dbspaces, consider optimally "one table per dbspace". On small systems this appears to be a waste, but in the long run it's really prudent. Consider something like this: System 1 2GB disk rootdbs 1 2GB disk physdbs 1 2GB disk logsdbs 4 2GB disks tempdbs Data 1 2GB disk dbs_dbs - use to create data bases in, but not tables 1 2GB disk datadbs01 - table spaces . . . 1 2GB disk datadbs10 You should be able to use your logical volume manager to set these disks up. > My questions about this are: 1) > If we fragment large tables across these 12 dbspaces, are we gaining anything > or did we defeat the fragmentation strategy by choosing to distribute the > logical volumes over as many physical volumes as possible in the first place? Yes, depending on how you fragment, you should see good performance. I'd fragment across as many dbspaces as possible. Loading your tables without a fragmentation strategy of some kind is more critical the larger the table becomes. You don't want to hammer one disk constantly because of bad data distribution. As to the logical volumes, I'd consider balancing against your disk controllers as much as possible. > 2) Should we have chosen less and different disks up front in smit? > See above example. Configure the disk sizes to accommodate the most efficient use, balancing across controllers. > About indexes: How does storing them in separate dbspaces increase > performance? Regarding indexes, from the current discussion there may be problems associated with detached indexes, or at the very least performance degradation. To answer your question though, the Informix DSA, or Dynamic Scalable Architecture is simply about divide-and-conquer. The more you divide, the faster it supposedly becomes. Evidently this is not always the case, from what folks here have said. 8.x does not allow detached indexes currently. I get "-999: Not Implemented Yet" errors when certain features are tried. > Is it true that you should you use only non-fragmented indexes > on fragmented tables? I don't have an answer for this one, perhaps someone else has the answer. It's our experience to use little to no indexing, and instead learn how to trigger the engine to hash on the fly. There are certainly times when indexing is indicated, and bitmap indexes are available to a limited degree to allow indexing in discrete ways. > Is this because they must be reassembled im memory? > > FYI. I'm pretty sure 7.x allows you to choose dbspaces as you fragment by > expression. > > Thanks again. > No problem. :-) Thanks, Tim -- - -- --- Tim Schaefer ---- tschaefe@mindspring.com --- http://www.inxutil.com -- -