Oracle 10g on HP-UX, Terrible Poor Performance!!

[Delrish]

Hi All,

I setup an Oracle 10g on HP-UX 11iv1. Server is a HP 9000, 4 CPUs (750 MHZ). It is connected to Disk System 2405 (Virtual Array 7110). Fiber Channels are connected at 2 GB speed.
I installed a cluster 10g database. First I installed CRS and after that I installed oracle database. ( I want to test clustered database with one instance)
I installed every thing line by line as oracle document wrote.
All the things, kernel parameters, patches, are like oracle wrote in its document.
I installed Golden quality package June 2004.
I increased shmmax to 2.3 G . My SGA is 1.7 G And change some other parameters as Sandy Gruver wrote in Best Practices for Oracle on HPUX.
I used oracle new storage system called ASM for this case.
When I put the system under the load, I was monitoring the system carefully.
Attached you can find â sar â d 5 1000â result.
I started gmp. When we sent some quarries to database (It is not heavy load, I tested it with a Linux system on proliant ML570 without any problem), suddenly DISK section in gpm changed to red (critical ) I read the warning. It said â Disk bottleneck probability = 100%â . I changed the output of disk report to â Report IO by Diskâ
â DISK%â was 100% and â RAW IO RTâ was about 1000 for two disks ( This two disks dedicated for ASM). In this situation CPU idle time was 1% or 2% for all the CPUs but load average was about 1. Performance is not acceptable at all ( In comparison with Oracle that installed on Linux).

[Eric Antunes]

Hi,

Why do you use this 1.7Gb huge SGA? It is recomended by Oracle for 10g??

When you get this issue solved, you should read this: http://forums1.itrc.hp.com/service/forums/helptips.do?#28

Best Regards,

Eric Antunes

[harry d brown jr]

Although glance (gpm) red-lined your disks, is your performance poor based upon transaction throughput or is the red-lined disks the only issue?

Can you point us to the Sandy Gruver document on Oracle Best Practices on HP-UX - specific to 10G + CRS + ASM ??

This is most likely an ASM issue, thus maybe Oracle has a solution.

live free or die
harry d brown jr

[Jean-Luc Oudart]

Harry,

this presentation can be found here :
http://h21007.www2.hp.com/dspp/files/unprotected/database/HP3KOracle.ppt


Regards
Jean-Luc

[harry d brown jr]

Jean-Luc,

Exactly what I was getting at. That document is for 9i NOT 10g with CRS and ASM. Thus it's not extremely useful.

live free or die
harry d brown jr

[Delrish]

Thank you,

Yes the presentation is about 9i, but i use some part of that talked about tuning the Oracle for RAW partitions.
The problem that I saw in GPM was bottleneck in Disk. As I said in my first email, I had a warning in glance. It said "Disk bottleneck probability = 100%". And at the same time when I got the Disk report with IO by Disk, "Disk" column was "100%" and "RAW IO RT" column was about "1000" for each disk (I used 2 disks for storage)
Glance reported Disk was in Critical situation.
I think the problem is IO or something about Disks
I used HP Disk System 2405. Fibber channels on both server side and Disk Array side are configured at 2 Gb and topologies are PTTOPT_FABRIC.
Is it ok that RAW IO RT about 1000 for each LUN?
Why Disk% in glance/report IO BY Disk/ was 100%?
Did you see sar result that I sent it with my first email. c6t0d3 & c6t0d4 are the Disks I used for storage. I found an error in STM logs. Attached you can find a picture of this error.(Do not attention to the date of error).

It logged this error about 12 times during the test.Any comment?

Regards,
Alireza

[Peyman Javaheri]

Hi Alireza,

How is the performance from queries? Do you see any slowness? I ask these questions because Glance may not be showing to you the right info. Its Max threshold may not be set to include your high fiber throughput, so it translates it as bottleneck. I see that same problem all the time with our systems. Glance shows a high I/O, but in fact that is good and we want to use the full capacity of our I/O systems. However, that I/O number that glance shows may not be acceptable for my other regular SCSI disk.

If you are concerned with the I/O you can start by looking more into how your filesystem, lvol, vg, kernel, F/C, Switches (if SAN), and the array are setup since you are not seeing any memory/cpu problems.

Regards,
Peyman;

[Delrish]

Thank you Peyman,

Yes,The system was very slow and performance was not acceptable at all.
Did you see my SAR results? in the pick time disks are 94% busy.I think the problem is in storage system (Disk array, san switch, fibber cable or fibber channel)
What is your idea? At the time of occurance CPU idle time on all the CPUs is about 2% but load average is acceptable and equal about 1.0 .
Did you see the picture i attached to the last email? STM find a problem but it was temporary problem. During the test i found it. It was repeated 12 times, during the test. Waht is your idea? Do you think my SAN has a hardware problem?
I setup a test. i copied a 5 GB file from server's local HDD to SAN, it took about 6 mins to complete. is it ok? I think it should be very faster.Do you think i detect the problem correctly? IS it realy IO problem or another thing is wrong? Please advice.

Regards,
Alireza

[Philip Kernohan]

Alireza,

Is this array and the layout of the array the same for both Linux and HP-UX? If not, it could be your devices are in contention with each other I'm not certain how the db is laid out.

How much RAM is on your server total?

Size of swap defined on HP-UX? Is paging heavy in sar also?

[
Lastly it is important to most users of these forums to make sure you assign points (You have assigned points to 0 of 29 responses to your questions).
]

Good luck,
PK

[David Child_1]

Take a closer look at c1t2d0. According to the sar output you initially attached it doesn't look too good. The utilization is low (~20%), but the average queue gets up to 20 occasionally and wait/service times are very high.

What is that drive used for?

David

[Delrish]

Dear David & Phillip,

The linux server is not connected to SAN. It uses its local scsi hard disk for storage.
This server has 8G memory and 2*4 swap partition.
c1t2d0 is local hard disk that Hp-UX operating system installed on it.
c6t0d3 & c6t0d4 are two LUNs defined on Disk Array.
Did you see STM resualt in my previous email? Can it a hardware problem?
We did not get Oracle licence for this server yet. We want to sure this solution is good enuogh in performance and than buy Oracle for HP server.
Is it possible that oracle without licence (unregistered databse) be the source of problem. I mean the trial version has some performance problem on HP.

Regards,
Alireza

[Eric Antunes]

Hi Alireza,

Can you post here the output.txt file resulting of the following command:

#ioscan -fn > output.txt

Best Regards,

Eric Antunes

[Delrish]

Eric,

Yes, I will send it for you next week. We are in holidays in our country for 4 days.
Anyway could you help with the questaions that i asked the forum in my previous emails?

Alireza

[Eric Antunes]

Hi,

"Did you see STM resualt in my previous email? Can it a hardware problem?"

That's why I need the ioscan output results: you seem to have hardware problems...

Best Regards,

Eric Antunes

[Binukuttan VM_1]

what is the output of

1.sar -v 1 111
2. vmstat 1 111

see the outputs and try to tune the kernel parameters

good luck
binu

[Alexander M. Ermes]

Hi there.
Try to run Glance in character mode and press the key 'u' .
That will give you an overview over the different phyiscal disks and the load on them.
Rgds
Alexander M. Ermes

[wayne_104]

Hi Alireza

How did you sort out the problem of Oracle 10g on HP-UX, Terrible Poor Performance.

I am having the same issue.

Wayne

[Delrish]

My problem is in VA. I have poor performance on reading from VA. I do not find the root of problem, but I am sure the problem is in VA. I setup some tests and got this result.
Are you sure that your VA is Ok and you have a good performance on it?
There is a benchmark called bonnie. If you would like we can test it on our systems.
If you are interested please download it from http://www.textuality.com/bonnie/
and after compiling, run it with these options: ./bonnie -s 1024 -d "Target directory on VA" -m "Your Machine's name".

We can compare our results with together.

Alireza

[wayne_104]

Hi Alireza

all my disks are internal and there is no IO on them in fact the cpio backup puts more load on the system disks than the database and the application.

I read that the 2004 december patch bundle with phne 31097 is supposed to help.

i am on june 2004 still.

the problem is that the app guys are saying it is os and hardware.

[Delrish]

The root of problem was in Storage system.(VA & SAN Switch)