topic Re: Progress database performance problem in Operating System - HP-UX

Progress database performance problem

Luis Toro — Wed, 10 Apr 2002 15:14:09 GMT

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.

Re: Progress database performance problem

Roger Baptiste — Wed, 10 Apr 2002 15:33:07 GMT

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

Re: Progress database performance problem

Luis Toro — Wed, 10 Apr 2002 15:57:22 GMT

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.

Re: Progress database performance problem

Jeff Schussele — Wed, 10 Apr 2002 23:16:36 GMT

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

Re: Progress database performance problem

Luis Toro — Thu, 11 Apr 2002 12:51:14 GMT

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.

Re: Progress database performance problem

Jeff Schussele — Thu, 11 Apr 2002 13:11:18 GMT

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

Re: Progress database performance problem

Bill Hassell — Thu, 11 Apr 2002 13:23:32 GMT

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.

Re: Progress database performance problem

Luis Toro — Thu, 11 Apr 2002 15:13:54 GMT

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.

Re: Progress database performance problem

Richard Wardle — Fri, 19 Apr 2002 14:10:57 GMT

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

Re: Progress database performance problem

Richard Wardle — Fri, 19 Apr 2002 14:16:06 GMT

....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

Re: Progress database performance problem

Luis Toro — Fri, 19 Apr 2002 17:00:35 GMT

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".

Re: Progress database performance problem

Brad Kozak — Tue, 22 Jul 2003 18:26:50 GMT

For tuning the Progress side of things, this link is golden!

http://esupport.progress.com/esupport/consumer/esupport.asp?hotid=2bcd76f2-5ac1-472f-bc40-55c15e1b13e5&number=4&nShowFacts=&nShowCause=&nShowChange=&nShowAddInfo=&activepage=statement.asp&bForceMatch=&strCurrentSymptom=&searchtype=normal&searchclass=QuickSearch&bnewsession=false&selecttype=match

HTH,

bk