Re: refragging a table

Getting back to my fragmentation question. We've decided it's best to fragment on the serial token column, because of how our queries are joined. What's the best practice when doing such a thing as far as table growth. As you can see, we've run into a bit of a problem, because all of the latest tokens go into the last fragment that says "token > xxx in spacexx. so now, I have to refrag the table. Is there a way I can frag it, so that when I see the last fragment filling up, I can simply add another fragment to the table with a higher token range ? If I make the last fragment say "where token <= y and > x. then as I see the tokens approaching the value of y, I can add a new fragment ? Thanks, floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: Art S. Kagel <kagel@BLOOMBERG.NET> To: Informix List (comp.databases.informix) <informix-list@iiug.org> Sent: Tuesday, July 18, 2006 5:48:28 PM Subject: Re: refragging a table Floyd Wellershaus wrote: > Ah ok. Well that takes care of that then. I won't be checking any > further into that. We're trying to get some better query times. > Hey Art, here's one for ya. My boss is trying to save money again. He > has this bright idea that raid 5 has as good as, or almost as good as > read speed as raid 10. So he is wanting to consider putting some of our > older data that is never written to, on fragments that are attached to > raid5. > Whatchya think of that ? I know how you feel about raid5. haha. Does he LIKE his historical data? If these disks are seldom accessed how will he know if the disks have degraded? How will he know if ANY of the last <N> archives (where N is the number of back archives you keep) even has a good version of the data from before the drives began to go south? You know how I feel, while important, the whole RAID5 write penalty issue is the least of the several problems with RAID5. Just point him at the BAARF web site and let him read my paper, the papers from experts working for Oracle like Carl Millsap's paper on configuring RDBMS servers for VLDB environments and Juan Loaiza's discussion Optimal Storage configuration where he pushes the SAME strategy which is essentially RAID10, and the many comments on the members' 'why I joined' area. (www.baarf.com) Art S. Kagel > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > > email: fwellers@yahoo.com <mailto:fwellers@yahoo.com> > > Home: 703-430-0805 > > Cell: 703-477-6045 > ======================== > > http://www.one.org/ > > > ----- Original Message ---- > From: Art S. Kagel <kagel@BLOOMBERG.NET> > To: Floyd Wellershaus <fwellers@YAHOO.COM> > Cc: Yunyao (Frank) Qu <Yunyao.Qu@noaa.gov>; bozon <curtis@crowson1.com>; > informix-list@iiug.org > Sent: Tuesday, July 18, 2006 5:14:39 PM > Subject: Re: refragging a table > > Floyd Wellershaus wrote: > > Gotchya. Thanks. > > > > Hey, I was reading the old performance tuning book, and noticed they > > made mention of a supposedly EXCELLENT way of fragmenting a serial and > > or an integer field, using mod. > > > > something like mod(token, 3) = 0 in dbs1 > > mod (token 3) = 1 in dbs2 > > > > Does anyone know where I can get further info on that ? > > > <SNIP> > > It's a normal hash fragmentation scheme, that's in the Guide to SQL Syntax > manual. It does load balance and roughly evenly spread the IOs. It is > VERY > efficient for rapid data insertion from multiple clients. However, there > can be no fragment elimination so much of the savings at query time are > traded away for the insert throughput. > > Art S. Kagel > _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Floyd Wellershaus wrote: > Is there a way I can frag it, so that when I see the last fragment filling up, I can simply add another fragment to the table with a higher token range ? If I make the last fragment say "where token <= y and > x. then as I see the tokens approaching the value of y, I can add a new fragment ? Here is what I would do. I would make sure that I had one empty space after what I am currently using. Then I would check to see the first time anything is put in that empty space dbspace I would add another dbspace. You could get by with a toggle at some range in the current space like if I am over 1500001 send me an email telling me to add the next dbspace. For example: token between 1 and 1000001 token between 1000001 and 2000000 token between 2000001 and 3000000 Where your tokens are currently in the second range. This way you can write a job to page and send an email the DBA's when any token >= 2000001 to make sure you have time to get the space added.

Good point Bozon. Thanks. I'll just monitor the max(token) so I know when to add the next fragment. ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: bozon <curtis@crowson1.com> To: informix-list@iiug.org Sent: Thursday, July 20, 2006 10:35:26 AM Subject: Re: refragging a table Floyd Wellershaus wrote: > Is there a way I can frag it, so that when I see the last fragment filling up, I can simply add another fragment to the table with a higher token range ? If I make the last fragment say "where token <= y and > x. then as I see the tokens approaching the value of y, I can add a new fragment ? Here is what I would do. I would make sure that I had one empty space after what I am currently using. Then I would check to see the first time anything is put in that empty space dbspace I would add another dbspace. You could get by with a toggle at some range in the current space like if I am over 1500001 send me an email telling me to add the next dbspace. For example: token between 1 and 1000001 token between 1000001 and 2000000 token between 2000001 and 3000000 Where your tokens are currently in the second range. This way you can write a job to page and send an email the DBA's when any token >= 2000001 to make sure you have time to get the space added. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list