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 of the resource pool, 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 TENANTstatement modifies the number of units in all resource pools under the tenant.Before you execute the
ALTER RESOURCE TENANTstatement to scale in or out a tenant, you must set a load balancing strategy for the tenant. Otherwise, the operation 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 unit number.
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
Parameter |
Description |
|---|---|
| tenant_name | The name of the tenant whose resource pool configuration is to be modified. |
| unit_num | The number of units in all resource pools under the tenant. When you increase UNIT_NUM, its value must be less than or equal to the number of nodes in the target zone. |
| unit_group_id_list | This parameter is applicable only to scale-in scenarios in homogeneous zone mode. When you decrease unit_num, you can use DELETE UNIT_GROUP to delete the specified unit group. If you do not specify DELETE UNIT_GROUP, the system will randomly delete the corresponding number of unit groups. |
Examples
Change the number of units in all resource pools under the tenant
tenant1to 3.obclient [oceanbase]> ALTER RESOURCE TENANT tenant1 UNIT_NUM = 3;In a heterogeneous zone mode, delete the unit group whose
UNIT_GROUP_IDis1006from the tenanttenant2.Run the following commands to enable load balancing.
obclient [oceanbase]> ALTER SYSTEM SET enable_rebalance = true TENANT = 'tenant2';obclient [oceanbase]> ALTER SYSTEM SET enable_transfer = true TENANT = 'tenant2';Obtain the
TENANT_IDof the tenant.obclient [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 setView the units of the tenant and obtain the
UNIT_GROUP_IDof the unit group to be deleted.obclient [oceanbase]> SELECT * 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 | CREATE_TIME | MODIFY_TIME | ZONE | SVR_IP | SVR_PORT | MIGRATE_FROM_SVR_IP | MIGRATE_FROM_SVR_PORT | MANUAL_MIGRATE | UNIT_CONFIG_ID | MAX_CPU | MIN_CPU | MEMORY_SIZE | LOG_DISK_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT | +---------+-----------+--------+------------------+---------------+----------------------------+----------------------------+-------+----------------+----------+---------------------+-----------------------+----------------+----------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+ | 1016 | 1006 | ACTIVE | 1003 | 1006 | 2023-09-25 17:06:38.569419 | 2023-09-25 18:01:30.069547 | zone1 | xxx.xx.xxx.194 | 2882 | NULL | NULL | NULL | 1002 | 1 | 1 | 5368709120 | 16106127360 | 9223372036854775807 | 9223372036854775807 | 1 | | 1017 | 1006 | ACTIVE | 1003 | 1007 | 2023-09-25 17:06:38.571540 | 2023-09-25 17:06:51.030929 | zone1 | xxx.xx.xxx.198 | 2882 | NULL | NULL | NULL | 1002 | 1 | 1 | 5368709120 | 16106127360 | 9223372036854775807 | 9223372036854775807 | 1 | | 1018 | 1006 | ACTIVE | 1003 | 1006 | 2023-09-25 17:06:38.573614 | 2023-09-25 18:01:30.070603 | zone2 | xxx.xx.xxx.192 | 2882 | NULL | NULL | NULL | 1002 | 1 | 1 | 5368709120 | 16106127360 | 9223372036854775807 | 9223372036854775807 | 1 | | 1019 | 1006 | ACTIVE | 1003 | 1007 | 2023-09-25 17:06:38.575723 | 2023-09-25 17:06:51.030929 | zone2 | xxx.xx.xxx.196 | 2882 | NULL | NULL | NULL | 1002 | 1 | 1 | 5368709120 | 16106127360 | 9223372036854775807 | 9223372036854775807 | 1 | | 1020 | 1006 | ACTIVE | 1003 | 1006 | 2023-09-25 17:06:38.579946 | 2023-09-25 18:01:30.070603 | zone3 | xxx.xx.xxx.204 | 2882 | NULL | NULL | NULL | 1002 | 1 | 1 | 5368709120 | 16106127360 | 9223372036854775807 | 9223372036854775807 | 1 | | 1021 | 1006 | ACTIVE | 1003 | 1007 | 2023-09-25 17:06:38.581002 | 2023-09-25 17:06:51.031986 | zone3 | xxx.xx.xxx.197 | 2882 | NULL | NULL | NULL | 1002 | 1 | 1 | 5368709120 | 16106127360 | 9223372036854775807 | 9223372036854775807 | 1 | +---------+-----------+--------+------------------+---------------+----------------------------+----------------------------+-------+----------------+----------+---------------------+-----------------------+----------------+----------------+---------+---------+-------------+---------------+---------------------+---------------------+-------------+ 6 rows in setPerform a scale-down operation based on the obtained
UNIT_GROUP_ID.obclient [oceanbase]> ALTER RESOURCE TENANT tenant2 UNIT_NUM = 1 DELETE UNIT_GROUP = (1006);After the operation is completed, you can view the execution status of the task to reduce the unit number in the
DBA_OB_TENANT_JOBSview.obclient [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