Maximum table size in 12.10
User asked about maximum table fragment size limits in Informix 12.10, noting documentation was harder to find than in 11.70. Experts confirmed the limit is 2^24 pages (~16.7 million pages) regardless of version, which equals 32GB for 2K pages, 64GB for 4K pages, and 256GB for 16K pages. The limit is fundamental to row addressing architecture.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Was wondering if the maximum fragment size in Informix has changed since last versions. I know in the old versions the limit was 32GB which I could verify in the limits of the 11.70 version for 2K spaces (16M pages per fragment). The limitations section does only speak about 2K pages, so does that mean 64GB for 4K, tec. Upto 256GB for 16K ? Also I was unable to find any limitations section in the documentation of version 12.10. In 11.70 this was all noted in the release notes, but in 12.10 the release nots are no longer a single document but more of a pages with links to other sections/pages. But the limitations sections seems to be gone ;-) Thx
The maximum partition size for tables (no limit for indexes) has always been 2^24 or 16,777,216 pages. On older systems that were limited to a fixed page size that was 32GB on most systems, 64GB on AIX and Windows where the default page size was 4K. Today that means that in a 16K page size dbspace a partition can be as large as 256GB. AFAIK there is no hard plan to change this because the row addressing is limited to 32bits 8 bits of which are assigned to the row's slot table offset in the page. This is also the reason why pages sizes can't exceed 16K and why index partitions are not limited to 2^24 pages (no rowids). That is a fundamental internal value treatment that would have to change in order for larger partitions to be possible. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Fri, Sep 5, 2014 at 8:55 AM, ROB PROP <r.prop@informa.nl> wrote: > Hi, > > Was wondering if the maximum fragment size in Informix has changed since > last > versions. I know in the old versions the limit was 32GB which I could > verify > in the limits of the 11.70 version for 2K spaces (16M pages per fragment). > The limitations section does only speak about 2K pages, so does that mean > 64GB > for 4K, tec. Upto 256GB for 16K ? > > Also I was unable to find any limitations section in the documentation of > version 12.10. In 11.70 this was all noted in the release notes, but in > 12.10 > the release nots are no longer a single document but more of a pages with > links to other sections/pages. But the limitations sections seems to be > gone > ;-) > > Thx > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c221ae27d4ee0502514c06
This is clearly documented in 12.10: http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/id s_adr_0721.htm?lang=en It's stated in terms of pages. So you can calculate for different page size (because the limit is related to page addressing as Art mentioned) and not with the effective size of the fragment. Regards. On Fri, Sep 5, 2014 at 2:18 PM, Art Kagel <art.kagel@gmail.com> wrote: > The maximum partition size for tables (no limit for indexes) has always > been 2^24 or 16,777,216 pages. On older systems that were limited to a > fixed page size that was 32GB on most systems, 64GB on AIX and Windows > where the default page size was 4K. Today that means that in a 16K page > size dbspace a partition can be as large as 256GB. AFAIK there is no hard > plan to change this because the row addressing is limited to 32bits 8 bits > of which are assigned to the row's slot table offset in the page. This is > also the reason why pages sizes can't exceed 16K and why index partitions > are not limited to 2^24 pages (no rowids). > > That is a fundamental internal value treatment that would have to change in > order for larger partitions to be possible. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on 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 Fri, Sep 5, 2014 at 8:55 AM, ROB PROP <r.prop@informa.nl> wrote: > > > Hi, > > > > Was wondering if the maximum fragment size in Informix has changed since > > last > > versions. I know in the old versions the limit was 32GB which I could > > verify > > in the limits of the 11.70 version for 2K spaces (16M pages per > fragment). > > The limitations section does only speak about 2K pages, so does that mean > > 64GB > > for 4K, tec. Upto 256GB for 16K ? > > > > Also I was unable to find any limitations section in the documentation of > > version 12.10. In 11.70 this was all noted in the release notes, but in > > 12.10 > > the release nots are no longer a single document but more of a pages with > > links to other sections/pages. But the limitations sections seems to be > > gone > > ;-) > > > > Thx > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c221ae27d4ee0502514c06 > > > > ******************************************************************************* > 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... --001a11c2d6768a4ec5050251f086
Fernando, I think that on that limits page the entry for "Data bytes per fragment" is incorrect. It assumes a 2K page. On a 16K page the databytes per fragment is ~255GB not 31GB. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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 Fri, Sep 5, 2014 at 10:04 AM, Fernando Nunes <domusonline@gmail.com> wrote: > This is clearly documented in 12.10: > > > http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/id s_adr_0721.htm?lang=en > > It's stated in terms of pages. So you can calculate for different page size > (because the limit is related to page addressing as Art mentioned) and not > with the effective size of the fragment. > > Regards. > > On Fri, Sep 5, 2014 at 2:18 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > The maximum partition size for tables (no limit for indexes) has always > > been 2^24 or 16,777,216 pages. On older systems that were limited to a > > fixed page size that was 32GB on most systems, 64GB on AIX and Windows > > where the default page size was 4K. Today that means that in a 16K page > > size dbspace a partition can be as large as 256GB. AFAIK there is no hard > > plan to change this because the row addressing is limited to 32bits 8 > bits > > of which are assigned to the row's slot table offset in the page. This is > > also the reason why pages sizes can't exceed 16K and why index partitions > > are not limited to 2^24 pages (no rowids). > > > > That is a fundamental internal value treatment that would have to change > in > > order for larger partitions to be possible. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on 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 Fri, Sep 5, 2014 at 8:55 AM, ROB PROP <r.prop@informa.nl> wrote: > > > > > Hi, > > > > > > Was wondering if the maximum fragment size in Informix has changed > since > > > last > > > versions. I know in the old versions the limit was 32GB which I could > > > verify > > > in the limits of the 11.70 version for 2K spaces (16M pages per > > fragment). > > > The limitations section does only speak about 2K pages, so does that > mean > > > 64GB > > > for 4K, tec. Upto 256GB for 16K ? > > > > > > Also I was unable to find any limitations section in the documentation > of > > > version 12.10. In 11.70 this was all noted in the release notes, but in > > > 12.10 > > > the release nots are no longer a single document but more of a pages > with > > > links to other sections/pages. But the limitations sections seems to be > > > gone > > > ;-) > > > > > > Thx > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a11c221ae27d4ee0502514c06 > > > > > > > > > > ******************************************************************************* > > 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... > > --001a11c2d6768a4ec5050251f086 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133c96665a2f00502521c59
Hmmmm.... true... I agree... I didn't notice the limit in terms of space was there. I'll forward this internally... Thanks! On Fri, Sep 5, 2014 at 3:16 PM, Art Kagel <art.kagel@gmail.com> wrote: > Fernando, I think that on that limits page the entry for "Data bytes per > fragment" is incorrect. It assumes a 2K page. On a 16K page the databytes > per fragment is ~255GB not 31GB. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on 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 Fri, Sep 5, 2014 at 10:04 AM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > This is clearly documented in 12.10: > > > > > > > > http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/id s_adr_0721.htm?lang=en > > > > It's stated in terms of pages. So you can calculate for different page > size > > (because the limit is related to page addressing as Art mentioned) and > not > > with the effective size of the fragment. > > > > Regards. > > > > On Fri, Sep 5, 2014 at 2:18 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > The maximum partition size for tables (no limit for indexes) has always > > > been 2^24 or 16,777,216 pages. On older systems that were limited to a > > > fixed page size that was 32GB on most systems, 64GB on AIX and Windows > > > where the default page size was 4K. Today that means that in a 16K page > > > size dbspace a partition can be as large as 256GB. AFAIK there is no > hard > > > plan to change this because the row addressing is limited to 32bits 8 > > bits > > > of which are assigned to the row's slot table offset in the page. This > is > > > also the reason why pages sizes can't exceed 16K and why index > partitions > > > are not limited to 2^24 pages (no rowids). > > > > > > That is a fundamental internal value treatment that would have to > change > > in > > > order for larger partitions to be possible. > > > > > > Art > > > > > > Art S. Kagel, Principal Consultant > > > ASK Database Management > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > opinions > > > and do not reflect on 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 Fri, Sep 5, 2014 at 8:55 AM, ROB PROP <r.prop@informa.nl> wrote: > > > > > > > Hi, > > > > > > > > Was wondering if the maximum fragment size in Informix has changed > > since > > > > last > > > > versions. I know in the old versions the limit was 32GB which I could > > > > verify > > > > in the limits of the 11.70 version for 2K spaces (16M pages per > > > fragment). > > > > The limitations section does only speak about 2K pages, so does that > > mean > > > > 64GB > > > > for 4K, tec. Upto 256GB for 16K ? > > > > > > > > Also I was unable to find any limitations section in the > documentation > > of > > > > version 12.10. In 11.70 this was all noted in the release notes, but > in > > > > 12.10 > > > > the release nots are no longer a single document but more of a pages > > with > > > > links to other sections/pages. But the limitations sections seems to > be > > > > gone > > > > ;-) > > > > > > > > Thx > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a11c221ae27d4ee0502514c06 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > 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... > > > > --001a11c2d6768a4ec5050251f086 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1133c96665a2f00502521c59 > > > > ******************************************************************************* > 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... --bcaec51869dc19421f05025236bb
A bug was opened thanks to Jasna Krmpotic who as always was very quick to answer. Regards On Fri, Sep 5, 2014 at 3:23 PM, Fernando Nunes <domusonline@gmail.com> wrote: > Hmmmm.... true... I agree... I didn't notice the limit in terms of space > was there. I'll forward this internally... > Thanks! > > On Fri, Sep 5, 2014 at 3:16 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Fernando, I think that on that limits page the entry for "Data bytes per > > fragment" is incorrect. It assumes a 2K page. On a 16K page the databytes > > per fragment is ~255GB not 31GB. > > > > Art > > > > Art S. Kagel, Principal Consultant > > ASK Database Management > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on 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 Fri, Sep 5, 2014 at 10:04 AM, Fernando Nunes <domusonline@gmail.com> > > wrote: > > > > > This is clearly documented in 12.10: > > > > > > > > > > > > > > > http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/id s_adr_0721.htm?lang=en > > > > > > It's stated in terms of pages. So you can calculate for different page > > size > > > (because the limit is related to page addressing as Art mentioned) and > > not > > > with the effective size of the fragment. > > > > > > Regards. > > > > > > On Fri, Sep 5, 2014 at 2:18 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > > > The maximum partition size for tables (no limit for indexes) has > always > > > > been 2^24 or 16,777,216 pages. On older systems that were limited to > a > > > > fixed page size that was 32GB on most systems, 64GB on AIX and > Windows > > > > where the default page size was 4K. Today that means that in a 16K > page > > > > size dbspace a partition can be as large as 256GB. AFAIK there is no > > hard > > > > plan to change this because the row addressing is limited to 32bits 8 > > > bits > > > > of which are assigned to the row's slot table offset in the page. > This > > is > > > > also the reason why pages sizes can't exceed 16K and why index > > partitions > > > > are not limited to 2^24 pages (no rowids). > > > > > > > > That is a fundamental internal value treatment that would have to > > change > > > in > > > > order for larger partitions to be possible. > > > > > > > > Art > > > > > > > > Art S. Kagel, Principal Consultant > > > > ASK Database Management > > > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions > > > > and do not reflect on 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 Fri, Sep 5, 2014 at 8:55 AM, ROB PROP <r.prop@informa.nl> wrote: > > > > > > > > > Hi, > > > > > > > > > > Was wondering if the maximum fragment size in Informix has changed > > > since > > > > > last > > > > > versions. I know in the old versions the limit was 32GB which I > could > > > > > verify > > > > > in the limits of the 11.70 version for 2K spaces (16M pages per > > > > fragment). > > > > > The limitations section does only speak about 2K pages, so does > that > > > mean > > > > > 64GB > > > > > for 4K, tec. Upto 256GB for 16K ? > > > > > > > > > > Also I was unable to find any limitations section in the > > documentation > > > of > > > > > version 12.10. In 11.70 this was all noted in the release notes, > but > > in > > > > > 12.10 > > > > > the release nots are no longer a single document but more of a > pages > > > with > > > > > links to other sections/pages. But the limitations sections seems > to > > be > > > > > gone > > > > > ;-) > > > > > > > > > > Thx > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > > > > > --001a11c221ae27d4ee0502514c06 > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > 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... > > > > > > --001a11c2d6768a4ec5050251f086 > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1133c96665a2f00502521c59 > > > > > > > > > > ******************************************************************************* > > 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... > > --bcaec51869dc19421f05025236bb > > > > ******************************************************************************* > 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... --047d7b10c847092bb30502558f79