IDS 7 slower than OnLine 5 - why?

I've heard a lot of talk about people managing to get IDS 7 to run more slowly than OnLine 5 on an identical configuration. For what kind of reasons does this come about? What parameters simply cannot be left the same when you migrate to IDS? I've never had a client experience this problem (unless there was something weird in their use of SQL) but I'd like to be forewarned. -Andy Kent- Bristol, England

Andy Kent wrote: > > I've heard a lot of talk about people managing to get IDS 7 to run more > slowly than OnLine 5 on an identical configuration. > > For what kind of reasons does this come about? What parameters simply cannot > be left the same when you migrate to IDS? > > I've never had a client experience this problem (unless there was something > weird in their use of SQL) but I'd like to be forewarned. The biggest problem is when 5.xx users continue to do UPDATE STATISTICS with no level option, as they did in 5.xx, and so have no data distributions for the 7.xx optimizer to work with. Others are not configuring additional CPU VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and CLEANERS (5.xx used a different algorithm for buffer access which was slower but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to reduce contention). Failure to take advantage of 7.xx features that improve performance is a biggy also, no fragmentation no parallelism, detached indexes can help spread hot spots across disks also. Art S. Kagel

Online 5 is faster than IDS 7 on uniprocessor systems, even if both environments are optimised. UPDATE STATISTICS without the HIGH verb usually makes V7 woefully slow. Most DBA's set OPTCOMPIND to 0 in the onconfig file which only requires an UPDATE STATISTICS as it uses the simpler optimiser, which is fine for OLTP. NOEL GRIFFIN Ireland Art S. Kagel <kagel@bloomberg.net> wrote in message news:386BC39D.38A25138@bloomberg.net... > Andy Kent wrote: > > > > I've heard a lot of talk about people managing to get IDS 7 to run more > > slowly than OnLine 5 on an identical configuration. > > > > For what kind of reasons does this come about? What parameters simply cannot > > be left the same when you migrate to IDS? > > > > I've never had a client experience this problem (unless there was something > > weird in their use of SQL) but I'd like to be forewarned. > > The biggest problem is when 5.xx users continue to do UPDATE STATISTICS with > no level option, as they did in 5.xx, and so have no data distributions for > the 7.xx optimizer to work with. Others are not configuring additional CPU > VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and > CLEANERS (5.xx used a different algorithm for buffer access which was slower > but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to > reduce contention). Failure to take advantage of 7.xx features that improve > performance is a biggy also, no fragmentation no parallelism, detached indexes > can help spread hot spots across disks also. > > Art S. Kagel

Don't let OPTCOMPIND fool you. DOing the full UPDATE STATISTICS suite as recommended in the PERFORMANCE GUIDE is still required for best optimizer performance. We run OPTCOMPIND == 0 and I was still prompted to write dostats to run the suite for me. And you are right setting OPTCOMPIND to anything other than 0 in an OLTP environment is asking for dog slow performance and yes IDS 7.xx/9.2x really does shine on an SMP system. Art S. Kagel Noel Griffin wrote: > > Online 5 is faster than IDS 7 on uniprocessor systems, even if both > environments are optimised. UPDATE STATISTICS without the HIGH verb usually > makes V7 woefully slow. Most DBA's set OPTCOMPIND to 0 in the onconfig file > which only requires an UPDATE STATISTICS as it uses the simpler optimiser, > which is fine for OLTP. > > NOEL GRIFFIN > Ireland > > Art S. Kagel <kagel@bloomberg.net> wrote in message > news:386BC39D.38A25138@bloomberg.net... > > Andy Kent wrote: > > > > > > I've heard a lot of talk about people managing to get IDS 7 to run more > > > slowly than OnLine 5 on an identical configuration. > > > > > > For what kind of reasons does this come about? What parameters simply > cannot > > > be left the same when you migrate to IDS? > > > > > > I've never had a client experience this problem (unless there was > something > > > weird in their use of SQL) but I'd like to be forewarned. > > > > The biggest problem is when 5.xx users continue to do UPDATE STATISTICS > with > > no level option, as they did in 5.xx, and so have no data distributions > for > > the 7.xx optimizer to work with. Others are not configuring additional > CPU > > VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and > > CLEANERS (5.xx used a different algorithm for buffer access which was > slower > > but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to > > reduce contention). Failure to take advantage of 7.xx features that > improve > > performance is a biggy also, no fragmentation no parallelism, detached > indexes > > can help spread hot spots across disks also. > > > > Art S. Kagel

Don't let OPTCOMPIND fool you. DOing the full UPDATE STATISTICS suite as recommended in the PERFORMANCE GUIDE is still required for best optimizer performance. We run OPTCOMPIND == 0 and I was still prompted to write dostats to run the suite for me. And you are right setting OPTCOMPIND to anything other than 0 in an OLTP environment is asking for dog slow performance and yes IDS 7.xx/9.2x really does shine on an SMP system. Art S. Kagel Noel Griffin wrote: > > Online 5 is faster than IDS 7 on uniprocessor systems, even if both > environments are optimised. UPDATE STATISTICS without the HIGH verb usually > makes V7 woefully slow. Most DBA's set OPTCOMPIND to 0 in the onconfig file > which only requires an UPDATE STATISTICS as it uses the simpler optimiser, > which is fine for OLTP. > > NOEL GRIFFIN > Ireland > > Art S. Kagel <kagel@bloomberg.net> wrote in message > news:386BC39D.38A25138@bloomberg.net... > > Andy Kent wrote: > > > > > > I've heard a lot of talk about people managing to get IDS 7 to run more > > > slowly than OnLine 5 on an identical configuration. > > > > > > For what kind of reasons does this come about? What parameters simply > cannot > > > be left the same when you migrate to IDS? > > > > > > I've never had a client experience this problem (unless there was > something > > > weird in their use of SQL) but I'd like to be forewarned. > > > > The biggest problem is when 5.xx users continue to do UPDATE STATISTICS > with > > no level option, as they did in 5.xx, and so have no data distributions > for > > the 7.xx optimizer to work with. Others are not configuring additional > CPU > > VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and > > CLEANERS (5.xx used a different algorithm for buffer access which was > slower > > but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to > > reduce contention). Failure to take advantage of 7.xx features that > improve > > performance is a biggy also, no fragmentation no parallelism, detached > indexes > > can help spread hot spots across disks also. > > > > Art S. Kagel

Don't let OPTCOMPIND fool you. DOing the full UPDATE STATISTICS suite as recommended in the PERFORMANCE GUIDE is still required for best optimizer performance. We run OPTCOMPIND == 0 and I was still prompted to write dostats to run the suite for me. And you are right setting OPTCOMPIND to anything other than 0 in an OLTP environment is asking for dog slow performance and yes IDS 7.xx/9.2x really does shine on an SMP system. Art S. Kagel Noel Griffin wrote: > > Online 5 is faster than IDS 7 on uniprocessor systems, even if both > environments are optimised. UPDATE STATISTICS without the HIGH verb usually > makes V7 woefully slow. Most DBA's set OPTCOMPIND to 0 in the onconfig file > which only requires an UPDATE STATISTICS as it uses the simpler optimiser, > which is fine for OLTP. > > NOEL GRIFFIN > Ireland > > Art S. Kagel <kagel@bloomberg.net> wrote in message > news:386BC39D.38A25138@bloomberg.net... > > Andy Kent wrote: > > > > > > I've heard a lot of talk about people managing to get IDS 7 to run more > > > slowly than OnLine 5 on an identical configuration. > > > > > > For what kind of reasons does this come about? What parameters simply > cannot > > > be left the same when you migrate to IDS? > > > > > > I've never had a client experience this problem (unless there was > something > > > weird in their use of SQL) but I'd like to be forewarned. > > > > The biggest problem is when 5.xx users continue to do UPDATE STATISTICS > with > > no level option, as they did in 5.xx, and so have no data distributions > for > > the 7.xx optimizer to work with. Others are not configuring additional > CPU > > VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and > > CLEANERS (5.xx used a different algorithm for buffer access which was > slower > > but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to > > reduce contention). Failure to take advantage of 7.xx features that > improve > > performance is a biggy also, no fragmentation no parallelism, detached > indexes > > can help spread hot spots across disks also. > > > > Art S. Kagel

