OceanBase之oracle租户的使用体验

最新推荐文章于 2025-03-23 20:08:33 发布

原创

最新推荐文章于 2025-03-23 20:08:33 发布 · 4.5k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#dba

oceanbase2.2集群——oracle租户的使用体验

1、创建oracle兼容类型的租户

注意：
（1）、创建资源池，资源池最小规格要求是1c5g，如果不满足最小资源要求，就会报错。详见上一篇mysql租户的体验文章。
MySQL [oceanbase]> create resource pool mysql_pool_test unit = ‘my_unit_1c2g’, unit_num = 1;
ERROR 1235 (0A000): unit min memory less than __min_full_resource_pool_memory not supported
MySQL [oceanbase]>

（2）、另外，还要注意，每个资源池，指定的unit_num数量，不能超过每个zone下的observer服务器个数，否则也会报错。
可以创建不同的资源池，每个不同的资源池对应的unit单元规格可以相同。详见上一篇mysql租户的体验文章。
MySQL [oceanbase]> create resource pool mysql_pool_test unit = ‘my_unit_1c2g’, unit_num =2;
ERROR 4656 (HY000): resource pool unit num is bigger than zone server count
MySQL [oceanbase]>

（3）、在上一篇mysql租户的体验文章里也说了，同一个资源池resource pool 只能属于一个租户。也就是说，一个资源池已经分配给某个租户了，不能再分配给其它租户。不然会报以下错误：
MySQL [oceanbase]> create tenant ora_test_tent resource_pool_list=(‘mysql_pool_test’), primary_zone=‘RANDOM’,comment ‘oracle tenant/instance’, charset=‘utf8’ set ob_tcp_invited_nodes=’%’, ob_compatibility_mode=‘oracle’;
ERROR 4626 (HY000): resource pool ‘mysql_pool_test’ has already been granted to a tenant
MySQL [oceanbase]>

开始创建资源单元和资源池：
$ mysql -h172.16.18.81 -uroot@sys#obdemo -P2883 -padmin123 -c -A oceanbase
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MySQL [oceanbase]>
MySQL [oceanbase]> select unit_config_id,name,max_cpu,min_cpu,round(max_memory/1024/1024/1024) max_mem_gb, round(min_memory/1024/1024/1024) min_mem_gb, round(max_disk_size/1024/1024/1024) max_disk_size_gb
-> from __all_unit_config
-> order by unit_config_id;
±---------------±----------------±--------±--------±-----------±-----------±-----------------+
| unit_config_id | name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | max_disk_size_gb |
±---------------±----------------±--------±--------±-----------±-----------±-----------------+
| 1 | sys_unit_config | 5 | 2.5 | 13 | 11 | 40 |
| 1002 | my_unit_1c5g | 1 | 1 | 5 | 5 | 10 |
±---------------±----------------±--------±--------±-----------±-----------±-----------------+
2 rows in set (0.00 sec)

MySQL [oceanbase]>

这里基于已有的资源单元unit，创建一个新的资源池，供oracle租户使用：
MySQL [oceanbase]> create resource pool ora_pool_test unit = ‘my_unit_1c5g’, unit_num = 1;
Query OK, 0 rows affected (0.08 sec)

MySQL [oceanbase]> select t1.name resource_pool_name, t2.name unit_config_name, t2.max_cpu, t2.min_cpu,
-> round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb,
-> t3.unit_id, t3.zone, concat(t3.svr_ip,’:’,t3.svr_port) observer,t4.tenant_id, t4.tenant_name
-> from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
-> join __all_unit t3 on (t1.resource_pool_id = t3.resource_pool_id)
-> left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
-> order by t1.resource_pool_id, t2.unit_config_id, t3.unit_id
-> ;
±-------------------±-----------------±--------±--------±-----------±-----------±--------±------±------------------±----------±----------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name |
±-------------------±-----------------±--------±--------±-----------±-----------±--------±------±------------------±----------±----------------+
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 1 | zone1 | 172.16.18.81:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 2 | zone2 | 172.16.18.82:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 3 | zone3 | 172.16.18.91:2882 | 1 | sys |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1001 | zone1 | 172.16.18.81:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1002 | zone2 | 172.16.18.82:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1003 | zone3 | 172.16.18.91:2882 | 1001 | mysql_test_tent |
| ora_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1004 | zone1 | 172.16.18.81:2882 | NULL | NULL |
| ora_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1005 | zone2 | 172.16.18.82:2882 | NULL | NULL |
| ora_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1006 | zone3 | 172.16.18.91:2882 | NULL | NULL |
±-------------------±-----------------±--------±--------±-----------±-----------±--------±------±------------------±----------±----------------+
9 rows in set (0.00 sec)

MySQL [oceanbase]>

创建oracle类型的租户
创建租户的需指定租户使用的资源池、数据分布策略(primary_zone为RANDOM)、租户字符集(默认utf8，也可以改为gbk)、租户访问白名单(ob_tcp_invited_nodes)、租户兼容级别(ob_compatibility_mode)。

MySQL [oceanbase]> create tenant ora_test_tent resource_pool_list=(‘ora_pool_test’), primary_zone=‘RANDOM’,comment ‘oracle tenant/instance’, charset=‘utf8’ set ob_tcp_invited_nodes=’%’, ob_compatibility_mode=‘oracle’;
Query OK, 0 rows affected (4.06 sec)

MySQL [oceanbase]>

查看OB集群所有租户信息: 租户类别compatibility_mode，0为mysql兼容模式的租户， 1为oracle兼容模式的租户
MySQL [oceanbase]> select tenant_id, tenant_name, compatibility_mode,zone_list, locality ,gmt_modified from __all_tenant;
±----------±----------------±-------------------±------------------±--------------------------------------------±---------------------------+
| tenant_id | tenant_name | compatibility_mode | zone_list | locality | gmt_modified |
±----------±----------------±-------------------±------------------±--------------------------------------------±---------------------------+
| 1 | sys | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL