Informix

Expand all | Collapse all

Number of extents

  • 1.  Number of extents

    Posted Wed March 03, 2021 02:23 PM
    Dears,

    We got issue for number of chunks exceed. Is this Informix bug or Informix can not read extent if over 200 extents?

    if we move the table in other dbspace where dbspace has much space left, will solve the problem without changing the table data and schema?
     

    Error Could not insert new row into the table. :ISAM[-136]
     

    Message Log:

    03/01/21 03:36:46  WARNING: partition 'oltp:informix.cs_ptrg_trns': no more pages

     

    Cause:

                    table "cs_ptrg_trns" is big size and cannot auto pages allocation due to number of extents exceed limitation at 200.


    Kindly revert.

    Thanks
    Amit Patel

    ------------------------------
    AMIT PATEL
    ------------------------------


  • 2.  RE: Number of extents

    Posted Wed March 03, 2021 02:51 PM
    Amit:

    What version of Informix are you using? There used to be a soft limit on the number of extents a table could have which varied by pagesize and the number of attached (versus detached) indexes and "special" column (like blobs). That limit was about 240 for a 2K pagesize more for wider page dbspaces.

    However, that limit was removed in early v11.70 releases and today the number of extents is virtually unlimited (there is a hard limit of 32765 extents but with extent compression and extent size doubling there is no practical way to reach that limit before you hit the maximum pages in a partition limit of 2^24 or 16,777,215 data pages. That is more likely the limit that you have hit and the -136 error is spurious. You DO have a problem, just a different one than you think. oncheck -pT <table> will tell you the number of pages or you can look in sysmaster:sysptnhdr joined to sysmaster:systabnames for the same information. 

    If I am correct, the solutions are one or more of the following approaches (they can be used together in various combinations):
    1. move the table to a dbspace with wider pages so that it takes up fewer pages
    2. partition the table so that each partition has fewer than 2^24 pages for the forseeable future
    3. shard the table across multiple server instances
    4. purge old unneeded data
    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Number of extents

    Posted Wed March 10, 2021 02:54 AM
    Thanks Art,
           
       Currently in my company IDS 11.7 using and may in few months we will upgrade with 14.10

    We already using partition table but here each chunk size limited to 2GB only and whole DB size is around 1TB.


    If we create new dbspacess with higher size like 4K or 8 KB (current 2K only),

    Can this problem be reduced and will it impact to backup and Restoration as few dbspacess will have different page size?


    Thanks
    Amit Patel


    ------------------------------
    AMIT PATEL
    ------------------------------



  • 4.  RE: Number of extents

    Posted Wed March 10, 2021 05:41 AM
    Amit:

    There is no problem or issue with having dbspaces with different pages sizes, no. There are advantages, including:
    • Each page size gets its own buffer pool which can improve performance for busy tables and isolate them from affecting other tables.
    • Indexes perform better on wider pages so some sights have moved all indexes to 16K dbspaces which, again, isolates the index IO from data IO.
    • At checkpoint time IO threads are assigned to clean dirty data by dbspace. More dbspaces, more IO threads.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: Number of extents

    Posted Thu March 04, 2021 02:30 AM

    Hello,

    just have a look at the limits for a table fragment regarding the number of records and the number of pages.

    If one of these limits hit, then you hace to fragment or partition the table.

    Here a script to check:

    {#####################################################################
    ### Check limit for rows and pages ###################################
    #################################### gerd.kaluzinski@de.ibm.com ######
    #####################################################################}

    database sysmaster;

    set isolation to dirty read;

    select first 5
    n.tabname[1,20], dbsname[1,8] as db,
    4277659295 - h.nrows as rows_left,
    h.nrows,
    (round(100 * (4277659295 - h.nrows)/4277659295,2))::char(3) as prc_left
    from sysptnhdr h, systabnames n
    where h.partnum = n.partnum
    and n.tabname[1] != ' '
    and h.flags != -2147481599
    order by 3
    ;
    select first 5
    n.tabname[1,24], dbsname[1,12] as db,
    16777215 - h.nptotal as pages_left,
    h.nptotal,
    (round(100 * (16777215 - h.nptotal)/16777215,2))::char(3) as prc_left
    from sysptnhdr h, systabnames n
    where h.partnum = n.partnum
    and n.tabname[1] != ' '
    and h.flags != -2147481599

    order by 3
    ;



    ------------------------------
    Gerd Kaluzinski
    ------------------------------



  • 6.  RE: Number of extents

    Posted Fri March 05, 2021 03:45 AM
    Amit

    I take it you have never experienced the infamous -136 in a Production environment before. Once experienced, never forgotten.

    ------------------------------
    Kirit Rana
    ------------------------------



  • 7.  RE: Number of extents

    Posted Fri March 05, 2021 09:10 AM
    Amith,
     
    What version of informix are you running ?
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
    The current internal ZACS Informix Page can be found here: