Question About Memory Allocation For Database...

Hello,

I am not very expereienced in Progress, from Oracle and SQL-Server I know if too much memory is allocated to database buffers swapping occurs and that can lead to performance degradation. My system is setup by a third party - I expect a great deal of experience from them. This is what my TOP command shows:
top - 08:02:30 up 172 days, 12:55, 8 users, load average: 1.37, 1.60, 1.61
Tasks: 587 total, 2 running, 585 sleeping, 0 stopped, 0 zombie
Cpu(s): 9.1%us, 1.7%sy, 0.0%ni, 86.5%id, 2.3%wa, 0.1%hi, 0.3%si, 0.0%st
Mem: 16360448k total, 16276052k used, 84396k free, 30268k buffers
Swap: 4707036k total, 24908k used, 4682128k free, 11312424k cached

As you can see practically all of the 16GB of memory is allocated. Although swapping is very low (only 24M used) I would like to ask what is your opinion? Should I monitor the system at this stage and worry?

With Oracle databases I avoid swapping at all costs. With SQL-Server I have reduced the buffer allocation to allow 4GB free for the system, the rest is for the DB server. Above that Windows event viewer was showing processed crashing and clear message there was not enough physical memory.

Any recommendations?

My Progress is on a Red Hat Linux.

Thank you
Richard
 

Cringer

ProgressTalk.com Moderator
Staff member
Hi Richard. Thanks for posting. I can't help you directly, but it would undoubtedly be useful to the folks that can to know a little about the Progress databases that are running on this machine.
What version of Progress is this?
How many databases are running on the box?
What are the startup parameters for each of these? You can get this from the <db>.lg file in the same directory as the database itself. If you look for the last instance of "(333)" within the log file and select the next 50 or so lines it will contain all of this info. It's up until the line that says "Database connections have been enabled." Do this for each database.
 
Thanks, the version is:
OpenEdge Release 11.6.1 as of Fri Feb 19 18:20:45 EST 2016

I think these are the paramters for the database:

# cat broker.pf
-n 450
-B 524288
-Mn 50
-Ma 5
-Mi 5
-Mpb 39
-L 100000
-bibufs 40
-S 12500
-N TCP
-ServerType 4GL
-B2 16384
-pinshm
-lruskips 50
-minport 12600
-maxport 12700
-baseindex 1
-indexrangesize 10000
-basetable 1
-tablerangesize 2000
-spin 2000
-PendConnTime 30
-semsets 4

Ah, and one more:

# cat sqlbroker.pf
-m3
-S 12601
-minport 13100
-maxport 13200
-Mpb 10
-N TCP
-ServerType SQL


This is the startup procedure:
proserve package -pf broker.pf # Serve the database
probiw package # Enterprise Progress
proapw package # Enterprise Progress
proapw package # Enterprise Progress
proserve package -pf sqlbroker.pf # SQL - Unixware problem
prowdog package # Enterprise Progress
 

Cringer

ProgressTalk.com Moderator
Staff member
Yes that looks about right.

It looks like someone has put some thought into this at least.
-B is the buffer cache, and it's the number of blocks to allocate in memory. Assuming your block size is 8k that means you're allocating around 4GB of memory at the database startup.

I know nothing about the application that's running, but the indexrangesize and tablerangesize parameters look very large. Are there really that many tables and indexes? These values should be set to the number of tables and indexes (plus a small extra amount to allow for additional ones in future). Setting them too large means virtual resources are allocated that are never used. I presume these use up memory although others will no doubt wade in here.

I don't know much about Linux at all, I'm a Windows man (I can hear the booing already!), but is there a way to see what processes are using the memory?

Like I say, others will wade in when the US comes online with further things, just want to get you started.
 

TheMadDBA

Active Member
By default Linux will consume all available memory to use for OS buffer cache. Use the "free" command to see how much OS cache is being used... free -m. I usually trust the output from free more than top but on most Linux distros they are the same.

You can use promon to see how much memory is being allocated by Progress and quite a few other things.

promon <full path to your database without the .db extension)

Then choose R&D --> 1 --> 13 (Look for Shared memory allocated)

R&D --> 1 --> 1 will show you the database block size

There are kernel options to control how Linux treats memory, buffer cache and swap.

Red Hat Enterprise Linux Oracle Tuning Guide

This is specifically for Oracle but the memory related concepts are the same (not the async IO or most of the other IO tunings).

Check to see what the settings are and see if you could benefit from some of the tuning. It might be that you have short term spikes that cause swap to be consumed.. usually because the need for memory for client processes grows faster than the OS is currently able to free up buffer cache.
 

TomBascom

Curmudgeon
Also -- you cannot conclude that you are swapping from the fact that swap space has been allocated.

Swap space usage is normal and expected.
 

TheMadDBA

Active Member
Gah... totally glossed over the fact that it was 24MB of swap being used :oops:

That is certainly no cause for concern.
 

TomBascom

Curmudgeon
If your DB has 8k blocks you're using around 4GB for that db (if those are the actual startup parameters -- Cringer's "look at the .lg file" is the easy way to see what parameters are *really* being used...)

