Re: number of extents

Hi Listers, I searched my emails for those with topics related to the number of extents. I found several relating to the maximum number of extents; however, I have not found any relating to the optimum number of extents for any table. The Informix manuals don't have recommendations. Perhaps, they resist making statements that might be proved incorrect or unreliable. So, I guess it's up to the experts who have done hands-on testing and analysis to make those kind of statements. I recall hearing or reading that a table should not have more than 8 extents without being reorganized. Is there any validity in this statement. If not, please explain what should be the criteria for determining the optimum number of extents for a table. We are using IDS 7.30.UC3 on Solaris 2.7. We are not using fragmented tables. We are using raw devices. We are not mirroring any disks. All IDS chunks and dbspaces are on one disk for each SERVER. Is dbexport/dbimport the most effective way to reorganize the data for all tables into one extent given our configuration? Thanks for your time. Regards, Denmark W. ----- Original Message ----- From: Art S. Kagel <kagel@bloomberg.net> To: <informix-list@iiug.org> Sent: Tuesday, December 28, 1999 9:25 AM Subject: Re: number of extents Neil Truby wrote: > > I found an unpleasant side-effect of ALTER TABLE ... NEXT EXTENT. the next > extent size is applied also to any detached indexes of the table, which is > almost certainly not what you want. This is understandable since the next size of the detached indexes was originally based on the table's next extent size when it was created. It would be natural to recalculate the next size for the indexes based on the new next size of the table. Question though: Was the next size of the index set to the new next size of the table or that size times the ratio of the keysize to the rowsize (as it is originally calculated)? > It wasn't what I wanted! Hey I want to be able to specify my own extent sizing for detached indexes, we can't always get what we want. Put in a feature request. If you can make a strong enough case, Menlo Park may go for it. Art S. Kagel > Neil > > Art S. Kagel wrote in message <38677613.CA69F9D3@bloomberg.net>... > >In general the fastest way to defrag a table is with: > > ALTER FRAGMENT ON TABLE <tabname>> > INIT IN <dbspacename or fragmentation clause>; > > > >You can name the same dbspace the table already resides in and if you ALTER > >the NEXT SIZE first you should get only one or two fragments afterward if > >there is sufficient contiguous space available. > > > >Art S. Kagel > > > >Alan Caldera wrote: > >> > >> It's not that it's impenetrable, it just varies due to the nature of the > table in question. All > >> tables have a single page descriptor known as a tablespace tablespace > page or partition page (also > >> partnum). Factors such as number of special (read BLOB and VARCHAR) > columns, and the number of index > >> keys can influence the maximum number of extents. Since there can be 2K > or 4K pages depending on the > >> implementation of IDS employed, this introduces further consideration. > When you hit the limit, the > >> appropriate (cannot insert new row) message will appear. My experience > has shown that large numbers > >> of extents are very bad for performance. An easy way to remedy the > problem is to run an ALTER INDEX > >> ..TO CLUSTER on the unique index for the table. Since this reorders the > table it has the pleasant > >> side effect of globbing the entire table into a neat single extent. After > careful analysis of future > >> growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should be > issued to keep the table in > >> a nice manageable number of extents. Of course, you will have to check to > make sure that the DBspace > >> has sufficient space to hold another entire copy of the table you are > reorganizing. Hope this helps. > >> > >> Alan > >> > >> Neil Truby wrote: > >> > >> > I think there is still a maximum number of extents, whose precise value > is > >> > subject to an impentetrable formula, of around 240 extents. > >> > > >> > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... > >> > >I think I remember reading somewhere that with newere versions of IDS > >> > >you did not need to worry about the number of extents. Is this > >> > >correct? I have some tables with over 100 extents and I suspect that > >> > >they are severely fragmented. I am formulating a plan for fixing this > >> > >situation as well as setting up a good data > distribution(fragmentation) > >> > >Plan. should I be concerned with the extents? > >> > > > >> > > > >> > >Sent via Deja.com http://www.deja.com/ > >> > >Before you buy.

Denmark, 8 extents for a table is just fine for IDS 7.3x and later. I think you should start to worry when you get around the 250 extents range. I remember seeing/hearing that somewhere. Sorry I couldn't provide for you an actual quote from a book or manual. Anyway, if you're going to do any reorganization, dbexport/dbimport is one way you could do it. Remember to first find out what to set initial extent and next extent to, run onstat -pt to get your table stats such as #pages allocated. The modify the dbimport script to reflect initial (based on # pages allocated) and next extent size (about 10% of initial) so as to grab all that contiguous space from the get go. Remember that when you set extent sizes, the units are in KB while the onstat reports are in units of pages. Another way of doing this would be to unload the table to a file, drop the table, recreate the table with the proper extent sizes and reload the data. This procedure works fine unless you're working with a really big table. "Denmark B. Weatherburn" wrote: > Hi Listers, > > I searched my emails for those with topics related to the number of extents. > I found several relating to the maximum number of extents; however, I have > not found any relating to the optimum number of extents for any table. > The Informix manuals don't have recommendations. Perhaps, they resist making > statements that might be proved incorrect or unreliable. > So, I guess it's up to the experts who have done hands-on testing and > analysis to make those kind of statements. > I recall hearing or reading that a table should not have more than 8 extents > without being reorganized. Is there any validity in this statement. If not, > please > explain what should be the criteria for determining the optimum number of > extents for a table. > > We are using IDS 7.30.UC3 on Solaris 2.7. We are not using fragmented > tables. We are using raw devices. We are not mirroring any disks. > All IDS chunks and dbspaces are on one disk for each SERVER. > Is dbexport/dbimport the most effective way to reorganize the data for all > tables into one extent given our configuration? > > Thanks for your time. > > Regards, > > Denmark W. > > ----- Original Message ----- > From: Art S. Kagel <kagel@bloomberg.net> > To: <informix-list@iiug.org> > Sent: Tuesday, December 28, 1999 9:25 AM > Subject: Re: number of extents > > Neil Truby wrote: > > > > I found an unpleasant side-effect of ALTER TABLE ... NEXT EXTENT. the > next > > extent size is applied also to any detached indexes of the table, which is > > almost certainly not what you want. > > This is understandable since the next size of the detached indexes was > originally based on the table's next extent size when it was created. It > would be natural to recalculate the next size for the indexes based on the > new next size of the table. Question though: Was the next size of the > index > set to the new next size of the table or that size times the ratio of the > keysize to the rowsize (as it is originally calculated)? > > > It wasn't what I wanted! > > Hey I want to be able to specify my own extent sizing for detached indexes, > we can't always get what we want. Put in a feature request. If you can > make > a strong enough case, Menlo Park may go for it. > > Art S. Kagel > > > Neil > > > > Art S. Kagel wrote in message <38677613.CA69F9D3@bloomberg.net>... > > >In general the fastest way to defrag a table is with: > > > ALTER FRAGMENT ON TABLE <tabname>> > > INIT IN <dbspacename or fragmentation clause>; > > > > > >You can name the same dbspace the table already resides in and if you > ALTER > > >the NEXT SIZE first you should get only one or two fragments afterward if > > >there is sufficient contiguous space available. > > > > > >Art S. Kagel > > > > > >Alan Caldera wrote: > > >> > > >> It's not that it's impenetrable, it just varies due to the nature of > the > > table in question. All > > >> tables have a single page descriptor known as a tablespace tablespace > > page or partition page (also > > >> partnum). Factors such as number of special (read BLOB and VARCHAR) > > columns, and the number of index > > >> keys can influence the maximum number of extents. Since there can be 2K > > or 4K pages depending on the > > >> implementation of IDS employed, this introduces further consideration. > > When you hit the limit, the > > >> appropriate (cannot insert new row) message will appear. My experience > > has shown that large numbers > > >> of extents are very bad for performance. An easy way to remedy the > > problem is to run an ALTER INDEX > > >> ..TO CLUSTER on the unique index for the table. Since this reorders the > > table it has the pleasant > > >> side effect of globbing the entire table into a neat single extent. > After > > careful analysis of future > > >> growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should > be > > issued to keep the table in > > >> a nice manageable number of extents. Of course, you will have to check > to > > make sure that the DBspace > > >> has sufficient space to hold another entire copy of the table you are > > reorganizing. Hope this helps. > > >> > > >> Alan > > >> > > >> Neil Truby wrote: > > >> > > >> > I think there is still a maximum number of extents, whose precise > value > > is > > >> > subject to an impentetrable formula, of around 240 extents. > > >> > > > >> > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... > > >> > >I think I remember reading somewhere that with newere versions of > IDS > > >> > >you did not need to worry about the number of extents. Is this > > >> > >correct? I have some tables with over 100 extents and I suspect > that > > >> > >they are severely fragmented. I am formulating a plan for fixing > this > > >> > >situation as well as setting up a good data > > distribution(fragmentation) > > >> > >Plan. should I be concerned with the extents? > > >> > > > > >> > > > > >> > >Sent via Deja.com http://www.deja.com/ > > >> > >Before you buy. -- Phillip Tien Database Administrator Whole Foods Market, Inc.

In article <3A7751EB.27738D3B@wholefoods.com>, Phillip <tienp@wholefoods.com> writes >Denmark, > >8 extents for a table is just fine for IDS 7.3x and later. I think you should >start to worry when you get around the 250 extents range. I remember We tend to hit a limit around 180-200 extents. >20 needs looking at, Try to keep <5 if possible, but for really big tables e.g. >70-80Mb this can be difficult to achieve. I normally do not worry unless large tables get >50 since they take a long time to reorganise. >70-80 is panic time! If the next extent size is too high then you can get the problem that lots of tables allocate a new extent which is hardly used and so there is no more space to allocate extents for other tables! >seeing/hearing that somewhere. Sorry I couldn't provide for you an actual quote >from a book or manual. Anyway, if you're going to do any reorganization, >dbexport/dbimport is one way you could do it. Remember to first find out what >to set initial extent and next extent to, run onstat -pt to get your table stats >such as #pages allocated. The modify the dbimport script to reflect initial >(based on # pages allocated) and next extent size (about 10% of initial) so as >to grab all that contiguous space from the get go. Remember that when you set >extent sizes, the units are in KB while the onstat reports are in units of >pages. > >Another way of doing this would be to unload the table to a file, drop the >table, recreate the table with the proper extent sizes and reload the data. >This procedure works fine unless you're working with a really big table. > >"Denmark B. Weatherburn" wrote: > >> Hi Listers, >> >> I searched my emails for those with topics related to the number of extents. >> I found several relating to the maximum number of extents; however, I have >> not found any relating to the optimum number of extents for any table. >> The Informix manuals don't have recommendations. Perhaps, they resist making >> statements that might be proved incorrect or unreliable. >> So, I guess it's up to the experts who have done hands-on testing and >> analysis to make those kind of statements. >> I recall hearing or reading that a table should not have more than 8 extents >> without being reorganized. Is there any validity in this statement. If not, >> please >> explain what should be the criteria for determining the optimum number of >> extents for a table. >> >> We are using IDS 7.30.UC3 on Solaris 2.7. We are not using fragmented >> tables. We are using raw devices. We are not mirroring any disks. >> All IDS chunks and dbspaces are on one disk for each SERVER. >> Is dbexport/dbimport the most effective way to reorganize the data for all >> tables into one extent given our configuration? >> >> Thanks for your time. >> >> Regards, >> >> Denmark W. >> >> ----- Original Message ----- >> From: Art S. Kagel <kagel@bloomberg.net> >> To: <informix-list@iiug.org> >> Sent: Tuesday, December 28, 1999 9:25 AM >> Subject: Re: number of extents >> >> Neil Truby wrote: >> > >> > I found an unpleasant side-effect of ALTER TABLE ... NEXT EXTENT. the >> next >> > extent size is applied also to any detached indexes of the table, which is >> > almost certainly not what you want. >> >> This is understandable since the next size of the detached indexes was >> originally based on the table's next extent size when it was created. It >> would be natural to recalculate the next size for the indexes based on the >> new next size of the table. Question though: Was the next size of the >> index >> set to the new next size of the table or that size times the ratio of the >> keysize to the rowsize (as it is originally calculated)? >> >> > It wasn't what I wanted! >> >> Hey I want to be able to specify my own extent sizing for detached indexes, >> we can't always get what we want. Put in a feature request. If you can >> make >> a strong enough case, Menlo Park may go for it. >> >> Art S. Kagel >> >> > Neil >> > >> > Art S. Kagel wrote in message <38677613.CA69F9D3@bloomberg.net>... >> > >In general the fastest way to defrag a table is with: >> > > ALTER FRAGMENT ON TABLE <tabname>>> > > INIT IN <dbspacename or fragmentation clause>; >> > > >> > >You can name the same dbspace the table already resides in and if you >> ALTER >> > >the NEXT SIZE first you should get only one or two fragments afterward if >> > >there is sufficient contiguous space available. >> > > >> > >Art S. Kagel >> > > >> > >Alan Caldera wrote: >> > >> >> > >> It's not that it's impenetrable, it just varies due to the nature of >> the >> > table in question. All >> > >> tables have a single page descriptor known as a tablespace tablespace >> > page or partition page (also >> > >> partnum). Factors such as number of special (read BLOB and VARCHAR) >> > columns, and the number of index >> > >> keys can influence the maximum number of extents. Since there can be 2K >> > or 4K pages depending on the >> > >> implementation of IDS employed, this introduces further consideration. >> > When you hit the limit, the >> > >> appropriate (cannot insert new row) message will appear. My experience >> > has shown that large numbers >> > >> of extents are very bad for performance. An easy way to remedy the >> > problem is to run an ALTER INDEX >> > >> ..TO CLUSTER on the unique index for the table. Since this reorders the >> > table it has the pleasant >> > >> side effect of globbing the entire table into a neat single extent. >> After >> > careful analysis of future >> > >> growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should >> be >> > issued to keep the table in >> > >> a nice manageable number of extents. Of course, you will have to check >> to >> > make sure that the DBspace >> > >> has sufficient space to hold another entire copy of the table you are >> > reorganizing. Hope this helps. >> > >> >> > >> Alan >> > >> >> > >> Neil Truby wrote: >> > >> >> > >> > I think there is still a maximum number of extents, whose precise >> value >> > is >> > >> > subject to an impentetrable formula, of around 240 extents. >> > >> > >> > >> > MATTHEW H. DEVLIN wrote in message <84040r$bhb$1@nnrp1.deja.com>... >> > >> > >I think I remember reading somewhere that with newere versions of >> IDS >> > >> > >you did not need to worry about the number of extents. Is this >> > >> > >correct? I have some tables with over 100 extents and I suspect >> that >> > >> > >they are severely fragmented. I am formulating a plan for fixing >> this >> > >> > >situation as well as setting up a good data >> > distribution(fragmentation) >> > >> > >Plan. should I be concerned with the extents? >> > >> > > >> > >> > > >> > >> > >Sent via Deja.com http://www.deja.com/ >> > >> > >Before you buy. > >-- >Phillip Tien >Database Administrator >Whole Foods Market, Inc. > > -- David Williams

David Williams (and others) wrote ... > We tend to hit a limit around 180-200 extents. >20 needs looking at, > Amen to that. I've seen actual corruption when the extents overflow. Perhaps that problem is fixed in these newer engines. Large number of extents definitely cripple performance and must be reduced. This implies you definitely need to consider table growth on a production system. I try to think of a timespan of several years. Happy guessing ;-) > If the next extent size is too high then you can get the problem that > lots of tables allocate a new extent which is hardly used and so there > is no more space to allocate extents for other tables! > Which reinforces the need for thoughtful analysis of table growth >>Anyway, if you're going to do any reorganization, >>dbexport/dbimport is one way you could do it. Noting that unless you export all databases, drop them and reload them, it's highly likely that there will be lots of holes in the chunks and thus tables may well be reloaded into a fragmented layout because it has to fill the gaps. >>Remember to first find out what >>to set initial extent and next extent to, run onstat -pt to get your table stats >>such as #pages allocated. The modify the dbimport script to reflect initial >>(based on # pages allocated) and next extent size (about 10% of initial) so as >>to grab all that contiguous space from the get go. Remember that when you set >>extent sizes, the units are in KB while the onstat reports are in units of >>pages. Don't ya hate that last point? IF you don't set the extent sizes, but you load into fresh spaces (at least, spaces without other old databases) then the tables will be loaded contiguously with one extent of whatever size is necessary. So, you have a small amount of spare to set next extent sizes. However, most growing tables will probably allocate a new extent fairly soon so you have to be quick and you'd definitely end up with at least 2 extents per growing table. When I say the tables will be loaded contiguously, let me clarify for those who haven't read this part of the manual: the rows will be loaded into the small extents as per normal, and when a new extent is allocated, IF it is right after the old extent then the two extents are glued together (coalesced), so you tend to get one large extent when you load a large table even if you don't set the extent size. I suppose the load may be a bit slower here as the engine continuously allocates little extents instead of a single great big one. But that's not the end of your troubles - you MUST set the next extent size if it will grow, especially for a large table. TIP: if you are attempting to export/import a few databases, don't attempt to load them in parallel on different sessions. The allocations will tend to conflict with each other and the extents will not be glued together because an extent from another table is in the way. >>Another way of doing this would be to unload the table to a file, drop the >>table, recreate the table with the proper extent sizes and reload the data. >>This procedure works fine unless you're working with a really big table. >> If you are working with a big table, consider using the High Performance Loader or RAW TABLEs >>> >>> I searched my emails for those with topics related to the number of extents. >>> I found several relating to the maximum number of extents; however, I have >>> not found any relating to the optimum number of extents for any table. >>> The Informix manuals don't have recommendations. Perhaps, they resist making >>> statements that might be proved incorrect or unreliable. >>> Informix have written somewhere that no more than 8 is ideal because the extent pointers can all fit on one page in the system data for the table. Once you go over 8 extents, it has to allocate another page to store the extra extent pointers, and so it takes an extra page read to get to the actual data pages. This is interesting and a good guide, but I can't say I've noticed a clunk from the machinery as the extents grow slightly over that limit. Many many extents is the real enemy of performance. I don't consider 10 or 12 a performance problem, just a developing one. >>> Hey I want to be able to specify my own extent sizing for detached indexes, >>> we can't always get what we want. Put in a feature request. If you can >>> make a strong enough case, Menlo Park may go for it. >>> >>> Art S. Kagel >>> They haven't so far, but then again, maybe my little whinge to support didn't get that far.

Hi, you read a lot about the extents in the articles before and I think you know that the optimum is a single extent. Important would be the question: Why are less extents better than more ? ... not the disk positioning: Have a look at a table with 200 Extents and a size of 2GB ( extent doubling after each 16th extent ). If the IDS must read the whole table from a disk with a disk speed of 10MB/sec it would take ( 2048MB / 10MB/sec = 204.8 seconds ) without additional disk positioning. Now assume you`ld have 200 Extents instead of a single one. We must add 200 times the average disk positioning overhead ( 8ms/seek ). ( 200 * 8ms = 1.6seconds ). I would say, ignore the disk positioning because the overhead of 1.6secs compared to the main disk read of 204.8 secs is unimportant. And if the table is very small ( <= (BUFFERS/2) ), disk positioning is less important because even if you read the table several times. The table would be cached. ... it's the internal CPU overhead: The Buffer Pool contains a configurable number of buffers, where each buffer has the size of a page. If a thread wants to access a page the thread first looks up the cache, if the page is already inside the buffer pool. BUT, in order to find out if the page is in the cache, it must know the physical page address. If the thread wants to read the page via an index, it will find the rowid pointer in the leaf nodes of the index and must calculate the phyiscal page address from the rowid ( contains only the logical page id ). The more extents a table contains, the more expensive will be the calculation. Whenever the IDS must calculate the physical page id from a logical page id it will face the problem with the extent table. ( All index pages are chained by using the logical page id ). You can reduce the amount of CPU-time if you could reduce the number of extents. But it's difficult to find an optimum. A good idea I think is to reduce the number of extents for the highly accessed tablespaces. Sure, there is an extent limit and everyone should monitor the number of extents, but noone can predict if 10 extents are really that bad. It depends on the frequency, how often the IDS accesses the extent table. To reduce the number of extents, follow the instructions of Art. S. Kagel. To find highly accessed tables after a few hours: select -- first 20 <- 7.3/9.2 and higher sum( bufreads + bufwrites ), sum(nextns) c.tabname,c.dbsname from sysmaster:sysptprof a, sysmaster:sysptnhdr b, sysmaster:systabnames c where a.partnum = b.lockid and b.lockid = c.partnum group by 3,4 order by 1 desc; Best regards, Stefan Weideneder Denmark B. Weatherburn wrote: > > Hi Listers, > > I searched my emails for those with topics related to the number of extents. > I found several relating to the maximum number of extents; however, I have > not found any relating to the optimum number of extents for any table. > The Informix manuals don't have recommendations. Perhaps, they resist making > statements that might be proved incorrect or unreliable. > So, I guess it's up to the experts who have done hands-on testing and > analysis to make those kind of statements. > I recall hearing or reading that a table should not have more than 8 extents > without being reorganized. Is there any validity in this statement. If not, > please > explain what should be the criteria for determining the optimum number of > extents for a table. > > We are using IDS 7.30.UC3 on Solaris 2.7. We are not using fragmented > tables. We are using raw devices. We are not mirroring any disks. > All IDS chunks and dbspaces are on one disk for each SERVER. > Is dbexport/dbimport the most effective way to reorganize the data for all > tables into one extent given our configuration? > > Thanks for your time. > > Regards, > > Denmark W. > > ----- Original Message ----- > From: Art S. Kagel <kagel@bloomberg.net> > To: <informix-list@iiug.org> > Sent: Tuesday, December 28, 1999 9:25 AM > Subject: Re: number of extents > > Neil Truby wrote: > > > > I found an unpleasant side-effect of ALTER TABLE ... NEXT EXTENT. the > next > > extent size is applied also to any detached indexes of the table, which is > > almost certainly not what you want. > > This is understandable since the next size of the detached indexes was > originally based on the table's next extent size when it was created. It > would be natural to recalculate the next size for the indexes based on the > new next size of the table. Question though: Was the next size of the > index > set to the new next size of the table or that size times the ratio of the > keysize to the rowsize (as it is originally calculated)? > > > It wasn't what I wanted! > > Hey I want to be able to specify my own extent sizing for detached indexes, > we can't always get what we want. Put in a feature request. If you can > make > a strong enough case, Menlo Park may go for it. > > Art S. Kagel > > > Neil > > > > Art S. Kagel wrote in message <38677613.CA69F9D3@bloomberg.net>... > > >In general the fastest way to defrag a table is with: > > > ALTER FRAGMENT ON TABLE <tabname>> > > INIT IN <dbspacename or fragmentation clause>; > > > > > >You can name the same dbspace the table already resides in and if you > ALTER > > >the NEXT SIZE first you should get only one or two fragments afterward if > > >there is sufficient contiguous space available. > > > > > >Art S. Kagel > > > > > >Alan Caldera wrote: > > >> > > >> It's not that it's impenetrable, it just varies due to the nature of > the > > table in question. All > > >> tables have a single page descriptor known as a tablespace tablespace > > page or partition page (also > > >> partnum). Factors such as number of special (read BLOB and VARCHAR) > > columns, and the number of index > > >> keys can influence the maximum number of extents. Since there can be 2K > > or 4K pages depending on the > > >> implementation of IDS employed, this introduces further consideration. > > When you hit the limit, the > > >> appropriate (cannot insert new row) message will appear. My experience > > has shown that large numbers > > >> of extents are very bad for performance. An easy way to remedy the > > problem is to run an ALTER INDEX > > >> ..TO CLUSTER on the unique index for the table. Since this reorders the > > table it has the pleasant > > >> side effect of globbing the entire table into a neat single extent. > After > > careful analysis of future > > >> growth patterns, an appropriate ALTER TABLE .. MODIFY NEXT ... should > be > > issued to keep the table in > > >> a nice manageable number of extents. Of course, you will have to check > to > > make sure that the DBspace > > >> has sufficient space to hold another entire copy of the table you are > > reorganizing. Hope this helps. > > >> > > >> Alan > > >> > > >> Neil Truby wrote: > > >> > > >> > I think there is still a maximum number of extents, whose precise > value > > is > > >> > subject to

In article <957egl$iia$1@news.xmission.com>, "Denmark B. Weatherburn" <dweatherb@btl.net> wrote: > > Hi Listers, > > I searched my emails for those with topics related to the number of extents. > I found several relating to the maximum number of extents; however, I have > not found any relating to the optimum number of extents for any table. > The Informix manuals don't have recommendations. Perhaps, they resist making > statements that might be proved incorrect or unreliable. > So, I guess it's up to the experts who have done hands-on testing and > analysis to make those kind of statements. > I recall hearing or reading that a table should not have more than 8 extents > without being reorganized. Is there any validity in this statement. If not, > please > explain what should be the criteria for determining the optimum number of > extents for a table. > [ ... ] We run over 100 servers with different sized INFORMIX instances on them. To keep the number of extents as small as posible (see Stephan Weideneder's posting, why we want it to be small) this is what we do: We record number of extents, no of pages allocated, no of pages used for all tables on all servers. From this we can estimate table growth almost exactly. If tables are reorganized, we allocate enough space, that the tables will not allocate any next extent during 6 months of time. This keeps extent contention to zero if we reorg 2 times a year, and it does not allocate too much disk space in advance and thus does not force us to buy disks which in fact would be in usage only in the far future .... We also monitor pages allocated minus pages used to uncover changes in the trend of table growth ( ... just in case :) HTH dic [ aye, that's Dic_k for those with email filters - LOL ] -- Richard Kofler debis Systemhaus Austria Vienna / Austria Sent via Deja.com http://www.deja.com/

In article <3a77a94b$1@news.iprimus.com.au>, Andrew Hamm <ahamm@sanderson.net.au> writes >Informix have written somewhere that no more than 8 is ideal because the >extent pointers can all fit on one page in the system data for the table. >Once you go over 8 extents, it has to allocate another page to store the >extra extent pointers, and so it takes an extra page read to get to the >actual data pages. This is interesting and a good guide, but I can't say This used to apply ot Online 5.x and is why oncheck included a warning for >8 extents. However under Online 7.x this information is cached (?? in the dictionary cache) and hence no extra reads are needed. (I asked this question in the Online 7.x training course I took at Informix UK, the trainer talked to Online developers in Menlo Park who checked the source for me!!). >I've noticed a clunk from the machinery as the extents grow slightly over >that limit. Many many extents is the real enemy of performance. I don't >consider 10 or 12 a performance problem, just a developing one. > >>>> Hey I want to be able to specify my own extent sizing for detached >indexes, >>>> we can't always get what we want. Put in a feature request. If you can >>>> make a strong enough case, Menlo Park may go for it. >>>> >>>> Art S. Kagel >>>> >They haven't so far, but then again, maybe my little whinge to support >didn't get that far. > > > -- David Williams

True. In the old days, the system kept a fixed table of 8 entries for the extent list. For any partitions having more than 8 extents, we had to read the extent list from disk to calculate the actual physical address. While it is still true that we have to calculate the address and having more extents causes a bit of additional arithmetic. So from a performance standpoint, it's not really a big hit to have a lot of extents. However, if you get to 100+ extents and have a lot of indexes on a table, then you might be a bit concerned. Not so much because of performance reasons, but because you might exceed the number of extents that you can have for the table. David Williams wrote: > In article <3a77a94b$1@news.iprimus.com.au>, Andrew Hamm > <ahamm@sanderson.net.au> writes > >Informix have written somewhere that no more than 8 is ideal because the > >extent pointers can all fit on one page in the system data for the table. > >Once you go over 8 extents, it has to allocate another page to store the > >extra extent pointers, and so it takes an extra page read to get to the > >actual data pages. This is interesting and a good guide, but I can't say > > This used to apply ot Online 5.x and is why oncheck included a > warning for >8 extents. However under Online 7.x this information > is cached (?? in the dictionary cache) and hence no extra reads are > needed. (I asked this question in the Online 7.x training course I > took at Informix UK, the trainer talked to Online developers in Menlo > Park who checked the source for me!!). > > >I've noticed a clunk from the machinery as the extents grow slightly over > >that limit. Many many extents is the real enemy of performance. I don't > >consider 10 or 12 a performance problem, just a developing one. > > > >>>> Hey I want to be able to specify my own extent sizing for detached > >indexes, > >>>> we can't always get what we want. Put in a feature request. If you can > >>>> make a strong enough case, Menlo Park may go for it. > >>>> > >>>> Art S. Kagel > >>>> > >They haven't so far, but then again, maybe my little whinge to support > >didn't get that far. > > > > > > > > -- > David Williams

David Williams wrote in message ... >In article <3a77a94b$1@news.iprimus.com.au>, Andrew Hamm ><ahamm@sanderson.net.au> writes >>Informix have written somewhere that no more than 8 is ideal because the >>extent pointers can all fit on one page in the system data for the table. >>Once you go over 8 extents, it has to allocate another page to store the >>extra extent pointers, and so it takes an extra page read to get to the >>actual data pages. This is interesting and a good guide, but I can't say > > > This used to apply ot Online 5.x and is why oncheck included a > warning for >8 extents. However under Online 7.x this information > is cached (?? in the dictionary cache) and hence no extra reads are > needed. (I asked this question in the Online 7.x training course I > took at Informix UK, the trainer talked to Online developers in Menlo > Park who checked the source for me!!). > Ahhh - good to know. Old reality becomes fantasy over time unless you keep learning.