IBM Support

IT02028: INSTANCE CAN GET BLOCKED IN CHECKPOINT IF MAX_FILL_DATA_PAGES SET TO 1 AND CONCURRENT INSERTS OF 'LONG' ROWS OCCUR

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

APAR status

  • Closed as program error.

Error description

  • If you have the onconfig parameter MAX_FILL_DATA_PAGES set to 1,
    Informix Server tries to fill the data pages in tables
    containing VARCHAR columns to their maximum. The instance uses
    so called 'page free space cache' (PFSC) which holds information
    about recently used data pages and an amount of free space on
    them. When a session inserts a new row, it looks into PFSC at
    first and if no page with enough space for the new row is found
    there, the session searches the bitmap pages in the table,
    looking for pages marked as semifull (0x4). If some is found,
    the session reads the page and tries to insert the new row. If
    the free space on that page is not enough, the session continues
    in searching the bitmaps and trying the next semifull data page.
    This goes until the a page with enough free space is found and
    the row inserted. This whole process is done as a part of one
    critical section.
    In case all or majority of the pages in the table are marked as
    semifull, but the free space on them is not enough for the new
    row, the session can potentially read the whole table before it
    finds the proper data page. In case there are more sessions
    doing the same type of 'long row' insert into the same table at
    the same time and they enter their critical section just before
    a checkpoint occurs, the instance can get blocked in the
    checkpoint for very long time (possibly up to tens of minutes;
    depends on the configuration and performance of your system).
    The symptoms are:
    
    - 'onstat -u' output shows several sessions in critical section,
    all but one waiting on the same buffer:
    
    Userthreads
    address          flags   sessid   user     tty      wait
    tout locks nreads   nwrites
    7000000805412c8  ---PX-- 21       informix 3        0
    0    2     42       0
    700000080542338  B--PX-- 22       informix 6
    700000012c377b0  0    2     0        0
    700000080543be0  B--PX-- 25       informix 6
    700000012c377b0  0    2     0        0
    700000080544418  B--PX-- 24       informix 6
    700000012c377b0  0    2     0        0
    700000080544c50  B--PX-- 23       informix 6
    700000012c377b0  0    2     0        0
    700000080545488  B--PX-- 26       informix 6
    700000012c377b0  0    2     0        0
    700000080545cc0  B--PX-- 27       informix 6
    700000012c377b0  0    2     0        0
    7000000805464f8  B--PX-- 28       informix 6
    700000012c377b0  0    2     0        0
    700000080546d30  B--PX-- 29       informix 6
    700000012c377b0  0    2     0        0
    700000080547568  B--PX-- 30       informix 6
    700000012c377b0  0    2     0        0
    700000080547da0  B--PX-- 31       informix 6
    700000012c377b0  0    2     0        0
    7000000805485d8  B--PX-- 32       informix 6
    700000012c377b0  0    2     0        0
    700000080548e10  B--PX-- 33       informix 6
    700000012c377b0  0    2     0        0
    700000080549648  B--PX-- 34       informix 6
    700000012c377b0  0    2     0        0
    700000080549e80  B--PX-- 35       informix 6
    700000012c377b0  0    2     0        0
    70000008054a6b8  B--PX-- 36       informix 6
    700000012c377b0  0    2     0        0
    70000008054aef0  B--PX-- 37       informix 6
    700000012c377b0  0    2     0        0
    ...
    
    - 'onstat -X' shows that the buffer everyone is waiting on holds
    a bitmap page (pgflgs 0x804)
    
    Buffers (Access)
    address          owner            flags pagenum          memaddr
    nslots pgflgs scount   waiter
    700000012c377b0  7000000805412c8  80    3:1023
    70000001f6ea000  0      804    0        700000080542338
    
    700000080544418
    
    700000080545488
    
    700000080546d30
    
    ....
    
    
    - 'onstat -g stk' for the main_loop thread shows it's in
    wait4critex() call
    - 'onstat -g ppf <partnum>' for the table into which the
    sessions are inserting will show a huge and ever growing numbers
    in bfrd and bfwrt
    
    Partition profiles
    partnum    lkrqs lkwts dlks  touts isrd  iswrt isrwt isdel bfrd
    bfwrt    seqsc rhitratio
    0x300043   90    0     0     0     0     31    0     0
    26403161 24799999 0     94
    

Local fix

  • Set the MAX_FILL_DATA_PAGES parameter to 0 and restart the
    engine. Then run 'oncheck -cD <dbname> -y' for each database
    containing tables with [N|L]VARCHAR columns. This will cause
    that all pages which don't have enough room for the maximum row
    size will be marked as full in the bitmaps.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Informix users using onconfig MAX_FILL_DATA_PAGES            *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to IDS-11.70.xC9                                      *
    ****************************************************************
    

Problem conclusion

  • Problem Fixed In IDS-11.70.xC9
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT02028

  • Reported component name

    INFORMIX SERVER

  • Reported component ID

    5725A3900

  • Reported release

    B70

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-05-26

  • Closed date

    2017-06-09

  • Last modified date

    2017-06-09

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    INFORMIX SERVER

  • Fixed component ID

    5725A3900

Applicable component levels

  • RA10 PSN

       UP

  • RA10 PSY

       UP

  • RB10 PSN

       UP

  • RB10 PSY

       UP

  • RB50 PSN

       UP

  • RB50 PSY

       UP

  • RB70 PSN

       UP

  • RB70 PSY

       UP

  • RC10 PSN

       UP

  • RC10 PSY

       UP



Document information

More support for: Informix Servers

Software version: B70

Reference #: IT02028

Modified date: 09 June 2017


Translate this page: