Performance Issues

[taqvia]

Hi,

We are facing performance issues or to be more precise not able to use the system resources well.

Progress 9.1e
Slorais 8
-B 150000
-aibuffs 50
-bibuffs 50
-semsets 30
-spin 60000

208 G RAM/ 64 CPU (900 MHZ)

At any point of time we are only able to use 15-20 % of cpu and 25 G of memory. which is unerusage of resources. Still the performance is not optimal.
Tried several poosible conditions by increasing / decreasing the -B (buffer hits are more than 98%) no of APWs everything seems to be well when we use diffrent monitoring tools such as promon/protop.

Also checked parameters at the Opearting system level by running mpstat vmstat top there is no such huge swapping or paging happening. there is something wrong which is not allowing us to use the full the system resources.

Is there any other parameter than can be used to utilize the system resources.Because one thing that is sure hardware is not an issue here.


Arshad

 

[parul]

You are limited in the amount of memory you can use by 32bit progress.
Maximum memory progress can address with 32 bit is around 2.5 Gigs for Solaris.

The memory usage that you see is the agregate usage (OS + Progress).

The best option (IMHO) is to Upgrade to 64 bit progress. But dont do that with 9.X.

Upgrade to 10.1X 64 bit and you should be able to use resources better.

-Parul.

 

[TomBascom]

1) A 98% buffer hit ratio is nothing to brag about. It's more along the lines of "barely acceptable".

2) Using 100% of everything shouldn't be a goal in and of itself.

so...

3) What's the real problem? Is there some part of the application that you want to "go faster"?

 

[sekarv018]

Hi ,

i would like to add a question to this....if in 32 bit itself we are getting a 98 percent buffer hit ratio will it make any difference on moving to 64 bit (no open edge).....just for remaining 2 percent? Since minus b here can be ruled out but can any other tuning be made to improve performance with 64 bit.. .can we gain anything out of this ?

Sekar

 

[Casper]

I think you shouldn;t be focussed on buffer hits, but you should focus on what Tom already said:

3) What's the real problem? Is there some part of the application that you want to "go faster"?

For example if your database is lets say 4GB and you have a 64bit system and that much memory as you claim then you can put the entire database in memory and would have 100% buffer hits. A bad written program then still can cause for serious performance problems.....

You give a mixed question you say:

We are facing performance issues or to be more precise not able to use the system resources well.

The first thing performance issue is something completely different then not using the systems resources well. It looks like you got a real urge to use all the cpu's and memory you have got available for no apparant reason.

If you are experiencing a perfromance issue then you have to tell us what the particualr performance issue is. Then we can tell what could be done about it or what would be the best approach to identify the problem.

So if you are more specific then we can give more specific answers.

Regards,
Casper.

 

[taqvia]

performance issues time taken to process a job. When a job is processed and there are no locking contentions the process should complete if we have the resources available to process it without waiting to acquire resources(h/w).

We even looked at the programming side and compiled the process code with xref to figure out the index usage there were no whole index found used by queries.

I do agree with Tom that using 100 % of resources should not be the aim
but what else factor should i look to meet the SLA. programs seems to be fine,no operating system constraints then what else needs to be looked into ? Is there any limitaion from the progress end or any setup that can be looked from the databse side....

Arshad Taqvi

 

[TomBascom]

I'm still a bit unclear but I'll make a few guesses...

It sounds like you have a specific process that takes longer than you would like to run.

You've looked at the code and you don't see any obvious reasons (like WHOLE-INDEX) for it to be slower than you would like.

At runtime you don't see any lack of available resources.

Unfortunately this is a very superficial analysis.

Checking for WHOLE-INDEX is a good thing to do but it is a long ways from being a definitive answer about how efficient your code is. Here is a quick efficiency self-test, ask yourself these questions about the slow process:

1) What does the process in question produce? (For instance it produces a data extract summarizing sales activity. Today's extract consists of 1,500 lines.)

2) What is the minimum number of database records that should be required to produce that output? (X order headers + Y order details = Z records in total.)

Real processes will, of course, be more complex than my example but you should be able to roughly estimate an answer.

Now find out the answer to the following:

3) How many records did the process actually access?

You can use tools like PROMON or ProTop to help answer that question.

Is the number of records actually accessed at all close to the number that should have been accessed? If it is not then your queries are not efficient. Regardless of whether or not you found WHOLE-INDEX in your XREF.

If you have a query efficiency problem then you will need to fix your queries before you move on.

If, on the other hand, your queries are relatively efficient then you need to find the next bottleneck.

There is far too little information provided to provide an answer but it might be disk IO -- most performance problems that are not coding problems are disk IO problems. In that case a larger -B and a better hit ratio will help. But you'll have to double -B to improve your hit ratio from 98% to 99%. And you will need to double it again to improve from 99% to 99.5%. And so on... (This is a "rule of thumb" based on well behaved application software -- bad queries will mess it up.)

Or perhaps you just need to properly stripe the database across multiple disk spindles.

Or, if there is no disk bottleneck, you might have an opportunity to exploit your hardware. You say that you have 64 CPUs. If the process that is too slow is single threaded (Progress sessions are a single OS thread so unless the slow process consists of multiple cooperating tasks it is single-threaded) you might be able to break it into multiple pieces that can execute in parallel. This, of course, requires some knowledge of the application and a small amount of skill But it can be a powerful technique. (But it's not very useful if the underlying queries are the real problem.)

The process of improving performance is one of careful and methodical analysis. It takes time and patience and (shameless plug!) it's helpful to have someone experienced around.

 

[TomBascom]

Hi ,
i would like to add a question to this....if in 32 bit itself we are getting a 98 percent buffer hit ratio will it make any difference on moving to 64 bit (no open edge).....just for remaining 2 percent? Since minus b here can be ruled out but can any other tuning be made to improve performance with 64 bit.. .can we gain anything out of this ?
Sekar

A 98% hit ratio means that 1 in 50 db access operations is going to disk.

A 99% hit ratio means that only 1 in 100 db access operations is going to disk.

Disk access is literally a million times slower than memory access. Thus even relatively small percentages of disk access can completely dominate query performance.

Improving your hit ratio from 98% to 99% will basically double the performance of a long running query so yes, it is worth doing.