Only half CPUs being fully utilized

[Kenneth Penland]

On one of our systems our DBAs are complaining about how their database is running extrememly slow. after taking a look at top/glance/vmstat we all see something odd that I cannot explain. any thoughts as to why all 8 cpus appear to be "used" but only 4 are getting hit hard, while the other 4 seem to be practically idle?

attached is a txt output from the top command.

[Torsten.]

It would be nice to know what server model / what CPUs / what OS you are using.

Hyperthreading enabled?

[Kenneth Penland]

rx7640 running 11.23

[Dennis Handly]

Can you check the rest of the top(1) output and see which processes are running on those 4 busy CPUs?

If you aren't using threading, 4 processes can only use 4 CPUs.

[Kenneth Penland]

sorry, it took me a minute to remember the command to get additional information:

Number of CPUs = 8
Clock speed = 1598 MHz
Bus speed = 533 MT/s
CPUID registers
vendor information = "GenuineIntel"
processor serial number = 0x0000000000000000
processor version info = 0x0000000020010104
architecture revision: 0
processor family: 32 Intel(R) Itanium 2 9100 series
processor model: 1 Intel(R) Itanium 2 9100 series
processor revision: 1 Stepping A1
largest CPUID reg: 4
processor capabilities = 0x0000000000000005
implements long branch: 1
implements 16-byte atomic operations: 1
Bus features
implemented = 0xbdf0000020000000
selected = 0x0020000000000000
Exclusive Bus Cache Line Replacement Enabled

Cache info (per core):
L1 Instruction: size = 16 KB, associativity = 4
L1 Data: size = 16 KB, associativity = 4
L2 Instruction: size = 1024 KB, associativity = 8
L2 Data: size = 256 KB, associativity = 8
L3 Unified: size = 9216 KB, associativity = 9

Memory = 16249 MB (15.868164 GB)

Firmware info:
Firmware revision = 9.048
FP SWA driver revision: 1.18
IPMI is supported on this system.
ERROR: Unable to obtain manageability firmware revision info.

Platform info:
model string = "ia64 hp server rx7640"

OS info:
sysname = HP-UX
release = B.11.23
version = U (unlimited-user license)
machine = ia64
@(#) $Revision: vmunix: B11.23_LR FLAVOR=perf Fri Aug 29 22:35:38 PDT 2003 $

[Kenneth Penland]

Okay, here is top with more info...basically the question they are asking is why are all of these just using cpu 1-4 and skipping 0, 5-8?

[Dennis Handly]

>the question they are asking is why are all of these just using CPU 1-4 and skipping 0, 5-7?

Good question.
All of the ora_s*_dlcdbap and ora_s*_dlcdbap processes seem to be on 1-4.

Are you using any workload manager? Or fairshare scheduler?

Or is Oracle using mpctl(2)?

[Kenneth Penland]

I do not believe so....the common consensus between admins here are that there are just some "big processes" that happened to of started on 1-4...and those are plugging the pipe so to speak on those 4 CPUs, while the other CPUs are able to keep up... is there a way to tell if there is some sort of workload manager at work on the box? I dont think there is cause it would have to of been placed on the box, and I know I didnt do it!

[Dennis Handly]

>the common consensus between admins here are that there are just some "big processes" that happened to have started on 1-4 and those are plugging the pipe so to speak on those 4 CPUs, while the other CPUs are able to keep up.

top(1) says they are idle, nothing to "keep up". The ones that are busy are mostly in the kernel (SYS).

>is there a way to tell if there is some sort of workload manager at work on the box?

Since they cost money, it should be obvious. :-)
Do you see anything obvious with:
swlist -l bundle

[Dennis Handly]

I should have asked, do you have glance to see what those processes are doing?
You could also try caliper, it's free.

[Kenneth Penland]

came back from lunch and it is no longer "in the red" so to speak, but looking at top you can still see that 1-4 are favored (see attached)

I even looked at psrset and it looks like all cpus are just in the default grouping:

$ /usr/sbin/psrset
PSET 0
SPU_LIST 0 1 2 3 4 5 6 7
OWNID 0
GRPID 0
PERM 755
IOINTR ALLOW
NONEMPTY DFLTPSET
EMPTY FAIL
LASTSPU DFLTPSET
LDOM_LIST 0 1
RTE DISALLOW

[djoshi]

Hi,

My opinion, if the database is running slow, it does not mean that problem with the UNIX system. My advice check the database logs as well.

If your system having 8 CPU, it does not mean all 8 will be busy every time, it depends on the incoming request and load.

This is what I can think as of now.

[Elmar P. Kolkman]

What kind of storage are you using for your database?
SYS could also mean the system is really busy providing the data for the database, blocking almost all I/O for other processes on the filesystem but showing 'activity' in top. (Glance does this better, especially when zooming in on processes or looking at syscalls.)

[Ganesh Balraman]

Hi Penland!

Could you provide some more details.

parstatus -V
vparstatus -V (if vpar exist)
sar -Muqc
sar -b
sar -v
sar -u
ioscan | grep -i processor
uname -r


Best Regds,
Ganesh

[Kuntal Gupta]

I think you are using SIM where 4 cores are assigned by any policy.
To confirm whether SIM is running use
#ps -eaf | grep -i wbem

If yes, login to SIM by http://:280 to confirm

[Bill Hassell]

> only 4 are getting hit hard...

This means that only 4 processes are busy computing and the rest are either sleeping or waiting on I/O. You can't expect a single process to be split across multiple CPUs. Your DBA configures the number of available processors in Oracle and Oracle with adjust internal code to take advantage of the resources. But for a single thread of code, only one CPU will be used. If it uses 100% of that CPU, you cannot 'share' the load across 2 CPUs.

[Kenneth Penland]

thanks all for your responses...currently the system is down....my fingers are crossed that once it comes back up it will be on something OTHER than 1-4 ;)

[chris huys_4]

Hi Kenneth,

Your second top output, shows that also cpu 6 gets hammered, so I dont think the problem is the # of cpu "an sich".

However its not normal that oracle processes are spending that much time in 'system mode'.

Log a call with oracle support and see if they can find out which system calls are causing the oracle processes to spend that much time in system mode.

If oracle support doesnt come up with satisfying answers, log a performance case with hp support and a internal tool as kitrace should be able to find out what is going on.

Greetz,
Chris
PS. Installing the latest patchbundle should also be a good idea.

[Kenneth Penland]

thanks, I shared your thoughts with the DBAs to see what they say....on a side note..the box was rebooted a few days ago and when everything was restarted cpus 1-4 took the top spots again, leaving the rest of the CPUs idle in comparison. I was hoping 4 new CPUs would be maxed out but it was the exact same ones as before...we have a call out to the next level of HP support to try and figure it out.

[Mark H Stewart]

Kenneth, I did a Google search on those Oracle process names, and didn't find any hits; I'm curious what they are.

I have a somewhat similar situation, but in my case, it is when I invoke the PL/SQL package dbms_stats.gather_table_stats with the degree parameter specifying parallel processing. Oracle proceeds to launch 72 processes, but they only run on 4 of the 8 CPUs. I enabled hyperthreading, and same results: the processes only run on 8 of the 16 CPUs! I've had installed the Oracle-recommended patches for 11.31, and same results. I have a ticket open with Oracle. I can do other queries that do use all the CPUs, but that particular command is using just half the CPUs.

[Zinky]

2 Possibilities:

1.) SOme of your Oracle DB processes are likely set for processor affinity. I have seen and done this in various TPC and SwingBench benchmark recipes.

2.) The DB is missing a setting in its ora.init file -- which I can't remember.. I will post once I recall what it is.

Cheers.

[Mark H Stewart]

Thanks for the ideas, Alzhy. I tweaked some code from http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=6809 to use the mpctl call to display the current processor and locality domain binding type for the PIDs in question, and sure enough, the parallel process PIDs came back with the value of MPC_LDOM_BINDING (103 - see sys/mpctl.h). I've attached the code as getproc.c. And what else is interesting is that all of the Oracle background processes *also* had that flag set, which could explain the original poster's issue.

I've already checked the init.ora values; they look good.

[Zinky]

Kenneth and Mark,

Are you guys still having this issue?

[Pete Randall]

I have to agree with Alzhy on this. It sounds like processor affinity to me. I'm not an Oracle type, we use Informix, but in my world the DBA controls processor affinity, so I would ask them.


Pete

[Kenneth Penland]

yeah, we got it fixed...turned out the problem was with the oracle release that we were running...the DBA's upgraded to Oracle 10.2.0.5 and it resolved the issue.