Performance of servers

[rustam_2]

Hi everybody. I would like check performance of my servers where run HP-UX 11iv3 and soft DB oracle. Last days many queries run a long time… First I want to determine, do these problems are appeared coz my server’s low CPU value or RAM. Then want to check the DB Oracle. There are 2 servers, on 1st server runs PROD database and on 2nd server run 2 instances and 2 instances for datewarehouse (total 4 on 2nd one).
The servers’s hardware configurations are the same – RAM 16Gb and number of process are 4. I think performance of 2nd is too low.
Top shows me these amounts on prod:
http://img89.imageshack.us/img89/9053/prod.jpg
and on test shows these:
http://img545.imageshack.us/img545/2915/testfm.jpg

and swapinfo shows me on prod:
http://img440.imageshack.us/img440/2036/prodswap.jpg
swapinfo shows on test:
http://img143.imageshack.us/img143/4091/testswap.jpg

Regards,
Rustam

P.S. On prod arent many people yet, everything is checking and doing and test server.

[Hakki Aydin Ucar]

on the second picture idle time seems famiilar to me, not bad. I wrote a simple script to check in details if you want to use:

# cat statister
#!/bin/sh

sar -u 5 20 > /tmp/peracf$$
sar -d 5 20 > /tmp/perfd$$
vmstat 5 20 > /tmp/refvm$$
cat /tmp/peracf$$ |grep HP-UX|grep -v grep
cat /tmp/peracf$$ |grep %idle|grep -v grep
cat /tmp/peracf$$ |grep Average|grep -v grep
cat /tmp/perfd$$ |grep HP-UX|grep -v grep
cat /tmp/perfd$$ |grep %busy|grep -v grep
cat /tmp/perfd$$ |grep Average|grep -v grep
cat /tmp/refvm$$

[Steven E. Protter]

Shalom,

See this to gather more data.

http://www.hpux.ws/?p=6

That being said, your pictures show an oracle problem. Oracle is pegged on top using 100% of CPU.

This could be due to i/o wait, it could be due to an application problem requiring an oracle patch an OS patch or both.

The script I posted will let you identify an i/o problem or hot spot.

SEP

[chris huys_4]

Hi Rustam,

Top shows that the test system with 80008K = around = 80Mbyte of free physical RAM is desperately low on RAM. (80Mbyte of 16Gbyte physical RAM is less then 0.5% of free RAM)

swapinfo shows that the system is/was heavily paging.

The problem is finding out were all the physical memory is going.

Log a call with HP support and get the kmeminfo utility;

# kmeminfo
# kmeminfo -user

;the above commands executed should allready give a better idea about what is causing the "low memory situation".

Greetz,
Chris

[Alzhy]

It could be that on your 2nd server your Database Instances have needlessly large SGAs configured - thereby depleting usable RAM.

Please send: "ipcs -a" output.

And we can tell you how large SGAs each of your DB is using. Then you can ask your DBA if it can be trimmed down.

[muruganantham raju]

Rustam,
It is the second server that eats both the memory and the CPU. Off course the Oracle processes are consuming more CPU. Almost all the cpus are consumed fully (90%).

Now coming to memory, it drains out of memory and hence there are too much swapping activity (80% swap being utilized). This is further hindering the over all system performance.

Both CPU and memory bottlenecks lead to performance degradation. Both of them are hogging your second server.

Check the Oracle processes. I am not sure if they are leaking memory. Are there too much user operations destined to DB in 2nd server? Close monitoring of the requests to DB in 2nd server would help you to pin point the issue.

[rustam_2]

Thanks for all of you.

Hi Hakki Aydin, should I create script with values(which you suggest) and then run?

Hi Steven,
>> The script I posted will let you identify an i/o problem or hot spot.
This script gave nothing
# ./system.perf.sh
#

>> That being said, your pictures show an oracle problem. Oracle is pegged on top using 100% of CPU.
Because of SGA size or SQL quires? What do you think?

Hi Chris,
>> Top shows that the test system with 80008K = around = 80Mbyte of free physical RAM is desperately >>low on RAM. (80Mbyte of 16Gbyte physical RAM is less then 0.5% of free RAM)
I thought that free virtual memory is 80Mbytes.

What does mean? : >
Memory: 12547684K (3422440K) real, 19791712K (6147136K) virtual, 80008K free Page# 1/36
Does it means that 12,5 Gb memory of RAM is busy and 3,4Gb is free? 19,7Gb of virtual memory is busy and 6,1 Gb is free? What is 80008Kbytes then?
top also shows CPU loads, such as USER. What kind of percentages this columns shows? 0-70.1%, 1-80.5%, 2-48.4%, 3-85.7% and avg?
Moreover, there are (on test) 3 process : CPU-100.73% COMMAND- oracleprodleprod, CPU-98.69% COMMAND oracle CDWOWB â ¦
>> # kmeminfo
I havenâ t installed yet this utility


Hi Alzhy,
I attached result in attached file

Regards,
Rustam

[rustam_2]

Alzhy,
>>And we can tell you how large SGAs each of your DB is using. Then you can ask your DBA if it can be >>trimmed down.
on 2nd server run 4 instances as said. 2 ones SGA=6 Gb and PGA=8GB and other 2 ones (datewarehouseâ s ones) SGA=1.7 Gb and PGA 1.7GB total will be = 34.8 Gb, isnâ t crazy?

[chris huys_4 · Accepted solution]

Hi Rustam,

> Top shows that the test system with
> 80008K = around = 80Mbyte of free physical
> RAM is desperately >>low on RAM. (80Mbyte
> of 16Gbyte physical RAM is less then 0.5% of
> free RAM)

> I thought that free virtual memory is
> 80Mbytes.

No, its free (RAM) memory that is 80Mbyte.

# man top
[..]
Memory Data
Reports virtual and real memory used by user
processes (with the amount of memory considered "active" in parentheses) and
the amount of free memory.

Can be crosschecked through "free memory" in the "Memory report" screen of glance.

> Memory: 12547684K (3422440K) real,
> 19791712K (6147136K) virtual, 80008K free
> Page# 1/36

> Does it means that 12,5 Gb memory of RAM
> is busy and 3,4Gb is free?

No.
Thats why I prefer kmeminfo output, its much more clear about all values.

The virtual memory values are not important. Also the active values of real and virtual memory, i.e. the values between () are not interesting.

I would say that userprocesses are in total using 12,547,685 kbyte of physical memory, or around 12.5Gbyte of memory of the total of 16Gbyte of memory and as said the system, at the time top was taken, had 80Mbyte of free memory, which leaves for the kernel + filecache = 16Gbyte - 12.5Gbyte - 80Mbyte = 3.42 Gbyte.

The amount of "kernel + filecache" memory of 3.42Gbyte looks "standard" for a 16Gbyte RAM system, so I would also say that the long querys are probably due to the user processes asking more RAM then there is available, leading to heavy paging of the system during that time.

Which can be observed with vmstat 1 1000 and checking the pi/po values together with the free value, were the latter gives the amount of free RAM. (all values of vmstat are in pages, 1 page = 4k)

How much RAM extra would have been needed to avoid the paging, I would, in this case, were RAM cant be removed from the kernel or filecache, as they seem allready on there "minimum", follow Patrick's Wallek advise of thread, https://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=1449580 , and add at least 2,748,424 kbyte, or the amount of "kb used" of dev swapspace. But to be sure, I would probably double that figure and round it upwards to the next "power of 2" i.e. 2.8*2 = 5.6 Gbyte, which "power of 2 up'd" would give an extra 8Gbyte of RAM.

Offcourse you could also ask the application vendor how much RAM they would advise for the amount off apps that are running on the system...

Greetz,
Chris

[Steve Post]

You might want to get with your Oracle DBA to compare the two system's databases.

Just recently I had a problem with the cpu was at 100% and the io's were going crazy. The problem was due to missing index on a database table.

On the systems I have, it is typical for 98% of the memory to be used by the database. This is because that is the purpose of the boxes here. And there's still plenty for normal, non-massive programs. But when you look at the numbers, it looks like the box is going to blow up.

