Some Performance issues

[so_2]

Hi, One of my hpux servers is showing performance degrade in on accessing data base. I had checked the server for top , vmstat , iostat etc. all of which did not point to any hunch.

I have 8 processors and 16 gb memory .All processors are acyive as per top and I checked using SAM.
The only problem I am finding is the
device %busy avque r+w/s blks/s
c3t0d3 100.00 0.50 232 7065
or some times at 79% busy.

This disc is used to hold the data base.
Now our capacity team suggest that eventhough the processors are showing as 8 active only 2 are active in the system as of now. (The system is fooling OS!!!). Also they are saying that there are a reduction in the system calls recently which also points to the same problem that active CPUs are redused.

Could you suggest me other than checking with top or sam how do we find out the active CPUs?

Also how do we find the number of system calls ?

Any other recommendation on this is also highly aprciated.

Thanks in advance.
S.O

[Jaime Bolanos Rojas. · Accepted solution]

So,

ohh, you forgot to mention what OS are you using,

for 11.11 to find out how many procs are in use:

echo runningprocs/D | adb -k /stand/vmunix /dev/mem

Also if you want data cpu load for each proc run this command

sar -Mu 5 100

Regards,

Jaime.

[so_2]

Thanks a lot Jaime for your fast response.
I am using Hpux 11:00. Hence your fisrt command is not working.
Any other options ? Also how do i find the number of system calls ?

Thanks
S.O

[Bill Hassell]

All your processors are being used. The fact that only 2 are showing activity is because only two processes are running. There is no way to spread a process across multiple CPUs -- it requires rewriting the program into a threaded application. When a database seems to run more slowly, the solution is not simple and the classic Unix tools like sar and top are not very usefuk in finding the exact reason. The majority of database performance issues are related to the design of the database (lack of indexes, unbalanced indexes, data and rollback file contention), as well as the SQL query designs.

The only solution to finding out program details is to use gloance, a priced product from HP. No other tools provides the detail that you need to solve these types of problems. And if you are using Oracle, the statpak tool is also invaluable.

[spex]

Hi,

1) Another way to see CPU utilization (note that CPU# does not appear):

# UNIX95=1 ps -e -o pcpu,pid,ppid,user,vsz,args | sort -n -k1

2) System calls:

# sar -c 5 100
# glance -Y

PCS

[Jaime Bolanos Rojas.]

So,

I hope this helps, the proc command is almost identical,

Try this one for procs then to see if it works:

echo "runningprocs/D" | adb -k /stand/vmunix /dev/mem


And this link should answer your question about system calls.

Regards,

Jaime.

[so_2]

Thanks Bill,
Now let us assume that my server ans OS is fine, here is where I am challenged by the capacity team.

s.o

[Jaime Bolanos Rojas.]

Jez, I forgot about the link :-)

http://www.faqs.org/faqs/hp/hpux-faq/section-200.html

Sorry,

Jaime.

[Marvin Strong]

Unfortunately as Bill said, the system tools are limited in helping with database performance problems. Glance while it is a purchase there is also a 30 day trial on most application cd's you could install that. If you haven't purchased glance.

Another thing to note is system tweaking will not gain you much performance in a database. maybe 10-20% increase. While database tweaking the gains can be much higher.

So you might suggest to your dba's that they look and see what they can maybe tweak from their side. Specifically what part of the database is on c3t0d0? If possible move some of the busy tables to another less busy disk.

Are your disks external on an array? If so do they have multiple paths? If so are you spreading the IO down both paths? This may or may not be a problem. But it is worth looking into.

[Steven E. Protter]

Shalom,

Best Doc for Oracle

http://www1.itrc.hp.com/service/cki/search.do?searchString=UPERFKBAN00000726&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchCrit=allwords&search.x=28&admit=-682735245+1156421605815+28353475&category=c0&mode=id&search.y=8

SEP

[Peter Godron]

Hi,
you don't mention the signals for the performance degrade. Are database processes running slower or response times longer?

If your machine has not changed, something else must have. Has the code changed, are there more users, are process/disk intensive processes running not in their normal timeslot.

If for example an user is running an import, whilst other users are using the DB everything would slow down.

From a Performance Tuning book :
"Programs - 60%
System Design - 20%
Database Engine - 17.5%
System Problems - 2.5% "