Informix

 View Only
  • 1.  Creating index stuck forever on an ipl thread

    Posted Tue September 17, 2024 08:50 AM

    Hi,

    IBM Informix Dynamic Server Version 11.70.FC5XE

    A CREATE INDEX statement stuck forever with 'join wait' status.
    A thread that it was waiting for was named 'ipl'. Its read/write counters were still.
    Terminating the session got nothing.
    To abort the dead wait, `onmode -kuy` was used, and from that moment the ipl thread started reading and writing actively.
    After shutting down and restarting the instance, the index is present.
    Please, what is a bug that we encountered?

    $ onstat -g ses 3148737
    <...>
    tid      name     rstcb            flags    curstk   status
    3687506  sqlexec  70000040cec9b70  ---P---  5840     join wait  3706720   -
    <...>

    $ onstat -g ath | grep 3706720
     3687506  7000003de419c88  70000040cec9b70  1    join wait  3706720      9cpu         sqlexec
     3706720  7000003b4296518  700000398b662f8  1    sleeping secs: 1       12cpu         ipl


    $ onstat -u | grep 700000398b662f8
    700000398b662f8  ---PR-D 3162564  informix -        0                0    1     56887    56320



    ------------------------------
    Sincerely,
    Dennis
    ------------------------------


  • 2.  RE: Creating index stuck forever on an ipl thread

    IBM Champion
    Posted Tue September 17, 2024 09:36 AM

    What do you have LOG_INDEX_BUILDS set to in your onconfig?

    Can you run "onstat -g rss verbose"?



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



  • 3.  RE: Creating index stuck forever on an ipl thread

    Posted Tue September 17, 2024 09:57 AM

    Mike,

    LOG_INDEX_BUILDS        1

    $ onstat -g rss verbose

    IBM Informix Dynamic Server Version 11.70.FC5XE -- On-Line -- Up 02:48:49 -- 18631552 Kbytes

    Local server type: Standard
    Index page logging status: Enabled
    Index page logging was enabled at: 2012/08/24 16:10:30
    Number of RSS servers: 0



    ------------------------------
    Sincerely,
    Dennis
    ------------------------------



  • 4.  RE: Creating index stuck forever on an ipl thread

    IBM Champion
    Posted Tue September 17, 2024 10:16 AM

    I thought that you were using RSS from your earlier postings.  If you are not, then you shouldn't need LOG_INDEX_BUILDS enabled, although it should still be fine.

    Anyway, is there anything in the Informix log around this time?  Long checkpoints (onstat -g ckp), although this info may have rolled off by now.

    I would recommend running an oncheck to check that the new index is good.



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



  • 5.  RE: Creating index stuck forever on an ipl thread

    Posted Tue September 17, 2024 11:08 AM

    Mike,

    That is a QA system, hence no replication.

    Such messages were repeating in the log:

    16:44:09  WARNING: Not enough temp space for parallel index build.
              Space required = 11577944 pages; space available = 5210842 pages.
              Partial index build started.

    Some checkpoints took longer than usual, but not that much.



    ------------------------------
    Sincerely,
    Dennis
    ------------------------------



  • 6.  RE: Creating index stuck forever on an ipl thread

    IBM Champion
    Posted Tue September 17, 2024 12:42 PM

    Well, I can't explain why the index build "hung" like it did, but I bet that temp space shortage has something to do with it.  I'd still run an oncheck against the index because you brought Informix down and then the index was there when it came back up, so I'd be a bit wary.  It is likely okay, but worth checking.

    If this was to happen again, it would be good to look for blocking threads (onstat -g BTH) and for locked and waiting mutexes (onstat -g lmx  onstat -g wmx).



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



  • 7.  RE: Creating index stuck forever on an ipl thread

    Posted Thu September 19, 2024 08:57 AM

    Mike,

    Thank you.

    BTW, `onstat -g BTH` (nor bth) is not an option in 11.70.



    ------------------------------
    Sincerely,
    Dennis
    ------------------------------



  • 8.  RE: Creating index stuck forever on an ipl thread

    Posted Wed September 18, 2024 02:10 AM
    Edited by Doug Lawry Wed September 18, 2024 02:10 AM

    Hi Dennis.

    We always have PSORT_DBTEMP set to a file system in memory:

    https://www.oninitgroup.com/faq-items/ram-disk-and-informix

    That's faster and more stable, particularly for index builds.


    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 9.  RE: Creating index stuck forever on an ipl thread

    Posted Thu September 19, 2024 03:24 AM

    I am reasonably sure this will be a bug since the IPL thread works without issue in the latest 14.10 releases.

    The IPL thread can be very slow prior to 12.10.xC6 when this was implemented.

    IT05272 INDEX CREATION WITH RSS CONNECTED IS MUCH SLOWER THAN WITHOUT

    However, not sure this is your issue. You appear to be showing that the IPL thread goes immediately to the sleep state when it wakes while another thread is waiting for it to do something, a situation which may not resolve itself without intervention.

    Possibly:

    IT12508 IPL THROTTLING ON SYSTEMS WITH LARGE LOGICAL LOG SPACE CAN SEE THE IPL THREAD YIELD FOR LONG PERIODS OF TIME

    If not these then another bug fix, possibly one without an APAR.

    I am sceptical of the suggestions that anything that optimises the index build will help because the IPL thread activates after the bulk of the index build including all sorting by scanning the logical log.

    The solution here is to upgrade I think. I am guessing this option is not open to you since your version is over 15 years old.

    Ben.



    ------------------------------
    Benjamin Thompson
    ------------------------------