Re: Index Fragmentation strategy

On Wed, 22 Jul 1998, ART KAGEL, BLOOMBERG/ NEW YORK wrote: > ---- Original Msg from: Stefan Weideneder <stefan@weideneder.de> At: 7/22 10:3 > > Hi Art, > > > No. All of my fragmented tables created under 7.13 with attached > > indexes do not have separate sysfragments entries for those indexes > > (the index pages are just part of the tables' extents) while the newer > > tables created since upgrading to 7.21+ there are sysfragments entries > > even for attached indexes if the table is fragmented. This was a > > significant change and caused me problems. > > I can hardly believe this. I found several fragments while I was > trying to understand, how the optimizer is using the fragmentation > expressions. I analysed the behaviour at the Informix office with > version 7.0. Odd. I'll have to look into this again to make sure I'm not talking through my hat here, but I'm pretty confident about the change. I'll keep you informed. > > I have large fragmented > > tables in their own dbspaces with some attached indexes. Since there > > is only the one table/fragment in each dbspace I never worry about the > > extent size for these tables. However, when we had to drop one of the > > attached indexes, after upgrading, and rebuild it the index begain to > > grow with multiple extents interleaved with the table's data extents and > > since the extent size was small the engine at some point would not > > expand the index complaining that it had reached the maximum number of > > extents allowed for an index. We had to drop the index, compress the > > table to recover space, change the next size for the table and rebuild > > the index again. So you see there was a change from 7.1x to 7.2x. > > > ---- > > > Only if you can guess or know how much data will be loaded into the > > table during a particular period of time for which it is best that the > > data be contiguous (tortuous language I know). For example EDGAR tells > > us that we will be getting 80-95GB of data in 1998 so I give each > > fragment of the table its own private dbspace so there will only be one > > extent per chunk as allowing THAT much data be become interleaved with > > some other table would be performance death. However, sometimes you > > just do not know in advance and must guess and reorg later. > Okay, far as I know Pete Smith is going to migrate to a new hardware > platform, and therefore he can calculate the current amount of data. > Possibly he monitored the growth of his tables and so he must be > able to calculate the correct first and next extent sizes at the moment. Yep, I just wanted to lay out the issues for Pete. > > > just talking about different terms (dbspace/extent). When the > > > first extent becomes full, Informix will automatically add an > > > additional extent. If you decided to have a seperate dbspace, > > > then you must add a new chunk to the dbspace ( yet another extent ). > > > > I have no idea what you have said?! So I present little tutorial to > > set the terms that we are constrained by the Informix docs to using: > > ... > > Thank you Art, for the description :-) But I'm working for Informix > for about 8 years as a trainer and consultant. And I like your > postings and your comments in th NG, because I know you are a > experienced user. No slight intended, and I hope none taken, I did not get your association with Informix from your address or sig. > It's just my English - possibly I'm not able to say what I mean. > Let's try it again. > > When you create your indexes in a seperate dbspace, then the dbspace > must be large enough to store the index. ( Undoubtedly correct, Yes. > isn't it ? ) Therefore it's a MUST, to know something about > the number of rows in your table and how fast your index tree > will grow. And if we talk about the future growth of the table ? OK We're on the same wavelength. And your English is fine here. > Let's assume we decided to have a dbspace size of 500MB at the > beginning for the index dbspace. If the table will grow your > dbspace might become too small and you must add an additional chunk. > This chunk will be the second extent. Truth, by definition. > My suggestion was: If you know that your index will need 500MB > > at the beginning, then you can calculate the correct first extent size > for your table, let's say 1GB ( depends on the row size and the > indexed columns ). Because Informix will automatically calculate > the first index extent size ( 500MB ), I will also have > just one extent for my index. And if the table will grow, Informix > will automatically add a new extent to my existing first extent. > Therefore I cannot understand, why I should have a seperate dbspace. Ahh! I've got it now. I agree that if you have an idea of the size and growth of your database you can certainly reduce the 'costs' of growing tables and adding extents. My point was that when you do not know, and for critical or highly active tables, dedicated dbspaces is the only way to go. There will always be tables for which any extent size less than 2GB will present unacceptable performance. And of course that last depends on how one define "acceptable performance". As I state in the article I have written for the FALL Tech Notes, you must determine your response time requirements before you can do proper capacity planning including how one structures dbspaces, what kind of disk farm one sets up and even which CPU architecture one selects. > If your suggestion would be a rule, how will you manage systems > like SAP or Baan, where you have more than 6000 tables, each > with 3 or more indexes. We can just track 2048 dbspaces ? Determine the 10 or 50 critical tables, map their growth and usage patterns, and map them to an appropriate number of dbspaces so that performance goals can be met. You know as well as I do that over 90% of the tables in Baan or SAP or PeopleSoft are lookups and that most of the remaining tables are less than active, so... > Okay, if I don't want to care about the number of extents, I > can place each table and each index into a seperate dbspace. > > But in the end I must choose a correct chunk size. In other > words: your chunk is acting as an extent. Wellllll yes that is strictly true. I'm just used to using 2GB chunks for those most active tables. I can see that I've been working with huge databases too long! I've got to get beck to the real world again. Thanks for keeping me honest and for the dialogue, together we've given Pete and anyone else with similar needs lots to think about. I will get back to that index version thing and let you know what I find. Art S. Kagel, kagel@bloomberg.com