If your db has 4k blocks (the default) then you are only using about 2GB for that db.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Based on the above I don't see an immediate cause for concern. But given 16 GB of RAM and either 2 or 4 GB of buffer pool, and the various other parameter settings, there may well be room for tuning to improve performance if this database is the only workload on the machine.

That said, tuning should be about eliminating obvious problems and eliminating bottlenecks based on run-time metrics. As Cringer said, someone clearly has attempted to tune this database already. As we don't know your database size or activity, it isn't clear that there is a problem to be solved, apart from the fact that I see no evidence you are using after imaging. I assume this is production?
 

TomBascom

Curmudgeon
Ditto what Rob said about after-imaging. Any serious database with important data in it should have after-imaging enabled.
 

cj_brandt

Active Member
On RedHat we use sar -r -R to see what % of memory is in use. That seems to work better for us than trying to look at swap column in top output.
 
Also -- you cannot conclude that you are swapping from the fact that swap space has been allocated.

Swap space usage is normal and expected.

Hi,

yes, I am aware of that, but thanks for reassuring me. It's just that for the past almost 20 years I mainly had AIX system, where a certain amount of swapping would harm the system. With RH Linux this may slightly differ.

Rich.
 
I think I calculated the actual memory usage by all processes. Ran this command:
ps aux | grep -v COMMAND | awk '{ SUM += $5} END { print SUM/1024 }'

ps aux - shows the 5th column VSZ which is virtual memory size. The total of all processed are about over 800MB. That would suggest Linux has all the rest allocated to it's own buffers. I hope I am correct. I seem to have a problem uploading a .txt file with all the output, so here is a sample (4837968 is the size for each _progress thread):

USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 1 0.0 0.0 10368 684 ? Ss 2016 0:03 init [5]
root 2 0.0 0.0 0 0 ? S< 2016 0:06 [migration/0]
root 3 0.0 0.0 0 0 ? SN 2016 0:01 [ksoftirqd/0]
root 4 0.0 0.0 0 0 ? S< 2016 0:13 [migration/1]
root 5 0.0 0.0 0 0 ? SN 2016 0:00 [ksoftirqd/1]
root 6 0.0 0.0 0 0 ? S< 2016 0:05 [migration/2]
root 7 0.0 0.0 0 0 ? SN 2016 0:00 [ksoftirqd/2]
root 8 0.0 0.0 0 0 ? S< 2016 0:02 [migration/3]
root 9 0.0 0.0 0 0 ? SN 2016 0:00 [ksoftirqd/3]
root 10 0.0 0.0 0 0 ? S< 2016 0:02 [migration/4]
root 11 0.0 0.0 0 0 ? SN 2016 0:23 [ksoftirqd/4]
root 12 0.0 0.0 0 0 ? S< 2016 0:13 [migration/5]
root 13 0.0 0.0 0 0 ? SN 2016 0:00 [ksoftirqd/5]
root 14 0.0 0.0 0 0 ? S< 2016 0:04 [migration/6]
root 15 0.0 0.0 0 0 ? SN 2016 0:00 [ksoftirqd/6]
root 16 0.0 0.0 0 0 ? S< 2016 0:02 [migration/7]
root 17 0.0 0.0 0 0 ? SN 2016 0:00 [ksoftirqd/7]

root 32023 0.0 0.1 4837968 25224 pts/3 S Jan26 0:00 /bcp/dlc/bin/_progres
root 32033 0.0 0.1 4837968 25188 pts/3 S Jan26 0:00 /bcp/dlc/bin/_progres
root 32043 0.0 1.0 4837076 174100 pts/3 S Jan26 0:01 /bcp/dlc/bin/_progres
root 32053 0.0 0.1 4836476 30252 pts/3 S Jan26 0:00 /bcp/dlc/bin/_progres
root 32063 0.0 1.0 4839172 177812 pts/3 S Jan26 0:00 /bcp/dlc/bin/_progres
root 32137 0.0 2.5 4838632 419396 pts/3 Sl Jan26 0:08 /bcp/dlc/bin/_sqlsrv2
root 32146 0.0 0.2 4837156 33156 pts/3 Sl Jan26 0:01 /bcp/dlc/bin/_sqlsrv2
root 32156 10.9 2.7 4832644 450408 pts/3 Sl Jan26 153:55 /bcp/dlc/bin/_sqlsrv2
root 32160 0.0 4.0 4835876 665372 pts/3 Sl Jan26 0:42 /bcp/dlc/bin/_sqlsrv2
root 32556 0.1 2.4 4810088 407492 pts/3 S Jan26 2:46 /bcp/dlc/bin/_mprosrv
root 32744 0.0 1.8 4810116 305076 pts/3 S Jan26 0:55 /bcp/dlc/bin/_mprosrv



etc. etc. etc.

This is what promon is reporting:
Database Status:
Database version number: 8365
Database state: Open (1)
Database damaged flags: None (0)
Integrity flags: None (1536)
Database block size (bytes): 8192
Total number of database blocks: 2547851
Database blocks high water mark: 2547757
Free blocks below highwater mark: 1056
Record blocks with free space: 4
Before image block size (bytes): 8192
Before image cluster size (kb): 512
After image block size (bytes): 8192
Last transaction number: 689436863
Highest file number defined: 0
Database created (multi-volume): 05/25/16 10:48
Most recent database open: 01/26/17 12:11
Previous database open: 01/26/17 12:11
Local schema cache time stamp: 01/17/17 11:24
Most recent .bi file open: 01/26/17 12:11


And some other options:

Shared Resources:
Shared memory version number: 6413614
Locking table entries in use: 7
Locking table high water mark: 2927
Number of semaphores used: 511
Broker status: Executing
BI Writer status: Executing
AI Writer status: Not executing
Watchdog status: Executing
Number of page writers: 2
Number of self-service users: 114
Number of remote users: 79
Number of servers: 22
Number of shut-downs: 0
Number of monitors: 1
After-image Management Archival Directory List (-aiarcdir): Not Enabled
Create After-image Management Archival Directory(s) (-aiarcdircreate): Not Enabled
After-image Management Archival Interval (-aiarcinterval): -1


Even better, the Activity shows:

Activity - Sampled at 01/27/17 12:01 for 23:50:22.

Event Total Per Sec Event Total Per Sec
Commits 1264050 14.7 Undos 208 0.0
Record Updates 1175545 13.7 Record Reads 4942110226 57585.6
Record Creates 236059 2.8 Record Deletes 17051 0.2
DB Writes 1020357 11.9 DB Reads 2603003 30.3
BI Writes 229944 2.7 BI Reads 9819 0.1
AI Writes 0 0.0
Record Locks 4595202491 53543.4 Record Waits 180 0.0
Checkpoints 3089 0.0 Buffs Flushed 57171 0.7

Rec Lock Waits 0 % BI Buf Waits 0 % AI Buf Waits 0 %
Writes by APW 93 % Writes by BIW 79 % Writes by AIW 0 %
Buffer Hits 100 % Primary Hits 100 % Alternate Hits 100 %
DB Size 19 GB BI Size 1813 MB AI Size 0 K
FR chain 1056 blocks RM chain 4 blocks
Shared Memory 4678M Segments 1

23 Servers, 195 Users (114 Local, 81 Remote, 114 Batch),2 Apws


Shared Memory 4678M ----> does this mean 4GB is actually the buffer size?




 
Last edited:

TheMadDBA

Active Member
That means that is the actual total shared memory for the OE/Progress service.. most of that would be for buffers.

There are also screens in promon that will show you the status/size of the buffer pools and other shared memory structures, along with a ton of other things. From what you have posted you would be using 4GB for primary buffers and 128MB for the secondary buffer pool.

For a clearer look at what is going on you can download ProTop.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
ps aux | grep -v COMMAND | awk '{ SUM += $5} END { print SUM/1024 }'
This command would overestimate your process memory utilization.

You are taking the virtual memory utilization of each process, including shared memory, and adding them together. You show multiple clients and servers, each with about the same VSZ, around 4.61 GB. They are each approximately the same because they each map the 4.57 GB of database shared memory into their process virtual memory address space. The difference is the other part of their address space that is (mostly) not shared: thread stacks, heap, code pages, private buffers, etc.
root 32023 0.0 0.1 4837968 25224 pts/3 S Jan26 0:00 /bcp/dlc/bin/_progres
etc...
ps aux - shows the 5th column VSZ which is virtual memory size. The total of all processed are about over 800MB.
How are you arriving at 800 MB?

Shared Memory 4678M Segments 1
Shared Memory 4678M ----> does this mean 4GB is actually the buffer size?

As TheMadDBA said, most of shared memory is buffer pools. But there are other data structures that you control the size of that affect it as well, including AI and BI buffers, OM cache, and VSTs, in particular the _*stat tables. Based on your startup parameters, your record counts will be:
_tablestat: 2,000 (-tablerangesize)
_indexstat: 10,000 (-indexrangesize)
_usertablestat: 1,004,000 (-tablerangesize * (-Mn + -n + 2))
_userindexstat: 5,020,000 (-indexrangesize * (-Mn + -n + 2))

These records have a memory cost. They are each small (about 32 bytes/user/table and 40 bytes/user/index, according to the KB) but it adds up. Those numbers are from an old KB article and may be higher now that the schema has changed. The point is, as Cringer noted, if those numbers are much higher than needed (do you really have close to 2,000 tables and 10,000 indexes?) then it's just a waste of RAM to set the parameters that high.
 

TomBascom

Curmudgeon
Certain OE schemas (names withheld to protect the guilty) do indeed have ten thousand or so tables. And indexes to go along with that.
 
Hello,

I really appreciate all your answers and I will have to digest the info over the course of some time. Not that I am ignoring anything, a bit of a complex matter on a UNIX box. Some really great responses here, thank you.

Regards,
Richard
 
Top