temp table performance issue on IDS 14.10FC3 EE

Hi We have an issue with creating temp table on Informix 14.10FC3 EE + Red Hat 7.9 . When we are running a big query than we hang on a IO wait issue. We configured the DIRECT_IO to 5 for the direct io function and we didn't gained any performance. What are we missing? IBM Informix Dynamic Server Version 14.10.FC3 -- On-Line -- Up 01:27:21 -- 3282468 Kbytessession effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 78 userid - localpc 6064 localpc1 4677632 4623888 off Program : - tid name rstcb flags curstk status 126 sqlexec 68784dc8 ---P--- 9392 IO Wait- Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 78 V 6bb43040 4673536 53056 4393 112 78*O0 V 6bdb3040 4096 768 1 1 name free used name free used overhead 0 6656 scb 0 144 opentable 0 112328 filetable 0 16208 ru 0 608 misc 0 136 blobio 0 5096 log 0 16536 temprec 0 22688 blob 0 320 keys 0 3408 ralloc 0 4057992 gentcb 0 1616 ostcb 0 2992 sort 0 104 sqscb 0 194392 sql 0 46008 hashfiletab 0 552 osenv 0 2576 buft_buffer 0 5384 sqtcb 0 34384 fragman 0 46944 shmblklist 0 4200 sapi 0 4944 udr 0 19760 rsam_seqscan 0 17832 sqscb info scb sqscb optofc pdqpriority optcompind directives 694d51c0 6bcaa028 0 0 2 1 Part of onconfig: LOGFILES 200 LOGSIZE 30000 DYNAMIC_LOGS 2 LOGBUFF 4096 DBSPACETEMP tempdbs SBSPACETEMP NETTYPE ipcshm,1,50,CPU NETTYPE soctcp,1,250,NET LISTEN_TIMEOUT 60 MAX_INCOMPLETE_CONNECTIONS 1024 FASTPOLL 1 MULTIPROCESSOR 1 VPCLASS cpu,num=8,noage VPCLASS aio,autotune=1 VP_MEMORY_CACHE_KB 0 SINGLE_CPU_VP 0 AUTO_TUNE 1 AUTO_READAHEAD 1,1024 AUTO_LRU_TUNING 1AUTO_AIOVPS 1 AUTO_REPREPARE 1 AUTO_STAT_MODE 1 AUTO_CKPTS 1 CLEANERS 32 DIRECT_IO 5 BUFFERPOOL size=2K,extendable=1,buffers=1000000,next_buffers=2000,max_extends=16,lrus=128,lru_min_dirty=70,lru_max_dirty=80top top - 13:23:40 up 1:48, 1 user, load average: 0.07, 0.06, 0.05 Tasks: 276 total, 1 running, 275 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.3 us, 0.2 sy, 0.0 ni, 99.5 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 32727832 total, 29123716 free, 544288 used, 3059828 buff/cache KiB Swap: 16449532 total, 16449532 free, 0 used. 28985052 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1787 informix 10 -10 3550736 1.8g 1.8g S 5.0 5.8 4:40.64 oninit 1778 informix 10 -10 3547956 2.5g 2.5g S 4.6 7.9 4:28.80 oninit 1788 informix 10 -10 3550736 602760 599012 S 2.3 1.8 2:29.86 oninit 1789 informix 10 -10 3547956 296264 292536 S 2.3 0.9 2:30.21 oninit 1790 informix 10 -10 3547956 248424 244696 S 2.3 0.8 2:21.15 oninit 1791 informix 10 -10 3547956 215824 212100 S 2.3 0.7 2:19.86 oninit 1792 informix 10 -10 3547956 194872 191148 S 2.3 0.6 2:19.87 oninit 1793 informix 10 -10 3547956 196296 192572 S 2.3 0.6 2:20.35 oninit 8334 informix 20 0 162236 2480 1584 R 0.7 0.0 0:00.09 top ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------ #Informix

Hi Gabor. Set TEMPTAB_NOLOG 1 in $ONCONFIG to make sure temp tables are always put in temp dbspaces (not the default!). Have a look at temp dbspace contents to see whether it's actual temp tables or sorts/joins that is the major factor: https://www.oninitgroup.com/faq-items/dbspaces-how-to-list-temp-dbspace-contents Set PSORT_DBTEMP to use file system(s) for sorts and joins, preferably in RAM disk: https://www.oninitgroup.com/faq-items/ram-disk-and-informix Set DS_NONPDQ_QUERYMEM higher. ------------------------------ Doug Lawry Oninit Consulting ------------------------------ #Informix

plus: since 12.10.FC14 : Enhanced TEMP DBspace Resiliency Whenever you start the database server, all chunks in temporary dbspaces are recreated from scratch. These chunks can therefore be located on RAM drives if desired. This means, if your Linux has considerable ram memory free, you can put your temp chunks to /dev/shm , no physical disk will be used. ------------------------------ Cesar Martins ------------------------------

Hi Gabor. Please also post "onstat -d" output so we can check whether DIRECT_IO is effective. ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Hi Cesar. Since 12.10.FC11, in fact: https://www.ibm.com/docs/en/informix-servers/12.10?topic=overview-whats-new-in-informix#newxc11_toc However, there are some problems. Have just retested with 14.10.FC6, and: 1) The empty cooked device files have to exist. 2) DIRECT_IO isn't enabled on those chunks unless you bounce IDS again. Our article (see link in previous post) advises that temp dbspace chunks are reinstated in RAM disk from a "tar" archive after reboot before IDS is started. ------------------------------ Doug Lawry Oninit Consulting ------------------------------

If you are running VMs then look at strace for the thread, if might being 'blocked' at the OS level Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC

Found my notes for a slow temp space VM instance. Looking at the AIO VP I saw it 'blocked' at the kernel with a the process showing State D (disk sleep). An AIO VP should never block forever in some disk sleep state. The issue was an overloaded VM Cheers Paul

| view attached (4) Hi Doug I checked the server with the TEMPTAB_NOLOG=1 parameter and we have a same problem when all the temp tables in tempdbs. (I saw the issue with the temp table in a datadbs or tempdbs) I upload the onstat -g ses 0 command output (onstat_g_ses_0.txt). I uploaded the onstat -d output (onstat_d.txt). Every chunk has a direct io (D flag). I checked this earlier. The OS is running directly on the Dell r530 server. (onstat -g osi ) And here is our onconfig file: onconfig.d530_db1.txt ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Agreed all chunks are using DIRECT_IO according to "onstat -d". There are a number of things I would change, but they don't explain a persistent IO wait state. Are these chunks unmounted logical volumes, as I now suspect, which is best? On Linux, point your chunk links to the block devices directly and not pseudo raw devices which are no longer necessary and slower. If they are "cooked" files, they should be in a separate non-journaled file systems. Might be time to raise a case with IBM (as well as implementing all the above advice which will help). ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Also, add this to /etc/sysctl.conf: # Controls the maximum number of aio threads fs.aio-max-nr = 1048576 Make effective with "sysctl -p". Is there anything of interest in the Informix message log? ------------------------------ Doug Lawry Oninit Consulting ------------------------------

| view attached I didn't see any interesting thing in the log file. I attached the part of log file when the instance was started a last time. Because we configured the server for a dinamic CPU VP-s and a dinamic Extending bufferpool i think there is a normal behavor. currently the fs.aio-max-nr = 65536 I will consulting a root user about this parameter and we will try it to set up. ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Hi Gabor, I'm not sure if this detail fixes it: DIRECT_IO 0x5 You set to "5" , only.......Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 ------------------------------

Have a fixed number of CPU VPs (perhaps cores minus 1) so that you get even KAIO handling, and increase KAIO request limit as already discussed, to get rid of messages such as: 05:17:48 Dynamically added 1 cpu VP 05:17:49 ** AUTO TUNING - Added CPU VP. 05:17:51 requested number of KAIO events (5461) exceeds limit (1024). using 1024. 05:17:51 VP 13 is requesting 1024 kaio event resources.I believe the above may be the main problem. Also have a bigger initial buffer pool size (or fixed) to avoid this: 06:00:49 Performance Advisory: Unable to extend bufferpool 2K. 06:00:49 Results: Bufferpool has reached the # of extends '8' allowed. 06:00:49 Action: Increase the amount of memory the bufferpool can utilize. ------------------------------Doug Lawry Oninit Consulting ------------------------------