Database is not releasing Memory and performing slowly.

[Ziko]

Hi,

I am using Mfg/Pro(Progress V9.1D) database in AIX 5.2. Below is my server configurations.

a) Number Of Processors: 2
b) Processor Clock Speed: 1453 MHz
c) Memory Size: 8192 MB
d) Total Paging Space: 8192MB

My total user is about 100 and sql92 service are running in windows to read some information from progress.

Here is my Problem,

1. Very beginning of the day the server performance is good.
2. During the pick hour, the system performance is very solw.
3. During this time memory uses about 90%
4. But when i have stopped the database, the memory uses are same(90%). It is not releasing memory.
5. Only after rebooting, it is able to release memory.

Please help me asap.

Regards,

Ziko

 

[TomBascom]

How are you measuring memory use?

(You are probably looking at some gross measure from the OS POV -- on all modern operating systems, including AIX, the OS will seek to use all memory. You're probably just seeing the growth of the filesystem cache. That memory is released to other uses on demand -- if there is no demand it stays in use, just in case. This is normal.)

Next question... why do you care about memory use? Is there a problem that you suspect this is related to? If there is a performance problem you have omitted most of the data that would be helpful in analyzing it. Most performance problems are disk IO problems -- so details of disk configuration are critical. Progress startup parameters and configuration options are critical too. And is is always very helpful to see some performance metrics sampled while a performance problem is occurring (the PROMON "s" command).

Finally an observation -- 9.1D is ancient, obsolete and unsupported. You should upgrade. There are, of course, some tuning improvements possible with 9.1D but the really good stuff is in later versions.

 

[Ziko]

Hi Tom,

Thank you for your reply.

Please see the attached file, output from nmon( performance checking tool).

1) Here is my startup script of Progress database

# QAD DATABASE
$DLC/bin/_mprosrv /INTERSPEC/live/lbbleba -H risc -S qadlive -N tcp -L 71680 \
-n 150 -c 450 -B 2048

# ADMIN DATABASE
$DLC/bin/_mprosrv /data2/ebdata/admlbbla -L 8000 -n 150 -c 350 -B 1000

# INDENT DATABASE
$DLC/bin/_mprosrv /INTERSPEC/live/indenteba -H risc -S indlive -N tcp \
-L 71680 -n 150 -c 450 -B 1024

# SHERPA DATABASE
$DLC/bin/_mprosrv /SHERPA/apoext -L 75000 -n 150 -c 350 -B 1000

2) Script for Multiuser session for Mfg/Pro

exec $DLC/bin/_progres \
/INTERSPEC/live/lbbleba -ld qaddb -F -znotrim -trig triggers \
-db /data1/mfgproeb/db/hlpprod -ld qadhelp -RO \
-db /data2/ebdata/admlbbla -ld qadadm \
-db /INTERSPEC/live/indenteba -ld indent \
-d dmy -yy 1920 -Bt 350 -T /data2/temp -cpinternal ibm850 -c 30 -D 100 -mmax 8
192 -nb 200 -s 63 -p mf.p;

Thanks,

Ziko

 

[TomBascom]

That's what I thought. You are looking at total OS memory utilization and it is behaving as I described. Nothing to see here.

The slow performance alluded too in the posts title is likely due to poor startup parameter configuration. Just for starters your -B is way too low. -B 100000 is "a good start" for a server with 8GB of RAM.

Your client parameters are exotic. Why do you have -F in there? Is your data worth anything at all?

 

[Ziko]

Hi Tom,

Thank you.

After changing startup parameter to -B 100000, it is throwing the following message

"Maximum number of shared-memory segments per process exceeded."

Thanks,

Ziko

 

[TomBascom]

Are you running a 32 bit Progress executable? At a command line type "file $DLC/bin/_progres".

(You shouldn't be -- you have Progress 10.1C on AIX so a 64 bit executable is available and should be what you use... Not doing so would be yet another reason why your performance is "slow".)

 

[TomBascom]

In the meantime... reduce -B until you do not get errors. Try 50000.

 

[Ziko]

Hi Tom,
After executing the command "file $DLC/bin/_progres". It is giving following message.

/data1/appln/pro91b/bin/_progres: executable (RISC System/6000) or object module
not stripped

Regards,

Ziko

 

[Ziko]

I am using progress version 9.1D

 

[TomBascom]

Oops, sorry, I mixed you up with someone else.

9.1D... ancient, obsolete and unsupported. On AIX it is also limited to 32 bits. Thus there are limits on how much memory it can address.

The AIX limit for 32 bit processes is 11 shared segments. Each segment can be up to 128MB. If a process connects multiple databases that is 11 segments in total -- not 11 per db.

It looks like you have 4 databases making up your QAD instance? I would start by setting -B to 50000 for the "qad" db and lower (perhaps 10000) for the others.

 

[Ziko]

Hi Tom,

Thank you.

I have increased the buffer and distributed two primary db's two different hard disk.

It is showing me better performance. But sometimes it is performing slowly again. When i have been monitoring it through "topas" . It is showing "CPU idle 80%". Database processes is not using it.

Thanks,

Ziko

 

[TomBascom]

You might want to download ProTop: http://dbappraise.com/protop.html (you will need the ChUI version, not the .NET version).