Ben:
I did a simple test on 11.50.fc6 and 11.70.fc5 for this problem.
For the test scenario I used the schema of the table my customer is using.
My client creates and uses a table similar to the schema below each month. This table stores approximately 440 million records each month.
The table consists of a round robin schema, but the unique index is not fragmented.
So the unique index of this table is nearly 15 million pages at the end of the month.
create table log_12
(
col1 varchar (50),
col2 varchar (32),
col3 varchar (5),
col4 varchar (5),
col5 varchar (10)
.....
) fragment by round robin in dbs1, dbs2, dbs3
extent size 16000000 next size 10000000;In order to save time, I executed the following unique index after inserting 500 million dummy data.
create unique index log_12_pk on log_12 (col1, col2, col3, col4, col5) in idx1;
As a result of testing in version 11.5, when the number of unique index pages reached 2^24 (16,775,134), the create index operation was rolled back with the following message in the online log.
15:22:42 partition 'demo: informix.log_12_pk': no more pages
In 11.7, the unique index was created, and 35,999,944 pages were allocated to the unique index. (oncheck -pt)
Index log_12_pk fragment partition idx1 in DBspace idx1
Physical Address 5:5
Creation date 02/14/2020 16:47:53
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 107
Number of special columns 0
Number of keys 1
Number of extents 2
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8672897
Next extent size 5420560
Number of pages allocated 35999944
Number of pages used 35705525
Number of data pages 0
Number of rows 0
Partition partnum 5242882
Partition lockid 2097217
Extents
Logical Page Physical Page Size Physical Pages
0 5:53 23999947 23999947
23999947 9:3 11999997 11999997
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Original Message:
Sent: Fri February 14, 2020 05:01 AM
From: Benjamin Thompson
Subject: What is the maximum number of pages for an index in Informix 11.70?
As the author of that blog post I had been told on more than one occasion by IBMers that storage improvements were done in 11.70 and an increase in the index page limit was one of the benefits. The way it was described it was not just a documentation update, although the change to increase the index page limit did not involve a lot of code changes. Normally I try to bust any myths or uncertainty by testing; it is not difficult to write a script to create many GBs of dummy data. However I don't have an 11.50 version around to test and prove this any more and of course when we were running 11.50 we never allowed this limits to be reached! As 11.50 isn't supported any more I am not going to invest the time but please feel free to put a comment on the blog post questioning its accuracy and I will approve the comment.
While somewhat understandable given the complexity and risk, I am disappointed to hear that the data page limitation will never be addressed. I find that this, combined with the 255 rows/page limit, greatly restrict options around page sizes and partitioning strategies. For example for large tables, partitioning by month is ruled out if a month is likely to contain more than 2^24 data pages. This reduces the utility of these features.
Furthermore if compression is used for tables the 255 rows/page limit still applies and means that 2 kB and 4 kB page sizes are the only realistic options for most tables. I would be glad to see both these limitations gone or limits raised by 2^8.
Ben.
------------------------------
Benjamin Thompson
Original Message:
Sent: Thu February 13, 2020 05:53 AM
From: Art Kagel
Subject: What is the maximum number of pages for an index in Informix 11.70?
Hi:
Yes, while data partitions have a limit of 2^24 pages index partitions are only limited by the 32bit relative page number (since index pages do not have a slot table taking up 8 bits of the page number) so 2^31-1 (
2,147,483,647).
I've spoken to the kernel engineers and the partition page limits are not likely to change. Ever.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
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.
Original Message------
Hi All,
I read a post on Informix's Index fragment page limit on Ben's blog.
https://informixdba.wordpress.com/2014/04/22/index-fragment-page-limit/
According to the Informix 12.10 knowledge center document, the maximum number of pages per index fragments is 2,147,483,647.
The date that the post was created is 2014, but the content of the Informix 11.70 version of the ibm knowledge center still does not have an index fragment page limit.
Later I will test against this limit, but does anyone know if the index has a page limit of 2,147,483,647 in 11.70?
------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix