RE: 9.40 vs 9.21 performance

Folks, Working for Pegasus, my team has noticed the degradation in performance from 7 to 9.4x, both from an in place upgrade and from a fresh install. We've opened a number of cases with IBM and the bug has been coined as performance issues for singleton selects based on optimizer changes. I can dig up the bug number if anyone thinks it will apply to their situation. We have not moved our 9.2x / 9.3x instance to 9.4x, so I do not have this information. The reversion issue discussed on this board is the primary reason for those upgrade projects being delayed. Let me know, Michael Brizendine -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Neil Truby Sent: Tuesday, February 24, 2004 1:00 PM To: informix-list@iiug.org Subject: Re: 9.40 vs 9.21 performance We've been trying to expand this into a general case that IBM Tech Support will look at, and not been too successful. This leads me to suspect that the problem may exist with databases and tables that have been successivley converted from 7 -> 9 -> 9.40 .... Will keep looking. "Neil Truby" <neil.truby@ardenta.com> wrote in message news:... > Has anyone done performance tests of 9.21 against 9.40 ("level 0")? > > We have, and found 9.40 to be much slower. > > We've tried to break this down on one table, and I reproduce the > results below. > > The problem seems to be with the rate of insert of new rows. > > We're working on digging deeper (can we reproduce it on any table, do > we get > the same with indexes dropped etc?) before opening a Tech Support > case, in order to make the call as generic and reproducible as possible. > > Any comments gratefully received. Thaniks to the marvels of SAN technology, > you can be sure that the tests were done in identical circumstances > save for > the IDS version. > > (For reference: 9.21 FC4, 9.40 FC3W2 on HP-UX 11.11). > > thanks > Neil > > ======================================================================== ==== > == > IDS9.21 - Bench Mark Testing > Informix Dynamic Server 2000 Version 9.21.FC4 Database unload - table > COLINE Database selected. > 222284 row(s) unloaded. > Database closed. > > real 3:36.10 > user 2:37.95 > sys 4.20 > > Database delete - Table COLINE > 222284 row(s) deleted. > Data committed. > Database closed. > > real 27:05.74 > user 0.04 > sys 0.03 > > Database Load - Table COLINE > Database selected. > 222284 row(s) loaded. > Database closed. > real 1:09:37.28 > user 1:49.94 > sys 5.09 > > IDS9.40 - Bench Mark Testing > IBM Informix Dynamic Server Version 9.40.FC3W2 (Expanded chunk > capacity > mode: disabled) > Database unload - table COLINE > Database selected. > 222284 row(s) unloaded. > Database closed. > > real 3:37.50 > user 2:30.99 > sys 4.46 > > Database delete - Table COLINE > Table locked. > 222284 row(s) deleted. > Data committed. > Database closed. > > real 26:24.11 > user 0.07 > sys 0.04 > > Database Load - Table COLINE > Database selected. > 222284 row(s) loaded. > Database closed. > real 2:01:46.36 > user 1:51.89 > sys 4.70 > > sending to informix-list

I think that it has been discussed before, and generally expected, that some gransactions will be slower in 9.2 thatn 7.31 due to compiler errors. For this client, who is desperate to move from 9.21 to benefit from 9.40's btscanner functionality, we always test the critical processes. And some of these have been found to run much more slowly under 9.40 than 9.21. Experience and common sense tell us that there's little point in raising a support call until we can present to IBM Informix Tech Support a simple and reproducible case. But this is proving very difficult at present. Our non-client specific tests show 9.40 performance to be similar to that of 9.21, and if there were a general problem we'd have heard about it by now. So it's something to do with the client's set-up - this is a 500GByte database successively converted from 7.13 -> 7.24 ->9.20 -> 9.21 - but we haven't found it yet. On the subject of the regression problem: we have tested 9.40FC3W2 exhaustively and it does seem to be fixed now, so you can be pretty confident that 9.40FC4 (due in April I believe) will have licked this problem. "Brizendine, Michael" <Michael.Brizendine@pegs.com> wrote in message news:c1glqt$98l$1@terabinaries.xmission.com... > > Folks, > > Working for Pegasus, my team has noticed the degradation in performance > from 7 to 9.4x, both from an in place upgrade and from a fresh install. > We've opened a number of cases with IBM and the bug has been coined as > performance issues for singleton selects based on optimizer changes. I > can dig up the bug number if anyone thinks it will apply to their > situation. We have not moved our 9.2x / 9.3x instance to 9.4x, so I do > not have this information. The reversion issue discussed on this board > is the primary reason for those upgrade projects being delayed. > > Let me know, > > Michael Brizendine > > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Neil Truby > Sent: Tuesday, February 24, 2004 1:00 PM > To: informix-list@iiug.org > Subject: Re: 9.40 vs 9.21 performance > > We've been trying to expand this into a general case that IBM Tech > Support will look at, and not been too successful. > This leads me to suspect that the problem may exist with databases and > tables that have been successivley converted from 7 -> 9 -> 9.40 .... > Will keep looking. > > > "Neil Truby" <neil.truby@ardenta.com> wrote in message news:... > > Has anyone done performance tests of 9.21 against 9.40 ("level 0")? > > > > We have, and found 9.40 to be much slower. > > > > We've tried to break this down on one table, and I reproduce the > > results below. > > > > The problem seems to be with the rate of insert of new rows. > > > > We're working on digging deeper (can we reproduce it on any table, do > > we > get > > the same with indexes dropped etc?) before opening a Tech Support > > case, in order to make the call as generic and reproducible as > possible. > > > > Any comments gratefully received. Thaniks to the marvels of SAN > technology, > > you can be sure that the tests were done in identical circumstances > > save > for > > the IDS version. > > > > (For reference: 9.21 FC4, 9.40 FC3W2 on HP-UX 11.11). > > > > thanks > > Neil > > > > > ======================================================================== > ==== > > == > > IDS9.21 - Bench Mark Testing > > Informix Dynamic Server 2000 Version 9.21.FC4 Database unload - table > > COLINE Database selected. > > 222284 row(s) unloaded. > > Database closed. > > > > real 3:36.10 > > user 2:37.95 > > sys 4.20 > > > > Database delete - Table COLINE > > 222284 row(s) deleted. > > Data committed. > > Database closed. > > > > real 27:05.74 > > user 0.04 > > sys 0.03 > > > > Database Load - Table COLINE > > Database selected. > > 222284 row(s) loaded. > > Database closed. > > real 1:09:37.28 > > user 1:49.94 > > sys 5.09 > > > > IDS9.40 - Bench Mark Testing > > IBM Informix Dynamic Server Version 9.40.FC3W2 (Expanded chunk > > capacity > > mode: disabled) > > Database unload - table COLINE > > Database selected. > > 222284 row(s) unloaded. > > Database closed. > > > > real 3:37.50 > > user 2:30.99 > > sys 4.46 > > > > Database delete - Table COLINE > > Table locked. > > 222284 row(s) deleted. > > Data committed. > > Database closed. > > > > real 26:24.11 > > user 0.07 > > sys 0.04 > > > > Database Load - Table COLINE > > Database selected. > > 222284 row(s) loaded. > > Database closed. > > real 2:01:46.36 > > user 1:51.89 > > sys 4.70 > > > > > > > > > sending to informix-list