Error Connections exceeding.

Mike

Mike

Moderator
Hi,

We have a situation where sql connection exceeding. User complaining about connection exceeding. With error in db log file:-

:00:04.200-0600] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.
[2024/11/06@13:54:28.711-0600] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.
[2024/11/06@13:54:34.624-0600] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.
[2024/11/06@13:54:44.047-0600] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.
[2024/11/06@13:54:56.249-0600] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.
[2024/11/06@13:57:04.177-0600] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.

4GL connection primary broker :-
-Mi 1
-minport 51900
-mmax 8192
-Mn 20
-Mpb 20
-n 400
-nb 200

SQL server secondary broker parameters are below: -
-m3
-Ma 1
-maxport 51999
-Mi 1
-minport 51900
-N TCP
-ServerType SQL

When i open promon session 17 option server by broker it shows me below:-


No Pid Type Protocol Logins Users Users Users Num

0 3723 Login TCP 7718 0 0 5 49030
2 13782 Auto TCP 3473 0 0 5 51905
3 13784 Auto TCP 738 0 1 5 51906
4 13786 Auto TCP 18 0 0 5 51907
5 13788 Auto TCP 1261 0 1 5 51908
6 13804 Auto TCP 1420 0 1 5 51916
7 13806 Auto TCP 21 0 1 5 51917
8 13808 Auto TCP 12 0 1 5 51918
9 13869 Auto TCP 6 0 1 5 51927
10 13871 Auto TCP 12 0 1 5 51928
11 13873 Auto TCP 7 0 1 5 51929
12 13875 Auto TCP 6 0 1 5 51930
13 13877 Auto TCP 7 0 1 5 51931
14 14441 Auto TCP 204 0 1 5 51938

Enter <return> for more, R, P, T, or X (? for help):


11/07/24 Status: Servers By Broker
01:26:18

Sv Pend. Cur. Max. Port
No Pid Type Protocol Logins Users Users Users Num

16 24142 Auto TCP 513 0 0 5 51948
17 32615 Auto TCP 13 0 1 5 51952
19 2300 Auto TCP 4 0 0 5 51956
20 2302 Auto TCP 3 0 0 5 51957


750 Login TCP 14193 0 0 1 49035 ( these are the sql broker port and logins in bold where user facing issue )
15 6572 Auto TCP 12618 0 0 1 51961
18 18636 Auto TCP 11 0 0 1 51966

Issue resolve when I terminate server no :- 18. But its coming so frequent. Can you please suggest what can i do to fix this issue?


Thanks Regards
Mike
 
You haven't specified your OpenEdge release. This is important to know when discussing broker parameters.

Three major points:
  1. Some broker parameters interact with each other. All of their values need to make sense together. Currently, they don't.
    These include -n, -Mn, and all values of -Mpb, -Ma, -minport, -maxport.
  2. Don't terminate database servers.
    You aren't solving a problem; you're just temporarily treating symptoms (frequently) and masking the real problem, which is incorrect database broker configuration.
  3. Your parameter values need to be in alignment with your business requirements.
    For example, if you hired me as a consultant to fix your SQL configuration, my first question would be this: at a maximum, how many SQL clients, how many remote and self-service ABL clients, and how many database connections in total should be allowed to connect to the database concurrently? (My second question would be: have you purchased the appropriate Progress licenses to allow those connections?) You can't choose the correct broker parameters until you answer at least those questions.
    That said, your current configuration has some obvious problems, regardless of your business requirements.
These are the basic client/server parameters for login brokers; this is not a complete list:

-ServerType
-Mpb
-Ma
-Mi
-minport
-maxport

Every login broker should explicitly specify all of these parameters, at a minimum. There are other parameters that you should specify for performance tuning, depending on your OpenEdge release, some of which are ABL-specific and some are SQL-specific. But let's start with the basics.

You have the following configuration (missing and problematic values are highlighted in bold):

Primary/ABL login broker client/server parameters:

-ServerType 4GL
-Mpb 20 (will allow up to 19 4GL servers, if they are able to spawn before any SQL servers)
-Ma (unspecified! defaults to 4)
-Mi 1
-minport 51900
-maxport (unspecified!)
Maximum remote ABL clients allowed with this configuration is (-Ma) * (-Mpb) = 4 * 19 = 76, and only if all 4GL servers are able to spawn. Note that the calculation is not 4 * 20 because -Mn is too low.

Primary broker (global) parameters relevant to client/server connections:

-n 400
-Mn 20

Invalid parameters:

-mmax 8192
-nb 200
These are client startup parameters, not database broker parameters. They should not be specified on a broker.

Useless parameters:

-N TCP
This specifies the network protocol. The only valid value is TCP and it is the default value. Specifying this parameter explicitly has no effect, unless you are using a very ancient version of Progress.

Secondary SQL login broker:

-ServerType SQL
-Mpb (unspecified! defaults to -Mn; will allow up to 19 SQL servers, if they are able to spawn before any 4GL servers)
-Ma 1
-Mi 1
-minport 51900
-maxport 51999
Maximum concurrent SQL clients allowed with this configuration is (-Ma) * (-Mpb) = 1 * 19 = 19, and only if all SQL servers are able to spawn. Note that the calculation is not 1 * 20 because -Mn is too low.

Important notes:
  • -Mn on the primary broker determines the number of records in the _Servers table. Every secondary broker and every 4GL and SQL server requires a record in this table. Therefore the size of -Mn should be at least the sum of the value of -Mpb for each login broker, plus the number of secondary brokers. For example, if you have one secondary broker (SQL), -Mpb 20 for 4GL, and -Mpb 5 for SQL, -Mn must be at least 1 + 20 + 5 = 26.
  • The minport and maxport should be specified for all login brokers.
  • All minport/maxport ranges should be mutually exclusive; do not re-use or overlap your port ranges.
  • All minport/maxport ranges should be below 32768.
  • All minport/maxport ranges should be for port numbers that are not defined in the operating system's services file.
  • The number of ports in the minport/maxport range for a broker should match its -Mpb value.
  • -Mpb should be specified for all login brokers.
  • -Ma should be specified for all login brokers.
You are seeing error 8839 because the _Servers table fills up before the brokers are able to spawn all of their servers, because the value of -Mn is too low.

The problem:
Your database brokers are both configured incorrectly.

Steps to resolve the problem:
  1. Choose the maximum number of concurrent remote ABL connections. I can't tell you this value. It is a business requirement, based on your users' needs and your licensing limitations.
  2. Choose a reasonable value for maximum clients per server. This is the new value for -Ma for the 4GL broker.
  3. Divide maximum remote 4GL connections by -Ma; this is the new value for -Mpb for the 4GL broker.
  4. Choose appropriate values for -minport and -maxport for the 4GL broker, given the value of -Mpb.
  5. Update your 4GL broker configuration with the new values.
  6. Choose the maximum number of concurrent SQL connections. I can't tell you this value. It is a business requirement, based on your users' needs and your licensing limitations.
  7. Choose a reasonable value for maximum clients per server. This is the new value for -Ma for the SQL broker.
  8. Divide maximum remote 4GL connections by -Ma; this is the new value for -Mpb for the SQL broker.
  9. Choose appropriate values for -minport and -maxport for the SQL broker, given the value of -Mpb.
  10. Update your SQL broker configuration with the new values.
  11. Update the value of -Mn on the primary broker. Set it to (4GL -Mpb) + (SQL -Mpb) + 1.
  12. Ensure that the value of -n is still high enough to accommodate all database connections that are not brokers or servers. This includes remote ABL clients, self-service ABL clients, SQL clients, database utilities, daemons, and background processes.
  13. Double-check that you have explicitly specified valid values for all of the basic client/server parameters listed above.
  14. Restart your databases.
  15. Validate that you are able to concurrently connect the maximum number of clients that you expect, and that you are able to spawn as many servers as you have configured.
 
Hi Rob ,

Thanks for the valuable response.
So The progress version is :- 11.7


So here we go :-
4gl broker parameters below: -

Ma 5
-maxport 51999
-Mi 1
-minport 51900
-mmax 8192
-Mn 20
-Mpb 20
-n 400
-nb 200

Secondary broker configuration
-m3
-Ma 1
-maxport 51999
-Mi 1
-minport 51900
-N TCP
-ServerType SQL

Promon option 17:- Server by broker: - ( Also PFA attached the files for clear picture port 49035 is secondary broker starts to spawn servers 51961 and 51966 even though not define value of -Mpb)

