Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

Table reaching npused limit

  • 1.  Table reaching npused limit

    Posted Tue January 17, 2023 09:17 AM
    Hi all,

    I have a huge db here, where a central table is part of.
    We recently ran into an issue with a huge table (181 000 000 rows), reaching the 16773048 pages limit, 
    which we temporarily solved by archiving parts of the table.
    Now we were searching for other tables which might reach the limit soon.

    We found the following table.
     tabid     rowsize     ncols     nrows     tabtype     npused     fextsize     nextsize     pagesize    
     --------  ----------  --------  --------  ----------  ---------  -----------  -----------  -----------
     220       2269        157       20839369  T           11858498   20000        20000        2048

    The weird thing is: multiplying the number of rows with the rowsize would be very much
    higher then the npused value in systables.
    My assumtion would be that the (very old) table has evolved through time (by in place alters) and old rows did not reach 
    the same row length resulting in a lower page usage.

    Some time in the future, the npused will probably reach the limit. We tried to copy data to an identical table
    with different extent sizes, which did not help at all but reached the limit of max 16 mio pages very fast.
    This is probably because in the new table, all rows will consume the whole row length.
    So extent sizes do not really matter (even if the error message implies there is an extent problem), but the max for npused
    was reached.
    Since this is a WE, fragmentation is not an option.

    The choice we have from my point of view is either archiving parts of the table (deleting rows, which is not that easy,
    because the customer insists to be able to access everything),
    or putting the table to bigger dbspace (8k pages), where one page would probably be able to keep 3-4 rows
    (maybe we can shorten some rows in order to get a row size < 2k, resulting in 4 rows per page -> npused would be ~5 000 000,
    which is well below the limit).

    Can you see any other options ? 

    Thank you for your thoughts !
    MARCUS HAARMANN

    #Informix


  • 2.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 09:25 AM

    Yes, unfortunately I have to deal with this issue fairly frequently as well!  There should be an enhancement request for this to my knowledge (this and the annoying 32K limit on SQL's  that get hit if you, for example, include seven or eight large LVARCHAR 5000 columns in a report for example!).  These are just not cutting it any more in today's multi-terabyte databases that store all sorts of stuff that Informix is actually great in handling otherwise!

     

    That other option is fragmenting the table if you have that option as this limit applies to each table fragment in that case.  I had to increase the number of table fragments recently on my issue.

     

    Hal Maner

    M Systems International, Inc.

    https://www.msystemsintl.com

     

     






  • 3.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 09:37 AM

    At the end of 2023 Q2/beginning of 2023 Q3 there will be a Vnext release of Informix. One of major features that will be part of this release is to raise product limits as seen here. Not all of these limits on that page are to be raised. Assuming all development goes as planned and without delays or unforeseen issues, this pages per partition is likely to be one of the ones to be raised. The new target limit sizes are not known presently as development has not completed.

     




    Original Message


  • 4.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 01:28 PM
    Hal:

    The 32K limit on SQL statement length is long gone. It was increased to 64K around 11.10 era (and SDK v3.10) and made unlimited (well limited by server memory anyway) in 11.50 (and SDK v 3.50).d

    Yes, there is still a limit of 32K on each returned row for in-row data, so not BYTE, TEXT, BLOB, or CLOB and not LVARCHAR (which is stored in a CLOB if it is longer than 4K) if you retrieve it with smart blob functions.

    Art

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

    Original Message


  • 5.  RE: Table reaching npused limit

    Posted Wed January 18, 2023 08:59 AM
    Hal Maner said:
    > That other option is fragmenting the table if you have that option as this limit applies to each table fragment in that case.

    This is exactly the solution I used at a few places.  And I made additionally sure that the DBSpaces for the data were configured for 16K pages.  That latter setting results in more of each page being used -> less wasted space/page.

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------

    Original Message


  • 6.  RE: Table reaching npused limit

    Posted Wed January 18, 2023 09:21 AM
    Jacob:

    Unfortunately Marcus's system is Workgroup Edition so no partitioning, only a single fragment.

    Henri:

    Marcus could also use my dbcopy utility or he could set up a loopback ER between the existing table and the new one then sync them. ER will copy the data to the new table and maintain it in sync with the original until he has a maintenance window to drop the original and rename the new one.

    Art

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

    Original Message


  • 7.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 09:34 AM
    Split the table into pieces and drop a view on them ? Doesn't work in all cases but ....

    Cheers
    Paul

    On 1/17/2023 8:17 AM, Marcus Haarmann via IBM Community wrote:
    01000185c01764e9-296d0375-e93f-456c-bad1-33909468266b-000000@email.amazonses.com">
    Hi all, I have a huge db here, where a central table is part of. We recently ran into an issue with a huge table (181 000 000 rows), reaching... -posted to the "Informix" group
    IBM Community

    Informix

    Post New Message
    Table reaching npused limit
    Reply to Group Reply to Sender
    Marcus Haarmann
    Jan 17, 2023 9:17 AM
    Marcus Haarmann
    Hi all,

    I have a huge db here, where a central table is part of.
    We recently ran into an issue with a huge table (181 000 000 rows), reaching the 16773048 pages limit, 
    which we temporarily solved by archiving parts of the table.
    Now we were searching for other tables which might reach the limit soon.

    We found the following table.
     tabid     rowsize     ncols     nrows     tabtype     npused     fextsize     nextsize     pagesize    
     --------  ----------  --------  --------  ----------  ---------  -----------  -----------  -----------
     220       2269        157       20839369  T           11858498   20000        20000        2048

    The weird thing is: multiplying the number of rows with the rowsize would be very much
    higher then the npused value in systables.
    My assumtion would be that the (very old) table has evolved through time (by in place alters) and old rows did not reach 
    the same row length resulting in a lower page usage.

    Some time in the future, the npused will probably reach the limit. We tried to copy data to an identical table
    with different extent sizes, which did not help at all but reached the limit of max 16 mio pages very fast.
    This is probably because in the new table, all rows will consume the whole row length.
    So extent sizes do not really matter (even if the error message implies there is an extent problem), but the max for npused
    was reached.
    Since this is a WE, fragmentation is not an option.

    The choice we have from my point of view is either archiving parts of the table (deleting rows, which is not that easy,
    because the customer insists to be able to access everything),
    or putting the table to bigger dbspace (8k pages), where one page would probably be able to keep 3-4 rows
    (maybe we can shorten some rows in order to get a row size < 2k, resulting in 4 rows per page -> npused would be ~5 000 000,
    which is well below the limit).

    Can you see any other options ? 

    Thank you for your thoughts !
    MARCUS HAARMANN
      Reply to Group Online   View Thread   Recommend   Forward  



     
    You are subscribed to "Informix" as famouseric@gmail.com. To change your subscriptions, go to My Subscriptions. To unsubscribe from this community discussion, go to Unsubscribe. 

    --  Paul Watson Oninit www.oninit.com Tel: +1 913 364 0360 Cell: +1 913 387 7529  Oninit® is a registered trademark of Oninit LLC  If you want to improve, be content to be thought foolish and stupid Failure is not as frightening as regret





  • 8.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 09:51 AM
    Using a dbspace with a larger page size is hassle but probably the best thing to do.

    But note that you shouldn't just use rowsize to calculate the table size.  Does the table have VARCHAR columns?  The rowsize will show the maximum size of a row, and real life size will be less.

    The npused also doesn't take into account gaps in the table from deleted records.  A table may reach the maximum number of pages and you can still insert data (for a bit) until those gaps are filled.  I have used Art's script printfreeB that tells you how many unusued pages there are in the table.

    Because you hit the max pages when copying the data to another table may indeed mean that you have pending in-place alters as you suggested.  If you will be copying the data to a table in a new dbspace, then you will need to consider that the "new" table might be significantly larger.

    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 9.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 11:30 AM
    Marcus:

    The descrepancy between your calculated number of pages and the actual number of pages is most likely NOT because of IPAs but rather because the table has significant VARCHAR and LVARCHAR columns. The reported rowsize, 2269 bytes, does not even fit on a single page and would cause each row to have 2020 bytes written to a full page all by itself and the remaining 249 bytes to be written, with seven similar tails of other rows, onto a remainder page. So, if your rows were fixed length, the 20839369 rows would require 23816421 pages. Therefore your rows MUSt be variable length. The reported row size actually counts the maximum length of each variable length column as others have pointed out. 

    So, why then did the copy of the table need more pages? Well, the engine will not place a variable length row on to a page if its maximum length would not fit into the free space unless you have MAX_FILL_DATA_PAGES set to 1 to allow for variable length rows to grow after being inserted. Even if you have MAX_FILL_DATA_PAGES set, the engine will want 10% of the page to be free after inserting the new row. however, if your rows are ever updated after being inserted, then you may have had two or more smaller rows on a page that then grew such that the engine would only fit one of them onto a page in the new copy and that's ignoring rows that are bigger than a page.

    Your only choice in Workgroup Edition is to move the table onto wider pages. I would calculate the actual average and maximum length or tows in the table and use that to determine what page size dbspace will waste the least storage. For example, if you use 8K pages and all rows are maximum length (unlikely) you will be wasting about 1359 bytes per page or 453 bytes per rowand storing three rows on a page. Compage that to using 16K pages where seven maximum length rows will fit and only waste 463 bytes per page or 66 bytes per row. 

    Now you can't use those calcs because your rows are not all 2269 bytes long, so you have to do the work to figure out what page size will be ideal to minimize storage waste and allow your table to grow.

    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.








  • 10.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 03:23 PM
    Hi Art,
    thank you for your input.
    Your guess about a number of varchar fields in the table is correct. These are only filled partly, which would explain
    the lower number of consumed pages for a 2k+ rowsize. (if MAX_FILL_DATA_PAGES is set to 1)
    When looking at the real data length, there is room to reduce the column length in some cases in order to get to
    a total row size < 2k, which would be beneficial.
    As I said, the table is long-grown, it was probably initialized in an 11.70 instance and was upgraded to 14.10 in the meantime.

    But MAX_FILL_DATA_PAGES is currently set to 0 on the server, which makes me wonder why the actual data does not consume min. a page
    per row. 
    Does it make a difference if the data is copied using "insert into ... select * from" in dbaccess compared to single inserts from a JDBC based app ?
    Does the JDBC driver have any influence ? I would expect no. Otherwise we could copy the data with a JDBC app instead ....

    The parameter has been added in 11.x according to the documentation and I doubt it was ever changed on the instance. 
    I would assume this is mostly a result of inplace alter table activity resulting in this not-so-nice row length
    which probably left the older rows untouched (which have been smaller).

    We will give it a try on a restored server instance with a copy table which has some columns reduced in length 
    (to a total of < 2k per row, 10% under 2k would be better if I understand you right in order to leave room 
    for possible updates (which are rare)) on a 8k or 16k page dbspace
    and set the MAX_FILL_DATA_PAGES to 1.
    It should be easy to copy the data identically since the table has a sequential counter and a modify timestamp.
    Very old data is typically not touched at all.

    We will see where this is going, keep you informed.

    The message that the hard limits are subject to change this year probably is good news. There are a couple of things 
    we already touched in real life ...
    (number of tables in exclusive lock state, SBLOB metadata has been an issue in the past, number of extents reached max.)

    Best,
    MARCUS HAARMANN



    Original Message


  • 11.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 04:32 PM
    Marcus:

    Note that if you enable MAX_FILL_DATA_PAGES, you also must set:

    PFSC 1
    PFSC_BOOST 1

    PFSC enables a cache of pages within variable length tables that have space for more data to avoid having to search every page in the table that the bitmap pages note are not completely full for space to hold the row when a new row is added. PFSC_BOOST causes the engine to populate that cache on startup. It will delay startup for a minute or two, but it makes inserts much faster for variable length tables with MAX_FILL_DATA_PAGES set.

    It looks like you may be right that IPAs are at least partially responsible for the low number of pages in the source table. An oncheck -pT run will tell you if there are multiple versions of the table's schema out there.

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

    Original Message


  • 12.  RE: Table reaching npused limit

    Posted Tue January 17, 2023 02:01 PM

    Marcus,

     

    You could try a 16K page size, which would give you 7 records to a page and use less pages as a result and do less I/O to disk in the process. Presently, it looks like a 2269 byte table rowsize gives you more than one I/O to insert or retrieve a single page via select, update and / or delete. You are storing partial rows on a straight calculation basis as the 2020 2K usable pages is less than the table rowsize. You can't compress or fragment as it is Workgroup Edition.

     

    So a larger page size is one avenue.

     

    Another possible way is purge as present and wait for vnext then add the records back in later into the original table; this doesn't meet the availability requirements.

     

    Another possible way is to purge as present into a history table and modify your apps to pick from the history table as well for update, select and delete, perhaps via a view as previously indicated. Later, when upgrading to vnext, you can add those records back into the original table and remove the history table references from the apps, which obviously is a lot work twice.

     

    If you like, as a test, create a 16K page dbspace with enough storage space to accommodate the entire table. Create the table initially as a raw table with slightly different names for the table, indexes and constraints and load the data from the original table via fully qualified insert ...... selects. Create any indexes necessary after the load completes as detached in separate dbspaces just in case you have them attached. Update the stats on the table normally. Change the raw table back to standard at the end. Check your numbers as shown here. I think you may find your problem will be solved. In the case that this job is done on a weekend and there is no one else in the system when you do this, you could rename the original table and indexes to something else and your test table to that name and now the table you just created would be in production if everything goes right.

     

    Two caveats to this.

     

    One, if someone is in the original table while you're doing this then the data may have changed, especially if the row counts differ or the data changed but the row counts remain the same in both tables. Monitoring the ucnt field of onstat -tr 2 for the table partition is a way to monitor this situation and the number displayed there should always be 1 (you).

     

    Two,  If you have constraints on the table in question then you probably cannot do this as a one off without suspending all constraints involved with the table. Might be more time than you have time allotted to enable the constraints.

     

    My .02.

     






  • 13.  RE: Table reaching npused limit

    Posted Wed January 18, 2023 04:59 AM
    Hi Marcus,

    the best practice is to move the data fragments to larger page-size DBSpaces (ok, means copy by sql).

    Best Regards
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021 and 2022
    ------------------------------



Related Content