Operating System - HP-UX
1748180 Members
4036 Online
108759 Solutions
New Discussion юеВ

Re: Low performance and time responses

 
Manuel G
Frequent Advisor

Low performance and time responses

Hello:

- We have a binode cluster system composed by 2 D230 servers with 128 MB. RAM each.
- Operating system is HPUX 10.20 and we already have a Oracle 8.0.4 database.
- Database information is stored in a external discs box accesible from both nodes.
- A node works as the primary one and the other remains as secondary waiting for any problem can occur with primary. Cluster managment is made by HP Service Guard Software.
- We are running a PRO*C application on the system since February-3.(until now it has runned on the primary node)
- Response application times were OK during first 2 months more or less. Last days of March we began to appreciate a degratation of time responses.
- Degradation increases as information stored in database grows up.
- We tried to eliminate database information and response times were OK again, but we have to store a 13 months historical so the solution is not to eliminate information gradually.
- We have notice top?s load average reaches 6 points (even more) when 6 or 7 users are accesing database using application. The load continues growing as oracle user processes remains waiting for an answer.
- We have notice too that idle time isn?t so bad when the top?s load got high.

Can someone help to discover where we have the bottleneck?
Glance could help?
Could it be a disk I/O problem?
Someone told us to configurate database as MTS (multithread server), could it be a solution or could have any problem with it?
If someone need more information, please ask me.

Thanks a lot.
26 REPLIES 26
A. Clay Stephenson
Acclaimed Contributor

Re: Low performance and time responses

Hi:

128MB is very little memory for an Oracle server especially if it is also running an application. In your case, because your problem
seems to be very dependent upon the amount of data, I suspect that the majority of your bottleneck is in the Pro/C SQL code. It is very easy for poorly structured cursor loops to degrade at an N-squared rate or even worse
with nested selects. My first attack would be to enable SQL_TRACE to determine which statements are hogging the resources. If possible, I would also plot response vs. data volume, if the response degrades at N-squared then almost certainly you have a problem with poorly written joins. In many cases, one good choice of an index will clear up most of the performance problems.

Hope this at least gets you started, Clay

If it ain't broke, I can fix that.
Magdi KAMAL
Respected Contributor

Re: Low performance and time responses

Hi Manuel,

To solve your problem you may look for the following subjects :

1. hp-ux kernel parameters tuning.
2. Oracle SGA size according to physical memory.
3. Oracle tablespace distribution amoung disks.
4. Network configuration.
5. I/O subsystem speed.
6. Application design and implementing.
7. Physical memory increase.
8. Swap space.

1. hp-ux kernel parameters :
db_max_pct : should not be more 10 ( percent )
db_min_pct : should not be more 5 ( percent )
streampipes set to zero.

2. Oracle SGA should be as bigger as you can. Your physical memory ( 128 MB ) seems to be really not enough for what you attempt to do !
I would increase memory to 512 MB and give 300MB to the Oracle SGA ( The price of memory on D class serevr is not expensive !).

3. Try to fetch weather your tablespaces are well distributed amoung disks to insure access balancing .

4. Network configuration :
- the subnet mask value for either node 1, node 2 and package ?
- Broadcast value for either node 1, node 2 and package ?
- either node 1, node 2 and package sould be in the same subnet.
- If you are heavy communicating with another application or system, you would integrate it into the same subnet ( if possible ) to reduce network traffic ( bottleneck ).
- Network component specific, in case of mixing FDDI and ethernet protocols you may configure your the "Don't Fragment Bit" to ZERO .

- you may issue the command netstat -rn to check networking statistics and this could give you an idea about what is happening.

