Indexes contain multiple extents after reorg

Informix 11.50.FC6WE, HP-UX 11.31 PA-RISC. I have a process in place that periodically drops and re-creates indexes to consolidate multiple extents into a single extent. For most cases, this works fine, but there are several indexes which end up with exactly the same number of extents after the rebuild process as they had before. For example, I have a table with 2,690,628 rows, rowsize 219. Statistics have been updated, so the row count is correct. The table has npused = 299034 in systables. There are no variable length columns in this table. There is an index on a date and fixed char column, for a key length of 20. The index had 73 extents, the first of which was the largest, at 35843 - I assume this is pages (since this is an odd number ), it came from sysptnext.pe_size. Since the CREATE INDEX statement does not have a provision for specifying first/next sizes, I assume that the engine assigns these sizes based on information about the table. Based on testing, it seems that the index extent sizes are based on the table's first extent size, although it seems that next extent size can have some influence in assigning the size of subsequent index extents. I tried to influence the first extent size by doing an 'ALTER TABLE ... MODIFY EXTENT SIZE 625000;'. I chose that value by multiplying npused * 2 and rounding up a bit. The index still had 73 extents, with the first/largest still 35843. Adding up the pe_size for all of the extents gives a total of 37923. There are two chunks in this dbspace. One is completely free, other than the 3 reserved pages, with 2499997 free pages. The other has 2544688 free pages, with 303,600 free pages at the end of the chunk. Thus, either chunk should have ample space to accomodate this index as a single extent. What can I do to get the index to rebuild in a single extent?

On Fri, Nov 9, 2012 at 12:08 AM, MARK COLLINS <markc@myfastmail.com> wrote: > Informix 11.50.FC6WE, HP-UX 11.31 PA-RISC. > > I have a process in place that periodically drops and re-creates indexes to > consolidate multiple extents into a single extent. For most cases, this > works > fine, but there are several indexes which end up with exactly the same > number > of extents after the rebuild process as they had before. > > For example, I have a table with 2,690,628 rows, rowsize 219. Statistics > have > been updated, so the row count is correct. The table has npused = 299034 in > systables. There are no variable length columns in this table. There is an > index on a date and fixed char column, for a key length of 20. The index > had > 73 extents, the first of which was the largest, at 35843 - I assume this is > pages (since this is an odd number ), it came from sysptnext.pe_size. > You can get all that info easily with oncheck -pT db:table > > Since the CREATE INDEX statement does not have a provision for specifying > first/next sizes, I assume that the engine assigns these sizes based on > information about the table. Based on testing, it seems that the index > extent > sizes are based on the table's first extent size, although it seems that > next > extent size can have some influence in assigning the size of subsequent > index > extents. > > I tried to influence the first extent size by doing an 'ALTER TABLE ... > MODIFY > EXTENT SIZE 625000;'. I chose that value by multiplying npused * 2 and > rounding up a bit. The index still had 73 extents, with the first/largest > still 35843. Adding up the pe_size for all of the extents gives a total of > 37923. > > I would have to check, but the index extent size is based on the table's EXTENT SIZE and the next index extent size should be based on the table's next extent size. What is your table's NEXT SIZE? You mean you have 72 extents for a bit more than 2000 pages? That is possibly caused by a very small table's NEXT size. By the way, 11.70 allows for index extent definition, although most of the times I find this very unnecessary. > There are two chunks in this dbspace. One is completely free, other than > the 3 > reserved pages, with 2499997 free pages. The other has 2544688 free pages, > with 303,600 free pages at the end of the chunk. Thus, either chunk should > have ample space to accomodate this index as a single extent. > > What can I do to get the index to rebuild in a single extent? > Overestimate the table's extent (first) size and recreate the index or change the table's NEXT SIZE. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b67898aeef87904ce0511dd

