Re: Index Fragmentation strategy

Stefan Weideneder wrote: > > Art S. Kagel wrote: > > > > Pete Smith wrote: > > > > > > Hello all > > > > > I am trying to determine the best method to resolve this situation: > > > A table is fragmented by expression across 5 dbspaces. The table has 5 > > > indexes which reside in the same spaces. From the output from oncheck > > > -pe the table has nearly 800 extents! On closer inspection this was > > > found to be due to the indexes having their own extents ( and almost as > > > many as the table ). > > > > > To avoid this situation reoccuring I would like to separate the indexes > > > from the data. > > > My indecision lies in how best to do this. > > > Should I : > > > 1. create a dbspace for each index ( there are 4 indexes), > > > 2. create a single, larger, dbspace and place all 4 indexes in it, > > > 3. create a number of dbspaces ( say 4 or 5 ) and fragment the indexes > > > across these spaces, or > > > 4. do something else I haven't thought of? > > > > Because the index extent size is calculated using the ratio of key > > length to row length whether the index is attached or detached > > (actually an attached index on a fragmented table has actually just > > been detached and fragmented on the same scheme and dbspaces as the > > table itself since 7.20) ... > > I think it started with the first inofficial 7.0 version. 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 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. > > ...the ONLY WAY to keep the indexes from > > generating lots of extents is to give each index or index fragment its > > own dbspace so that all extents compress into a single extent per > > chunk. > > This will improve performance in general but may hurt performance if > > the table and index grew in such a way that the index extents and the > > data extents they reference exhibited a high degree of locality (ie > > index pages near to data pages) and the extent size was not too large. > > This is because caching and read-ahead, both by your disk/disk array > > controllers and by Informix will be more likely to sweep up needed > > pages together with the attached indexes. In general this gain is > > outweighed by the advantages of the same kind of sweeps catching up > > related index and data pages that you may also need, but sometimes not. > > > > Art S. Kagel > > Hi Art, > > I completely agree. But I think, if we have no idea of the first > extent size of our table, what should be the size of the first > chunks for the index dbspaces ? And if we would know the correct Whatever size you like! > size for the index dbspace, then it must be possible to calculate > the correct size for the table's first extent. I think, we are 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. > 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: Extents and chunks are different concepts. A dbspace is built of pieces of disk space called "chunks". A table or index allocates space within a dbspace by grabbing some number of unused pages within one chunk of the dbspace and creating a logical entity called an "extent". The size of a new extent is determined by the table's EXTENT SIZE or NEXT SIZE for data pages and index pages for attached indexes of non-fragmented tables and by the ratio of key-length to row-length times EXTENT SIZE or NEXT SIZE for fragmented and detached indexes. An extent is defined as a set of contiguous disk pages assigned to a single table, thus if these newly allocated pages are contiguous with an existing extent such that the page address of the first page in the new extent is one greater than the last page address in the existing extent the new extent is "compressed" or appended to the existing extent. The new extent could be contiguous to an existing extent but preceed it on disk in which case it will remain a distinct extent. Now notice that if there is only one table or index fragment in a dbspace all of the disk space in an chunk will be compressed into a single extent as the table grows. There will always be at least one extent per chunk that is in use, however, since an extent cannot span chunk boundaries. If this does not clear up the issue and you still have a question about what I think I said, post again. Art S. Kagel