Informix CPU Usage and VPAR

[Dan Ryan]

I've recently implemented VPARs on an N4000 running HPUX 11i. One VPAR has 4 CPUs and is our Informix (731fc4) Data WareHouse using raw disks.

Our DBA complains that when he runs a table scan (no index) it now takes several minutes when in the past it took several seconds. We don't have any historical data to verify his claims. But the latest change is the VPAR implementation.

When he runs his query I can see all four CPUs running oninit at 97% utilization for the duration of the query. Each CPU is running in a real time priority. This degrades the performance on non real time tasks.

I was surprised to see one query allocate all the CPUs. Is this a normal Informix interaction with HPUX?

There aren't any privileged groups on this server. I thought I'd find a /etc/privgroup file that bestowed the rtprio privilege to the Informix group.

I'm about to suggest we remove the VPAR and test Informix to see how it runs under straight vmunix. I'd appreciate any info concerning Informix use of rtprio and CPU utilization.

thanks for your time and input.

[Steven E. Protter]

Collect some data(attached sar script).

If the systems isn't stressed out whats running is going to get as much CPU as it asks for.

Other bottlenecks like Disk I/O can account for this.

Also kernel settings.

SEP

[Dan Ryan]

Thank you for the input and sar script.

[Edwin Dikhooff]

Hi Dan,

Informix has a configuration file 'onconfig..' in which the DB can set how many CPUs Informix may use. The variable names are MULTIPROCESSOR, NUMCPUVPS and SINGLE_CPU_VP.
It limits Informix processes to use only the assigned number of CPUs and give other processes some CPU time.

Hops this helps.

-- Edwin

[Rainer von Bongartz]

Best practice on a multi processor box is:

NUMCPUVPS = -1


Regards
Rainer

[Steve Lewis]

This sounds like normal behaviour for a full table scan in a data warehouse. Indexed reads consume very little cpu, non-indexed reads (FTS) consume a lot.

It is probably using all cpus because your DBA defined parallel data query across several dbspaces or dbslices.

PDQ normally improves performance.
I am guessing that the vpars have reduced performance since all the i/o needs to be re-directed to the correct vpar.

rtprio doesn't always work well, since your database starts affecting the performance of the o/s and user response can actually go down. I only recommend setting NOAGE=1 when the system isn't used by any other application.

The answer is to test the same query with and without vpars.

[JJ_4]

What is the throughput that you get just using dd to read from the "chunks" allocated to Informix?

i.e. timex dd if="chunk where some informix data is" bs=2k count=50000 of=/dev/null

and see what MB per second you get (the above pulls off 10 Mb). Informix reads in 2k chunks generally.

Above 4Mb per second is good :)
Below 1 Mb per second is bad :(

How do you get the oninit processes as rtprio? I wouldn't recommend this.

[Pete Randall]

Dan,

I would guess that the NUMCPUVPS was not adjusted from setting it had in the non-VPAR configuration. As Rainer mentioned, it should be set to one less than the number of CPUs available. The reason for this is that Informix will hog all the CPUs you allow it to have - to the detriment of the operating system itself. HP-UX needs to have a CPU reserved for it to do its own work. When Informix gets all the available CPUs and HP-UX has to fight for a time-slice to do it's own housekeeping, performance can vary widely bit its generally going to be poor.


Pete

[Dan Ryan]

Thanks you all these responses.
I believe the reason Informix is running in real time priorities is beaces the suid bit is set f or oninit with root being the owner.
I don't think the VPAR I/O redirection is an issue. My understanding is once a vpar is up the vpmon no longer involved and the HPUX instances have ownership of the hardware.


I'm going to recommend that the Informix folks look to reducing the MUMCPUVP parameter, which I know won't make them happy.

Another area to review is our KAIO configuration. Currently our Informix ENV has KAIOON=0 my understanding is that this disables KAIO. But when I do a onstat -g ath | grep kaio shows Informix is running kaio threads. The question is what will setting KAIOON=1 buy us? My thought is perhaps this would allow the ernel more control of the amount of I/O , priority etc.. any more input on this.

Thanks again everyone.

[JJ_4]

Having KAIOON set to anything enables informix to use kaio. Changing KAIOON=0 to KAIOON=1 will change nothing, unsetting KAIOON (unset KAIOON in the informix startup environment) will stop informix using kaio, however you then need to make sure that NUMAIOVPS is roughly the number of active chunks.

What is the dd throughput (as suggested previously)? What would you reasonably expect from this?