Set the maximum number of connections for a tenant

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_connections

  The max_connections system 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 is 2147483647. The modification of this variable takes effect after the OBServer node is restarted.

• max_user_connections

  The max_user_connections system 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 is 0, which specifies not to limit the number of connections. When a value greater than 0 is specified, the specified value is used. The modification of this variable takes effect after the OBServer node is restarted.

• _resource_limit_max_session_num

  The tenant-level hidden parameter _resource_limit_max_session_num specifies 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 is 0. Generally, you do not need to modify this parameter.

  When the value of the hidden parameter _resource_limit_max_session_num is greater than 0, the specified value is used. When the value of the _resource_limit_max_session_num hidden parameter is 0, 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. 100 is 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 is MAX(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_connections or max_user_connections system 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_num parameter does not take effect on the sys tenant 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 is root in MySQL mode and SYS in Oracle mode.

View the maximum number of connections for a MySQL tenant

1. Log on to a MySQL tenant of a cluster as the tenant administrator.

2. View the maximum number of connections for the tenant.

   • Use the following syntax to view the maximum number of connections for all users in the tenant:

     obclient> SHOW VARIABLES LIKE 'max_connections';
     

     Alternatively, use the following syntax:

     obclient> 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 set
     

   • Use the following syntax to view the maximum number of concurrent connections for a single user in the tenant:

     obclient> SHOW VARIABLES LIKE 'max_user_connections';
     

     Alternatively, use the following syntax:

     obclient> 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 set
     

   • Execute the following statement to view the maximum number of concurrent connections for all regular users in the tenant:

     obclient> SELECT * FROM oceanbase.GV$OB_PARAMETERS WHERE NAME LIKE '_resource_limit_max_session_num';
     

     Note

     If you have not modified the default setting of the hidden parameter, you can query it only from the GV$OB_PARAMETERS view. If you have modified the setting of the hidden parameter, you can query it by using the SHOW statement.

     The query result is as follows:

     +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+---------------+-----------+
     | SVR_IP         | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                            | DATA_TYPE | VALUE | INFO                                                            | SECTION        | EDIT_LEVEL        | DEFAULT_VALUE | ISDEFAULT |
     +----------------+----------+-------+--------+-----------+---------------------------------+-----------+------- +-----------------------------------------------------------------+----------------+-------------------+---------------+-----------+
     | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |      1004 | _resource_limit_max_session_num | INT       | 0     | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | 0             | YES       |
     +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+---------------+-----------+
     1 row in set
     

     In the query result, the values of the parameter are displayed in the VALUE column.

     For more information about the GV$OB_PARAMETERS view, see GV$OB_PARAMETERS.

3. 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:

     obclient> 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:

     obclient> 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:

     obclient> ALTER SYSTEM SET _resource_limit_max_session_num = 100;
     

     Note

     If the _resource_limit_max_session_num, max_user_connections, and max_connections parameters 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 the max_user_connections or max_connections parameter.

View the maximum number of concurrent connections of an Oracle tenant from the tenant

1. Log on to an Oracle tenant of a cluster as the tenant administrator.

2. Query the maximum number of concurrent connections allowed for a normal user in a view.

   obclient> SELECT * FROM SYS.GV$OB_PARAMETERS WHERE NAME LIKE '_resource_limit_max_session_num';
   

   Note

   If you have not modified the default setting of the hidden parameter, you can query it only from the GV$OB_PARAMETERS view. If you have modified the setting of the hidden parameter, you can query it by using the SHOW statement.

   The query result is as follows:

   +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+---------------+-----------+
   | SVR_IP         | SVR_PORT | ZONE  | SCOPE  | TENANT_ID | NAME                            | DATA_TYPE | VALUE | INFO                                                            | SECTION        | EDIT_LEVEL        | DEFAULT_VALUE | ISDEFAULT |
   +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+---------------+-----------+
   | 172.xx.xxx.xxx |     2882 | zone1 | TENANT |      1004 | _resource_limit_max_session_num | INT       | 0     | the maximum number of sessions that can be created concurrently | RESOURCE_LIMIT | DYNAMIC_EFFECTIVE | 0             | YES       |
   +----------------+----------+-------+--------+-----------+---------------------------------+-----------+-------+-----------------------------------------------------------------+----------------+-------------------+---------------+-----------+
   1 row in set
   

   In the query result, the values of the parameter are displayed in the VALUE column.

   For more information about the GV$OB_PARAMETERS view, see GV$OB_PARAMETERS.

3. Set the maximum number of concurrent connections allowed for a normal user.

   obclient> ALTER SYSTEM SET "_resource_limit_max_session_num" = 100;
   

References

For more information about parameters and variables, see the following topics:

• Set parameters
• Set variables
• View tenant sessions
• Terminate a tenant session