Set the maximum number of connections for a tenant

Since connections consume resources, a large number of long connections can affect the stability of small-scale tenants. OceanBase Database allows you to set the maximum number of connections for a tenant by using parameters or system variables.

The following parameters or variables are related to the maximum number of connections for a tenant:

• max_connections

  The system variable max_connections specifies the maximum number of connections for a tenant. This system variable is supported only in MySQL mode. The default value is 2147483647, and the value range is [1,2147483647]. After you modify this variable, you must restart the OBServer node for the modification to take effect.

• max_user_connections

  The system variable max_user_connections specifies the maximum number of concurrent connections for a single user in a tenant. This system variable is supported only in MySQL mode. The default value is 0, which indicates that the number of connections is not limited. When the value is greater than 0, the actual value is used. The value range is [0,4294967295]. After you modify this variable, you must restart the OBServer node for the modification to take effect.

• _resource_limit_max_session_num

  The tenant-level hidden parameter _resource_limit_max_session_num specifies the maximum number of concurrent connections for a normal user in a user tenant. This parameter takes effect immediately after you modify it, and you do not need to restart the OBServer node. The default value is 0, and the value range is [0,1000000]. 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 actual value is used. When the value of the hidden parameter _resource_limit_max_session_num is 0, the system automatically calculates the maximum number of connections based on certain rules. The calculation rules are as follows:

  Maximum number of concurrent connections for a user tenant = MAX(100, (Tenant memory * 5%) / 100 KB)

  Where:

  • Tenant memory: the memory size of the resource units in the resource pool allocated to the user tenant when the tenant is created, minus the memory size of the corresponding Meta tenant. For more information about the memory allocation of a user tenant and its corresponding Meta tenant, see Memory management within a tenant.

  • 100: the minimum number of connections.

  • 100 KB: the estimated memory occupied by a single session based on experience.

  According to this algorithm, if the memory size of a user tenant is 3 GB and the memory size of the corresponding Meta tenant is 1 GB, the tenant memory is 3 GB - 1 GB = 2 GB. The maximum number of connections for this tenant is MAX(100, 2 GB * 5% / 100 KB) = 1000.

Considerations

In OceanBase Database, a configuration item whose name starts with an underscore (_) is a hidden configuration item. You can use hidden configuration items only for troubleshooting or emergency maintenance.

Limitations

• You can use the system variables max_connections and max_user_connections to modify the maximum number of connections for a tenant or user in a tenant only for a MySQL tenant.

• To prevent the administrator of a sys tenant or user tenant (the default value of the root user in MySQL mode or the SYS user in Oracle mode) from being unable to log in due to a large number of long connections, the value of the _resource_limit_max_session_num parameter is not limited.

View the maximum number of connections for a MySQL tenant

1. Log in to the MySQL tenant of the cluster as the tenant administrator.

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

   • View the maximum number of connections for the tenant.

     obclient> SHOW VARIABLES LIKE 'max_connections';
     

     or

     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
     

   • View the maximum number of concurrent connections for a user in the tenant.

     obclient> SHOW VARIABLES LIKE 'max_user_connections';
     

     or

     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
     

   • View the total maximum number of concurrent connections for all users in the tenant.

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

     Note

     You cannot query hidden configuration items by using the SHOW statement before you modify them (that is, when they are in their default values). You can query only by using the GV$OB_PARAMETERS view. If you have modified a hidden configuration item to a non-default value, 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 value in the VALUE column is the value of the corresponding configuration item.

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

3. Set the maximum number of connections for the tenant.

   • Set the maximum number of connections for the tenant.

     obclient> SET GLOBAL max_connections = 1000;
     

     After the preceding statement is executed, you must restart the OBServer node for the setting to take effect.

   • Set the maximum number of concurrent connections for a user in the tenant.

     obclient> SET GLOBAL max_user_connections = 50;
     

     After the preceding statement is executed, you must restart the OBServer node for the setting to take effect.

   • Set the total maximum number of concurrent connections for all 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 set for the current tenant, a normal user cannot establish a new connection when the number of concurrent connections reaches any of the three values. An administrator cannot establish a new connection when the number of concurrent connections reaches either the max_user_connections or max_connections value.

View the maximum number of concurrent connections for a user in an Oracle tenant

1. Log in to the Oracle tenant of the cluster as the tenant administrator.

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

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

   Note

   You cannot query hidden configuration items by using the SHOW statement before you modify them (that is, when they are in their default values). You can query only by using the GV$OB_PARAMETERS view. If you have modified a hidden configuration item to a non-default value, 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 value in the VALUE column is the value of the corresponding configuration item.

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

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

   obclient> ALTER SYSTEM SET "_resource_limit_max_session_num" = 100;
   

References

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

• Set a parameter
• Set a variable
• View tenant sessions
• Terminate a tenant session