RE: Extent size limit (again)
If you have next size for the table set at 512000 at the 16th extent. When it creates the next extent it may increase next size on the table. Informix tries to locate the first free extent at the size of next size - located next to the table if possible. If none exist that size or larger it will take the largest free extent, whatever that size is. Hope this helps MW ________________________________________ From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Octavio Gomez Sent: Thursday, 14 June 2007 11:39 a.m. To: informix-list@iiug.org Subject: RE: Extent size limit (again) I apologize for bring this question again but I'm still confused about what will happen with this matter. Let me explain again: I have an IDS 9.3 on Windows where the chunk limit size is 2 GB or 512,000 Pages of 4KB each one. In this version the engine double the extent size every 16th. I have a couple of tables with 262,144 pages in extens size, One of these tables have 12 extents of this size (262, 144 pages), and many more extents with less size. At the actual rate this table will get the 16th extent in a 8-10 months lapse , so I need to explore options before this happen. I only can create 2 Gb chunks (512,000 pages) by engine restrictions. Then, this is my question (again): If the chunk size limit is 512,000 pages, what will happen when the table reach the 16th extent of 262,144 pages? I apologize again, but I need to clarify it, If you need more information please let me know. Thanks Octavio Gomez > From: art.kagel@gmail.com > Subject: Re: Extent size limit > Date: Wed, 13 Jun 2007 20:57:54 +0000 > To: informix-list@iiug.org > > On Jun 13, 3:28 am, scottishpoet <drybur...@yahoo.com> wrote: > > On Jun 13, 1:14 am, Octavio Gomez <sogo...@hotmail.com> wrote:> Sorry, but my initial question is still on air. > > > What will happen next time the engine try to increase the extent size? > > > > > I understand can happen 3 diferents scenarios: > > > > > It will stay at same size, > > > It will adjust to chunk size (512,000 pages, or available space in chunk) or > > > It will oversize chunk and create an error situation (after read your comments I can be sure this will not happen) > > > > octavio, > > > > extent doubling does not occur every time the engine allocates an > > extent > > > > Extent size doubling is allowed only if the total number of pages > > allocated so far is at least 16 times the current next extent size. > > This is a precautionary measure to limit the exponential doubling of > > next extent size,. > > Extent doubling happens every 32 extents. When the number of extents > reaches 32, 64, 96, etc. the engine doubles the NEXT SIZE. of the > table. > > Art S. Kagel > > > What is your NEXT EXTENT size set to? (select nextsize from systables > > where tabname = "<the name of your table>" > > > > An extent is always entirely contained in a chunk; an extent cannot > > cross chunk boundaries. > > If the database server cannot find the contiguous disk space that is > > specified for the next-extent size, it searches the next chunk in the > > dbspace for contiguous space. > > > > If it can't space of an appropiate size it may give an out of space > > error > > > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ad... > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list ________________________________________ Change is good. See what's different about Windows Live Hotmail. Check it out!
On 14 Jun, 03:31, "Murray Wood" <Murray.W...@integralgroup.co.nz> wrote: > If you have next size for the table set at 512000 at the 16th extent. When it creates the next extent it may increase next size on the table. Informix tries to locate the first free extent at the size of next size - located next to the table if possible. If none exist that size or larger it will take the largest free extent, whatever that size is. > > Hope this helps > > MW > > ________________________________________ > From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] On Behalf Of Octavio Gomez > Sent: Thursday, 14 June 2007 11:39 a.m. > To: informix-l...@iiug.org > Subject: RE: Extent size limit (again) > > I apologize for bring this question again but I'm still confused about what will happen with this matter. > > Let me explain again: > > I have an IDS 9.3 on Windows where the chunk limit size is 2 GB or 512,000 Pages of 4KB each one. > > In this version the engine double the extent size every 16th. > > I have a couple of tables with 262,144 pages in extens size, One of these tables have 12 extents of this size (262, 144 pages), and many more extents with less size. > > At the actual rate this table will get the 16th extent in a 8-10 months lapse , so I need to explore options before this happen. > > I only can create 2 Gb chunks (512,000 pages) by engine restrictions. > > Then, this is my question (again): > > If the chunk size limit is 512,000 pages, what will happen when the table reach the 16th extent of 262,144 pages? > > I apologize again, but I need to clarify it, > If you need more information please let me know. > > Thanks > > Octavio Gomez > > > > > > > > From: art.ka...@gmail.com > > Subject: Re: Extent size limit > > Date: Wed, 13 Jun 2007 20:57:54 +0000 > > To: informix-l...@iiug.org > > > On Jun 13, 3:28 am, scottishpoet <drybur...@yahoo.com> wrote: > > > On Jun 13, 1:14 am, Octavio Gomez <sogo...@hotmail.com> wrote:> Sorry, but my initial question is still on air. > > > > What will happen next time the engine try to increase the extent size? > > > > > I understand can happen 3 diferents scenarios: > > > > > It will stay at same size, > > > > It will adjust to chunk size (512,000 pages, or available space in chunk) or > > > > It will oversize chunk and create an error situation (after read your comments I can be sure this will not happen) > > > > octavio, > > > > extent doubling does not occur every time the engine allocates an > > > extent > > > > Extent size doubling is allowed only if the total number of pages > > > allocated so far is at least 16 times the current next extent size. > > > This is a precautionary measure to limit the exponential doubling of > > > next extent size,. > > > Extent doubling happens every 32 extents. When the number of extents > > reaches 32, 64, 96, etc. the engine doubles the NEXT SIZE. of the > > table. > > > Art S. Kagel > > > > What is your NEXT EXTENT size set to? (select nextsize from systables > > > where tabname = "<the name of your table>" > > > > An extent is always entirely contained in a chunk; an extent cannot > > > cross chunk boundaries. > > > If the database server cannot find the contiguous disk space that is > > > specified for the next-extent size, it searches the next chunk in the > > > dbspace for contiguous space. > > > > If it can't space of an appropiate size it may give an out of space > > > error > > > >http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ad... > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list > > ________________________________________ > Change is good. See what's different about Windows Live Hotmail. Check it out!- Hide quoted text - > > - Show quoted text - It will allocate whatever free space there is in the chunk.
modify your NEXT EXTENT size to something smaller
"I have a couple of tables with 262,144 pages in extens size, One of these tables have 12 extents of this size (262, 144 pages), and many more extents with less size. " First thing I would do is some serious re organisation and some space planning unload the table and use the FIRST EXTENT and NEXT EXTENT size to allocate appropriate extents to the table and reload the table http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.perf.doc/perf189.htm?resultof=%22%4e%45%58%54%22%20%22%6e%65%78%74%22%20%22%45%58%54%45%4e%54%22%20%22%65%78%74%65%6e%74%22%20 You may get some significant performance improvements especially if you are doing table scans if you can't do the above and want to add smaller extents then you can reduce the size of the next extend to be added by modifying the sizeof the next extent using the ALTER TABLE syntax and the MODFIY NEXT SIZE option http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqls.doc/sqls135.htm#sii-02alter-17205