9.4 slower than 9.3

I received the following email showing 9.4 IDS to be slower than 9.3 IDS. >From many classes and conversations I know 9.4 is faster. Same hardware, Digital Alpha/Compaq/HP ES40 TRU64 UNIX Ver 5.1B. Same inhouse software loaded with appropriate 9.3/9.4 libraries. Same onconfig, same data, nothing else running. 9.30.FC1X6 and 9.40.FC2X1 Can this be a result of the conversion taking place on the fly? Any ideas on why 9.4 appears to be almost 30% slower? Thanx in advance, Steve Willcoxon Duplex and transactions in the following refers to our application and its transactions, not Informix transactions. Subject: 9.4 Informix Test Results We had Imperial duplex this past Sunday and Monday with both 9.3 and 9.4 Informix. At the time of these tests, the duplex was the only thing running on the system. The times listed are from the beginning of the processing of the first transaction to the end of processing of the last transaction. Here are the results: 9.3 Informix: Sunday duplex - 2hr 41min Monday duplex - 4hr 41min 9.4 Informix: Sunday duplex - 3hr 28min Monday duplex - 5hr 45min So as you can see the 9.4 Informix took longer to do the same thing. Sunday - 9.4 took 47min longer Monday - 9.4 took 1hr 4min longer

Is there a case on this? "Steve Willcoxon" <stevew@shubertor To: ids@iiug.org g.com> cc: Sent by: Subject: 9.4 slower than 9.3 [2010] forum.subscriber@ iiug.org 10/08/2003 03:31 PM I received the following email showing 9.4 IDS to be slower than 9.3 IDS. >From many classes and conversations I know 9.4 is faster. Same hardware, Digital Alpha/Compaq/HP ES40 TRU64 UNIX Ver 5.1B. Same inhouse software loaded with appropriate 9.3/9.4 libraries. Same onconfig, same data, nothing else running. 9.30.FC1X6 and 9.40.FC2X1 Can this be a result of the conversion taking place on the fly? Any ideas on why 9.4 appears to be almost 30% slower? Thanx in advance, Steve Willcoxon Duplex and transactions in the following refers to our application and its transactions, not Informix transactions. Subject: 9.4 Informix Test Results We had Imperial duplex this past Sunday and Monday with both 9.3 and 9.4 Informix. At the time of these tests, the duplex was the only thing running on the system. The times listed are from the beginning of the processing of the first transaction to the end of processing of the last transaction. Here are the results: 9.3 Informix: Sunday duplex - 2hr 41min Monday duplex - 4hr 41min 9.4 Informix: Sunday duplex - 3hr 28min Monday duplex - 5hr 45min So as you can see the 9.4 Informix took longer to do the same thing. Sunday - 9.4 took 47min longer Monday - 9.4 took 1hr 4min longer

All the usual questions apply: Have you reviewed the configuration in 9.4 to check that you don't have any inappropriate settings (even if they were appropriate before)? CCFLAGS, for example. Have you got enough shared memory? What do the system performance monitoring utilities say is taking the time - is it CPU power or disk access time or something else that is causing the hot-spot? Have you run UPDATE STATISTICS? Are the databases running on the same machine but different disk drives or on the same disk drives each time? Do you use optimizer hints/directives in the code? Have you checked they're correct? Are you sure you have all the indexes the same on both? Have you run with SET EXPLAIN ON to see what the query plans look like? If the answer to all these is yes (well, the third one doesn't take yes/no as an answer), then maybe you should be contacting IBM/Informix Tech Support. If you have a firm case of worse performance, we might want to get a version of you application in house here so we can use it to prevent similar breakdowns in future (after we've analyzed why it is causing problems). One of the hardest things about any benchmarking is ensuring that the setups are really, really identical, and the other is that the results are very difficult to extrapolate to different systems. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "Steve Willcoxon"| | | <stevew@shubertor| | | g.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 10/08/2003 01:31 | | | PM | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: 9.4 slower than 9.3 [2010] | >------------------------------------------------------------------------------- --------------------------------------------------------------| I received the following email showing 9.4 IDS to be slower than 9.3 IDS. >From many classes and conversations I know 9.4 is faster. Same hardware, Digital Alpha/Compaq/HP ES40 TRU64 UNIX Ver 5.1B. Same inhouse software loaded with appropriate 9.3/9.4 libraries. Same onconfig, same data, nothing else running. 9.30.FC1X6 and 9.40.FC2X1 Can this be a result of the conversion taking place on the fly? Any ideas on why 9.4 appears to be almost 30% slower? Thanx in advance, Steve Willcoxon Duplex and transactions in the following refers to our application and its transactions, not Informix transactions. Subject: 9.4 Informix Test Results We had Imperial duplex this past Sunday and Monday with both 9.3 and 9.4 Informix. At the time of these tests, the duplex was the only thing running on the system. The times listed are from the beginning of the processing of the first transaction to the end of processing of the last transaction. Here are the results: 9.3 Informix: Sunday duplex - 2hr 41min Monday duplex - 4hr 41min 9.4 Informix: Sunday duplex - 3hr 28min Monday duplex - 5hr 45min So as you can see the 9.4 Informix took longer to do the same thing. Sunday - 9.4 took 47min longer Monday - 9.4 took 1hr 4min longer

