ALTER RESOURCE TENANT

Purpose

After a resource pool is assigned to a tenant, you can use the ALTER RESOURCE TENANT statement to modify the UNIT_NUM parameter in the resource pool configuration.

To modify other parameters in the resource pool configuration, use the ALTER RESOURCE POOL statement. For more information about the ALTER RESOURCE POOL statement, see ALTER RESOURCE POOL.

Limitations and considerations

• The ALTER RESOURCE TENANT statement modifies the number of units in all resource pools of the tenant.

• Before you use the ALTER RESOURCE TENANT statement to scale in or out a tenant, you must set a load balancing strategy for the tenant. Otherwise, the scaling in or out may fail. For more information about how to set a load balancing strategy for a tenant, see Scale in or out a tenant by adjusting the number of units.

Privilege requirements

Only the root user of the sys tenant (root@sys) can modify the resource pool configuration of a tenant. Other tenants cannot modify the resource pool configuration of a tenant.

Syntax

ALTER RESOURCE TENANT tenant_name UNIT_NUM [=] unit_num [ DELETE UNIT_GROUP = ( unit_group_id_list ) ];

Parameters

Examples

• Change the number of units in all resource pools of the tenant1 tenant to 3.

  obclient(root@sys)[oceanbase]> ALTER RESOURCE TENANT tenant1 UNIT_NUM = 3;
  

• In the homogeneous zone mode, delete the unit group with the UNIT_GROUP_ID of 1006 from the tenant2 tenant.

  1. Execute the following commands in sequence to enable load balancing.

     obclient(root@sys)[oceanbase]> ALTER SYSTEM SET enable_rebalance = true TENANT = 'tenant2';
     

     obclient(root@sys)[oceanbase]> ALTER SYSTEM SET enable_transfer = true TENANT = 'tenant2';
     

  2. Obtain the TENANT_ID of the tenant.

     obclient(root@sys)[oceanbase]> SELECT TENANT_ID,TENANT_NAME,TENANT_TYPE FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'tenant2';
     

     The query result is as follows:

     +-----------+-------------+-------------+
     | TENANT_ID | TENANT_NAME | TENANT_TYPE |
     +-----------+-------------+-------------+
     |      1006 | tenant2     | USER        |
     +-----------+-------------+-------------+
     1 row in set
     

  3. View the units of the tenant and obtain the UNIT_GROUP_ID of the unit group to be deleted.

     obclient(root@sys)[oceanbase]> SELECT UNIT_ID, TENANT_ID, STATUS, RESOURCE_POOL_ID, UNIT_GROUP_ID, ZONE, SVR_IP, SVR_PORT FROM oceanbase.DBA_OB_UNITS WHERE TENANT_ID = 1006;
     

     The query result is as follows:

     +---------+-----------+--------+------------------+---------------+-------+----------------+----------+
     | UNIT_ID | TENANT_ID | STATUS | RESOURCE_POOL_ID | UNIT_GROUP_ID | ZONE  | SVR_IP         | SVR_PORT |
     +---------+-----------+--------+------------------+---------------+-------+----------------+----------+
     |    1016 |      1006 | ACTIVE |             1003 |          1006 | zone1 | xxx.xx.xxx.194 |     2882 |
     |    1017 |      1006 | ACTIVE |             1003 |          1007 | zone1 | xxx.xx.xxx.198 |     2882 |
     |    1018 |      1006 | ACTIVE |             1003 |          1006 | zone2 | xxx.xx.xxx.192 |     2882 |
     |    1019 |      1006 | ACTIVE |             1003 |          1007 | zone2 | xxx.xx.xxx.196 |     2882 |
     |    1020 |      1006 | ACTIVE |             1003 |          1006 | zone3 | xxx.xx.xxx.204 |     2882 |
     |    1021 |      1006 | ACTIVE |             1003 |          1007 | zone3 | xxx.xx.xxx.197 |     2882 |
     +---------+-----------+--------+------------------+---------------+-------+----------------+----------+
     6 rows in set
     

  4. Execute the scaling in operation based on the obtained UNIT_GROUP_ID.

     obclient(root@sys)[oceanbase]>  ALTER RESOURCE TENANT tenant2 UNIT_NUM = 1 DELETE UNIT_GROUP = (1006);
     

  5. After the operation is executed, you can query the DBA_OB_TENANT_JOBS view to view the execution status of the task to scale in the tenant.

     obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.DBA_OB_TENANT_JOBS WHERE JOB_TYPE='ALTER_RESOURCE_TENANT_UNIT_NUM' AND TENANT_ID=1006;
     

     The query result is as follows:

     +--------+--------------------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------+--------------------+----------------+-------------+
     | JOB_ID | JOB_TYPE                       | JOB_STATUS | RESULT_CODE | PROGRESS | START_TIME                 | MODIFY_TIME                | TENANT_ID | SQL_TEXT                                                              | EXTRA_INFO         | RS_SVR_IP      | RS_SVR_PORT |
     +--------+--------------------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------+--------------------+----------------+-------------+
     |      6 | ALTER_RESOURCE_TENANT_UNIT_NUM | SUCCESS    |           0 |      100 | 2023-09-25 18:01:30.069506 | 2023-09-25 18:01:45.068740 |      1006 | ALTER RESOURCE TENANT tenant2 UNIT_NUM = 2                            | FROM: '1', TO: '2' | xxx.xx.xxx.196 |        2882 |
     |      7 | ALTER_RESOURCE_TENANT_UNIT_NUM | SUCCESS    |           0 |      100 | 2023-09-26 09:37:32.367076 | 2023-09-26 09:39:05.651200 |      1006 | ALTER RESOURCE TENANT tenant2 UNIT_NUM = 1 DELETE UNIT_GROUP = (1006) | FROM: '2', TO: '1' | xxx.xx.xxx.196 |        2882 |
     +--------+--------------------------------+------------+-------------+----------+----------------------------+----------------------------+-----------+-----------------------------------------------------------------------+--------------------+----------------+-------------+
     2 rows in set
     

References

• Scale in or out a tenant by adjusting the number of units

• ALTER TENANT