Question How much RAM does my database and processes use?

Hello,

I know this question is floating around, but I would like to try a simplified approach. I have a dedicated VM Red Hat Linux Server with Progress 11 and we are about to upgrade it, so it will have to move to new hardware with other systems. Questions are being raised, how much RAM does our DB and application actually need. I spent all day yesterday trying to analyse my usage. My current server was setup by a third party, it has 24GB allocated, as that was what the box had.

From the command "top" it shows this:
KiB Mem : 23942392 total, 347632 free, 3638500 used, 19956260 buff/cache
KiB Swap: 16777212 total, 12021000 free, 4756212 used. 13941312 avail Mem

Not sure if this is the tool I can rely on.


So roughly 3.7 is in use. The Linux server requires at least 2GB (min. requirement). That leaves me with less than 3GB used.
Here is what Linux may be using:
Code:
[root@accordrush rmusielak]# more /proc/meminfo
Buffers: 0 kB <-- data read off disk
Cached: 19331400 kB
Dirty: 14836 kB
Slab: 439780 kB <-- kernel memory

I gathered this based on this document:
https://access.redhat.com/sites/default/files/attachments/memory_usage.pdf


The database config file has these number allocated to the buffer cache:
broker.pf
-B 524288
-B2 16384

My database is at 60GB in size.

Base on this article:
Progress KB - What value should -B be set to?

Advice was that as a rule of thumb start with setting -B to be 10% – 15% of the database size.
Example: For a 50 GB database, 10% would be 5120 MB of RAM (5GB)
If the database blocksize is 1KB, set -B 5242880 (5242880/1)
If the database blocksize is 4KB bytes, set -B 1310720 (5242880/4)
If the database blocksize is 8KB bytes, set -B 655360 (5242880/8)
Based on this I think I should increase the -B parameter, although I know that some of you say here:
Memory Allocation And Usage

In reality it is a trade off between best performance and cost of RAM.
....
The notion that your buffer pool should be x% of your database size is antiquated and is now considered a worst practice. You want as much buffer pool as you can get, increasing it to the point that the increases continue to improve the ratio of logical to physical I/O and they don't hurt server performance by causing swapping.


My Infrastructure planner will never give me what I want, he wants to know how much less below the 24GB we can allocated, so I need proof.

This is what "promon" shows me:
Code:
Rec Lock Waits    0 %    BI Buf Waits      0 %    AI Buf Waits      0 %
Writes by APW    91 %    Writes by BIW    79 %    Writes by AIW     0 %
Buffer Hits      99 %    Primary Hits     99 %    Alternate Hits  100 %
DB Size          60 GB       BI Size     582 MB       AI Size       0 K
FR chain                  1433 blocks   RM chain                    7 blocks
Shared Memory   4678M        Segments      1

26 Servers, 170 Users (117 Local, 53 Remote, 117 Batch),2 Apws

It looks like 4.7.GB is rightly allocated to the database buffers.

This is what is in the buffer (Promon -> R&D -> 1 -> 7 )

Code:
06/07/22        Status: Buffer Cache
14:35:46

Total buffers:                     540674
Hash table size:                   137743
Used buffers:                      536009
Empty buffers:                     4665
On lru chain:                      524288
On lru2 chain:                     16384
On apw queue:                      0
On ckp queue:                      37
Modified buffers:                  357
Marked for ckp:                    37
Last checkpoint number:            553513
LRU force skips:                   50
LRU2 force skips:                  0


Then we come to the processes.

I have a test server, so I stared the process and checked the size in memory using the command "pmap <PID>" to check the size of each process.

I also made a note of what the DB shows during startup:
Code:
[root@accordwell pack]# proserve package -pf broker.pf
OpenEdge Release 11.6.1 as of Fri Feb 19 18:20:45 EST 2016
10:40:13 BROKER The startup of this database requires 4677Mb of shared memory. Maximum segment size is 8192Mb.
10:40:16 BROKER 0: Multi-user session begin. (333)
10:40:16 BROKER 0: Before Image Log Initialization at block 957 offset 2790. (15321)
10:40:16 BROKER 0: Login by root on /dev/pts/1. (452)
10:40:16 BROKER 0: Started for 12500 using TCP IPV4 address 0.0.0.0, pid 32302. (5644)

Here I show the DB processes and the related processes and the total memory usage (if this is correct)

Code:
[root@accordwell pack]# proadsv -start
OpenEdge Release 11.6.1 as of Fri Feb 19 18:20:45 EST 2016

31245 1 71 10:31 pts/1 00:00:21 /bcp/dlc/jdk/jre/bin/java
Pmap: total 5,038,712K
31419 1 3 10:31 ? 00:00:00 /bcp/dlc/jdk/jre/bin/java
Pmap: total 8,394,680K

31332 31245 0 10:31 pts/1 00:00:00 _pphpriv
(  TOP shows: aprox 300MB )

DB start process:
32302 1 3 10:40 pts/1 00:00:02 /bcp/dlc/bin/_mprosrv
PMap: total 4,813,140K
32589 1 0 10:44 pts/1 00:00:00 /bcp/dlc/bin/_mprshut package -C biw
Pmap: total 4,812,928K

I have yet to check user processed (background, user sessions).

Do I understand correctly that the "brokers" work in shared mode, so many of the processes are actually spawned and part of the main DB engine?

Can anyone show some advice how to approach those and check if they are part of the engine or a shared "broker"?

For example this process (background task)

Code:
usr    pid    time of login           user id     Type  tty                  Limbo?
 65  32742 Sat Mar  5 15:44:36 2022   in060.p1    BAT   batch                no

PMAP:
 total          4,851,044K

They all seem to be using about 4.9GB, which is similar to one of the DB "brokers".


Thanks,
RIchard
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
My Infrastructure planner will never give me what I want, he wants to know how much less below the 24GB we can allocated, so I need proof.
This is unfortunate and it is significant: this is not a technology problem. It is a people problem.

Your planner has already decided:
  • you need less than 24 GB of RAM;
  • you can't ask for what you want;
  • you must provide proof to justify what you will ask for.
Why 24? As opposed to 240? If it's because someone decided years ago that's how much RAM the last DB server should have, that's a poor justification. Ask yourself: why are you subject to a requirement to justify your opinions with proof but his opinions, despite being very specific, require no proof and are considered unassailable? That's a question for the people above you.

Questions are being raised, how much RAM does our DB and application actually need.
We don't know and neither do you. Your planner sure as hell doesn't. You need to test to find out.

What does "need" mean? That may sound like I'm playing at semantics but it is important. There is a distinction to be made between how much of a resource, e.g. RAM, a process is currently using, versus how much it could benefit from using based on current demand levels ("need").

OpenEdge RDBMS resource utilization is, for the most part, prescriptive. It won't take over your machine and use as much RAM as it wants to, to meet some performance goal. At a high level, it will use as much RAM as you tell it to via broker configuration, for buffer pools (-B/B2/bibufs/aibufs), caches (-omsize/secsize/cdcsize/mtpmsize), and other shared memory data structures (connection/server/transaction/lock/statistics/hash tables, etc.).

At some point in the past, someone decided to use a subset of the resources then available on a machine for your database (with some structure/schema), for some user count, for some version of your application(s), for some level of user activity, and it resulted in some level of application performance. I don't see any reason to slavishly adhere to that configuration when your workload will be different in the future.

Doubling the size of the buffer pool (-B) could improve I/O efficiency. If it does then that would be a worthwhile change. If it doesn't then your buffer pool may currently be large enough. Without testing both configurations, with a real-world load, you won't know. And of course, your workload can vary with time, for any number of reasons: application code changes, changes in user count/behaviour, seasonal variations in business activity, database growth, mergers/acquisitions, etc.

The largest single consumer of RAM in the database is the buffer pool. In your stated configuration, that is 4.13 GB out of a total of 4.57 GB of database shared memory. That will provide some level of caching efficiency, i.e. some ratio of logical to physical I/O, based on some level of user workload. And that, in turn, together with your database configuration and structure as a whole, will result in some level of application performance.

Is that current level of performance good enough to meet business objectives? Do you have room to grow? Does the business know how much better the application performance could be today, compared with the "normal" they are accustomed to? Could that 3-hour report actually run in 15 minutes? If so, would it help the business? Again, those aren't technology questions, they are business questions. But they are worth exploring.