/apps/scripts/pf-files/common/broker-4gl-standard.pf
/apps/scripts/pf-files/common/broker-4gl-standard.pf

/apps/scripts/pf-files/common/broker-4gl-standard.pf
Ma 5
-maxport 51999
-Mi 1
-minport 51900
-mmax 8192
-Mn 20
-Mpb 20
-n 400
-nb 200

Secondary broker configuration

-m3
-Ma 1
-maxport 51999
-Mi 1
-minport 51900
-N TCP
-ServerType SQL


Progress version :- 11.7
Pend. Cur. Max. Port
No Pid Type Protocol Logins Users Users Users Num

0 3723 Login TCP 7804 0 0 5 49030
2 13782 Auto TCP 3559 0 0 5 51905
3 13784 Auto TCP 738 0 1 5 51906
4 13786 Auto TCP 18 0 0 5 51907
5 13788 Auto TCP 1261 0 1 5 51908
6 13804 Auto TCP 1420 0 1 5 51916
7 13806 Auto TCP 21 0 1 5 51917
8 13808 Auto TCP 12 0 1 5 51918
9 13869 Auto TCP 6 0 1 5 51927
10 13871 Auto TCP 12 0 1 5 51928
11 13873 Auto TCP 7 0 1 5 51929
12 13875 Auto TCP 6 0 1 5 51930
13 13877 Auto TCP 7 0 1 5 51931
14 14441 Auto TCP 204 0 1 5 51938

Enter <return> for more, R, P, T, or X (? for help):


Pend. Cur. Max. Port
No Pid Type Protocol Logins Users Users Users Num

0 3723 Login TCP 7804 0 0 5 49030
2 13782 Auto TCP 3559 0 0 5 51905
3 13784 Auto TCP 738 0 1 5 51906
4 13786 Auto TCP 18 0 0 5 51907
5 13788 Auto TCP 1261 0 1 5 51908
6 13804 Auto TCP 1420 0 1 5 51916
7 13806 Auto TCP 21 0 1 5 51917
8 13808 Auto TCP 12 0 1 5 51918
9 13869 Auto TCP 6 0 1 5 51927
10 13871 Auto TCP 12 0 1 5 51928
11 13873 Auto TCP 7 0 1 5 51929
12 13875 Auto TCP 6 0 1 5 51930
13 13877 Auto TCP 7 0 1 5 51931
14 14441 Auto TCP 204 0 1 5 51938

Enter <return> for more, R, P, T, or X (? for help):



Pend. Cur. Max. Port
No Pid Type Protocol Logins Users Users Users Num

16 24142 Auto TCP 513 0 0 5 51948
17 32615 Auto TCP 13 0 1 5 51952
19 2300 Auto TCP 4 0 0 5 51956
20 2302 Auto TCP 3 0 0 5 51957

1 3750 Login TCP 14421 0 0 1 49035
15 6572 Auto TCP 12845 0 0 1 51961
18 18636 Auto TCP 12 0 0 1 51966





So my Question is. If we do not define -Mpb for secondary broker, what is the default value of spawn .I mean -Mpb in this case? .Coz I am just confusing even though -Mpb value not defined its showing new spawn broker.

And How to resolve this issue? Do I need to increase -Ma value of Secondary broker that is now -Ma 1 ( so in order to resolve I will make it -Ma 4) or we need to have -Mpb at least 1 .Like suppose i give -Mpb 1?

Also have you purchased the appropriate Progress licenses to allow those connections.
How to check this?sceen.pngsceen.png
 
In your second post it seems like you just ignored what I said.

Mike said:
If we do not define -Mpb for secondary broker, what is the default value of spawn .I mean -Mpb in this case?
Click to expand...
Why would you insist on not specifying -Mpb for a secondary broker when I just told you that you should always specify it, and that the fact that you didn't specify it is contributing to your problem?

Mike said:
And How to resolve this issue?
Click to expand...
I already outlined detailed steps on how to address this problem. If you don't want to follow my advice then I can't help you further.

Mike said:
Also have you purchased the appropriate Progress licenses to allow those connections.
How to check this?
Click to expand...
This can't be answered with information from your server. It is a business question. It should be posed to whoever manages your Progress licenses.
 
You must log in or register to reply here.
Back
Top