Mark, I think that Fernando is correct. You modified the table's first extent size and not its NEXT EXTENT size. However, the index's first and next extent sizes are calculated from the table's sizes by applying the ratio of the key length to the table's rowsize. Your key is about 9% of the rowsize, so the index's calculated first extent size would be (625000 * 0.09) = 56,525K or 28,263 pages. It looks like the average next extent added to the index was between 28.888K so it looks like the table's NEXT SIZE may set to about 325 but maybe much less if many of the table's smallest extents are much smaller than 29K it is likely that the larger ones (as well as the additional 7000K in the first extent beyond the calculated size) are the result of extent compression (compressing multiple contiguous extents into a single larger one) and the table's NEXT SIZE is actually much smaller than that! When it allocates extents, the engine will allocate new extents first from the smallest block of free space equal to or larger than the requested extent. That is to prevent fragmenting larger blocks of free space and to save them for larger extent requests. This will result in a badly fragmented table or index if the next size is set too small. So, increase the table's NEXT SIZE to something like 10% of its EXTENT SIZE, also if you want a single extent for an index that has a key 9.1% of the rowsize and which needs 38000 pages you will need to set EXTENT SIZE to 832200. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 8, 2012 at 7:08 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Informix 11.50.FC6WE, HP-UX 11.31 PA-RISC. > > I have a process in place that periodically drops and re-creates indexes to > consolidate multiple extents into a single extent. For most cases, this > works > fine, but there are several indexes which end up with exactly the same > number > of extents after the rebuild process as they had before. > > For example, I have a table with 2,690,628 rows, rowsize 219. Statistics > have > been updated, so the row count is correct. The table has npused = 299034 in > systables. There are no variable length columns in this table. There is an > index on a date and fixed char column, for a key length of 20. The index > had > 73 extents, the first of which was the largest, at 35843 - I assume this is > pages (since this is an odd number ), it came from sysptnext.pe_size. > > Since the CREATE INDEX statement does not have a provision for specifying > first/next sizes, I assume that the engine assigns these sizes based on > information about the table. Based on testing, it seems that the index > extent > sizes are based on the table's first extent size, although it seems that > next > extent size can have some influence in assigning the size of subsequent > index > extents. > > I tried to influence the first extent size by doing an 'ALTER TABLE ... > MODIFY > EXTENT SIZE 625000;'. I chose that value by multiplying npused * 2 and > rounding up a bit. The index still had 73 extents, with the first/largest > still 35843. Adding up the pe_size for all of the extents gives a total of > 37923. > > There are two chunks in this dbspace. One is completely free, other than > the 3 > reserved pages, with 2499997 free pages. The other has 2544688 free pages, > with 303,600 free pages at the end of the chunk. Thus, either chunk should > have ample space to accomodate this index as a single extent. > > What can I do to get the index to rebuild in a single extent? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9399c7132ab3d04ce0603bb

Fernando, Thanks for the quick response. Yes, oncheck -pT will give that information, and I should have checked that to confirm the fact that the pe_size units were in pages. I was using the sysptnext table because this index rebuild process is actually coded in an SPL procedure, so it uses sysfragments, systables, sysptnext, etc., in calculating how many extents a given index has. The procedure only rebuilds an index if it contains more than some specified number of extents (passed as an input parameter to the procedure). The table's EXTENT SIZE is 625000, NEXT SIZE is only 16. Are you saying that NEXT SIZE somehow figures into the calculation for the index's first extent? I can see how it might have an impact on the index's NEXT extent, but I would expect the first extent to be based on the table's EXTENT SIZE. I agree, the NEXT SIZE should be sized appropriately for tables that are experiencing growth, or for tables whose indexes grow due to updates in key values. I have overestimated the table's extent size, and updated it with the ALTER TABLE MODIFY EXTENT SIZE command. The table contains 298,959 pages (oncheck -pT output), so multiplying that by 2 (2k pages), I would have 597,918kb, so I bumped it up a bit more to 625,000. Even after that, rebuilding the index does not result in the index being consolidated into a single extent, which is the ultimate goal. I know I can change the table's NEXT SIZE to reduce the number of index extents below the current number of 72, but I would still have multiple extents in the index when the goal is to have a single extent after the index rebuild.

Art, Thanks for the information. I hadn't calculated the percent of keylength vs. row size. I follow the math that you provided, it makes sense. I just can't see why the index ends up with so many extents based on that math. It may very well be that the engine assigns 28,263 pages for the first extent, and then adds some additional extents and compresses them due to their contiguous pages, resulting in a first extent that ends up at 35,843. And then, assuming that there were no additional free pages adjacent when it needed to extend the index yet again, it would then base the index's secondary extents on the table's NEXT SIZE. The part that doesn't make sense to me yet is, why does the index end up requiring almost 38,000 pages when it is less than 10% of the row size? The table is less than 300,000 used pages (312,500 allocated through EXTENT SIZE 625000), so I would expect the index to be around 30,000 pages (I rounded up, I know you came up with 28,263). I have a spreadsheet where I attempt to calculate such things, and I end up with 1 root page, 8 level-1 non-leaf pages, 520 level-2 non-leaf pages, and 37,370 leaf pages, for a total of 37,899 pages. This compares very favorably to the oncheck -pT output, which shows 36 free pages, 10 bit-map pages (my spreadsheet doesn't attempt to calculate these, but it does include slot table entries and rowids when calculating 72 keys/2k page), and 37,877 index pages, for a total of 37,923. If my spreadsheet can get that close, why doesn't the engine? And why is the total number of pages so much larger than one would expect based on the 9.13% ratio of key length / rowsize? The index actually ended up consuming 12.667% as many pages as the table, and this is immediately after being dropped and rebuilt.

