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

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

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

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

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

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

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

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.sqlDATABASE 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 -zwait 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 KbytesProfile 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 -zwait 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:24Profile 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 ------------------------------

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

Please change DIRECT_IO in the onconfig.ubuntu from 0 to DIRECT_IO 0x5to allow the KIO access for all DBSpaces including TempSpaces and try again. ------------------------------ Gerd Kaluzinski ------------------------------

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

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

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

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

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

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.10Frankly 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_QUERIES5 -notset DS_MAX_SCANS5 1048576 DS_NONPDQ_QUERY_MEM1310720 256 DS_TOTAL_MEMORY26214400 -notset MAX_PDQPRIORITY0 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

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

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

Great, thanks a lot! Alexandre Marini

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

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

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

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

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 > $nullwrite-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 KbytesProfile 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 -zsleep 2 time dbaccess - /opt/scripts/speedtest.sql > /dev/null rm /opt/scripts/speedtest.sql sleep 5 onstat -pThe 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:23Database 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:37Profile 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 KbytesServer 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 ------------------------------

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

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: Create the speedtest.sql file with an editor before the testing. 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). 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; 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 ------------------------------

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

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

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

Hi, Just another simple but very important component, if you are running your systems over virtual machines do you have agents installed? VMware: vmware tools Hyper-V: integration tools SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

I am seeing a big drop in performance from 12.10.FC14 to 14.10.FC5, same hardware etc, and in place upgrade. Order of magnitude drop on very large queries Cheers Paul

Paul - do you have any more info about this? Is this more than an "update statistics" thing after the upgrade? A change to the optimizer? Something more fundamental? ------------------------------ Mike Walker ------------------------------

Not at the moment – investigating – the query plans are 100% identical. At the moment I suspect the size of the dataset as it seems to be worse the larger the dataset and/or the VM Cheers Paul

Hi Paul, what's this referring to, why in this thread, where are you seeing this? Cheers, Andreas ------------------------------ Andreas Legner ------------------------------

Hi, i running Ubuntu 20.04 LTS an per default the open-vm-tools are installed. Did you mean that with agent? ------------------------------ M D ------------------------------

Hi, open-vm-tools work fine but there are some other conditions related with virtual platform that can have performance impact. Almost of my systems run over virtual guests, on Hyper-V for example there are significant performance improvements disabling VMQ on physical adapter. Also over registry can force network speed to 10G. On VMware there are also some improvements using VMXNET3, anyway I have done some tests with Ubuntu over VMware and have verified slow performance comparing to CentOS and OpenSUSE... SP ------------------------------ Sergio Peres AIRC ------------------------------

Thank you very much for your reply. That sounds interessting. Can you tell me how much different you can report between ubuntu and centos? Is the difference only with high user loads? I test the Windows and Ubuntu Servers only with me as a user so there is no hight Network or CPU Bandwith used. I use VMXNET3 on Ubuntu. ------------------------------ M D ------------------------------