Informix

Expand all | Collapse all

temp table performance issue on IDS 14.10FC3 EE

  • 1.  temp table performance issue on IDS 14.10FC3 EE

    Posted Wed September 22, 2021 07:29 AM
    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
    ------------------------------


  • 2.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted Wed September 22, 2021 01:12 PM
    Edited by Doug Lawry Wed September 22, 2021 01:17 PM
    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
    ------------------------------


  • 3.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago
    Hi Gabor.

    Please also post "onstat -d" output so we can check whether DIRECT_IO is effective.

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



  • 4.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 29 days ago
    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
    ------------------------------



  • 5.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted Wed September 22, 2021 01:48 PM
    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
    ------------------------------



  • 6.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago

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



  • 7.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago
    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
    ------------------------------

    Attachment(s)

    txt
    onstat_g_osi.txt   1 KB 1 version
    txt
    onconfig.d530_db1.txt   88 KB 1 version
    txt
    onstat_g_ses_0.txt   17 KB 1 version
    txt
    onstat_d.txt   12 KB 1 version


  • 8.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago
    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





  • 9.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago

    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

     






  • 10.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago
    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
    ------------------------------



  • 11.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago

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



  • 12.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 30 days ago
      |   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
    ------------------------------

    Attachment(s)

    txt
    online_log.txt   8 KB 1 version


  • 13.  RE: temp table performance issue on IDS 14.10FC3 EE

    Posted 29 days ago
    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
    ------------------------------