Error Sql users getting error :-Exceeding permissible number of connections and No SQL servers are available

Mike

Mike

Moderator
Hi Tom and team,

Progress version:- 11.7
OS :- Linux


Sql users getting error :-Exceeding permissible number of connections and No SQL servers are available

2024/08/26@14:27:25.990-0500] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.
[2024/08/26@14:28:04.623-0500] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.
[2024/08/26@14:33:46.114-0500] P-3750 T-140117189777216 I BROKER 1: (8839) No SQL servers are available. Try again later.

We checked in Promon 17 option Server by broker:-


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

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

08/27/24 Status: Servers By Broker
00:43:07

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

16 24142 Auto TCP 491 0 1 5 51948
17 32615 Auto TCP 10 0 1 5 51952
19 2300 Auto TCP 1 0 1 5 51956
20 2302 Auto TCP 1 0 1 5 51957

1 3750 Login TCP 1470 0 0 1 49035
15 6572 Auto TCP 28 0 0 1 51961





Than we check in option 20 Broker startup and found below for 4gl and sql (secondary broker) connection parameters


broker startup parameter

Broker: 0 Pid: 3723 Logins: 832 Pend: 0 Connected: 0
TCP/IP Version (-ipver): IPV4
Maximum Port for Auto Servers (-maxport): 51999
Minimum Port for Auto Servers (-minport): 51900
Maximum Number of Clients Per Server (-Ma): 5
Minimum Clients Per Server (-Mi): 1
Message Buffer Size (-Mm): 1024
Servers per Protocol (-Mp): 0
Maximum Servers Per Broker (-Mpb): 20
Network Type (-N): TCP
Pending client connection timeout (-PendConnTimeout): 15
Service Name (-S): 49030
Broker server group support (-ServerType): ABL
SQL Server Max Open Cursors (-SQLCursors): 50
Number of seconds for a SQL client to wait for a record lock(-SQLLockWaitTimeout): 5.
SQL Server Stack Size (-SQLStack): 1000
SQL Server Statement Cache Size (-SQLStmtCache): 100
Size [1K byte units] of SQL Server temp table buffer (-SQLTempStoreBuff): 1000
Size [1K byte units] of SQL Server temp table disk storage (-SQLTempStoreDisk): 500000






Broker: 1 Pid: 3750 Logins: 1470 Pend: 0 Connected: 0
TCP/IP Version (-ipver): IPV4
Maximum Port for Auto Servers (-maxport): 51999
Minimum Port for Auto Servers (-minport): 51900
Maximum Number of Clients Per Server (-Ma): 1
Minimum Clients Per Server (-Mi): 1
Message Buffer Size (-Mm): 1024
Servers per Protocol (-Mp): 0
Maximum Servers Per Broker (-Mpb): 0
Network Type (-N): TCP
Pending client connection timeout (-PendConnTimeout): 0
Service Name (-S): 49035
Broker server group support (-ServerType): SQL
SQL Server Max Open Cursors (-SQLCursors): 50
Number of seconds for a SQL client to wait for a record lock(-SQLLockWaitTimeout): 5.
SQL Server Stack Size (-SQLStack): 1000
SQL Server Statement Cache Size (-SQLStmtCache): 100
Size [1K byte units] of SQL Server temp table buffer (-SQLTempStoreBuff): 1000
Size [1K byte units] of SQL Server temp table disk storage (-SQLTempStoreDisk): 500000


Secondary broker only having -Ma 1 and -Mi 1
Can you please suggest what can we do to resolve issue for permanent fix up?

And Can we disconnect user in live scenario? And what are these auto Logins?

Thanks regards
Mike
 
I don't know why there are two identical threads on this subject. The other one should be closed.

Mike said:
Maximum Servers Per Broker (-Mpb): 0
Click to expand...
I don't know how you managed to start this broker. Zero is not a valid value for -Mpb. It would be better for you to show the startup parameter values of your database brokers from wherever they are actually configured (e.g. shell script, .pf file, conmgr.properties; whatever you use). The above is promon's interpretation of the current configuration, and I don't trust it.

Mike said:
Can we disconnect user in live scenario?
Click to expand...
If you want to know whether it is technically possible to disconnect a remote user, yes, of course it is. Whether you should do that, or what the business impact might be, is a question for someone you work with. In my opinion, if you find yourself wanting to disconnect users so that other users can connect, then likely the environment is licensed inadequately, or the database is configured improperly, or maybe both. It is also possible that some user is misbehaving, e.g. opening multiple application clients simultaneously when they should only open one. Though in this case the SQL broker configuration looks suspect.

Mike said:
And what are these auto Logins?
Click to expand...
That screen is promon R&D, 1, 17, Servers by Broker. The "Auto" type records are servers. The "Login" type records are brokers.

Mike said:
Secondary broker only having -Ma 1 and -Mi 1
Can you please suggest what can we do to resolve issue for permanent fix up?
Click to expand...
No. The client/server configuration of your brokers should be within the constraints of your license counts, and should be determined by your business needs, i.e. how many clients of each type (4GL versus SQL) need to connect to your database concurrently? I can't answer that. It is a business question, not a technical question. The technical portion is creating a reasonable broker configuration once the business requirements are determined. That must happen within your organization.

Note that the -Mi parameter does not impact how many clients can connect to the servers of a broker. It impacts the algorithm used to decide when to spawn servers and how to distribute client connections across servers.

The -Ma parameter determines the maximum number of concurrent client connections per server, for a given broker. A value of -Ma 1 means that each server will only allow one client connection. Again, it is up to you to determine how many SQL connections you require. You don't need a high value for -Mpb on a SQL broker, as the SQL server is multi-threaded. There isn't a benefit to having many of them. This is different from the 4GL broker in 11.x and prior, where the 4GL server is single-threaded. The product -Mpb * -Ma gives the total number of client connections the broker will allow, assuming the maximum number of servers can be spawned successfully.
 
Last edited:
Rob Fitzpatrick said:
I don't know how you managed to start this broker. Zero is not a valid value for -Mpb. It would be better for you to show the startup parameter values of your database brokers from wherever they are actually configured (e.g. shell script, .pf file, conmgr.properties; whatever you use). The above is promon's interpretation of the current configuration, and I don't trust it.


If you want to know whether it is technically possible to disconnect a remote user, yes, of course it is. Whether you should do that, or what the business impact might be, is a question for someone you work with. In my opinion, if you find yourself wanting to disconnect users so that other users can connect, then likely the environment is licensed inadequately, or the database is configured improperly, or maybe both. It is also possible that some user is misbehaving, e.g. opening multiple application clients simultaneously when they should only open one. Though in this case the SQL broker configuration looks suspect.


That screen is promon R&D, 1, 17, Servers by Broker. The "Auto" type records are servers. The "Login" type records are brokers.


No. The client/server configuration of your brokers should be within the constraints of your license counts, and should be determined by your business needs, i.e. how many clients of each type (4GL versus SQL) need to connect to your database concurrently? I can't answer that. It is a business question, not a technical question. The technical portion is creating a reasonable broker configuration once the business requirements are determined. That must happen within your organization.

Note that the -Mi parameter does not impact how many clients can connect to the servers of a broker. It impacts the algorithm used to decide when to spawn servers and how to distribute client connections across servers.

The -Ma parameter determines the maximum number of concurrent client connections per server, for a given broker. A value of -Mpb 1 means that each server will only allow one client connection. Again, it is up to you to determine how many SQL connections you require. You don't need a high value for -Mpb on a SQL broker, as the SQL server is multi-threaded. There isn't a benefit to having many of them. This is different from the 4GL broker in 11.x and prior, where the 4GL server is single-threaded. The product -Mpb * -Ma gives the total number of client connections the broker will allow, assuming the maximum number of servers can be spawned successfully.
Click to expand...
Hi Job,

Thanks for your reply. Really Appreciated. I think issue is happening coz no -Mbp is supplied.

So below is the Secondary broker configuration

more broker-sql-mfg.pf (main script)
-pf /apps/scri/com/broker-sql-standard.pf

-S mfgdb-sql ( I guess if i add these line below it will work fine
-Ma 4
-Mi 1
-Mpb 10)( -Mn value is already 20 in database startup)


more /apps/scri/com/broker-sql-standard.pf
-m3
-Ma 1
-maxport 41999
-Mi 1
-minport 41900
-N TCP
-ServerType SQL


Can you suggest? If real scenario, how can we disconnect the user to make room of sql connection? Can we terminate the server with administrative option in Promon? What is the process to disconnect connection?


Thanks Mike
 
Mike said:
more broker-sql-mfg.pf (main script)
-pf /apps/scri/com/broker-sql-standard.pf

-S mfgdb-sql ( I guess if i add these line below it will work fine
-Ma 4
-Mi 1
-Mpb 10)( -Mn value is already 20 in database startup)
Click to expand...

Based on what you have already provided, that won't work.

The first broker you start is your primary broker; in your case, it is also your 4GL login broker. The -Mn parameter on the primary broker determines the total number of servers and secondary brokers that may be started database-wide. The -Mpb parameter is the total number of servers that can be started by a single broker. So the database should have one -Mn value; and it should have one -Mpb per login broker. Every login broker you start after the primary broker is considered a secondary broker.

For example, if you want to have the following:
  • Primary broker which is also a 4GL login broker, maximum 20 4GL servers;
  • One secondary SQL broker, maximum 20 SQL servers;
then you need -Mn to be a minimum of 41 (20 4GL + 20 SQL + 1 secondary broker).

Each login broker should have its own -minport/-maxport range. My rules for port ranges:
  • Do not re-use or overlap port ranges.
    • On a given machine, every login broker of every database should have its own unique port range.
  • Size the ranges appropriately.
    • The number of ports, from -minport to -maxport inclusive, must be at least -Mpb, as the broker needs to assign one port to each server it spawns.
    • I prefer to set the number of ports equal to -Mpb. I leave space between port ranges in case I need to increase them later.
    • Making the port range much larger than -Mpb is wasteful and can cause problems with security people, as you may have to justify opening large numbers of ports on a firewall when you are not actually using them.
  • Avoid ports that are already in use.
    • Do not use ports in the operating system's ephemeral range, as they may not be available when the broker needs them.
      https://en.wikipedia.org/wiki/Ephemeral_port
      Often these are 32768 and above; check your OS documentation/configuration to be sure.
    • Check the operating system's services file (e.g. /etc/services or %WINDIR%\system32\drivers\etc\services) before selecting a port range, to ensure it is not already in use.
    • Keep your services file in port number order.
    • Every login broker static port (-S) should already have a services file entry. The ports in the min/max range do not require entries as they are not named. But when I choose a range, I like to add a comment line at the appropriate place in the file with the range of ports, database, and broker. E.g.:
      mydb-4gl 20000/tcp # mydb, 4GL broker
      mydb-sql 20200/tcp # mydb, SQL broker
      # 20001 - 20020 # mydb, 4GL min/max
      # 20201 - 20221 # mydb, SQL min/max
 
Rob Fitzpatrick said:
I originally wrote:

This should have read: A value of -Ma 1 means that each server will only allow one client connection.

Sorry for the error. I have corrected it above.
Click to expand...
Hi Rob,

I have a question. We have Mn=20 value (its including 4GL+ SQL connection) and Ma = 1 for SQL broker and -Mpb =0 but why I can see below 2 server started by broker?
What is the default value of -Mpb .why below to connections broker started even the value is -Mpb=0 for secondary SQL broker?You mention earlier that -Mpb secondary broker is multi-threading So Does it mean If i did not supply the value of -Mpb it will spawn one broker? In my case I have given -Mpb = 0 even though 1 server per broker showing.

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

16 24142 Auto TCP 492 0 1 5 51948
17 32615 Auto TCP 11 0 0 5 51952
19 2300 Auto TCP 3 0 1 5 51956
20 2302 Auto TCP 2 0 1 5 51957

1 3750 Login TCP 5924 0 0 1 49035
15 6572 Auto TCP 4466 0 0 1 51961
18 28074 Auto TCP 16 0 0 1 51962
 
You must log in or register to reply here.
Back
Top