7.3 performance problem with stored procedure
Posted in 1998
Topics: Performance & Tuning, Stored Procedures & SPL, Server Administration, Platform-Specific Issues
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