- Control your MTU size ( Ethernet must be 1500 and FDDI 4352.


5. I/O subsystem :
The command vmstat can give you an idea about running, blocking and waiting process.
It's very important to appreciate the values giving by this commands :
- Wainting process : Waiting for I/O request to be acomplish.
- Blocking process : process waiting for a resource ( could be db record, ... )
The command sar ( system activity reporter ) can give you also alot of information about the I/O performance.

6. You have to increse your physical memroy, 128 seams to be really not enough. I would recommand 512 MB ( 300 for Oracle SGA and the rest for the hp-ux and users process )
Notice : Any way you must set the db_max_pct and db_min_pct kernel parameters describe before.

7. Your swap space have to be three or four times the amount of physical memory.
So in case of 128 MB -> swap will be 512 MB
and in case of 512 MB -> swap will be 2048 MB.


8. Application desgin and implementing could be a bottleneck itself. If a process will executing is asking for a lot of resources at the same time, this will conclude on process competition to look for resources.

Hope this can help you, I can give you more details if you want ( I solved this problem last year for my customer with this approch and all this elements were involved in the final solution and now it works VERY GOOD ).

Magdi

Phil Miesle
Occasional Contributor

Re: Low performance and time responses

Manuel,

It seems that you only have 6 or 7 users on the system, I assume that it is only used as a database server for this application. Given that your load average is so high at this user level, I too would look for poor-performing SQL and PL/SQL code. It is not uncommon for applications to perform worse and worse as more data is added to the system; this is solved by careful design and tuning of the application.

I would suggest using the utlbstat/utlestat utility provided with Oracle, setting TIMED_STATISTICS=true in the init.ora parameter file. There are some great documents on http://metalink.oracle.com (you need a valid Oracle support agreement), start with Note 61998.1 to get an overview, and then look into Note 62161.1 for how to interpret the bstat/estat report. There are also documents on Metalink which can help you with tuning SQL.

Two good books on the subject of tuning Oracle are "Oracle Performance Tuning" (published by O'Reilly) and "Oracle SQL High-Performance Tuning" (published by Prentice-Hall and written by Guy Harrison).

It could be that you're trying to do too much on a 1-CPU machine with 128 MB of memory, but I would do everything that you can to tune your application on the current hardware before upgrading; otherwise, your boss could be disappointed that a $5000 upgrade didn't solve the problem!

:-Phil
Manuel G
Frequent Advisor

Re: Low performance and time responses

Clay:

- We have done what you suggest 2 months ago. In spite of this we are going to review again the code.

- We reindexed all database and "tkprof" code. We appreciate certain improve but when data grew the problem arised again.

- We use "tkprof" for reviewing, know other way?

- Thanks.
Charles McCary
Valued Contributor

Re: Low performance and time responses

Just out of curiousity, how are your lvols set up? Are they striped?

Also, in order to keep up interest in solving your problem, you may want to assign points to some of the other folks that have already worked hard for you on this - of course, I have yet to provide you anything useful, but hopefully if you provide the lvol info, I might be able to pluck some "low hanging fruit" for you.
Manuel G
Frequent Advisor

Re: Low performance and time responses

Hello again:

- I said you our RAM memory was 128 MB, I was wrong, we have 256 MB.

- Magdi, I reviewed pontis you notice with the next result:

1) HP-UX kernel parameters:
dbc_max_pct: 2
dbc_min_pct: 2
streampipes: 0
We have the tuned kernel parameter sam offers for database servers.

2) Oracle SGA: 17 MB.

3) Tablespaces:
All tablespaces are in the same disk. We could distribute them across 2 disks (in the external disk box) but we are not sure having 2 controllers one for each disk.

4) Network:
Any system communicates with ours at low performance periods. Communicating runs at nigth/low using system times.
All users access our application via telnet sessions using an industrial network, so all the processes run locally on the system.
We have a Ethernet Network with 1500 MTU.

5) I/O system:
I read some about vmstat but I don?t now where it shows causes of waiting processes. Can it show them?

6) Memory:
We will try to resolve the problem withoput increasing memory because we are not sure if this is the bottleneck. The system seems to not swap for example.

7) Swap space:
We have 512 MB of swapspace, I think it?s OK, is it?

8) App. dessign:
As I told Clay, we are working on it trying to minimize accesing to database.

- I have started "sar" this afternoon, I hope tomorrow can show you some results.

- I will run vmstat and netstat too.

Thanks all for your help, we continue working on it.

PD: How can I point your answers?
Charles McCary
Valued Contributor

Re: Low performance and time responses

Manuel, you should see an "Assign Points" beside the headings of each of our answers.

tx,

C
Manuel G
Frequent Advisor

Re: Low performance and time responses

Charles:

What kind of lvol information do you need?
Charles McCary
Valued Contributor

Re: Low performance and time responses

How about an lvdisplay on all of your database data file systems?