Mark: Actually, I have to apologize, when you calculate the ratio of rowsize to keysize, you have to add a few piddling items onto the key (forgot that, getting old you know). You have to add 4 bytes for the row pointer (8 if the table is partitioned) plus another byte (or two I don't remember -- Fernando?) for the deleted key flag. That brings your key size up from 20 bytes to 25/26 or 33/34 depending on whether the table is partitioned or not. Assuming 26 that's 11.87% (34 bytes is 15.53%) not far off of the 12.667% you calculated. Anyway, the contiguous blocks added to the initial 28000+ page extent would be accidents that happened when later extents (say the 20th?) had run out of 16K free space blocks are started using bits of the larger block (probably that 300000 page free block you mentioned) that was used to allocate the first extent but prior to that all of the other smaller extents were allocated from the dregs left over from allocating extents for other tables and indexes from block just a wee bit bigger than needed. That's why you got fragmented. (As an aside for bystanders, this extent compression behavior causing new pages to be inserted before other existing pages is one reason why you cannot depend on the order of rows returned by the engine to match the order of insertion unless you can use an ORDER BY clause on an insertion timestamp or SERIAL/SERIAL8/BIGSERIAL/sequence column to force the ordering.) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Nov 8, 2012 at 9:02 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Art, > > Thanks for the information. I hadn't calculated the percent of keylength > vs. > row size. I follow the math that you provided, it makes sense. I just can't > see why the index ends up with so many extents based on that math. > > It may very well be that the engine assigns 28,263 pages for the first > extent, > and then adds some additional extents and compresses them due to their > contiguous pages, resulting in a first extent that ends up at 35,843. And > then, assuming that there were no additional free pages adjacent when it > needed to extend the index yet again, it would then base the index's > secondary > extents on the table's NEXT SIZE. > > The part that doesn't make sense to me yet is, why does the index end up > requiring almost 38,000 pages when it is less than 10% of the row size? The > table is less than 300,000 used pages (312,500 allocated through EXTENT > SIZE > 625000), so I would expect the index to be around 30,000 pages (I rounded > up, > I know you came up with 28,263). > > I have a spreadsheet where I attempt to calculate such things, and I end up > with 1 root page, 8 level-1 non-leaf pages, 520 level-2 non-leaf pages, and > 37,370 leaf pages, for a total of 37,899 pages. This compares very > favorably > to the oncheck -pT output, which shows 36 free pages, 10 bit-map pages (my > spreadsheet doesn't attempt to calculate these, but it does include slot > table > entries and rowids when calculating 72 keys/2k page), and 37,877 index > pages, > for a total of 37,923. If my spreadsheet can get that close, why doesn't > the > engine? And why is the total number of pages so much larger than one would > expect based on the 9.13% ratio of key length / rowsize? The index actually > ended up consuming 12.667% as many pages as the table, and this is > immediately > after being dropped and rebuilt. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93406d7bc51f304ce06b1ad

Art, Yes, those other "piddling items" were in my spreadsheet, except for the deleted key flag (I'll have to add that now). I also attempted to account for the non-leaf pages. So, including these extra bytes (and the bytes for the slot table entries), we get very close to what the index actually ended up requiring. Which is why I still don't get why the first extent is so far off from what was needed. I'm not trying to belabor the point here, I'm just trying to understand why things work the way they do. Once I understand it, I can change the code in the procedure so that it will calculate the correct first/next extent sizes and use those when it does the ALTER TABLE prior to the index rebuild. Let's go back and rework the math from your first reply. The table is not fragmented, so we'll use the 11.87% ratio. Since the table takes about 300,000 pages, we would expect the index's first extent to be about 35,610 pages. This is very close to the 35,843 that is now in my first extent, close enough that I can accept the difference as a rounding error, or possibly bit-map pages and other overhead that is not included in the 11.87%. I'm just wondering why it actually needed over 2000 more pages than that. I understand your point about the engine trying to reuse space from the smallest block of free pages, rather than grabbing from the large supply of free pages at the end of the chunk. But if the engine calculated that it needed 38,000 pages and saw that there were that many at the end of the chunk, it would use those as a single extent, right? I mean, it wouldn't ignore that huge block of free pages and grab 35,843 because it was sitting there if it knew that it really needed almost 38,000 pages, right? Could FILLFACTOR account for the discrepancy? There is no explicit FILLFACTOR for the index, nor for the table. The onconfig file has FILLFACTOR 90. If I add that to my spreadsheet, I end up with an estimated index size of 42,711 pages, which is about as far off as the current situation, just in the opposite direction. Maybe some sleep will help make this all make sense.

As I said yesterday I don't have the calculation details i my mind. The engine usually does a pretty decent job and personally I don't care if it does use a bit more than one extent... In any case fill factor has to be considered as well as something much more tricky: key uniqueness (for non unique indexes). In fact the only time where I saw a situation where the calculation was completely wrong (wasting around 1GB) was with a non unique index where most of the table had the same value for the key. On Nov 9, 2012 4:47 AM, "MARK COLLINS" <markc@myfastmail.com> wrote: > Art, > > Yes, those other "piddling items" were in my spreadsheet, except for the > deleted key flag (I'll have to add that now). I also attempted to account > for > the non-leaf pages. > > So, including these extra bytes (and the bytes for the slot table > entries), we > get very close to what the index actually ended up requiring. Which is why > I > still don't get why the first extent is so far off from what was needed. > I'm > not trying to belabor the point here, I'm just trying to understand why > things > work the way they do. Once I understand it, I can change the code in the > procedure so that it will calculate the correct first/next extent sizes and > use those when it does the ALTER TABLE prior to the index rebuild. > > Let's go back and rework the math from your first reply. The table is not > fragmented, so we'll use the 11.87% ratio. Since the table takes about > 300,000 > pages, we would expect the index's first extent to be about 35,610 pages. > This > is very close to the 35,843 that is now in my first extent, close enough > that > I can accept the difference as a rounding error, or possibly bit-map pages > and > other overhead that is not included in the 11.87%. > > I'm just wondering why it actually needed over 2000 more pages than that. > > I understand your point about the engine trying to reuse space from the > smallest block of free pages, rather than grabbing from the large supply of > free pages at the end of the chunk. But if the engine calculated that it > needed 38,000 pages and saw that there were that many at the end of the > chunk, > it would use those as a single extent, right? I mean, it wouldn't ignore > that > huge block of free pages and grab 35,843 because it was sitting there if it > knew that it really needed almost 38,000 pages, right? > > Could FILLFACTOR account for the discrepancy? There is no explicit > FILLFACTOR> for the index, nor for the table. The onconfig file has FILLFACTOR 90. If I > add that to my spreadsheet, I end up with an estimated index size of 42,711 > pages, which is about as far off as the current situation, just in the > opposite direction. > > Maybe some sleep will help make this all make sense. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54ee1c095da4f04ce0b8d8d

Fernando, I should have mentioned, the index in this case is unique. I understand how a highly duplicate index could cause the engine to over-allocate space, but at least that would cause the index to end up with a single extent, compared to what I'm seeing. Still trying to work through alternatives to get this index down to a single extent.

The calculation is documented here: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.perf.doc/ids _prf_365.htm I think that if you take all this into consideration you'll probably reach a good value. today I don't have time to match this to your case to check if there is something "wrong". But as you know, you just have to keep increasing the ALTER TABLE ... FIRST EXTENT untill you reach a value that will give you just one extent... But unless your table is static, you must change the next size. otherwise the index will keep growing and will allocate too much extents. Regards On Fri, Nov 9, 2012 at 3:02 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Fernando, > > I should have mentioned, the index in this case is unique. I understand > how a > highly duplicate index could cause the engine to over-allocate space, but > at > least that would cause the index to end up with a single extent, compared > to > what I'm seeing. > > Still trying to work through alternatives to get this index down to a > single > extent. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --485b397dd4d900b18f04ce11f6fe

Fernando, Thanks for the link. I knew I had seen it documented somewhere, but could not recall where. I will see how this calculation compares to my spreadsheet and to the results that I am seeing in the database.