Now onto that memory. I have been running "vmstat 1 2". Then I take the "free" column and divide by 256 to get megs of memory free.
I have almost 16 gigs of megs used

[Alzhy]

Alzhy,
>>And we can tell you how large SGAs each of your DB is using. Then you can ask your DBA if it can be >>trimmed down.
on 2nd server run 4 instances as said. 2 ones SGA=6 Gb and PGA=8GB and other 2 ones (datewarehouseâ  s ones) SGA=1.7 Gb and PGA 1.7GB total will be = 34.8 Gb, isnâ  t crazy?

Yikes!

Your server only has 16GB of Memory No? Your ipcs clearly shows 4DB instances whose SGA (shared memory) claim already exceeds RAM!

Have your DBAs please reduce those SGA sizes to total below at least 12GB and you'll have "better" performance.

[Steven E. Protter]

Shalom again,

Oracle pegged to 100%

Joke: Crystal ball is at home.

Reasons why Oracle can be 100%

1) Users are hitting the application to hard and its always doing a lot of I/O. These could be read transactions, but based on your output, I would assume it is write transactions.

2) Poorly written application could be just eating CPU for no good reason or writing inefficient sql requests.

3) Oracle patch could be required.

4) I/O layout of the system could have hotspots. This could include SAN configuration that is raid 5 and needs raid 10, or bad configuration of SAN based LUNS. Need to work with your storage team on that.

How to find out:
Work with the DBA. Have them check oracle statistics. See what is being written where. Look for inefficient sql statements.

Open a TAR with Oracle support. Provide them with data, see what they say.

SEP

[Alzhy]

Rustam Sir,

Here are your 4 Databases as they lay claim to your precious RAM:

m 4882437 0x8cb21ca0 --rw-r----- oratst tdba oratst tdba 33 1627398144 2434 2487 14:55:43 14:55:43 20:19:45
m 32774 0xb8bd04d0 --rw-r----- oratst tdba oratst tdba 52 6459244544 2077 2477 14:54:37 14:54:37 20:18:12
m 7 0xd79db4f4 --rw-r----- oratst tdba oratst tdba 38 1627398144 2256 2482 14:55:12 14:55:12 20:18:52
m 8 0x2b4499fc --rw-r----- oratst tdba oratst tdba 27 6459244544 2633 2484 14:55:23 14:55:23 20:20:13

Your DBA should have a means of telling whether those DB instances (being just test in the first place) do need such amount of SGA.

I bet your stats from sar on paging will exhibit the issue more clearly.

[rustam_2]

I'll come back to this thread in few hours.

[rustam_2]

I've come back. I installed some patches on my server which are newest driver for SAS.
I increased swap area from my HDD (set 24Gb). I know my RAM has little amount still. I told to my chief and he said ok but tell me how many Gb we should buy. I have some questions, could u help me?
1. As you know rx6600 has 48 slots(HP product bulletin says). Now my server has 16 RAM * 1Gb. Can i buy 8Gb or 16Gb RAMs and mixed them? or i must buy only 1Gb RAMs?
2. On server run 4 Oracle instances and about 300 hundred users. Each instances SGA+PGA sizes amount are 15Gbs. Will be enough if i will buy 64 RAM Gb for each other or better to make RAM size 128Gb? In any case i have to give substantiate to my chief.
3. I have 2 processors:
#machinfo
2 Intel(R) Itanium 2 9100 series processors (1.59 GHz, 18 MB)
532 MT/s bus, CPU version A1
4 logical processors (2 per socket)

Should u buy 2 processors or it will be enough?

Best regards,
Rustam

[Alzhy]

The way I am looking at it is you will be safe at least going with 64GB-- that is IF these servers are purely DB Servers and the 300 users are direct connect (no MTS enabled) on the Databases.

Your DBA should be able to tell how much each direct connection to the databaseis memory wise -- or you can actually observe via ps or glance - the process name for those diret connection to the database is something like oracle - for example if your dbname is SAP, the connection process (aka foreground) will be named oracleSAP.