We are attempting to open our databases to remote connections for the first time (remote ABL). I've tried to do as much of my homework as possible so that I understand some of the related parameters (-n -Mn -Mpb -Mi -Ma -prefetch* and so on). We also have a consultant who has given us some initial recommendations on the parameters to use for our primary broker (which supports ABL) and backup (which supports only SQL).
While reviewing his recommendations, I was cross-referencing them to KB articles to try to understand them. Here is one example of a KB article that attempts to explain these parameters.https://knowledgebase.progress.com/articles/Article/How-to-set-the-values-for-the-startup-parameters-Ma-Mn-and-n
What is really hard for me to visualize is the model that underlies some of the parameters -Mpb and -Ma and -Mi. My understanding is that the "broker" for the ABL logins will look at the -Mi and as soon as it reaches that number of concurrently connected clients, it will start a new server until it reaches the -Mi and the process will repeat. Once all the servers are created (as limited by -Mpb), then additional clients (above -Mi) will start to pile into the existing servers until they all reach the value of -Ma. At that point the database will start rejecting new client connections. Does this sound accurate?
Hopefully I have things right so far. Based on this alone, it strike me that there is quite a lot of responsibility placed on the DBA to know the number of clients and how quickly they might connect and how many might be using the database concurrently.
Assuming I'm understanding this correctly so far, the next question is about the available remote servers (-Mpb). Given that we are running 11.7.4, are these all single-threaded processes? I've heard that they are, and that they use some kind of cooperative multi-tasking in order to allow work to be done in a serialized way on behalf of all the client connections (-Ma). Please let me know if I misunderstood.
My biggest concerns are related to the "broker" for ABL. How does it distribute new client connections to the available servers (-Mpb) once all of the servers have all been started? Does it just round-robin the incoming clients? Or does it try to somehow determine if a server is busy first? If there are a number of different (-Ma) client connections within a given server, I'm assuming that some of them may not be acting in a neighborly way (eg. generating a large result set of NO-LOCK records, or waiting on a database lock, or performing a large update). If this is the case then it seems to me that the other client connections within the same server might suffer performance penalties. And the performance penalties will be compounded by the number of over-active clients that are hosted in a single server... Worse yet, if the "broker" has no idea that the server contains these over-active clients, then it may drop new ones in place and make matters worse.
Can someone please tell me if there is any in-depth KB or whitepaper that explains how client load is distributed among the servers? Given that the OE DBA is responsible for managing all those different startup parameters then it makes sense that Progress should give us some understanding about how the parameters will impact the behavior and remote client performance. Otherwise there will probably be a lot of trial-and-error.
It would also be nice to know if there are any metrics being tracked by the remote servers like total number of clients requests processed, records processed, transactions processed, and average duration (ms or fractions of ms) to process each client request.
Any help to understand this would be greatly appreciated. I am guessing that there may be whitepapers that elaborate on this stuff but I haven't found them yet.
Thanks in advance, David
PS... Here is a small section of the KB article that has raised the most concern, especially since we are currently starting out with an -Ma of 100.
For 4gl brokers I almost always use -Mi 1 -- that means "round robin" and _generally_ provides the best balance.
The sql server is multi-threaded so for SQL brokers I will use -Mi = -Ma.
You shouldn't use "both" brokers but, if you do, -Mi = -Ma is probably wiser. That way you are much less likely to get painted into a corner where all the servers are taken by 4gl or sql before the other type has a chance to start any.
There are a lot of KB entries that are badly outdated or very misguided.
-Ma 5 is a very old recommendation. YMMV but, in a very "broad brush" kind of way, current systems can probably easily support -Ma 10 or 20. -Ma 100 OTOH is probably not such a good idea. As you say, the time slicing means that one bad query can either cause a lot of problems directly or perform very poorly in such an environment.
The DBA has to also understand the application and how well, or how poorly, it is written and what the characteristics of the queries in the application are.
There are a number of sources of information about what is going on with remote servers in the VSTs. ProTop has a couple of handy views of client/server activity that are based on that data:
┌──────────────────────────────────────────────────────────────────── Login Broker Config ─────────────────────────────────────────────────────────────────────┐
│ Typ v IPver PID -S -minport -maxport -PendCx -Mm -Mpb -Ma -Mi Cnx Cnx% SQLTrc SQLWUp SQLcurs SQLstck SQLstmt SQLtmpb SQLtmpd SQLtmpz │
│ ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── │
│ >SQL IPV4 45979 9050 26200 26399 0 1024 16 5 1 0 0% OFF OFF 0 0 0 0 0 0 │
│ SQL IPV4 45965 9000 25100 25199 0 1024 6 5 1 0 0% OFF OFF 0 0 0 0 0 0 │
│ BOTH IPV4 45715 9030 1025 2000 0 8192 0 4 1 0 ? OFF ON 0 0 0 0 0 0 │
│ ABL IPV4 45951 9990 20000 21999 0 8192 100 5 1 0 0% OFF OFF 0 0 0 0 0 0 │
┌────────────────────────────────────────────────────────────────────── Server Activity ───────────────────────────────────────────────────────────────────────┐
│ Srv Type Port Cnx Max QryRcvd recSndQry bytSndQry MsgRcvd RecRcvd rr/msg MsgSent RecSent rs/msg MB Sent v MB Rcvd RcvdSz SendSz │
│ > 9999 Total 0 48 449 614 3 960 1502 0 0.00 871 1902 2.18 0.56 0.38 262 678 │
│ 24 Auto 20028 1 5 570 3 892 1096 0 0.00 579 1538 2.65 0.49 0.29 281 878 │
│ 46 Auto 20053 1 5 10 7 2131 69 0 0.00 54 73 1.37 0.02 0.01 172 414 │
│ 25 Auto 20029 1 5 12 7 1576 130 0 0.00 109 88 0.81 0.02 0.03 248 171 │
│ 28 Auto 20032 1 5 6 8 2301 47 0 0.00 37 49 1.32 0.01 0.01 179 390 │
│ 22 Auto 20026 1 5 3 8 1507 24 0 0.00 14 21 1.54 0.00 0.01 221 286 │
│ 20 Auto 20024 1 5 1 15 2269 16 0 0.00 9 20 2.13 0.00 0.00 196 317 │
│ 15 Auto 20019 1 5 2 12 1690 15 0 0.00 9 19 2.20 0.00 0.00 208 314 │
│ 21 Auto 20025 1 5 2 7 1477 17 0 0.00 10 13 1.34 0.00 0.00 219 266 │
│ 14 Auto 20018 1 5 2 6 1279 17 0 0.00 10 12 1.20 0.00 0.00 212 256 │
│ 18 Auto 20022 1 5 2 9 1461 15 0 0.01 9 16 1.73 0.00 0.00 204 270 │
│ 17 Auto 20021 1 5 1 10 1897 15 0 0.00 9 13 1.50 0.00 0.00 208 280 │
│ 23 Auto 20027 1 5 1 10 1641 13 0 0.00 8 12 1.61 0.00 0.00 213 277 │
│ 16 Auto 20020 1 5 1 10 1931 12 0 0.00 7 10 1.48 0.00 0.00 198 280 │
│ 19 Auto 20023 1 5 1 12 2124 9 0 0.00 5 9 1.62 0.00 0.00 203 281 │
│ 62 Auto 20069 1 5 0 35 4290 2 0 0.00 1 3 2.92 0.00 0.00 165 357 │
│ 47 Auto 20054 1 5 0 35 4290 2 0 0.00 1 3 2.92 0.00 0.00 165 357 │
│ 36 Auto 20041 1 5 0 30 3211 1 0 0.07 1 3 3.00 0.00 0.00 112 321 │
│ 750 Inact 0 0 0 0 0 0 0 0 0.00 0 0 0.00 0.00 0.00 0 0 │
│ 749 Inact 0 0 0 0 0 0 0 0 0.00 0 0 0.00 0.00 0.00 0 0 │
│ 748 Inact 0 0 0 0 0 0 0 0 0.00 0 0 0.00 0.00 0.00 0 0 │
Your understanding (paragraph 3 above) is correct. I always set -Mi to 1 so after the first Mpb clients have connected, assuming no disconnects, all servers have been spawned.
I don't think there's as much for the DBA to juggle as you imagine. Yes, there are several parameters but they are interrelated; setting some determines how others can be set. I'll explain that below.
For a particular connection broker, you have:
-Mpb: max servers for that broker
-Ma: max clients for each server
-Mi: minimum clients before a new server will be spawned, if not all servers have been spawned
Also, for the primary broker (which may also be a connection broker, as it is in your case):
-n: total database connections allowed (not including brokers and servers)
-Mn: total "servers"
I should also note that this is far from being an exhaustive list of parameters related to C/S. It's just the very basics.
As you know, the maximum number of concurrent clients that can connect through a connection broker is Mpb * Ma. You need to know what that product is. The -Mi parameter defaults to 1 and that's a good default so I always leave it there. So, knowing the desired maximum number of concurrent remote ABL clients, you essentially have one decision to make. You need to decide the trade-off between number of servers and number of clients per server. In choosing one, you are also choosing the other.
For example, let's say you want a maximum of 500 remote clients. You said you plan to use -Ma 100, so that would make -Mpb 5. Setting a relatively high value for -Ma reduces the server footprint on the server, in terms of memory and CPU utilization and resource contention in the database, but it imposes a high penalty on clients. At maximum load, a client's query might have to wait before the server comes back to it until 99 other queries are processed. That would be a bad end-user experience, if those are interactive users. If the clients are App Servers, they may spend a lot of their time waiting on DB I/O.
On the other hand, if you were to set -Mpb 100 and -Ma 5, then clients would have much lower latency and much more consistent wait times for data, but at the cost of much higher 4GL server footprint on the server machine. I can't tell you what is the right trade-off for your hardware and your application, but I can tell you that -Ma 100 is very high in my experience. I typically keep -Ma in the range of 3 to 5.
As for setting other parameters, -Mn is easy: set it to a minimum of (sum of broker -Mpb values) + (total number of secondary brokers). Setting -n means anticipating how many total concurrent DB connections you will have at peak. This is limited by the number of rows in _connect, which is -Mn + -n + 2. The two extra are for the primary broker and for a proshut, to ensure you can always shut down your DB. So -n should include all 4GL clients (self-service or remote), all SQL clients, background processes (BIW, AIW, APWs, WDOG, AIMD, etc.), utilities, monitors, etc. You don't have to include remote servers or secondary brokers in the -n calculation as they are accounted for in -Mn. I always add a buffer of at least a dozen or two on -n, as there might be connections I need later (say, a monitoring or interactive client to see what's going on) that I don't anticipate up front.
You didn't mention parameters for server ports but it's important so I will, just in case. You should always specify -minport/-maxport on every connection broker. They determine the contiguous range of TCP ports used by the broker for spawning servers. Give every broker a unique range, different from every other broker in this and every other database on the same machine. Be sure to avoid ranges that are specified, whether they are used or not, in the OS services file. I like to add my brokers' port ranges as a comment in the services file so that I and others will know which ranges to avoid on that machine in future. Doing this will help avoid "unable to spawn server" errors that prevent clients from connecting.
For -prefetch*, I'm using -prefetchDelay, -prefetchFactor 100, -prefetchNumRecs 10000. These parameters will work best if you don't use the default message size (-Mm 1024). I use 8192 or 16384.
> Given that we are running 11.7.4, are these all single-threaded processes? I've heard that they are, and that they use some kind of cooperative multi-tasking in order to allow work to be done in a serialized way on behalf of all the client connections (-Ma).
Yes, in 11.x and earlier the 4GL server is single-threaded and uses a multiplexing design to process client queries, and connection/login requests, serially. This will change to a multi-threaded model in 12.0.
As for metrics, you can see current connections in promon's Servers by Broker screen (R&D 1 17). You can see the server metrics you asked about in the _Servers VST. There is a browse for that info in ProTop as well.
> My biggest concerns are related to the "broker" for ABL. How does it distribute new client connections to the available servers (-Mpb) once all of the servers have all been started? Does it just round-robin the incoming clients? Or does it try to somehow determine if a server is busy first?
Once all servers have been spawned and then some clients disconnect, the broker does not just round-robin between servers to distribute new client connections. It assigns clients to the servers with the least clients first, until the distribution is even.
> I have never needed to restart a running broker.
It happens very rare.
Recent war story: someone (I guess it was sysadmins rather the hackers) ping'ed the server's ports. As result the Progress servers crashed with memory violation. They were disconnected from database and login broker had tried to start new ones but the range of server's ports was too narrow. The ports used by the crashed servers stayed in use until a keepalive timeout. So the login broker failed to start new servers and new remote clients failed to connect the database. The solution could be simple: just to restart a login broker with new set of -minport/-maxport.
What are the reasons why the ranges between -maxport and -minport in your examples are a few times higher than the -Mpb value? A reserve of the ports (let's say, extra 10%) would be, of course, a good idea.
> As for setting other parameters, -Mn is easy: set it to a minimum of (sum of broker -Mpb values) + (total number of secondary brokers).
The -Mn value higher than the value calculated by the specified formula can allow to start a new login broker with its army of servers or to restart any running login broker without disconnecting the clients served by its servers.
> The -Mn value higher than the value calculated by the specified formula can allow to start a new login broker with its army of servers or to restart any running login broker without disconnecting the clients served by its servers.
This is true. But if my client's business needs suddenly changed enough that I wanted to start a new broker with its "army" of servers and clients, I'd want to understand that change clearly and re-evaluate and change my configuration.
I don't know if this is a common practice for others but I have never needed to restart a running broker.
> What are the reasons why the ranges between -maxport and -minport in your example
No good reason. Those values are very excessive.
I wasn't trying to show good sample values -- rather the point was to show that the information is available. Having it readily available makes it easier to see issues that ought to be corrected ;)
Good to know George. But that could be prevented with a firewall in front of the DB server.
Thank you very much for all the thorough answers. I think they have cleared up a lot of confusion. There should be a whitepaper or, better yet, a youtube animation that describes the type of mechanism that is used to connect an OE database to its remote ABL client/server sessions. That would help to make sense of the many startup parameters, and the way they interoperate with each other as a team.
My question was asked in the context of our PASOE sessions that are connecting remotely to the OE database. I was trying to make sure that my remote sessions would have efficient access to the database and wouldn't encounter too many bottlenecks.
I've been using "shared memory" connections for a long time but I'm somewhat new to client-server. One thing that didn't really "click" for me until I actually watched it happen myself was the way in which a ABL session in PASOE becomes partnered and permanently "married" to a _mprosrv process on the remote database server. The _mprosrv process on the database server will be used for the life of the ABL session in PASOE. Given the flexible and dynamic relationship that a HTTP session in PASOE has to the related ABL sessions (ie. both to the MS-agent process and its underlying ABL sessions), I thought that the ABL session would *also* have an very dynamic relationship to the back-end database, and would move around from "server" to "server" over time. To me this is the type of thing that is implied by such terms as "broker" and "server".
But that does *not* appear to be the case at all. The "broker" only seems to interact with the ABL session connection *once* - at the point where the session is first started. At that time the broker "marries" the ABL session to an _mprosrv process for *LIFE* (until the process on one side or the other has ended). In the event that the _mprosrv partner is a bad one - ie it happens to be co-habited by some other greedy connections - then the ABL session used by PASOE will be SOL. This is because the database broker won't ever move that ABL session to an alternate server which might be less busy.
In the best-case scenario (where PASOE is concerned), there will be an entire _mprosrv that will be dedicated to a *single* PASOE session connection (REMC/PASN) so that it will always be highly responsive, and there will be no unexpected bottlenecks from any other random database requests. IE. It should be a monogamous marriage between the ABL session from PASOE and the _mprosrv process. This is especially true given that PASOE is probably supporting a very heavy workload , and is already distributing the workload and processing it concurrently (across its various agents and sessions).
Thanks again for all the help. For those of you in the USA, have a happy Thanksgiving.
I’ve written about this many times in the past. Here is another rendition.
Connecting a 4GL client to an OpenEdge database via TCP/IP takes place as follows:
0: the 4GL client sends a connect request to the login broker specified by a -S service name or port number. The database login broker should have been set up to be listening for connect requests on this port.
1: when the login broker (which could be the primary or a secondary, depending on configuration) receives the connect request, it will find a suitable server process to handle the connection. Suitable means either a server with an unused client slot or if none are available, a new server process will be started (depending on configuration). Server assignment could be round-robin or fill-existing-first or some variation. This depends on min clients per server and max clients per server settings.
2: when the server has been chosen, the server is notified of the client connect request and it chooses a port on which to listen for a login request from the client.
3: the server tells the login broker which port it wants to use. the broker makes a note of this and other relevant information.
4: the login broker sends a message back to the client with the port information and then closes the connection. Once this has happened, all further communication occurs between the client and its assigned server.
5: the client send a login request to the server.
6: the server assigns a database connection slot on behalf of the client and establishes the database connection.
7. the server sends a login accepted back to the client.=
Yes, that is what I've observed. What was surprising to me was that the long-lived ABL sessions in PASOE are permanently bound to a single _mprosrv server for life.
If there were 30 ABL sessions in an MS-Agent that were using 10 _mprosrv's on the back end. Then it is possible for serious database contention to arise with only three ABL sessions in use. (IE. it is possible that those three sessions might be unfortunate enough to be connected to a *single* _mprosrv process on the database server. That _mprosrv is where the bottleneck would occur. This is despite the fact that there are 27 other ABL sessions and 9 other _mprosrv's which are not getting any utilization at all).
That is correct. however, you have choices.