Fragmentation and RAID 1+0?

Hi all, I am going to be exporting and importing a table in order to get rid of an extrodinary number of extents in the table. (>213) I figure as part of this project, I might as well fragment the table as well to squeeze some more performance out of the database. The table is a very busy one, and contains approx. 3GB data (growing fast too) The hardware is a 4-way Sun E4000 with 2gb RAM and has at least 250GB of space available. In the next 3 months I will be doing this to about 8-10 other tables as well. I seek advice about how to plan this effectively. Is it better to use Veritas to stripe and mirror the disks? Or is fragmentating the tables and mirroring a better option? Or both? On this newsgroup, I have seen some posts indicating that RAID 10 and fragmentation is better, but I don't really get why yet. With fragmentation and striping, aren't you in effect striping twice? Thanks! Duff Sent via Deja.com http://www.deja.com/ Before you buy.

duffybj1@my-deja.com wrote: > > Hi all, > > I am going to be exporting and importing a table in order to get rid of > an extrodinary number of extents in the table. (>213) I figure as part > of this project, I might as well fragment the table as well to squeeze > some more performance out of the database. The table is a very busy > one, and contains approx. 3GB data (growing fast too) The hardware is a > 4-way Sun E4000 with 2gb RAM and has at least 250GB of space available. > In the next 3 months I will be doing this to about 8-10 other tables as > well. > > I seek advice about how to plan this effectively. Is it better to use > Veritas to stripe and mirror the disks? Or is fragmentating the tables > and mirroring a better option? Or both? Both. > On this newsgroup, I have seen some posts indicating that RAID 10 and > fragmentation is better, but I don't really get why yet. With > fragmentation and striping, aren't you in effect striping twice? Yes you are and in sysadmin circles if you do this with filesystems by striping multiple stripe sets together it is called 'Plaiding' though I personally thing 'Weaving' would be a more appropriate description. ;-) The technique allows you to get load balancing from the stripes (RAID10 sets) and parallelism from the fragmentation. It permits you to select a fragmentation scheme purely on consideration of query patterns with an eye to fragment elimination and parallelism with less concern for load balancing and hotspots. Art S. Kagel

I think that I have the disk strategy down, now I'm confused about developing a fragmentation strategy. While I was RTFM regarding this, I found that the Informix Performance Guide recomends using dbschema to analyze the distribution of data in the table. Here's is some output of what I get on two columns: Distribution for table.other_table_id Constructed 6/30 High Mode, 0.500 Resolution --Distribution-- ( 4 ) 1:(95446, 60 128 ) 2:(95446, 32, 713 ) 3:(63678, 14, 2070225 ) --OVERFLOW-- 1:(1777472, 103 ) 2:( 210797, 105 ) 3:( 68168, 115 ) . . . 33:( 39936, 2070208) note:the value varies wildly up and down from 3800000 to 30000 in the first field. the second field goes from 100-700 and them jumps to 2070208 Distribution for table.last_modified constructed on 6/30 Medium, 2.5 Resolution, 0/95 Confidence --DISTRIBUTION-- ( 283182751) 1: ( 474907, 359638, 287526790) . . . 40:( 474907, 475236, 331097533) --OVERFLOW-- 1: ( 160553, 305640213) What do I look for in this report to base my strategy? The manuals do not really explain what DISTRIBUTION and OVERFLOW are. From looking at the data and comparing it to the distribution, I am guessing that certain in the table_id column some data is being refrenced more often than others, making the DISTRIBUTION data 'uneven' and causing more stuff into OVERFLOW buckets. The second table (last_modifed) records timestamps. I am again guessing that because the data naturally spreads itself out, the DISTRIBUTION is nearly the same, with little OVERFLOW. Here is my first impression, yell at me if I sound like I'm on crack: the table_id column is probaly a poor choice to fragment by, since the data tends to not spread evenly. I would need to write complex expressions to balance the data. The last_modified column spreads evenly, since it is time based. But here I am concerned about clustering the most recent, most used data in one disk space. The fragmentation expression would be easy to write, though. This is driving me insane, please help! Duff In article <395CA869.BB0F27D4@bloomberg.net>, kagel@bloomberg.net wrote: > duffybj1@my-deja.com wrote: > > > > Hi all, > > > > I am going to be exporting and importing a table in order to get rid of > > an extrodinary number of extents in the table. (>213) I figure as part > > of this project, I might as well fragment the table as well to squeeze > > some more performance out of the database. The table is a very busy > > one, and contains approx. 3GB data (growing fast too) The hardware is a > > 4-way Sun E4000 with 2gb RAM and has at least 250GB of space available. > > In the next 3 months I will be doing this to about 8-10 other tables as > > well. > > > > I seek advice about how to plan this effectively. Is it better to use > > Veritas to stripe and mirror the disks? Or is fragmentating the tables > > and mirroring a better option? Or both? > > Both. > > > On this newsgroup, I have seen some posts indicating that RAID 10 and > > fragmentation is better, but I don't really get why yet. With > > fragmentation and striping, aren't you in effect striping twice? > > Yes you are and in sysadmin circles if you do this with filesystems by > striping multiple stripe sets together it is called 'Plaiding' though I > personally thing 'Weaving' would be a more appropriate description. ;-) > > The technique allows you to get load balancing from the stripes (RAID10 > sets) and parallelism from the fragmentation. It permits you to select a> fragmentation scheme purely on consideration of query patterns with an eye > to fragment elimination and parallelism with less concern for load balancing > and hotspots. > > Art S. Kagel > Sent via Deja.com http://www.deja.com/ Before you buy.

duffybj1@my-deja.com wrote: > > I think that I have the disk strategy down, now I'm confused about > developing a fragmentation strategy. While I was RTFM regarding this, I > found that the Informix Performance Guide recomends using dbschema to > analyze the distribution of data in the table. Here's is some output of > what I get on two columns: > > Distribution for table.other_table_id > Constructed 6/30 High Mode, 0.500 Resolution > --Distribution-- > > ( 4 ) > 1:(95446, 60 128 ) > 2:(95446, 32, 713 ) > 3:(63678, 14, 2070225 ) > > --OVERFLOW-- > > 1:(1777472, 103 ) > 2:( 210797, 105 ) > 3:( 68168, 115 ) > . > . > . > 33:( 39936, 2070208) > note:the value varies wildly up and down from 3800000 to 30000 in the > first field. the second field goes from 100-700 and them jumps to > 2070208 The rightmost column is the max value for the variable for that stats bucket, the first column in the main section and the first column in the OVERFLOW section are the count of rows containing keys in that bucket. Any individual key value with more than 25% of the rows in its main section bucket will become an overflow with its own single key bucket. The count in the second column of the main section is the number of key values represented in the bucket. So the distribution above says: The minimum value for the column is 4. Values below 128 represent 60 distinct values and there are 95,446 rows with values in that range. There are 32 keys between 128 and 713 and 95,446 rows with these values, etc. The key 103 has 1,777,472 rows with it as a value which would skew that stats so it became an overflow bucket. Art S. Kagel > Distribution for table.last_modified > constructed on 6/30 > Medium, 2.5 Resolution, 0/95 Confidence > > --DISTRIBUTION-- > ( 283182751) > 1: ( 474907, 359638, 287526790) > . > . > . > 40:( 474907, 475236, 331097533) > > --OVERFLOW-- > 1: ( 160553, 305640213) > > What do I look for in this report to base my strategy? The manuals do > not really explain what DISTRIBUTION and OVERFLOW are. > > From looking at the data and comparing it to the distribution, I am > guessing that certain in the table_id column some data is being > refrenced more often than others, making the DISTRIBUTION data 'uneven' > and causing more stuff into OVERFLOW buckets. > > The second table (last_modifed) records timestamps. I am again guessing > that because the data naturally spreads itself out, the DISTRIBUTION is > nearly the same, with little OVERFLOW. > > Here is my first impression, yell at me if I sound like I'm on crack: > the table_id column is probaly a poor choice to fragment by, since the > data tends to not spread evenly. I would need to write complex > expressions to balance the data. > > The last_modified column spreads evenly, since it is time based. But > here I am concerned about clustering the most recent, most used data in > one disk space. The fragmentation expression would be easy to write, > though. > > This is driving me insane, please help! > > Duff > > In article <395CA869.BB0F27D4@bloomberg.net>, > kagel@bloomberg.net wrote: > > duffybj1@my-deja.com wrote: > > > > > > Hi all, > > > > > > I am going to be exporting and importing a table in order to get rid > of > > > an extrodinary number of extents in the table. (>213) I figure as > part > > > of this project, I might as well fragment the table as well to > squeeze > > > some more performance out of the database. The table is a very busy > > > one, and contains approx. 3GB data (growing fast too) The hardware > is a > > > 4-way Sun E4000 with 2gb RAM and has at least 250GB of space > available. > > > In the next 3 months I will be doing this to about 8-10 other tables > as > > > well. > > > > > > I seek advice about how to plan this effectively. Is it better to > use > > > Veritas to stripe and mirror the disks? Or is fragmentating the > tables > > > and mirroring a better option? Or both? > > > > Both. > > > > > On this newsgroup, I have seen some posts indicating that RAID 10 > and > > > fragmentation is better, but I don't really get why yet. With > > > fragmentation and striping, aren't you in effect striping twice? > > > > Yes you are and in sysadmin circles if you do this with filesystems by > > striping multiple stripe sets together it is called 'Plaiding' though > I > > personally thing 'Weaving' would be a more appropriate description. > ;-) > > > > The technique allows you to get load balancing from the stripes > (RAID10 > > sets) and parallelism from the fragmentation. It permits you to > select a> > fragmentation scheme purely on consideration of query patterns with an > eye > > to fragment elimination and parallelism with less concern for load > balancing > > and hotspots. > > > > Art S. Kagel > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.