Informix

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

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

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

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

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

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

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

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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 comparisonwrite-host "Informix Speedtest"new-item c:\temp\speedtest.sqlset-content c:\temp\speedtest.sql "DATABASE kmt;SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2FROM gf_posLEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artidWHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'ORDER BY gpo_bez1 DESC;CLOSE DATABASE;"onstat -zstart-sleep -s 2(measure-command {dbaccess - c:\temp\speedtest.sql > $null}).ToString()#dbaccess - c:\temp\speedtest.sql > $nullwrite-host "Queryexecutiontime"start-sleep -s 15onstat -premove-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.ps1Informix Speedtest    Verzeichnis: C:\tempMode                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 Kbytesdbaccess : 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.8278811QueryexecutiontimeIBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 13 days 03:03:51 -- 267968 KbytesProfiledskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached91595      91595      377989     75.77   0          0          0          0.00   isamtot    open       start      read       write      rewrite    delete     commit     rollbk927845     9          9          103147     0          0          0          0          0gp_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 Umgebungsvariablensource /opt/scripts/informix.shcat <<EOF > /opt/scripts/speedtest.sqlDATABASE kmt;SET EXPLAIN ON;SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2FROM gf_posLEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artidWHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'ORDER BY gpo_bez1 DESC;CLOSE DATABASE;EOFonstat -zsleep 2time dbaccess - /opt/scripts/speedtest.sql > /dev/nullrm /opt/scripts/speedtest.sqlsleep 5onstat -p

The result:

Informix SpeedtestIBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:24 -- 4217868 Kbytes2021-05-03 11:43:23Database selected.Explain set.782068 row(s) retrieved.Database closed.real    0m5.682suser    0m1.082ssys     0m0.249sIBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:37 -- 4217868 Kbytes2021-05-03 11:43:37Profiledskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached7467       238944     443196     98.32   7467       238944     7682       100.00isamtot    open       start      read       write      rewrite    delete     commit     rollbk925166     10         11         103148     17         0          0          0          0gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs0          0          0          0          0          0          0ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes0          0            0          2.19     0.11     0          0bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans0          0          825417     0          0          0          848        2ixda-RA    idx-RA     da-RA      logrec-RA  RA-pgsused lchwaits0          0          0          0          0          6informix@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 envIBM 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.cp1252COMSPEC             C:\Windows\system32\cmd.exeDBDELIMITER         |DBLANG              de_at.cp1252DBPATH              .DBPRINT             lprDBTEMP              /tmpDB_LOCALE           de_at.cp1252GL_USEGLU           1IGNORE_UNDERFLOW    1INFORMIXDIR         C:\PROGRA~1\Informix                    [C:\PROGRA~1\Informix]                    [/usr/informix]INFORMIXSERVER      ol_ifxBINFORMIXSQLHOSTS    C:\Program Files\Informix\etc\sqlhosts.ol_ifxBINFORMIXTERM        terminfoLC_COLLATE          en_us.8859-1LC_CTYPE            en_us.8859-1LC_MONETARY         en_us.8859-1LC_NUMERIC          en_us.8859-1LC_TIME             en_us.8859-1LKNOTIFY            yesLOCKDOWN            noNODEFDAC            noONCONFIG            onconfig.ol_ifxBPATH                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\WindowsAppsSERVER_LOCALE       en_us.8859-1TEMP                C:\Users\informix\AppData\Local\TempTERM                dumbTERMCAP             /etc/termcapTMP                 C:\Users\informix\AppData\Local\TempC:\Users\admin>

onstat -g env --> Ubuntu:

Server start-up environment:Variable            Value [values-list]CLIENT_LOCALE       de_at.1252DBDATE              DMY4DBDELIMITER         |DBLANG              de_at.1252DBMONEY             .DBPATH              .DBPRINT             lp -sDBTEMP              /tmpDB_LOCALE           de_at.1252GL_USEGLU           1IGNORE_UNDERFLOW    1INFORMIXDIR         /opt/informix                    [/opt/informix]                    [/usr/informix]INFORMIXSERVER      ol_kmt1INFORMIXSQLHOSTS    /opt/informix/etc/sqlhosts.ol_kmt1INFORMIXTERM        terminfoLANG                C.UTF-8LC_COLLATE          C.UTF-8LC_CTYPE            C.UTF-8LC_MONETARY         C.UTF-8LC_NUMERIC          C.UTF-8LC_TIME             C.UTF-8LKNOTIFY            yesLOCKDOWN            noNODEFDAC            noONCONFIG            onconfig.ol_kmt1PATH                /opt/informix/bin:/opt/informix/extend/krakatoa/jre/bin:/us                     r/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:                     /snap/binSERVER_LOCALE       en_US.819SHELL               /bin/bashTERM                dumbTERMCAP             /etc/termcap

Thank you very much.