Don't let OPTCOMPIND fool you. DOing the full UPDATE STATISTICS suite as recommended in the PERFORMANCE GUIDE is still required for best optimizer performance. We run OPTCOMPIND == 0 and I was still prompted to write dostats to run the suite for me. And you are right setting OPTCOMPIND to anything other than 0 in an OLTP environment is asking for dog slow performance and yes IDS 7.xx/9.2x really does shine on an SMP system. Art S. Kagel Noel Griffin wrote: > > Online 5 is faster than IDS 7 on uniprocessor systems, even if both > environments are optimised. UPDATE STATISTICS without the HIGH verb usually > makes V7 woefully slow. Most DBA's set OPTCOMPIND to 0 in the onconfig file > which only requires an UPDATE STATISTICS as it uses the simpler optimiser, > which is fine for OLTP. > > NOEL GRIFFIN > Ireland > > Art S. Kagel <kagel@bloomberg.net> wrote in message > news:386BC39D.38A25138@bloomberg.net... > > Andy Kent wrote: > > > > > > I've heard a lot of talk about people managing to get IDS 7 to run more > > > slowly than OnLine 5 on an identical configuration. > > > > > > For what kind of reasons does this come about? What parameters simply > cannot > > > be left the same when you migrate to IDS? > > > > > > I've never had a client experience this problem (unless there was > something > > > weird in their use of SQL) but I'd like to be forewarned. > > > > The biggest problem is when 5.xx users continue to do UPDATE STATISTICS > with > > no level option, as they did in 5.xx, and so have no data distributions > for > > the 7.xx optimizer to work with. Others are not configuring additional > CPU > > VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and > > CLEANERS (5.xx used a different algorithm for buffer access which was > slower > > but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to > > reduce contention). Failure to take advantage of 7.xx features that > improve > > performance is a biggy also, no fragmentation no parallelism, detached > indexes > > can help spread hot spots across disks also. > > > > Art S. Kagel

Don't let OPTCOMPIND fool you. DOing the full UPDATE STATISTICS suite as recommended in the PERFORMANCE GUIDE is still required for best optimizer performance. We run OPTCOMPIND == 0 and I was still prompted to write dostats to run the suite for me. And you are right setting OPTCOMPIND to anything other than 0 in an OLTP environment is asking for dog slow performance and yes IDS 7.xx/9.2x really does shine on an SMP system. Art S. Kagel Noel Griffin wrote: > > Online 5 is faster than IDS 7 on uniprocessor systems, even if both > environments are optimised. UPDATE STATISTICS without the HIGH verb usually > makes V7 woefully slow. Most DBA's set OPTCOMPIND to 0 in the onconfig file > which only requires an UPDATE STATISTICS as it uses the simpler optimiser, > which is fine for OLTP. > > NOEL GRIFFIN > Ireland > > Art S. Kagel <kagel@bloomberg.net> wrote in message > news:386BC39D.38A25138@bloomberg.net... > > Andy Kent wrote: > > > > > > I've heard a lot of talk about people managing to get IDS 7 to run more > > > slowly than OnLine 5 on an identical configuration. > > > > > > For what kind of reasons does this come about? What parameters simply > cannot > > > be left the same when you migrate to IDS? > > > > > > I've never had a client experience this problem (unless there was > something > > > weird in their use of SQL) but I'd like to be forewarned. > > > > The biggest problem is when 5.xx users continue to do UPDATE STATISTICS > with > > no level option, as they did in 5.xx, and so have no data distributions > for > > the 7.xx optimizer to work with. Others are not configuring additional > CPU > > VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and > > CLEANERS (5.xx used a different algorithm for buffer access which was > slower > > but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to > > reduce contention). Failure to take advantage of 7.xx features that > improve > > performance is a biggy also, no fragmentation no parallelism, detached > indexes > > can help spread hot spots across disks also. > > > > Art S. Kagel

Hi, I wonder why so many people think that the IDS optimizer cannot work without the UPDATE STATISTICS HIGH/MEDIUM options. If there is a common rule, how to run the "update statistics high/medium/low", why does Informix not automatically suggest one of the options ? Sure, Informix made a few changes, i.e. the sort merge join seems not to be available on IDS. But most of all the optimizer behaviours didn't change from 5.x to 6 or 7.x. A lot of customers who migrated from 5.x to 7.x showed me their output of the SET EXPLAIN when they complained about the new performance. And the query plans didn't change. And the question is, as long as the query plans do not change, why is the new version slower than the version 5.x ? The reason why version 7.x is sometimes slower than version 5.x doesn't always depend on the changes inside the optimizer. It depends on the test itself. Starting with version 6.x Informix changed the internal architecture to multithreaded. This new architecture costs more processes than before in a single-user environment. Version 5.x needed a "tbinit" process along with the "sqlturbo" process. To do just the same things, 7.x needs at least 6 "oninit" processes. Additionally Informix integrated a lot of new features starting with version 6.x, i.e. scheduling, b-tree cleaner threads and so on. Noone will get all these new things for free. So my advice whenever you intend to compare both versions is: Never run the test with just a few sessions. Best regards -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----

WOW, you liked this message so much you posted it 5 times. LOL ;-) "Art S. Kagel" wrote: > Don't let OPTCOMPIND fool you. DOing the full UPDATE STATISTICS suite > as recommended in the PERFORMANCE GUIDE is still required for best > optimizer performance. We run OPTCOMPIND == 0 and I was still prompted > to write dostats to run the suite for me. And you are right setting > OPTCOMPIND to anything other than 0 in an OLTP environment is asking > for dog slow performance and yes IDS 7.xx/9.2x really does shine on an > SMP system. > > Art S. Kagel > > Noel Griffin wrote: > > > > Online 5 is faster than IDS 7 on uniprocessor systems, even if both > > environments are optimised. UPDATE STATISTICS without the HIGH verb usually > > makes V7 woefully slow. Most DBA's set OPTCOMPIND to 0 in the onconfig file > > which only requires an UPDATE STATISTICS as it uses the simpler optimiser, > > which is fine for OLTP. > > > > NOEL GRIFFIN > > Ireland > > > > Art S. Kagel <kagel@bloomberg.net> wrote in message > > news:386BC39D.38A25138@bloomberg.net... > > > Andy Kent wrote: > > > > > > > > I've heard a lot of talk about people managing to get IDS 7 to run more > > > > slowly than OnLine 5 on an identical configuration. > > > > > > > > For what kind of reasons does this come about? What parameters simply > > cannot > > > > be left the same when you migrate to IDS? > > > > > > > > I've never had a client experience this problem (unless there was > > something > > > > weird in their use of SQL) but I'd like to be forewarned. > > > > > > The biggest problem is when 5.xx users continue to do UPDATE STATISTICS > > with > > > no level option, as they did in 5.xx, and so have no data distributions > > for > > > the 7.xx optimizer to work with. Others are not configuring additional > > CPU > > > VPs, sufficient AIO VPs (where KAIO is not available), not enough LRUS and > > > CLEANERS (5.xx used a different algorithm for buffer access which was > > slower > > > but less prone to LRU contention so 7.xx needs more LRUs than 5.xx did to > > > reduce contention). Failure to take advantage of 7.xx features that > > improve > > > performance is a biggy also, no fragmentation no parallelism, detached > > indexes > > > can help spread hot spots across disks also. > > > > > > Art S. Kagel