Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Informix CPU Usage and VPAR

Dan Ryan
Advisor

Informix CPU Usage and VPAR

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.

Don't have time to do it right the first time, but always time to do it twice
10 REPLIES
Steven E. Protter
Exalted Contributor

Re: Informix CPU Usage and VPAR

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Dan Ryan
Advisor

Re: Informix CPU Usage and VPAR

Thank you for the input and sar script.

Don't have time to do it right the first time, but always time to do it twice
Edwin Dikhooff
Occasional Visitor

Re: Informix CPU Usage and VPAR

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
Try anything once
Rainer von Bongartz
Honored Contributor

Re: Informix CPU Usage and VPAR

Best practice on a multi processor box is:

NUMCPUVPS = -1


Regards
Rainer
He's a real UNIX Man, sitting in his UNIX LAN making all his UNIX plans for nobody ...
Steve Lewis
Honored Contributor

Re: Informix CPU Usage and VPAR

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
Frequent Advisor

Re: Informix CPU Usage and VPAR

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.
Not enough Zappa makes you sad.
Pete Randall
Outstanding Contributor

Re: Informix CPU Usage and VPAR

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

Pete
Dan Ryan
Advisor

Re: Informix CPU Usage and VPAR

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.

Don't have time to do it right the first time, but always time to do it twice
JJ_4
Frequent Advisor

Re: Informix CPU Usage and VPAR

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?
Not enough Zappa makes you sad.
Dan Ryan
Advisor

Re: Informix CPU Usage and VPAR

JJ

Thanks for the infor on KAIOON. I was wondering why I saw kaio threads when this variable was set to 0.

Concerning the dd throughput .I ran a dozen or so tests on different chunks. The elapsed time varied between 14 an 17 elapsed seconds for 50,000 2KB records.

The dd commands don't get into the REalTime priority like the oninit does.

Don't have time to do it right the first time, but always time to do it twice