Creating index stuck forever on an ipl thread

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 37067203687506 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 ------------------------------

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 ------------------------------

Mike, LOG_INDEX_BUILDS 1$ onstat -g rss verbose IBM Informix Dynamic Server Version 11.70.FC5XE -- On-Line -- Up 02:48:49 -- 18631552 KbytesLocal 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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

Mike, Thank you. BTW, `onstat -g BTH` (nor bth) is not an option in 11.70. ------------------------------ Sincerely, Dennis ------------------------------