It sounds like your planner is trying to optimize resources in one way: fitting as many VMs/applications onto as little hardware as possible. While virtualization can bring some good efficiencies, it is also easy to be too aggressive in this approach. It is a false economy for the business to save a few hundred or few thousand dollars worth of RAM or disk space, while starving a mission-critical application and throttling its performance. Your business could lose more than that in a day if something goes wrong.

What if your database or your user count double in size next year? And what if it doubles again? Will he still defend the position that 24 GB is enough? Will you have DB VMs with 24 GB of RAM forever? If he is willing to change in the future, then why not now? Someone above him should be looking at the bigger picture.

This is what is in the buffer (Promon -> R&D -> 1 -> 7 )
This doesn't tell me much as I don't know database uptime or user demand at or prior to this screenshot.

Here I show the DB processes and the related processes and the total memory usage (if this is correct)
You're getting down into the weeds looking at process memory. It's likely going to be fairly small compared with your database shared memory.

DB start process: 32302 1 3 10:40 pts/1 00:00:02 /bcp/dlc/bin/_mprosrv PMap: total 4,813,140K 32589 1 0 10:44 pts/1 00:00:00 /bcp/dlc/bin/_mprshut package -C biw Pmap: total 4,812,928K
You can't add these PMap numbers together. The primary broker allocates database shared memory. Other database processes running on the database server machine, excluding remote clients, map that shared memory into their address spaces. PMap shows 4.59 GB (4,813,140 KB) used for the broker. Other shared memory processes, such as the BIW process you showed above, will show a similar number but their actual marginal RAM utilization is much much smaller; maybe a few megabytes for their private memory.

Can anyone show some advice how to approach those and check if they are part of the engine or a shared "broker"?
I'm not really sure what you're asking. You're better off abandoning the idea that there is something called a "database engine". This is generic terminology and doesn't map to any specific RDBMS process.

I suggest you read through the Database Essentials manual; it explains the various database processes, at least at a high level. It would also be instructive for you to look at a running database on a server. Find all of the processes associated with that database, either with a ps -ef | grep <some string> or with sudo lsof <dbname>.db. See if you can explain to yourself what they are and what they do. If not, post the list here and we can help to improve your understanding. And don't get worked up about their RAM utilization; it's mostly small potatoes.
 

TomBascom

Curmudgeon
This:

Total buffers: 540674
Hash table size: 137743
Used buffers: 536009
Empty buffers: 4665

Says that of the 540,674 buffers (the value of -B) that were allocated 4,665 have never been used. If this data was gathered shortly after db startup that is probably meaningless. If, on the other hand, this is a production database that has been up and running for a day or two and which has exercised all of the expected business functionality those "empty buffers" are saying that increasing -B will not have any benefit.

Key point is whether or not all of the business functionality has been exercised. Even if it has been a few days you might have some rarely run processes that could still benefit from more cache.
 
Hello All,

I appreciate all your hints and I understand all Rob's comments about allocating as much as possible to the memory for the DB and all his ridicule about our infrastructure approach ( not mine BTW :rolleyes: ), with which I agree.

Tom, your answers are brief, thanks for the video, I'l watch it later. Sorry, I should have know the 500,000 is buffers, so I would multiply it by the 8KB block size, how silly of me ;)

We had a chat with one of our third party developer, who seems to know quite a lot, but since there is no contract for them to do any DB administration nor performance tuning he simply offered me some answers to my question.

As for my system, we've been running it continuously for months without stopping and an online full backup every night.

I am considering the ProTop, as I have never used it, I thought it required a licence, but it's not the case.

At the moment we will have 12GB allocated to the box, my estimate is:
OS - 2GB (probably not in use, but's it's the recommended minimum for Red Hat)
Other software - 2GB (shared memory mode)
Database - aprox. 5GB by my third party recommendation, they said judging by the DB status (cache hit ration, DB write waits, etc) we would not benefit much, but the guy said he will have a look at our setup.
When I asked if we will be safe with 10GB, they said yes, but recommended 12GB. So we got what we wanted.

Our third party chap also said, we'll be moving towards upgrading to Progess 12 on RH Linux 8, and there will be an additional Tomcat apps server, so RAM usage may go up, but since I they will do the major works they will recommend the numbers.

As for installing "smem", if accepted by my controllers, I might consider adding it, although I would have to check what it does.

Once again, many thanks,
Richard
 
Top