Kind Regards

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.

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

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 comparisonwrite-host "Informix Speedtest"new-item c:\temp\speedtest.sqlset-content c:\temp\speedtest.sql "DATABASE kmt;SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2FROM gf_posLEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artidWHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'ORDER BY gpo_bez1 DESC;CLOSE DATABASE;"onstat -zstart-sleep -s 2(measure-command {dbaccess - c:\temp\speedtest.sql > $null}).ToString()#dbaccess - c:\temp\speedtest.sql > $nullwrite-host "Queryexecutiontime"start-sleep -s 15onstat -premove-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.ps1Informix Speedtest    Verzeichnis: C:\tempMode                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 Kbytesdbaccess : 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.8278811QueryexecutiontimeIBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 13 days 03:03:51 -- 267968 KbytesProfiledskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached91595      91595      377989     75.77   0          0          0          0.00   isamtot    open       start      read       write      rewrite    delete     commit     rollbk927845     9          9          103147     0          0          0          0          0gp_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 Umgebungsvariablensource /opt/scripts/informix.shcat <<EOF > /opt/scripts/speedtest.sqlDATABASE kmt;SET EXPLAIN ON;SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2FROM gf_posLEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artidWHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'ORDER BY gpo_bez1 DESC;CLOSE DATABASE;EOFonstat -zsleep 2time dbaccess - /opt/scripts/speedtest.sql > /dev/nullrm /opt/scripts/speedtest.sqlsleep 5onstat -p

The result:

Informix SpeedtestIBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:24 -- 4217868 Kbytes2021-05-03 11:43:23Database selected.Explain set.782068 row(s) retrieved.Database closed.real    0m5.682suser    0m1.082ssys     0m0.249sIBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:37 -- 4217868 Kbytes2021-05-03 11:43:37Profiledskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached7467       238944     443196     98.32   7467       238944     7682       100.00isamtot    open       start      read       write      rewrite    delete     commit     rollbk925166     10         11         103148     17         0          0          0          0gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs0          0          0          0          0          0          0ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes0          0            0          2.19     0.11     0          0bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans0          0          825417     0          0          0          848        2ixda-RA    idx-RA     da-RA      logrec-RA  RA-pgsused lchwaits0          0          0          0          0          6informix@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 envIBM 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.cp1252COMSPEC             C:\Windows\system32\cmd.exeDBDELIMITER         |DBLANG              de_at.cp1252DBPATH              .DBPRINT             lprDBTEMP              /tmpDB_LOCALE           de_at.cp1252GL_USEGLU           1IGNORE_UNDERFLOW    1INFORMIXDIR         C:\PROGRA~1\Informix                    [C:\PROGRA~1\Informix]                    [/usr/informix]INFORMIXSERVER      ol_ifxBINFORMIXSQLHOSTS    C:\Program Files\Informix\etc\sqlhosts.ol_ifxBINFORMIXTERM        terminfoLC_COLLATE          en_us.8859-1LC_CTYPE            en_us.8859-1LC_MONETARY         en_us.8859-1LC_NUMERIC          en_us.8859-1LC_TIME             en_us.8859-1LKNOTIFY            yesLOCKDOWN            noNODEFDAC            noONCONFIG            onconfig.ol_ifxBPATH                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\WindowsAppsSERVER_LOCALE       en_us.8859-1TEMP                C:\Users\informix\AppData\Local\TempTERM                dumbTERMCAP             /etc/termcapTMP                 C:\Users\informix\AppData\Local\TempC:\Users\admin>

onstat -g env --> Ubuntu:

Server start-up environment:Variable            Value [values-list]CLIENT_LOCALE       de_at.1252DBDATE              DMY4DBDELIMITER         |DBLANG              de_at.1252DBMONEY             .DBPATH              .DBPRINT             lp -sDBTEMP              /tmpDB_LOCALE           de_at.1252GL_USEGLU           1IGNORE_UNDERFLOW    1INFORMIXDIR         /opt/informix                    [/opt/informix]                    [/usr/informix]INFORMIXSERVER      ol_kmt1INFORMIXSQLHOSTS    /opt/informix/etc/sqlhosts.ol_kmt1INFORMIXTERM        terminfoLANG                C.UTF-8LC_COLLATE          C.UTF-8LC_CTYPE            C.UTF-8LC_MONETARY         C.UTF-8LC_NUMERIC          C.UTF-8LC_TIME             C.UTF-8LKNOTIFY            yesLOCKDOWN            noNODEFDAC            noONCONFIG            onconfig.ol_kmt1PATH                /opt/informix/bin:/opt/informix/extend/krakatoa/jre/bin:/us                     r/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:                     /snap/binSERVER_LOCALE       en_US.819SHELL               /bin/bashTERM                dumbTERMCAP             /etc/termcap

Thank you very much.

Kind Regards

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.

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

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 comparisonwrite-host "Informix Speedtest"new-item c:\temp\speedtest.sqlset-content c:\temp\speedtest.sql "DATABASE kmt;SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2FROM gf_posLEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artidWHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'ORDER BY gpo_bez1 DESC;CLOSE DATABASE;"onstat -zstart-sleep -s 2(measure-command {dbaccess - c:\temp\speedtest.sql > $null}).ToString()#dbaccess - c:\temp\speedtest.sql > $nullwrite-host "Queryexecutiontime"start-sleep -s 15onstat -premove-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.ps1Informix Speedtest    Verzeichnis: C:\tempMode                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 Kbytesdbaccess : 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.8278811QueryexecutiontimeIBM Informix Dynamic Server Version 12.10.FC5IE -- On-Line -- Up 13 days 03:03:51 -- 267968 KbytesProfiledskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached91595      91595      377989     75.77   0          0          0          0.00   isamtot    open       start      read       write      rewrite    delete     commit     rollbk927845     9          9          103147     0          0          0          0          0gp_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 Umgebungsvariablensource /opt/scripts/informix.shcat <<EOF > /opt/scripts/speedtest.sqlDATABASE kmt;SET EXPLAIN ON;SELECT gf_pos.gpo_artid, gf_pos.gpo_bez1, artikel.art_bez2FROM gf_posLEFT JOIN artikel ON gf_pos.gpo_artid = artikel.art_artidWHERE gpo_bez1 LIKE '%e%' OR artikel.art_bez2 LIKE '%H%'ORDER BY gpo_bez1 DESC;CLOSE DATABASE;EOFonstat -zsleep 2time dbaccess - /opt/scripts/speedtest.sql > /dev/nullrm /opt/scripts/speedtest.sqlsleep 5onstat -p

The result:

Informix SpeedtestIBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:24 -- 4217868 Kbytes2021-05-03 11:43:23Database selected.Explain set.782068 row(s) retrieved.Database closed.real    0m5.682suser    0m1.082ssys     0m0.249sIBM Informix Dynamic Server Version 14.10.FC5IE -- On-Line -- Up 3 days 02:13:37 -- 4217868 Kbytes2021-05-03 11:43:37Profiledskreads   pagreads   bufreads   %cached dskwrits   pagwrits   bufwrits   %cached7467       238944     443196     98.32   7467       238944     7682       100.00isamtot    open       start      read       write      rewrite    delete     commit     rollbk925166     10         11         103148     17         0          0          0          0gp_read    gp_write   gp_rewrt   gp_del     gp_alloc   gp_free    gp_curs0          0          0          0          0          0          0ovlock     ovuserthread ovbuff     usercpu  syscpu   numckpts   flushes0          0            0          2.19     0.11     0          0bufwaits   lokwaits   lockreqs   deadlks    dltouts    ckpwaits   compress   seqscans0          0          825417     0          0          0          848        2ixda-RA    idx-RA     da-RA      logrec-RA  RA-pgsused lchwaits0          0          0          0          0          6informix@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 envIBM 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.cp1252COMSPEC             C:\Windows\system32\cmd.exeDBDELIMITER         |DBLANG              de_at.cp1252DBPATH              .DBPRINT             lprDBTEMP              /tmpDB_LOCALE           de_at.cp1252GL_USEGLU           1IGNORE_UNDERFLOW    1INFORMIXDIR         C:\PROGRA~1\Informix                    [C:\PROGRA~1\Informix]                    [/usr/informix]INFORMIXSERVER      ol_ifxBINFORMIXSQLHOSTS    C:\Program Files\Informix\etc\sqlhosts.ol_ifxBINFORMIXTERM        terminfoLC_COLLATE          en_us.8859-1LC_CTYPE            en_us.8859-1LC_MONETARY         en_us.8859-1LC_NUMERIC          en_us.8859-1LC_TIME             en_us.8859-1LKNOTIFY            yesLOCKDOWN            noNODEFDAC            noONCONFIG            onconfig.ol_ifxBPATH                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\WindowsAppsSERVER_LOCALE       en_us.8859-1TEMP                C:\Users\informix\AppData\Local\TempTERM                dumbTERMCAP             /etc/termcapTMP                 C:\Users\informix\AppData\Local\TempC:\Users\admin>

onstat -g env --> Ubuntu:

Server start-up environment:Variable            Value [values-list]CLIENT_LOCALE       de_at.1252DBDATE              DMY4DBDELIMITER         |DBLANG              de_at.1252DBMONEY             .DBPATH              .DBPRINT             lp -sDBTEMP              /tmpDB_LOCALE           de_at.1252GL_USEGLU           1IGNORE_UNDERFLOW    1INFORMIXDIR         /opt/informix                    [/opt/informix]                    [/usr/informix]INFORMIXSERVER      ol_kmt1INFORMIXSQLHOSTS    /opt/informix/etc/sqlhosts.ol_kmt1INFORMIXTERM        terminfoLANG                C.UTF-8LC_COLLATE          C.UTF-8LC_CTYPE            C.UTF-8LC_MONETARY         C.UTF-8LC_NUMERIC          C.UTF-8LC_TIME             C.UTF-8LKNOTIFY            yesLOCKDOWN            noNODEFDAC            noONCONFIG            onconfig.ol_kmt1PATH                /opt/informix/bin:/opt/informix/extend/krakatoa/jre/bin:/us                     r/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:                     /snap/binSERVER_LOCALE       en_US.819SHELL               /bin/bashTERM                dumbTERMCAP             /etc/termcap

Thank you very much.

Kind Regards

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.

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

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.

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

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.