Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Progress database performance problem

SOLVED
Go to solution
Luis Toro
Regular Advisor

Progress database performance problem

Hello,
I am running Progress ver. 9 on 11.0 HPUX. The DBA has been modifying the method in which the users access the DB. Every time a [Progress] change is made, users complain. The latest go around results in the following symptoms:
low CPU util.
low mem util.
low disk util.
high run queues
very high semop calls
After the change, the seam/s went from a high of ~ 10-50 to
3700.
My question is: is this indicative of a progress problem and/or config. issue, or do I simply need to increase some kernel parameters. Here are my current values:
sema 1
semaem 16384
semmap 752
semmni 750
semmns 2048
semmnu 2048
semmsl_override 2048
semume 512
semvmx 32767

Any insight would be greatly appreciated.
11 REPLIES
Roger Baptiste
Honored Contributor

Re: Progress database performance problem

hi,

The semaphore values you stated are ok. The problem could be with the way the database is tuned and the application is configured.

Run glance/measureware and see what are the top processes consuming resources.

Is your swap space ok? Is your buffer cache setting ok?

It is not always to do with kernel tuning.

HTH
raj
Take it easy.
Luis Toro
Regular Advisor

Re: Progress database performance problem

Hi Raj,
Swap util. is fairly low.
We have 2.3gb of device swap configured and pseudo swap turned on (another 4gb) and 6GB of memory (6 CPUs on a K580).
Max buffered cache is set to 25%. When the change was made, the glance main window showed very little activity. Sar data gave us the high run queues (normal is below 2; after the change they were creeping up to 3...thats when the users started to complain)and the drastic increase in sema/s.
Jeff Schussele
Honored Contributor

Re: Progress database performance problem

Hi Luis,

What's timeslice set at?
If it's still @ default (10) that could be the whole problem.
And 25% on buffer cache still seems a tad high - try 12-17%.
Also, Rajman could have nailed it - no kenel param can counteract crap coding.

HTH,
Jeff
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Luis Toro
Regular Advisor

Re: Progress database performance problem


Timeslice is still the default. The Progress brain trust has asked us to increase semmns and semmnu (both set to 2048; increase to 3200). As far as buffer cache, we did play with this value and found 20 - 25% to be the happy medium. Should I take the same approach with timeslice ? ie., decrease it slowly ? Are there any ramifications to dropping it to 1 ? I've never had to change that parm so I'm not familiar with any "downsides" of changing it.

Thank you.
Jeff Schussele
Honored Contributor
Solution

Re: Progress database performance problem

Luis,

10 is the proper setting. I thought maybe you had set the default DB recommendation of 1 that's templated by many vendors & even by SAM (mistakenly).

Here's some good threads on DB performance/tuning:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x8c8906295e00d6118ff40090279cd0f9,00.html


http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xf5e9ff77de2bd611abd50090277a778c,00.html
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x43188ffa98a2d5118ff10090279cd0f9,00.html

here's some good semaphore threads:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x868a8cc5e03fd6118fff0090279cd0f9,00.html

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x417db47b9a27d6118ff40090279cd0f9,00.html

Sorry for the confusion.

Rgds,
Jeff



PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Bill Hassell
Honored Contributor

Re: Progress database performance problem

Since CPU is low and disk is low but the run queue is high, this means that the programs are waiting on each other, which is typically how semaphores work. A semaphore-controlled process will not proceed until given the go-ahead by the master program. The high semop rate translates into very high system overhead which is unnecessary.

This is purely program code, so there isn't anything you can do to fix it in the kernel. The kernel is doing what the programs want to do. You probably need an expert Progress DBA to help with the code. It may simply be poorly constructed query statements, missing or corrupted index files or simply inefficient DB access methods. The code may work but that doesn't mean that it makes good use of the CPU...as you've seen, the machine seems to be idle.


Bill Hassell, sysadmin
Luis Toro
Regular Advisor

Re: Progress database performance problem

Thanks for all your input.
We've had similar tuning issues with Progress in the past, and as always, the OS is
guilty until proven innocent.
Richard Wardle
Occasional Visitor

Re: Progress database performance problem

We currently run around 12 separate Progress databases on a HP9000 running HP-UX 11.00 with no problems in performance. We cranked up the semaphores to allow for all the databases, but never experienced any performance problems with a single database and semaphore settings as default. Progress itself doesn't need looking after quite like Oracle does, so if your performance issues are related to the DBA changing the way the database is accessed, then it sounds to be an application configuration issue.

Our users connect through both sockets, particularly for the GUI front end, and direct connections using the standard 'mpro' script.

In what way has the DBA modified the way users access the database? If he's switched from telnet sessions to Progress Character Client, then the PC itself is given the responsibility of resolving any queries now. If you could give me a bit more information about the application - GUI-Client/Server or direct telnet clients (character applications). Do you run the Progress AppServer?

Regards,


Rich
Richard Wardle
Occasional Visitor

Re: Progress database performance problem

....and

you could take a look at the Progress Knowledgebase. There's entries there for performance related problems, and you may also like to consider the Progress Email Group (peg) and submit the question there?

www.progress.com
www.peg.com

Regards,

Rich
Luis Toro
Regular Advisor

Re: Progress database performance problem

Rich,

Thanks for your input. I do not know the specifics of the change. However, the cause of the problem was the result of not having the "spinlock retry" (?) set on the Progress side. I do not think it was related to the access method the users use to logon; thats still "telnet".