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.

Parameters and variables 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. 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, parameters whose names start with an underscore (_) are hidden parameters and 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.

Procedure

1. Log on to the sys tenant or a user tenant of the database as the administrator.

2. Query the maximum number of connections allowed for the current tenant in a view.

   Sample code:

   obclient [oceanbase]> SELECT * FROM GV$OB_PARAMETERS WHERE NAME LIKE '_resource_limit_max_session_num'\G
   *************************** 1. row ***************************
       SVR_IP: 172.XX.XX.XX
     SVR_PORT: 2882
         ZONE: zone1
        SCOPE: TENANT
    TENANT_ID: 1004
         NAME: _resource_limit_max_session_num
    DATA_TYPE: NULL
        VALUE: 0
         INFO: the maximum number of sessions that can be created concurrently
      SECTION: RESOURCE_LIMIT
   EDIT_LEVEL: DYNAMIC_EFFECTIVE
   1 row in set
   

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

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

   • Oracle tenants

     obclient [SYS]> ALTER SYSTEM SET "_resource_limit_max_session_num" = 100;
     

   • MySQL tenants

     obclient [(none)]> SET GLOBAL max_connections = 1000;
     
     obclient [(none)]> SET GLOBAL max_user_connections = 50;
     
     obclient [(none)]> ALTER SYSTEM SET _resource_limit_max_session_num = 100;
     

More information

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

• Set parameters
• Set variables