This topic describes how to create a business tenant of OceanBase Database by using SQL statements. OceanBase Database allows you to create resource units, resource pools, and business tenants under the sys tenant.
Background
When you create a tenant, if the current resource pool is occupied by another tenant, you need to create a new resource pool. Before you create a resource pool, you must determine the resource unit configurations and range of resources available to the tenant.
Procedure
Step 1: Create a resource unit
A resource unit only defines the specifications but does not allocate resources.
Syntax for creating a resource unit
CREATE RESOURCE UNIT unit_name
MAX_CPU [=] cpu_num,
[MIN_CPU [=] cpu_num,]
MEMORY_SIZE [=] mem_size,
[LOG_DISK_SIZE = disk_size,]
[MAX_IOPS [=] iops_num,MIN_IOPS [=] iops_num,IOPS_WEIGHT [=] weight_num];
Parameters
| Parameter | Description |
|---|---|
| unit_name | The name of the resource unit. |
| MAX_CPU | The maximum number of CPU cores for the resource unit. Minimum value: 1C. |
| MIN_CPU | The minimum number of CPU cores for the resource unit. This parameter is optional. By default, the value of the MIN_CPU parameter equals to that of the MAX_CPU parameter. Minimum value: 1C. |
| MEMORY_SIZE | The memory size for the resource unit. Minimum value: 1G. |
| LOG_DISK_SIZE | The log disk size for the resource unit. This parameter is optional. The default value is three times the specified memory size. Minimum value: 2G. |
| MAX_IOPS | The maximum number of input/output operations per second (IOPS). This parameter is optional. Minimum value: 1024. |
| MIN_IOPS | The minimum number of IOPS. This parameter is optional. Minimum value: 1024. |
| IOPS_WEIGHT | The weight of the specified IOPS. This parameter is optional. |
Note
By default, you do not need to specify the IOPS parameters. The system automatically calculates the parameter values based on the CPU specification. The following calculation rules are used:
- The minimum values of
MAX_IOPSandMIN_IOPSare1024, and the value ofMAX_IOPSmust be greater than or equal to that ofMIN_IOPS. - If both
MIN_IOPSandMAX_IOPSare not specified, the system automatically calculates their values based on the value ofMIN_CPUby using this rule:OneCPU core corresponds to10,000IOPS.MAX_IOPS = MIN_IOPS = MIN_CPU * 10000.- If
IOPS_WEIGHTis not specified,IOPS_WEIGHT = MIN_CPU. - If
IOPS_WEIGHTis specified, the specified value is used.
- If only
MAX_IOPSis specified, the value ofMAX_IOPSis used forMIN_IOPSand vice versa.- If you do not specify
IOPS_WEIGHT, the default value0is used.
- If you do not specify
Examples
Create a resource unit named unit001 and allocate 2 CPU cores and 6 GB of memory to the unit:
obclient> CREATE RESOURCE UNIT unit001
MAX_CPU 2,MEMORY_SIZE '6G';
Query OK, 0 rows affected
or
obclient> CREATE RESOURCE UNIT unit001
MAX_CPU 2,MEMORY_SIZE 6442450944;
Query OK, 0 rows affected
Step 2: Create a resource pool
The resource pool allocates resources based on the resource unit specifications.
Syntax for creating a resource pool
CREATE RESOURCE POOL pool_name
UNIT [=] unit_name,
UNIT_NUM [=] unit_num,
ZONE_LIST [=] ('zone' [, 'zone' ...]);
Parameters
| Parameter | Description |
|---|---|
| pool_name | The name of the resource pool to be created. |
| UNIT [=] unit_name | The name of the resource unit. |
| UNIT_NUM [=] unit_num | The number of resource units to be created in a zone. A server is automatically selected from each zone for each unit based on the current cluster load. However, multiple units of a resource pool cannot be allocated to the same server. That is, the number of units in a resource pool cannot exceed the number of servers in a zone. |
| ZONE_LIST [=] ('zone' [, 'zone' ...]) | The zone to which the resource pool to be created belongs. zone_list lists all zones in a cluster. |
Examples
Create a resource pool named pool001.
obclient> CREATE RESOURCE POOL pool001
UNIT='unit001',UNIT_NUM=1,ZONE_LIST=('zone1','zone2','zone3');
Query OK, 0 rows affected
Step 3: Create a tenant
Allocate the resource pool to the corresponding tenant.
Syntax for creating a tenant
CREATE TENANT [IF NOT EXISTS] tenant_name
[tenant_characteristic_list] [opt_set_sys_var];
tenant_characteristic_list:
tenant_characteristic [, tenant_characteristic...]
tenant_characteristic:
COMMENT 'string'
| {CHARACTER SET | CHARSET} [=] charset_name
| COLLATE [=] collation_name
| PRIMARY_ZONE [=] zone
| DEFAULT TABLEGROUP [=] {NULL | tablegroup}
| RESOURCE_POOL_LIST [=](pool_name [, pool_name...])
| LOCALITY [=] 'locality description'
opt_set_sys_var:
{SET | SET VARIABLES | VARIABLES} system_var_name = expr [,system_var_name = expr] ...
Parameters
| Parameter | Description |
|---|---|
| tenant_name | The name of the tenant. The name cannot exceed 128 characters in length. It must contain only uppercase and lowercase letters, digits, and underscores (_). The name must start with a letter or an underscore (_) and must not contain reserved keywords of OceanBase Database. |
| IF NOT EXISTS | If the tenant name already exists and IF NOT EXISTS is not specified, an error is returned. |
| RESOURCE_POOL_LIST | The resource pool list, which is required when you create a tenant. Only one resource pool is supported. |
| DEFAULT TABLEGROUP | The default table group of the tenant. When you set it to NULL, the system disables the default table group. If this parameter is not specified, the default value is NULL. |
| COMMENT | The comment. |
| CHARACTER SET | CHARSET | The character set of the tenant. |
| COLLATE | The collation. |
| PRIMARY_ZONE | The primary zone. Regarding the priorities of zones where leader replicas are allocated, the zones on two sides of a comma (,) have the same priority, and those on the left side of a semicolon (:) have a higher priority than those on the right side. Example: zone1,zone2;zone3. |
| LOCALITY | The distribution of replicas across zones. For example, F@z1,F@z2,F@z3,R@z4 indicates that z1, z2, and z3 are full-featured replicas, and z4 is a read-only replica. |
| system_var_name | The system variables of the tenant. This parameter is optional.
For more information about the system variables available for tenants in OceanBase Database, see System variables. |
Examples
Create a tenant in MySQL mode.
obclient> CREATE TENANT IF NOT EXISTS mysql001 CHARSET='utf8mb4', PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool001') SET ob_tcp_invited_nodes='%'; Query OK, 0 rows affectedCreate a tenant in Oracle mode.
obclient> CREATE TENANT IF NOT EXISTS oracle002 CHARSET='utf8mb4', PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool002') SET ob_tcp_invited_nodes='%',ob_compatibility_mode='oracle'; Query OK, 0 rows affected
What to do next
Notice
For a tenant created by using an SQL statement, the password of the tenant administrator is empty. Set a password before you use the tenant administrator.
Example:
Set the password of the root user under the mysql001 tenant to ******.
$ obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p -A
Enter password:
obclient> SET PASSWORD = PASSWORD('******');
Query OK, 0 rows affected
More information
- For more information about how to verify connectivity after you create the tenant, see Verify connectivity after tenant creation.
- For more information about how to create a resource unit, see CREATE RESOURCE UNIT.
- For more information about how to create a resource pool, see CREATE RESOURCE POOL.
- For more information about how to create a tenant, see CREATE TENANT.
- For more information about how to change the password of a user, see ALTER USER and SET PASSWORD.