RE: 7.30. To be or not to be ?

Emidio/Daniel, I recently (the memory still burns) went through an upgrade - 7.24 to 7.30.UC8 and I hope the experience helps your situation to be investigated further. I am also posting this as there is a question at the end that I have still not got answered and hope inputs can make me understand further. Environment Unix : HP-UX hercules B.10.20 U 9000/893 Informix :Informix Dynamic Server Version 7.30.UC8 The performance was shot after the upgrade and initially no parameters were changed. Checkpoints had increased in length and the tuning process started. After trying a couple of avenues, improvements were seen, but nothing that allowed me to go home early and go the long route (I ride a motorbike to and from work). Then came the analysis, reported to tech support. Two main things noticed : 1 Disk and CPU utilization was very high (100% most of the time) 2 Number of threads for each user had increased dramatically in the "onstat -u" (In 7.24 we had two user threads per user in a normal situation and in 7.30 there were >70). This was reported to Tech Support and they assumed it was related to PDQ and those parameters were analysed and changed. The process even went further to try and set individual user PDQ priority but no improvements. Another thing noticed was that since the disk utilization was so high, periodically the functioning of the apps came to a standstill and this was due to the disk being hit hard. Table distribution was modified across chunks. No help. Kernel AIO a new introduction after the upgrade was also stopped as it did not seem to help in anyway and elimination only seemed logical We had come to a point where the system had to be rebooted OR informix bounced as that was the only way to kill all users and allow them to log in through a controlled manner (sliced usage) so each business group could get some of their job done. Countless sleepless nights. Logging mode change from Unbuffered to Buffered to reduce IO. Waiting patiently for tech support to come back. Begin performance improvements on the application side (as we did also have a high number of seq scans, but values were not consistent). To cut a long story short, a flag that allows parallelization to stop/reduce (not sure which) was finally introduced in the puzzle "NO_SUBQF=1". And we lived happily ever after .... MY question : We use PowerBuilder for developing client apps. What I could not understand is that the paralelization is an added bonus for improvement of performance, but I had to turn it off. How can I use it to my advantage, rather than it degrade performance? I even analysed a "good" query which was tried and tested in 7.24. Query plan and timings were checked. This when applied on 7.30 would run off and create so many parallel threads with joins and sorts that were not seen in 7.24. I have to assume the optimizer works differently but again brings me back to my question listed above. How can I use it? Munaf DSL Transportation Services South Gate, CA Emidio wrote : <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Daniel, we are using Informix Dynamic Server Version 7.31.UC3 on HP-UX 10.20 on HP9000 model D (D330) and we have a big problem with performances. We migrate an ERP application from Informix SE 5.0 on HP-UX 10.20 on a HP workstation (712); in the current machine we have same users, more memory (256 vs 128), a faster CPU and more disk and swap space on disk, but we have worse prerformances, especially for a MRP procedure. J think that the problem is in the OS or in the machine, because we have, in another site, the same application on the old 712 workstation with Informix IDS 7.31 and, in that case, good performances. J have tried many different configurations in Informix and Kernel parameters and J have applied all of HP known pathches, without results. So, could you help me some information about your current machine and about its configuration? Could you send the output of kernel parameters configuration? Thank you in advance. Emidio Serafini. P.S. J'm going to send this message to the newsgroup, but J think it isn't interesting because too particular. >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >

OK, you ran into the one of the big differences between 7.2x and 7.3x which is that 7.3x does 'Correlated Sub-QUery Flattening' the SQF in the environment var to disable it that you mention below. Since all correlated sub-queries can be rewritten as simple joins (try it) the engine can automate this. The result is a simpler query that runs faster BUT IFF there are indexes available to support the resulting join(s) otherwise it is slower. You likely have to review your database design and those queries which are being flattened and create the missing indexes. Then you can turn NO_SUBQF off again. Art S. Kagel Munaf Razzak wrote: > > Emidio/Daniel, > I recently (the memory still burns) went through an upgrade - 7.24 to > 7.30.UC8 and I hope the experience helps your situation to be investigated > further. > > I am also posting this as there is a question at the end that I have still > not got answered and hope inputs can make me understand further. > > Environment > Unix : HP-UX hercules B.10.20 U 9000/893 > Informix :Informix Dynamic Server Version 7.30.UC8 > > The performance was shot after the upgrade and initially no parameters were > changed. > Checkpoints had increased in length and the tuning process started. > After trying a couple of avenues, improvements were seen, but nothing that > allowed me to go home early and go the long route (I ride a motorbike to and > from work). > Then came the analysis, reported to tech support. Two main things noticed : > 1 Disk and CPU utilization was very high (100% most of the time) > 2 Number of threads for each user had increased dramatically in the "onstat > -u" (In 7.24 we had two user threads per user in a normal situation and in > 7.30 there were >70). > > This was reported to Tech Support and they assumed it was related to PDQ and > those parameters were analysed and changed. The process even went further to > try and set individual user PDQ priority but no improvements. > > Another thing noticed was that since the disk utilization was so high, > periodically the functioning of the apps came to a standstill and this was > due to the disk being hit hard. Table distribution was modified across > chunks. No help. > > Kernel AIO a new introduction after the upgrade was also stopped as it did > not seem to help in anyway and elimination only seemed logical > > We had come to a point where the system had to be rebooted OR informix > bounced as that was the only way to kill all users and allow them to log in > through a controlled manner (sliced usage) so each business group could get > some of their job done. > > Countless sleepless nights. Logging mode change from Unbuffered to Buffered > to reduce IO. Waiting patiently for tech support to come back. Begin > performance improvements on the application side (as we did also have a high > number of seq scans, but values were not consistent). To cut a long story > short, a flag that allows parallelization to stop/reduce (not sure which) > was finally introduced in the puzzle "NO_SUBQF=1". And we lived happily ever > after .... > > MY question : > We use PowerBuilder for developing client apps. What I could not understand > is that the paralelization is an added bonus for improvement of performance, > but I had to turn it off. How can I use it to my advantage, rather than it > degrade performance? > > I even analysed a "good" query which was tried and tested in 7.24. Query > plan and timings were checked. This when applied on 7.30 would run off and > create so many parallel threads with joins and sorts that were not seen in > 7.24. I have to assume the optimizer works differently but again brings me > back to my question listed above. How can I use it? > > Munaf > DSL Transportation Services > South Gate, CA > > > > Emidio wrote : > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< > Daniel, > we are using Informix Dynamic Server Version 7.31.UC3 on HP-UX 10.20 on > HP9000 model D (D330) and we have a big problem with performances. > We migrate an ERP application from Informix SE 5.0 on HP-UX 10.20 on a HP > workstation (712); in the current machine we have same users, more memory > (256 vs 128), a faster CPU and more disk and swap space on disk, but we have > worse prerformances, especially for a MRP procedure. > J think that the problem is in the OS or in the machine, because we have, in > another site, the same application on the old 712 workstation with Informix > IDS 7.31 and, in that case, good performances. > J have tried many different configurations in Informix and Kernel parameters > and J have applied all of HP known pathches, without results. > So, could you help me some information about your current machine and about > its configuration? Could you send the output of kernel parameters > configuration? > Thank you in advance. > > Emidio Serafini. > > P.S. > J'm going to send this message to the newsgroup, but J think it isn't > interesting because too particular. > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > >