Connections consume resources and a large number of persistent connections affect the stability of a tenant with limited resources. OceanBase Database allows you to set the maximum number of connections allowed for a tenant by using parameters or system variables.
The following parameters and variables are related to the maximum number of connections allowed for a tenant:
max_connectionsThe
max_connectionssystem variable specifies the maximum number of connections allowed for the entire tenant. This variable is supported only in MySQL mode. The value range is [1,2147483647]. The default value is2147483647. The modification of this variable takes effect after the OBServer node is restarted.max_user_connectionsThe
max_user_connectionssystem variable specifies the maximum number of concurrent connections allowed for a single user in a tenant. This variable is supported only in MySQL mode. The value range is [0,4294967295]. The default value is0, which specifies not to limit the number of connections. When a value greater than0is specified, the specified value is used. The modification of this variable takes effect after the OBServer node is restarted._resource_limit_max_session_numThe tenant-level hidden parameter
_resource_limit_max_session_numspecifies the maximum number of concurrent connections allowed for a regular user in a user tenant. The modification of this parameter takes effect immediately. The value range is [0,1000000]. The default value is0. Generally, you do not need to modify this parameter.When the value of the hidden parameter
_resource_limit_max_session_numis greater than0, the specified value is used. When the value of the_resource_limit_max_session_numhidden parameter is0, the system will calculate the maximum number of connections allowed based on the following formula:Maximum number of concurrent connections allowed for a user tenant = MAX(100, Memory for the tenant × 5%/(100 KB))
The size of memory for a user tenant depends on the memory in the resource units of the resource pool allocated to the tenant when it was created. 100 KB is the size of memory that may be occupied by a single session and is calculated based on experience.
100is the smallest number of connections allowed. If the size of memory for a tenant is 2 GB, the maximum number of connections for the tenant isMAX(100, 2 GB × 5%/(100 KB)) = 1000.
Considerations
In OceanBase Database, the name of a hidden parameter begins with an underscore (_). Hidden parameters are used by developers only for troubleshooting or emergency O&M.
Limitations
You can use the
max_connectionsormax_user_connectionssystem variable to adjust the maximum number of connections only for a MySQL tenant or a single user in a MySQL tenant.The
_resource_limit_max_session_numparameter does not take effect on thesystenant and the administrator user of a user tenant. This is to avoid logon failures of the administrators due to a large number of persistent connections. The default administrator user isrootin MySQL mode andSYSin Oracle mode.
View the maximum number of connections for a MySQL tenant
Log on to a MySQL tenant of a cluster as the tenant administrator.
View the maximum number of connections for the tenant.
Execute the following statement to view the maximum number of connections for all users in the tenant:
SHOW VARIABLES LIKE 'max_connections';or
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'max_connections';The query result is as follows:
+-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_connections | 2147483647 | +-----------------+------------+ 1 row in setExecute the following statement to view the maximum number of concurrent connections for a single user in the tenant:
SHOW VARIABLES LIKE 'max_user_connections';or
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME = 'max_user_connections';The query result is as follows:
+----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | max_user_connections | 0 | +----------------------+-------+ 1 row in setExecute the following statement to view the maximum number of concurrent connections for all regular users in the tenant:
SELECT * FROM oceanbase.GV$OB_PARAMETERS WHERE NAME LIKE '_resource_limit_max_session_num';Note
Hidden parameters can be queried by using only the
GV$OB_PARAMETERSview but not theSHOWstatement.The query result is as follows:
+----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+ | SVR_IP | SVR_PORT | ZONE | SCOPE | TENANT_ID | NAME | DATA_TYPE | VALUE | INFO | SECTION | EDIT_LEVEL | +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+ | xx.xx.xx.196 | 2882 | zone1 | TENANT | 1006 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.194 | 2882 | zone2 | TENANT | 1006 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.197 | 2882 | zone2 | TENANT | 1006 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.64 | 2882 | zone3 | TENANT | 1006 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.192 | 2882 | zone3 | TENANT | 1006 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.198 | 2882 | zone1 | TENANT | 1006 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+ 6 rows in setIn the query result, the values of the parameter are displayed in the
VALUEcolumn.For more information about the
GV$OB_PARAMETERSview, see GV$OB_PARAMETERS.
Set the maximum number of connections for the tenant.
Execute the following statement to set the maximum number of connections for all users in the tenant:
SET GLOBAL max_connections = 1000;After the statement is executed, you need to restart the OBServer node for the setting to take effect.
Execute the following statement to set the maximum number of concurrent connections for a single user in the tenant:
SET GLOBAL max_user_connections = 50;After the statement is executed, you need to restart the OBServer node for the setting to take effect.
Execute the following statement to set the maximum number of concurrent connections for all regular users in the tenant:
ALTER SYSTEM SET _resource_limit_max_session_num = 100;Note
If the
_resource_limit_max_session_num,max_user_connections, andmax_connectionsparameters are all specified for the current tenant, for a normal user, no more connections can be established when the number of concurrent connections reaches any of these parameter values. For the administrator, no more connections can be established when the number of concurrent connections reaches the value of themax_user_connectionsormax_connectionsparameter.
View the maximum number of concurrent connections of an Oracle tenant from the tenant
Log on to an Oracle tenant of a cluster as the tenant administrator.
Query the maximum number of concurrent connections allowed for a normal user in a view.
SELECT * FROM SYS.GV$OB_PARAMETERS WHERE NAME LIKE '_resource_limit_max_session_num';Note
Hidden parameters can be queried by using only the
GV$OB_PARAMETERSview but not theSHOWstatement.The query result is as follows:
+----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+ | SVR_IP | SVR_PORT | ZONE | SCOPE | TENANT_ID | NAME | DATA_TYPE | VALUE | INFO | SECTION | EDIT_LEVEL | +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+ | xx.xx.xx.197 | 2882 | zone2 | TENANT | 1004 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.194 | 2882 | zone2 | TENANT | 1004 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.196 | 2882 | zone1 | TENANT | 1004 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.64 | 2882 | zone3 | TENANT | 1004 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.192 | 2882 | zone3 | TENANT | 1004 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | | xx.xx.xx.198 | 2882 | zone1 | TENANT | 1004 | _resource_limit_max_session_num | NULL | 0 | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+ 6 rows in setIn the query result, the values of the parameter are displayed in the
VALUEcolumn.For more information about the
GV$OB_PARAMETERSview, see GV$OB_PARAMETERS.Set the maximum number of concurrent connections allowed for a normal user.
ALTER SYSTEM SET "_resource_limit_max_session_num" = 100;
References
For more information about parameters and variables, see the following topics: