Re: 7.3 performance problem with stored procedure

>Hi, > >We have encountered a performance problem with Informix 7.3UC3 >on a Sun e3000 running solaris 2.6. > >It is a stored procedure that runs fine on an older config (sun ss1000e >with solaris 2.4 and informix 5.07). > >It is a simple insert into two tables (no explicit selects). >It runs subsecond on the old system. It takes about two seconds on >the new system. > >What is odd is that the problem does not manifest as excessive CPU, >disk or network activity. The only anomaly seen when running this >procedure on an otherwise idle machine is that sar shows excessive >values for 'sema/s', 'sycall/s', 'fork/s' and 'exec/s'. > >Overall, tho, the system CPU use is not high during this test (ie, >only a small fraction of one cpu is being used). > >Commensurate with the exec/s value, the system seems to be creating and >killing off about 25 PIDs per second while the procedure is being run from >dbaccess. I have however not been able to identify these ephemeral >processes. > >Note that we have tried setting the NO_SUBQF environment variable. >This did not help. We also tried the adjusting MAXPDQPRIORITY. This >also had no impact. > > >Has anyone else seen such behavior ? > >Any ideas would be most welcome. > > >thanks > > >rob > > The only reason that I can think of that those system values would be high is that the stored procedure is doing a lot of system calls. If you are using shared memory interface, then you would be getting some semaphore operations, but I can't think of a reason for the forks except for the sys() calls. Madison Pruet