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 Kbytes
session 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 1
AUTO_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=80
top
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