Hi, i also saw that 9.4 is slower than 9.3. But they are new threds : BTRESS Cleaners. They are started with IDS. You can stop and start these thread while IDS is online with "onmode -C <option>" (see in administrator reference guide). When you stop these threads, performances are goods. I launch these threads while low activity periods. Regards Jean-François BOUDRY -----Message d'origine----- De : forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]De la part de Steve Willcoxon Envoyé : mercredi 8 octobre 2003 22:31 À : ids@iiug.org Objet : 9.4 slower than 9.3 [2010] I received the following email showing 9.4 IDS to be slower than 9.3 IDS. >From many classes and conversations I know 9.4 is faster. Same hardware, Digital Alpha/Compaq/HP ES40 TRU64 UNIX Ver 5.1B. Same inhouse software loaded with appropriate 9.3/9.4 libraries. Same onconfig, same data, nothing else running. 9.30.FC1X6 and 9.40.FC2X1 Can this be a result of the conversion taking place on the fly? Any ideas on why 9.4 appears to be almost 30% slower? Thanx in advance, Steve Willcoxon Duplex and transactions in the following refers to our application and its transactions, not Informix transactions. Subject: 9.4 Informix Test Results We had Imperial duplex this past Sunday and Monday with both 9.3 and 9.4 Informix. At the time of these tests, the duplex was the only thing running on the system. The times listed are from the beginning of the processing of the first transaction to the end of processing of the last transaction. Here are the results: 9.3 Informix: Sunday duplex - 2hr 41min Monday duplex - 4hr 41min 9.4 Informix: Sunday duplex - 3hr 28min Monday duplex - 5hr 45min So as you can see the 9.4 Informix took longer to do the same thing. Sunday - 9.4 took 47min longer Monday - 9.4 took 1hr 4min longer

How much overhead do B-tree scanners create? Could it be alot until all indices are cleaned up and then minimal? What additional work has to be done for the conversion on the fly? Do only pages which need to be written anyway have the header updated or any pages read? For purposes of a test, can something be run to convert everything before the test? Steve Willcoxon Replies to my first 5 responses To: Mark Scranton "onmode -BC 1" and "onmode -BC 2" were not issued. From: Obnoxio Update statistics? To: Obnoxio Statistics were in the same condition for both 9.3 and 9.4. All statistics are updated once a week, 1/7th each night. Are there differences in statistics between 9.3 and 9.4? To: Gerd Kaluzinski No temp files are created. Our application is reprocessing a day of OLTP work from the applications own transaction records. This consists of lots of inserts and updates almost all accessed with a primary key and virtually no deletes. To: Jean-Francois BOUDRY I will try the same test with the B-tree scanners turned off. With the B-tree scanners off, does 9.4 run faster than 9.3? From: Madison Pruet Is there a case on this? To: Madison Pruet No case on this. Should there be? What would I tell them? ----- Original Message ----- From: "Mark Scranton" <mscranto@us.ibm.com> To: "Steve Willcoxon" <stevew@shubertorg.com> Sent: Wednesday, October 08, 2003 8:24 PM Subject: Re: 9.4 slower than 9.3 [2010] Steve - This is interesting. Had the "onmode -BC 1" or "onmode -BC 2" been issued after bringing up the 9.4 engine? Mark. Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com ************************************************* From: "Gerd Kaluzinski" <gerd.kaluzinski@bytec.de> To: "Steve Willcoxon" <stevew@shubertorg.com> Cc: <ids@iiug.org> Sent: Thursday, October 09, 2003 3:08 AM Subject: Re: 9.4 slower than 9.3 [2010] See if your Testscripts use temporary tables. In this case, you should add one additional "update statistics for xxx" before you select from these tables. ********************************* From: "Jean-Francois BOUDRY" <jeanfrancois.boudry@ferma.fr> To: "Steve Willcoxon" <stevew@shubertorg.com>; <ids@iiug.org> Sent: Thursday, October 09, 2003 2:58 AM Subject: RE: 9.4 slower than 9.3 [2010] Hi, i also saw that 9.4 is slower than 9.3. But they are new threds : BTRESS Cleaners. They are started with IDS. You can stop and start these thread while IDS is online with "onmode -C <option>" (see in administrator reference guide). When you stop these threads, performances are goods. I launch these threads while low activity periods. ***********************************************************8 "Steve Willcoxon" <stevew@shubertor To: ids@iiug.org g.com> cc: Sent by: Subject: 9.4 slower than 9.3 [2010] forum.subscriber@ iiug.org 10/08/2003 02:31 PM I received the following email showing 9.4 IDS to be slower than 9.3 IDS. >From many classes and conversations I know 9.4 is faster. Same hardware, Digital Alpha/Compaq/HP ES40 TRU64 UNIX Ver 5.1B. Same inhouse software loaded with appropriate 9.3/9.4 libraries. Same onconfig, same data, nothing else running. 9.30.FC1X6 and 9.40.FC2X1 Can this be a result of the conversion taking place on the fly? Any ideas on why 9.4 appears to be almost 30% slower? Thanx in advance, Steve Willcoxon Duplex and transactions in the following refers to our application and its transactions, not Informix transactions. Subject: 9.4 Informix Test Results We had Imperial duplex this past Sunday and Monday with both 9.3 and 9.4 Informix. At the time of these tests, the duplex was the only thing running on the system. The times listed are from the beginning of the processing of the first transaction to the end of processing of the last transaction. Here are the results: 9.3 Informix: Sunday duplex - 2hr 41min Monday duplex - 4hr 41min 9.4 Informix: Sunday duplex - 3hr 28min Monday duplex - 5hr 45min So as you can see the 9.4 Informix took longer to do the same thing. Sunday - 9.4 took 47min longer Monday - 9.4 took 1hr 4min longer