Informix

Expand all | Collapse all

Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

  • 1.  Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 12 days ago
      |   view attached

    Hi erveryone, i try to migrate from an Windows Server 2016 Informix 12.10 installation to an Ubuntu 20.04 LTS with Informix 14.10.FC5.
    Both servers run in an virtual machine on the same host. Both servers have configured 2 cores, the older Windows Server has only 3GB of RAM.

    The Ubuntu Server has 8GB of RAM. Both of the Servers use the innovator-C edition license.

    I was able to finde out how to use dbexport an dpimport to get the data from one server to the other, so i managed to test both servers with the exact same data.

    I tryed a lot of onconfig changes buth the result was always the same. The older Windows with 12.10 is faster. Always. I have an test query that is relativly complex. The Windows machine takes for this query on the first run 6.2s and on the second run 5.2s on the Ubuntu with 14.10.FC5 the first run took 7.3s and the second 7.2s.

    I played al lot with the settings and tryed to configure the Ubuntu onconfig the same as on the Windows. But the results stay the same or getting even worse. At the beginning the query took about 14s on the Ubuntu. So the best result i am able to produce are the 7.3s on the first run.

    The test query is an read only sql.

    How can this be? Are there people having the same problem? What can i try?

    Maybe if someone can take a look at the onconfig files and tell me what i did wrong?

    After weeks of trying i am close to giving up and let the 12.10 keep running on.

    Thank everybody for help or an hint.

    Kind regards



    ------------------------------
    M D
    ------------------------------

    Attachment(s)

    7z
    onconfig.7z   20 KB 1 version


  • 2.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 12 days ago

    Hard to tell without looking at the details, one simple test would be to run something close to the following sequence with both (otherwise idle!) 12.10 and 14.10:

    onstat -z

    sleep 2

    ((run query))

    sleep 2

    onstat -p

    onstat -p will give the server side of the story, in terms of CPU seconds needed to execute the query.

    If, indeed, onstat -p for the 14.10 indicates that server needed more time to execute the same query - it'll be possible to investigate further (language settings, query plan, number of pages involved in query execution, etc, etc)

    If the time reported by onstat -p is about same for 12.10 and 14.10 (and assuming that VM really uses same CPU ) then most likely cause for the difference is the client (dbaccess?) In 14.10 dbaccess is known to always check the edition type (that will be fixed in upcoming 14.10.xC6), and another likely cause is usually name resolution.

    To increase the precision of experiment - test can be modified by replacing single execution with 

    ((run query)) x N

    however 5 to 7 seconds should be enough to see the difference in onstat -p, if the issue is on the server side.

    Hope it helps.



    ------------------------------
    Vladimir Kolobrodov
    ------------------------------



  • 3.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 12 days ago
    M D:

    This is a tough one. I would suggest several things to look at:

    • SET EXPLAIN for the test query on both to see if the query plans are the same.
    • Did you run update statistics on the target server after the import? If the database is small, just run:
      UPDATE STATISTICS HIGH;
    • What are you using for storage in Win and on Linux? RAW? COOKED?
    • Post the ONCONFIG files (or email them to me if you want) and we'll look them over
    • Clear stats on both servers (onstat -z) then run your test and post the output from onstat -g ioh from both.
    • Post output from onstat -g iov after the test runs on both.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 4.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago
    Sounding interesting, indeed, and you shouldn't have waited weeks before raising this, and have raised a support case ... which we'd then possibly be able to work even in local language ;-)

    Windows:
    BUFFERPOOL size=4k,memory=500MB
    VPCLASS cpu,num=1,noage

    Linux:
    BUFFERPOOL size=2k,buffers=500000,lrus=8,lru_min_dirty=50,lru_max_dirty=60
    BUFFERPOOL size=8K,buffers=250000,lrus=8,lru_min_dirty=50,lru_max_dirty=60
    VPCLASS cpu,num=2,noage

    So the Linux one even seems to be better equipped in terms of resources configured - if that even matters to your problem.

    What's missing on Linux side is a DBSPACETEMP configuration, at least from onconfig's perspective.

    Beyond that, we'd really have a lot more details on the test query (only this one having this problem?) and its runtime behavior.

    BR,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 5.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago

    To be strict at comparison, you should set the same amount of shared memory and same amount of cpu vps:  this way you get rid of the SHMEM config parameters and also cpu vp number as potential cause of the difference.

    As Andreas said, the linux has much more memory than the windows box.

     

    Then, if you want to be strict at response time comparison, you should measure the execution of the query in strict identical conditions, that is either as soon as the engine started (no data in cache), and running several times the same query (data in cache). Best would be to shutdown engine, turn it on and just after run the query ( with no activity in between)

    Don't forget that when you execute a query more than once, after the first time all or part of the data will be cached in the bufferpool, which can make a difference from 7 seconds when data is not yet cache, and maybe 0,5 seconds (or less) when data is in the cache...

    Do this on the windows server, then on the linux server.

     

    Then, a query that takes 7 seconds is not a light query and as my colleagues say, you want to analyze in depth the query plan on both machines/versions and compare.

    Ensuring Update Statistics is accurate on both machines is also a determinant fact on the performance: outdated statistics can lead the engine to a bad query path....

     

    My 0.1 cents

    Hope this helps

     

    Eric






  • 6.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago
    Just noticed the attached ONCONFIG files. I agree with other commenters. The missing DBSPACETEMP may be a problem depending on the query. If you have a temp dbspace you should set DBSPACETEMP and if you do not, you should create one and set it.

    There is no 4K dbspace, so is the data on 2K or8K pages? Either way, the tables will perform either better or worse on 2K or 8K than they do on 4K depending on the nature of the test query/queries.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 7.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago
    Thank you all for your helpful reply. I am absolutely rapt.

    First I create a bunch of dbspaces and move the llog und plog in separate dbspaces. Then i create a datadbs where i put my table in and where i am query from. As i am on Linux the default pagesize is 2K, i read in the informix dokumentation and a biger pagesize may lead in performance increase, so i tryed to create the datadbs in 4K and even 8K page size. So thats why there are several Buffer entrys in the onconfig and i comment the old ones out.
    The performance is a little bit better with the pagesize of 8K on the Ubuntu Server. Thats why i did not change it back then.

    I run my performance benchmarks on an Windows 10 PC with an ODBC connection to both Servers. I wrote some querys in MS Access and measure the time it takes to complete it. And it did not mater which query i am running the performance is always a little bit better on the old Windows Server. The difference is always about 10- 30% worse.

    This is the first time i installing and configuring informix and i am not sure if i did everything right. I read a lot and watched the great videos from Lester Knutsen on his Homepage. That helped me a lot.

    I now added the DBSPACETEMP entry in the onconfig.

    After importing the data i make an UPDATE STATISTICS this are the commands from the scripts:
    dbimport -c -i /home/informix/backups/srv11 -d datadbs KMT

    cat <<EOF > /opt/scripts/lockmodegtg.sql
    DATABASE gtg;
    output to /opt/scripts/altermodegtg.sql
    without headings select "alter table", tabname, " lock mode (row);" from systables where
    tabid between 100 and 999999999 and tabtype = "T";
    EOF

    dbaccess - /opt/scripts/lockmodegtg.sql

    rm /opt/scripts/lockmodegtg.sql

    dbaccess gtg /opt/scripts/altermodegtg.sql

    rm /opt/scripts/altermodegtg.sql

    cat <<EOF > /opt/scripts/updategtg.sql
    DATABASE gtg;
    UPDATE STATISTICS;
    CLOSE DATABASE;
    EOF

    dbaccess - /opt/scripts/updategtg.sql

    rm /opt/scripts/updategtg.sql

    I am using cooked files and a EXT4 filesystem with LVM and i did not disable Filesystemjournaling. Because on the Windows Server its the same, but on NTFS. And i am also not sure if that makes any difference in reading performance?

    So now i try to performe the test you write me first on the Windows Server.
    onstat -z
    wait 2 seconds
    run query on MS Access = 7,46s
    wait 2 seconds
    onstat -p

    C:\Users\admin>onstat -z

    IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 9 days 23:21:20 -- 185088 Kbytes


    C:\Users\admin>onstat -p

    IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 9 days 23:21:37 -- 185088 Kbytes

    Profile
    dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
    0 146420 42 100.00 0 0 0 0.00

    isamtot open start read write rewrite delete commit rollbk
    51 0 1 24 0 0 0 0 0

    gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
    0 0 0 0 0 0 0

    ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
    0 0 0 0.00 0.00 0 0

    bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
    0 0 0 0 0 0 0 1

    ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits
    0 0 0 0 0 0


    C:\Users\admin>

    C:\Users\admin>onstat -g iov

    IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 9 days 23:35:13 -- 185088 Kbytes

    AIO I/O vps:
    class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors tempops
    fifo 7 0 i 0.0 0 0 0 0 0 0.0 0 0
    kio -1 0 i 136.8 113833 113827 6 0 6728789 0.0 0 0
    msc 6 0 i 0.0 0 0 0 0 0 0.0 0 220
    aio 5 0 i 0.0 0 0 0 0 0 0.0 0 0
    pio 4 0 i 0.0 0 0 0 0 0 0.0 0 0
    lio 3 0 i 0.0 0 0 0 0 0 0.0 0 0


    C:\Users\admin>





    The Same now on Ubuntu:
    onstat -z
    wait 2 seconds
    run query on MS Access = 7,87s
    wait 2 seconds
    onstat -p


    informix@srv36:/opt/scripts$ onstat -z

    IBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 00:19:46 -- 4217868 Kbytes
    2021-04-30 08:00:59

    informix@srv36:/opt/scripts$ onstat -p

    IBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 00:20:11 -- 4217868 Kbytes
    2021-04-30 08:01:24

    Profile
    dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
    33532 139620 2040210 98.36 2 386 1408 99.86

    isamtot open start read write rewrite delete commit rollbk
    2706002 247090 175937 576483 406 16 253 37 0

    gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
    0 0 0 0 0 0 0

    ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
    0 0 0 6.73 0.31 0 0

    bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
    360 0 1237131 0 0 0 11 7

    ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits
    86476 1473 1225 0 24031 778

    On-Line -- Up 00:24:22 -- 4217868 Kbytes
    2021-04-30 08:05:35


    informix@srv36:/opt/scripts$ onstat -g iov

    IBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 00:24:22 -- 4217868 Kbytes
    2021-04-30 08:05:35

    AIO I/O vps:
    class/vp/id s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors tempops
    fifo 7 0 i 0.0 0 0 0 0 0 0.0 0 0
    msc 6 0 i 0.0 0 0 0 0 0 0.0 0 9
    aio 5 0 i 58.7 16214 16115 43 0 10218 1.6 0 66
    aio 11 1 i 19.9 5491 5463 28 0 1149 4.8 0 0
    aio 12 2 i 5.5 1524 1495 29 0 1038 1.5 0 0
    aio 13 3 i 5.5 1527 1498 29 0 981 1.6 0 0
    aio 14 4 i 5.0 1380 1355 25 0 936 1.5 0 0
    pio 4 0 i 0.0 4 0 4 0 4 1.0 0 16
    lio 3 0 i 0.0 3 0 3 0 3 1.0 0 71



    On the second run the Windwos Server took 6,42s
    On the second run the Linux Server took 7,35s

    Thank you very much.

    ------------------------------
    M D
    ------------------------------



  • 8.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago
    M D:

    Note that the UPDATE STATISTICS run you did, with no modifiers, only updates some basic statistics, it does not generate the data distributions needed by the optimizer to produce a good query plan. So, run this then try again:

    UPDATE STATISTICS HIGH;

    Also I agree about setting DIRECT_IO and if this is a VM, as you indicated in your original post, then update the IO policies as noted in another response. 

    Finally, PLEASE run the queries under SET EXPLAIN ON; and post the two query plans. That may tell us why the query is running slower, if it still is.

    Finally, are the storage files you are using on Windows and on Linux on the same physical disk structures?

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 9.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 10 days ago

    Wanted to add my 2 cents.

    First:

    The collected "onstat -p" output makes some sense for Linux but not for Windows where we see mostly zero counters:

    server      12.1    14.1
    ------------------------
    dskreads       0   33532
    pagreads  146420  139620
    bufreads      42 2040210
    isamtot       51 2706002
    open           0  247090
    start          1  175937
    read          24  576483
    write          0     406
    rewrite        0      16

    delete         0     253
    commit         0      37
    usercpu     0.00    6.73    ???
    lockreqs       0 1237131


    I don't have readily available Informix on Windows environment - so cannot confirm if counters take much longer to manifest themselves on Windows (if 2 seconds delay is enough) or if the output is correct and Microsoft client just returns result from some cache without even hitting Informix server, or if on Ubuntu there is some extra activity in addition to running the query.

    Before going further with investigation - this issue need to be addressed, in  a sense that a reliable method for obtaining good (and repeatable!) stats must be established.

    One way of doing this will be running query once, then zeroing the counters (onstat -z) then running query maybe 10 times before collecting stats (onstat -p, etc.). That will improve "signal / noise" ratio.

    Note that the difference between stats collected for two (or more) such experiments  on the same system should not exceed single digit %.  On the otherwise idle system, if experiment is correct, the stats should match pretty much to the number.

    Once this is achieved one can continue investigation. ( note that with "dbcron" some activity may be happening on the background immediately after Informix restart, so observing  output from "onstat -z ; sleep 10 ; onstat -p" to confirm that Informix is idle can be useful)

    Second:

    Configuration is way too different.  My biggest concern is:

    SINGLE_CPU_VP
    1 - 12.10
    0 - 14.10

    Frankly I don't remember at what point SINGLE_CPU_VP stopped being taken into account, but if it still is - for SINGLE query execution it can make things faster. I am pretty sure that it has no effect in 14.10 though, so probably no use trying to set it there (and reduce number of cpuvps to 1)

    Informix memory footprint is also different between Windows and Linux, and so are quite a few parameters which may affect (complex) query execution, here are some examples:

    DS_MAX_QUERIES
    5
    -notset

    DS_MAX_SCANS
    5
    1048576

    DS_NONPDQ_QUERY_MEM
    1310720
    256

    DS_TOTAL_MEMORY
    26214400
    -notset

    MAX_PDQPRIORITY
    0
    100

    However I would not make any conclusions or changes before being able to obtain reliable counters for at least the basic set of stats (onstat -p) to confirm how things look from the server side. 

    Goes without saying that we expect statistics to be updated, and same query plan, same isolation level, same log mode, etc.,  but all of that should be visible in simple profile (onstat -p) output and at the moment these outputs simply don't make sense.

    BTW - you can also check if "onstat -g env" outputs are more or less the same, and don't contain any extra envvars. Same for sqlhosts. If we get result in one packet (or less packets) on Windows that will affect timing slightly.



    ------------------------------
    Vladimir Kolobrodov
    ------------------------------



  • 10.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 10 days ago
    Hi, all.
    My 1 cent on this.
    I have noticed that onconfig.std for Linux_64 14.10.xC5 shows that
    DIRECT_IO possible range is between 0 til 0x4.
    That also lead me to ask you all if 0x5 is still valid.
    Obs: both manual pages (IBM and HCL) are not even updated on that yet.




    ------------------------------
    Alexandre Marini
    ------------------------------



  • 11.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 10 days ago
    The parameter DIRECT_IO 0x5 is working on my 14.10.FC5 and it sets Direct IO active for normal
    DBSpaces and TempSpaces. The "onstat -d" shows that with an uppercase D"

    Mit freundlichen Grüßen / Kind regards


    Gerd Kaluzinski

    DataOps Application Consultant
    IBM Cloud and Cognitive Software
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland
    Mies-van-der-Rohe-Straße 6
    80807 München
    Phone: +49-175-228 1983
    E-Mail: gerd.kaluzinski@de.ibm.com
    -------------------------------------------------------------------------------------------------------------------------------------------
    IBM Deutschland GmbH

    Vorsitzender des Aufsichtsrats: Sebastian Krause
    Geschäftsführung: Gregor Pillen (Vorsitzender), Agnes Heftberger, Gabriele Schwarenthorer, Markus Koerner, Christian Noll, Nicole Reimer
    Sitz der Gesellschaft: Ehningen / Registergericht: Amtsgericht Stuttgart, HRB 14562 / WEEE-Reg.-Nr. DE 99369940






  • 12.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 10 days ago
    Great, thanks a lot!

    Alexandre Marini





  • 13.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 8 days ago
    Hi Alexandre,

    the direct-io  for temp DBSpace is a feature I requested from Informix Dev some years ago - it works very nice for large Queries :-)
    They introduce it after a half year discussion.

    Great to see , that's used from everyone now.

    Regards
    Henri

    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021
    ------------------------------



  • 14.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 9 days ago
    DIRECT_IO values are bits:

    0 - off
    1 - on
    2 - on with concurrent io (for AIX)
    4 - include temp dbspaces

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 15.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 9 days ago
    Wow now I got really confused.
    Think I am going to open a thread on this and beg for some manual updates.

    Thanks Art!
    Alexandre Marini





  • 16.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 9 days ago
    It is mentioned in the onconfig file 

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 17.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 8 days ago

    Thank you for your help.

    I try to make the new Ubuntu Server with 14.10 faster than the old Windows Server, thats the reason why i gave the Server more RAM and more Buffers.

    I made two scripts one for the Windows Powershell and one for the Ubuntu Bash. Both use dbaccess to run some useless but time expensive query.

    The Result is that both take too much time compared with running the query over Ms Access using ODBC. Then the query takes about 3 seconds.

    This is the Windows Scripts:

    # Speedtest for Windows 12.10 / Ubuntu 14.10 comparison
    write-host "Informix Speedtest"
    new-item c:\temp\speedtest.sql
    set-content c:\temp\speedtest.sql "
    DATABASE kmt;
    SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2
    FROM gf_pos
    LEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artid
    WHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'
    ORDER BY gpo_bez1 DESC
    ;
    CLOSE DATABASE;"
    
    onstat -z
    
    start-sleep -s 2
    
    (measure-command {dbaccess - c:\temp\speedtest.sql > $null}).ToString()
    #dbaccess - c:\temp\speedtest.sql > $null
    write-host "Queryexecutiontime"
    
    start-sleep -s 15
    
    onstat -p
    
    remove-item 'c:\temp\speedtest.sql'
    
    read-host -prompt "press key to exit"


    with this result:

    PS C:\Users\admin\Desktop> C:\Users\admin\Desktop\informix-speedtest.ps1
    Informix Speedtest
    
    
        Verzeichnis: C:\temp
    
    
    Mode                LastWriteTime         Length Name                                             
    ----                -------------         ------ ----                                             
    -a----       03.05.2021     11:41              0 speedtest.sql                                    
    
    IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 13 days 03:03:23 -- 205056 Kbytes
    
    dbaccess : 
    In C:\Users\admin\Desktop\informix-speedtest.ps1:18 Zeichen:19
    + (measure-command {dbaccess - c:\temp\speedtest.sql > $null}).ToString ...
    +                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:String) [], RemoteException
        + FullyQualifiedErrorId : NativeCommandError
     
    Database selected.
    782068 row(s) retrieved.
    Database closed.
    00:00:10.8278811
    Queryexecutiontime
    
    IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 13 days 03:03:51 -- 267968 Kbytes
    
    Profile
    dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
    91595      91595      377989     75.77   0          0          0          0.00   
    
    isamtot    open       start      read       write      rewrite    delete     commit     rollbk
    927845     9          9          103147     0          0          0          0          0
    
    gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs   
    0          0          0          0          0          0          0         
    
    ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes   
    0          0            0          1.90     0.52     0          0         
    
    bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans  
    1          0          821943     0          0          0          0          2         
    
    ixda-RA    idx-RA     da-RA      logrec-RA  RA-pgsused lchwaits  
    0          0          91591      0          91591      0       
    
    press key to exit: 


    The Linux script:

    echo "Informix Speedtest"
    # Als erstes Laden wir uns die Umgebungsvariablen
    source /opt/scripts/informix.sh
    
    cat <<EOF > /opt/scripts/speedtest.sql
    DATABASE kmt;
    SET EXPLAIN ON;
    SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2
    FROM gf_pos
    LEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artid
    WHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'
    ORDER BY gpo_bez1 DESC
    ;
    CLOSE DATABASE;
    EOF
    
    onstat -z
    
    sleep 2
    
    time dbaccess - /opt/scripts/speedtest.sql > /dev/null
    
    rm /opt/scripts/speedtest.sql
    
    sleep 5
    
    onstat -p
    


    The result:

    Informix Speedtest
    
    IBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:24 -- 4217868 Kbytes
    2021-05-03 11:43:23
    
    
    Database selected.
    
    
    Explain set.
    
    
    782068 row(s) retrieved.
    
    
    Database closed.
    
    
    real    0m5.682s
    user    0m1.082s
    sys     0m0.249s
    
    IBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:37 -- 4217868 Kbytes
    2021-05-03 11:43:37
    
    Profile
    dskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached
    7467       238944     443196     98.32   7467       238944     7682       100.00
    
    isamtot    open       start      read       write      rewrite    delete     commit     rollbk
    925166     10         11         103148     17         0          0          0          0
    
    gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs
    0          0          0          0          0          0          0
    
    ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes
    0          0            0          2.19     0.11     0          0
    
    bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans
    0          0          825417     0          0          0          848        2
    
    ixda-RA    idx-RA     da-RA      logrec-RA  RA-pgsused lchwaits
    0          0          0          0          0          6
    
    informix@srv36:/opt/scripts$


    So when using dbaccess the query is faster on Ubuntu. 5s on Ubuntu vs. 10s on Windows.

    So using dbaccess to performance comparisons is relativly useless or i am wrong?

    onstat -g env --> Windows:

    C:\Users\admin>onstat -g env
    
    IBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 13 days 02:09:08 -- 201728 Kbytes
    
    Server start-up environment:
    
    Variable            Value [values-list]
    CLIENT_LOCALE       de_at.cp1252
    COMSPEC             C:\Windows\system32\cmd.exe
    DBDELIMITER         |
    DBLANG              de_at.cp1252
    DBPATH              .
    DBPRINT             lpr
    DBTEMP              /tmp
    DB_LOCALE           de_at.cp1252
    GL_USEGLU           1
    IGNORE_UNDERFLOW    1
    INFORMIXDIR         C:\PROGRA~1\Informix
                        [C:\PROGRA~1\Informix]
                        [/usr/informix]
    INFORMIXSERVER      ol_ifxB
    INFORMIXSQLHOSTS    C:\Program Files\Informix\etc\sqlhosts.ol_ifxB
    INFORMIXTERM        terminfo
    LC_COLLATE          en_us.8859-1
    LC_CTYPE            en_us.8859-1
    LC_MONETARY         en_us.8859-1
    LC_NUMERIC          en_us.8859-1
    LC_TIME             en_us.8859-1
    LKNOTIFY            yes
    LOCKDOWN            no
    NODEFDAC            no
    ONCONFIG            onconfig.ol_ifxB
    PATH                C:\Program Files (x86)\ibm\gsk8\lib;C:\Program Files (x86)\
                         ibm\gsk8\bin;C:\ProgramData\Oracle\Java\javapath;C:\Progra
                         m Files\Informix\bin;C:\Program Files (x86)\Informix\Clien
                         tSDK\bin;C:\Windows\system32;C:\Windows;C:\Windows\System3
                         2\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\User
                         s\informix\AppData\Local\Microsoft\WindowsApps
    SERVER_LOCALE       en_us.8859-1
    TEMP                C:\Users\informix\AppData\Local\Temp
    TERM                dumb
    TERMCAP             /etc/termcap
    TMP                 C:\Users\informix\AppData\Local\Temp
    
    
    
    C:\Users\admin>


    onstat -g env --> Ubuntu:

    Server start-up environment:
    
    Variable            Value [values-list]
    CLIENT_LOCALE       de_at.1252
    DBDATE              DMY4
    DBDELIMITER         |
    DBLANG              de_at.1252
    DBMONEY             .
    DBPATH              .
    DBPRINT             lp -s
    DBTEMP              /tmp
    DB_LOCALE           de_at.1252
    GL_USEGLU           1
    IGNORE_UNDERFLOW    1
    INFORMIXDIR         /opt/informix
                        [/opt/informix]
                        [/usr/informix]
    INFORMIXSERVER      ol_kmt1
    INFORMIXSQLHOSTS    /opt/informix/etc/sqlhosts.ol_kmt1
    INFORMIXTERM        terminfo
    LANG                C.UTF-8
    LC_COLLATE          C.UTF-8
    LC_CTYPE            C.UTF-8
    LC_MONETARY         C.UTF-8
    LC_NUMERIC          C.UTF-8
    LC_TIME             C.UTF-8
    LKNOTIFY            yes
    LOCKDOWN            no
    NODEFDAC            no
    ONCONFIG            onconfig.ol_kmt1
    PATH                /opt/informix/bin:/opt/informix/extend/krakatoa/jre/bin:/us
                         r/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:
                         /snap/bin
    SERVER_LOCALE       en_US.819
    SHELL               /bin/bash
    TERM                dumb
    TERMCAP             /etc/termcap
    


    Thank you very much.


    Kind Regards



    ------------------------------
    M D
    ------------------------------



  • 18.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 8 days ago
    Oh no, I'd consider such local dbaccess test as the more real thing - since the query obviously is producing a lot of output, you'd still have a lot of back and forth between client and server, so you're never measuring the server only, but at least that traffic is local only.  Apparently your MS Access test case produces some extra overhead with your Linux server as compared to the Windows one - where's MS Access located relative to the two servers?

    In general the test query looks a little brutal, esp. with the two LIKE "%xx%" conditions, presumably on both tables that are joined there, so both tables *might* be scanned entirely (put a 'set explain on;' statement in front of the query to receive a query plan and query statistics dumped into a sqexplain.out file).  So you might mainly be testing the db servers' ability to scan tables, join them, then sort the result - nothing really fancy, and nothing the two versions should handle any differently, even on different platforms.  So any difference observed would rather indicate environmental or configuration differences as reasons.

    On Linux we're obviously doing a lot less disk i/o which might be the main reason for the run time difference;  what's I'm not understanding yet why there appears to be no read ahead taking place (all *RA* counters zero).

    ------------------------------
    Andreas Legner
    ------------------------------



  • 19.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 8 days ago
    I agree Andreas. Especially if the Access is running locally on the same Windows box as the database server then it is essentially a shared memory connection as compared to the network connection that Access will have to use to connect to the Linux VM environment! Also since they both VMs are running on the same frame the network connection isn't even a real network connection by a pipeline running through the underlying virtual environment manager which may or may not be as fast as the actual network.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 20.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 8 days ago
    Your timing methodology is flawed in several ways:

    • You are using OS timing outside of dbaccess. This includes the startup and shutdown time for dbaccess as well as the time to connect to the database. Starting and ending a process in Windows and in Linux are very different procedures with very different run times which have nothing to do with the performance of the Informix instance.
    • I see that the Linux session includes SET EXPLAIN ON. There is overhead to doing that that slows the query and also the time needed to write out the sqexplain.out file to disk. Even if you enabled the same on the Win side writing files on Linux and on Windows is different.
    • Finally, if you are timing the script itself, then you are also timing the time needed to write our the /temp/speedtest.sql file.
    I would suggest that you do the following:
    1. Create the speedtest.sql file with an editor before the testing. 
    2. Do not use SET EXPLAIN for the timed test (I had suggested using it in order to post the two query plans, which we have not seen yet).
    3. Add the following line both before and after the test query inside the speedtest.sql file. The difference between the two displayed times will be the actual runtime of the query itself without any other overhead (OK it will include the parse time of the timing query):
      SELECT CURRENT YEAR TO FRACTION(5) FROM systables where tabid = 1;
    4. Execute the scripts like this:
      dbaccess kmt /tmp/speedtest.sql
      or
      dbaccess kmt c:\temp\speedtest.sql
      As appropriate.


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 21.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 7 hours ago
      |   view attached
    Thank you very much for your reply and sorry for my late reply.
    I did some testing. I did not find out why Windows did not create an sqlexplain.out file. But for Linux I added two files. The first is for the "brutal" query and the second is for the "SELECT CURRENT YEAR TO FRACTION(5) FROM systables where tabid = 1;" query. The runtime difference is now only the query execution time. The effective runtime on Linux is 5.662s and on Windows its 14.7s so its much much slower on Windows. If I run the query over Access on my pc that is on the same Subnet. The runtime is on Linux about 3s and on Windows 2s so it´s definitely faster over an ODBC network connection. But again Windows is much faster. I attached a zip file with the testscripts, console-outputs and also with the sqexplain on Linux and the onconfig File.

    What config should i use on Linux to get the best performance?

    My goal was to beat the querytime on Linux 14.10 compared to Windows 12.10.

    Thank you again for your help. I realy want to find the problem and try to fix it.

    ------------------------------
    M D
    ------------------------------

    Attachment(s)

    zip
    Windows-Linux.zip   28 KB 1 version


  • 22.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 6 hours ago

    Hi MD ,

    it's depend from so many parameters. Start from the system infrastructure..... LAN, SAN, CPU, RAM.... kind of OS installations (virtual, bare metal....)

    From Informix site you can start to compare the sqexplain.out . I check your Linux file...miss your Windows ...out.

    It's a Informix Consulting task, finally, You are welcome to order this to us as a long term Informix Service Company. We will find the best solution and explanation for you on this subject, than.

    Best Regards

    Henri



    ------------------------------
    Henri Cujass
    leolo IT, CTO
    Germany
    IBM Champion 2021
    ------------------------------



  • 23.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago
    Hi all,

    I assume different DB_LOCALE settings can have an impact on the execution time of a given query...
    (I mean ISO-8859-1 versus UTF-8)

    Anyway when migrating databases you need to double check this.

    => is the V12 DB using the same locale as the new V14 DB?

    Connect to the sysmaster database and execute:

    SELECT * FROM sysdbslocale WHERE dbs_dbsname = 'mydb';

    (I learned about this query here, thanks to the community)

    Seb

    ------------------------------
    Sebastien FLAESCH
    ------------------------------



  • 24.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago

    I run the query and on both the result is de_AT.1252.

    I also set the environment variables for DBSLOCALE.

    Thank you very much.
    Kind regards



    ------------------------------
    M D
    ------------------------------



  • 25.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago

    Please change DIRECT_IO in the onconfig.ubuntu from 0 to

    DIRECT_IO 0x5

    to allow the KIO access for all DBSpaces including TempSpaces

    and try again.



    ------------------------------
    Gerd Kaluzinski
    ------------------------------



  • 26.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago

    I change the DIRECT_IO to 0x5 and restart informix and make an UPDATE STATISTICS HIGH.

    After that the query took 8s on the first run and 7.3s on the second and third run.

    So the performance is maybe a little bit worse but i would say its the same.

    Thank you very much.



    ------------------------------
    M D
    ------------------------------



  • 27.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago

    Hi,

    did you try to use the same CPU configuration on ubuntu side ?

    VPCLASS cpu,num=1,noage

    Using 2 cpu can trigger some extra process probably.

    It is just a test to check

    Regards



    ------------------------------
    jean-francois BOUDRY
    ------------------------------



  • 28.  RE: Performance of 14.10.FC5 is worse compared to 12.10 is that possible?

    Posted 11 days ago
    Over my systems I usually change the IO scheduler, as almost of my instances run over virtual machines and there is some performance improvement.

    Edit /etc/default/grub, such as gksudo gedit /etc/default/grub, here you need to add elevator=noop.

    Change GRUB_CMDLINE_LINUX_DEFAULT="quiet splash" to GRUB_CMDLINE_LINUX_DEFAULT="quiet splash elevator=noop".

    Then run sudo update-grub2 and restart.

    Best regards,

    SP




    ------------------------------
    Sergio Peres
    AIRC
    ------------------------------