Re: Informix hogging CPU
Mike writes:
>I am running Informix Online Server version 7.1 on D250, one cpu, HPUX
>10.10. When Informix gets hit with an SQL statement doing a few joins an a
>35,000 record database, the system just crawls while the statement
>executes (takes about 5 minutes). No one can login and if you are logged
>in, you get a response to hitting ENTER about 40 seconds after hitting it.
>TOP shows oninit processes running with a priority of -16 and oninit
>processes using about 90% of the CPU. I don't see any obvious errors in
>the ONCONFIG file. Has anyone seen anything like this and is there any way
>of configuring Informix to use less CPU?
Several things come to mind:
"...a few joins..." is a loaded statement. I could construct a few joins
that would bring any database to its knees. Look first there. Have you
run them with the SET EXPLAIN ON in your SQL? Is the optimizer trying to
perform some convoluted cartesian product? Have you run UPDATE STATISTICS
since the last significant change to the data?
Do you have any of the statistics collection parameters turned on? Look
for things like WAITSTATS in the onconfig file. I heard one time of
that particular monitor accessing the system "gettimeofday" call thousands
of times per *second*, and thus consuming an inordinate amount of CPU time.
You can check this with HP's outstanding implementation of "glance."
In general, the more you monitor the poorer the system will perform, if
your monitoring tools compete for resources with the processes they monitor.
OL 7.1 and HP-UX 10.10 were replaced with new versions that have a great
number of improvements made. If you find that the problem is with either the
O/S or the engine the advice you receive will probably be to upgrade.
Is PDQPRIORITY properly set? It can cause your engine to devote all its
resources to one user at a time, giving similar symptoms.
HTH,
______________________________________________________
Clem Akins (aka clem@informix.com) <- NEW ACCOUNT!
Informix Software, Inc (Standard disclaimers apply)
International Technical Support
Last seen: Pondering a rainy walk to work, Auckland