oceanbase企业版docker oceanbase使用教程

概述

本文主要演示OceanBase集群常用SQL用法。如建租户(实例)、建库建表、分析执行计划、扩容缩容、内部视图分析等等。非常适合初学者入门OceanBase。

如果您是第一次接触OceanBase，请先看前文《OceanBase数据库实践入门——手动搭建OceanBase集群》了解OceanBase集群安装过程。即使没有条件安装OceanBase，也可以通过本文的示例了解OceanBase特征。　

集群资源管理

OceanBase的创新之一在于支持多租户，具体来说就是对集群资源进行二次管理。

每个节点的OBServer进程撷取了主机的大部分资源后，通过集群聚合成一个很大的资源池，这就是集群的最大能力。然后在这个大的资源池里可以分配出小的资源池给某个业务用，这个就是租户(也叫实例)。

分配资源和创建租户

-- 删除测试租户drop tenant if exists tnt_mysql;drop tenant if exists tnt_oracle;-- 删除测试资源池drop resource pool if exists pool_mysql;drop resource pool if exists pool_oracle;-- 删除测试资源单元规格drop resource unit if exists unit_demo;-- 创建测试资源单元规格create resource unit unit_demo max_cpu=10, min_cpu=8, max_memory='10G', min_memory='10G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G'; -- 创建资源池(分配资源)create resource pool pool_mysql unit = 'unit_demo', unit_num = 1;create resource pool pool_oracle unit = 'unit_demo', unit_num = 1;-- 创建测试租户create tenant tnt_mysql resource_pool_list=('pool_mysql'), primary_zone='RANDOM',comment 'mysql tenant/instance' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';create tenant tnt_oracle resource_pool_list=('pool_oracle'), primary_zone='RANDOM',comment 'oracle tenant/instance' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';

备注：

1. 在创建资源池的时候分配资源，unit_num决定了能否使用多台机器的能力。
2. OceanBase 2.1版本支持两种兼容模式的租户类型：一是兼容mysql，一是兼容oracle。
3. 创建租户的时候可以设定primary_zone决定租户内数据(分区)的leader副本默认位置，设定白名单特征限制租户访问的客户端。

查看资源利用率

-- sys租户查看当前资源利用率select zone,concat(svr_ip,':',svr_port) observer, cpu_total, cpu_assigned,cpu_assigned_percent cpu_ass_pct, round(mem_total/1024/1024/1024) mem_total_gb, round(mem_assigned/1024/1024/1024) mem_ass_gb, mem_assigned_percent mem_ass_pct, unit_num, migrating_unit_num,leader_count,round(`load`,2) `load`, round(cpu_weight,2) cpu_weight, round(memory_weight,2) memory_weightfrom __all_virtual_server_statorder by zone,svr_ip,svr_port;

备注：

1. 目前OceanBase只对CPU和Memory资源进行分配，所以重点关注这两项资源的分配比例(百分数，pct字段)。
2. 默认情况下，cpu_total和mem_total资源都取自节点进程启动时从主机撷取的资源然后按 resource_hard_limit/100 展示。

-- 查看当前资源分配情况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_namefrom __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;

备注：

1. 资源池由每个Zone里的资源单元(Unit)组合而成。这里可以看到租户具体的资源单元分布位置。
2. 各个资源单元是彼此隔离的。

业务租户使用

连接业务租户(mysql租户)

使用mysql客户端或者OB客户端obclient连接mysql类型租户，默认密码是空。注意用户名格式是：集群名:租户名:用户名 或者 用户名@租户名#集群名。 应用代码建议用前者格式。

$mysql -h11.***.84.84 -uobdemo:tnt_mysql:root -P2883 oceanbase -A -p
Enter password:
MySQL [oceanbase]> show databases;+--------------------+| Database |+--------------------+| oceanbase || information_schema || mysql || test |+--------------------+4 rows in set (0.10 sec)
$obclient -h11.***.84.84 -uroot@tnt_mysql#obdemo -P2883 oceanbase -A -p
Enter password:
obclient> show databases;+--------------------+| Database |+--------------------+| oceanbase || information_schema || mysql || test |+--------------------+4 rows in set (0.03 sec)
obclient> create user my_user identified by '123456';
Query OK, 0 rows affected (0.01 sec)
obclient> select host,user from mysql.user;+------+---------+| host | user |+------+---------+| % | root || % | my_user |+------+---------+2 rows in set (0.05 sec)
obclient> create database demodb;
Query OK, 1 row affected (0.02 sec)
obclient> grant all privileges on demodb.* to my_user;
Query OK, 0 rows affected (0.01 sec)

连接业务租户(oracle租户)

使用obclient连接oracle类型租户，默认密码也是空。

$obclient -h11.***.84.84 -usys@tnt_oracle#obdemo -P2883 SYS -A -p
Enter password:
obclient> create user ora_user identified by 123456;
Query OK, 0 rows affected (0.02 sec)
obclient> select username, account_status, lock_date, expiry_date, created from dba_users;+----------+----------------+-----------+-------------+---------------------+| USERNAME | ACCOUNT_STATUS | LOCK_DATE | EXPIRY_DATE | CREATED |+----------+----------------+-----------+-------------+---------------------+| SYS | NULL | NULL | NULL | 2019-05-18 11:52:43 |+----------+----------------+-----------+-------------+---------------------+1 row in set (0.08 sec)
obclient> grant all privileges on ora_user.* to ora_user;
Query OK, 0 rows affected (0.04 sec)
obclient> grant select on sys.* to ora_user;
Query OK, 0 rows affected (0.04 sec)

mysql租户建表

$obclient -h11.***.84.84 -umy_user@tnt_mysql#obdemo -P2883 demodb -A -p123456DROP TABLE IF EXISTS `customers`;CREATE TABLE`customers` (`customerNumber` bigint(11) NOT NULL,`customerName` varchar(50) NOT NULL,`contactLastName` varchar(50) NOT NULL,`contactFirstName` varchar(50) NOT NULL,`phone` varchar(50) NOT NULL,`addressLine1` varchar(50) NOT NULL,`addressLine2` varchar(50) DEFAULT NULL,`city` varchar(50) NOT NULL,`state` varchar(50) DEFAULT NULL,`postalCode` varchar(15) DEFAULT NULL,`country` varchar(50) NOT NULL,`salesRepEmployeeNumber` bigint(11) DEFAULT NULL,`creditLimit` decimal(10,2) DEFAULT NULL,PRIMARY KEY (`customerNumber`),KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`) GLOBAL) partition by hash(`customernumber`) partitions 6DROP TABLE IF EXISTS `orders`;CREATE TABLE`orders` (`orderNumber` int(11) NOT NULL,`orderDate` date NOT NULL,`requiredDate` date NOT NULL,`shippedDate` date DEFAULT NULL,`status` varchar(15) NOT NULL,`comments` text DEFAULT NULL,`customerNumber` int(11) NOT NULL,PRIMARY KEY (`orderNumber`, `customerNumber`),KEY `customerNumber` (`customerNumber`) GLOBAL) PARTITION by HASH(customerNumber) PARTITIONS 6;DROP TABLE IF EXISTS `orderdetails`;CREATE TABLE `orderdetails` (`orderNumber` bigint(11) NOT NULL,`productCode` varchar(15) NOT NULL,`quantityOrdered` bigint(11) NOT NULL,`priceEach` decimal(10,2) NOT NULL,`orderLineNumber` smallint(6) NOT NULL,`customerNumber` bigint(11) NOT NULL,PRIMARY KEY (`customerNumber`, `orderNumber`, `productCode` ),KEY `productCode` (`productCode`) GLOBAL) PARTITION by HASH(customerNumber) PARTITIONS 6;
obclient> ^DBye
$obclient -h11.***.84.84 -uroot@tnt_mysql#obdemo -P2883 demodb -A -p
obclient> create tablegroup tg_orders partition by hash partitions 6;
Query OK, 0 rows affected (0.02 sec)
obclient> alter tablegroup tg_orders add customers, orders, orderdetails;
Query OK, 0 rows affected (0.02 sec)
obclient> show tablegroups;+-----------------+--------------+---------------+| Tablegroup_name | Table_name | Database_name |+-----------------+--------------+---------------+| oceanbase | NULL | NULL || tg_orders | customers | demodb || tg_orders | orderdetails | demodb || tg_orders | orders | demodb |+-----------------+--------------+---------------+4 rows in set (0.01 sec)

备注：

1. 业务上有强关联的表如果要拆分，可以选择同一个拆分策略和分片数。然后利用OceanBase的tablegroup机制可以将这些分区表的同号分区(即分区组PartitionGroup)聚合到同一个节点内部。
2. 分区组的分区策略和分片数必须跟分区表的分区策略和分片数一致。
3. 分区表支持全局索引，需要指定 GLOBAL。本地索引类型是LOCAL。

分区负载均衡和租户扩容

select t5.tenant_name, t3.database_name, t4.tablegroup_name, t1.table_name, t2.partition_id, t2.role, t2.zone, concat(t2.svr_ip, ':', t2.svr_port) observer, t2.`row_count`, round(t2.data_size/1024/1024) data_size_mbfrom __all_table t1 join gv$partition t2 on (t1.tenant_id=t2.tenant_id and t1.table_id=t2.table_id)join gv$database t3 on (t1.tenant_id=t3.tenant_id and t1.database_id=t3.database_id)left join __all_tablegroup t4 on (t1.tenant_id=t4.tenant_id and t1.tablegroup_id=t4.tablegroup_id)join __all_tenant t5 on (t1.tenant_id=t5.tenant_id)where t5.tenant_id=1012 and t2.role=1 and t1.table_name in ('customers','orders','orderdetails')order by t5.tenant_name, t3.database_name, t4.tablegroup_name, t2.partition_id, t2.zone, t2.svr_ip, t2.svr_port, t1.table_name,partition_id;

备注：

1. role为1表示leader副本，svr_ip:svr_port是副本所在的位置。此时租户资源池还是1-1-1布局，所以所有分区的leader都在同一个节点内部。
2. 虽然集群拓扑是2-2-2，这个租户只能使用到其中3个节点的能力。
3. 跟tablegroup的连接必须是外连接，因为可能有些表没有关联到任何tablegroup。

下面将租户资源扩容为2-2-2布局。随后我们再看看分区分布。

alter resource pool pool_mysql unit_num=2;

备注：

1. 这三个表的同号分区在同一个节点内部，不同分区在不同的节点上。这是分区负载均衡机制生效了。

备注：

1. 各个节点上的cpu和memory利用率基本均等。
2. 每个节点上都有leader副本，虽然数量不均衡并没有关系，因为均衡策略是综合租户、分区组、分区空间、资源(cpu和memory)利用率的。1136里大部分是sys租户的内部表。
   

备注：

1. 当把一个资源池的unit_num设置为2个时，意味着每个Zone里有2个Unit，这两个资源单元不能在一个节点内部。这样设置后这个mysql租户理论上就可以发挥多个节点的能力了。

oracle租户建表

$obclient -h11.***.84.84 -uora_user@tnt_oracle#obdemo -P2883 ORA_USER -A -p123456DROP TABLE customers;CREATE TABLE customers ( customerNumber number(11) NOT NULL,
 customerName number NOT NULL,
 contactLastName varchar2(50) NOT NULL,
 contactFirstName varchar2(50) NOT NULL,
 phone varchar2(50) NOT NULL,
 addressLine1 varchar2(50) NOT NULL,
 addressLine2 varchar2(50) DEFAULT NULL,
 city varchar2(50) NOT NULL,
 state varchar2(50) DEFAULT NULL,
 postalCode varchar2(15) DEFAULT NULL,
 country varchar2(50) NOT NULL,
 salesRepEmployeeNumber number(11) DEFAULT NULL,
 creditLimit decimal(10,2) DEFAULT NULL,PRIMARY KEY (customerNumber),) partition by hash(customernumber) partitions 6;DROP TABLE orders;CREATE TABLE orders (
 orderNumber number(11) NOT NULL,
 orderDate date NOT NULL,
 requiredDate date NOT NULL,
 shippedDate date DEFAULT NULL,status varchar2(15) NOT NULL,
 comments varchar2(4000),
 customerNumber number(11) NOT NULL,PRIMARY KEY (orderNumber, customerNumber)) partition by hash(customernumber) partitions 6;DROP TABLE orderdetails;CREATE TABLE orderdetails (
 orderNumber number(11) NOT NULL,
 productCode varchar2(15) NOT NULL,
 quantityOrdered number(11) NOT NULL,
 priceEach decimal(10,2) NOT NULL,
 orderLineNumber number(6) NOT NULL,
 customerNumber number(11) NOT NULL,PRIMARY KEY (customerNumber, orderNumber, productCode)) partition by hash(customernumber) partitions 6;create index salesRepEmployeeNumber on customers(salesRepEmployeeNumber) GLOBAL ;create index productCode on orderdetails(productCode) GLOBAL;create index customerNumber on orders(customerNumber) GLOBAL;
$obclient -h11.***.84.84 -usys@tnt_oracle#obdemo -P2883 SYS -A -p
obclient> create tablegroup tg_orders partition by hash partitions 6;
Query OK, 0 rows affected (0.04 sec)
obclient> alter tablegroup tg_orders add ora_user.customers, ora_user.orders, ora_user.orderdetails;
Query OK, 0 rows affected (0.03 sec)

同样也对oracle租户资源进行扩容

alter resource pool pool_oracle unit_num=2;

下面看看负载均衡的效果。

备注：

1. ORACLE里的表默认都是大写的，tablegroup机制将三个表的同号分区约束在同一个节点内部。

备注：

1. 可以观察到leader_count有了变化。

查看执行计划

无论是mysql租户，还是oracle租户，查看执行计划的命令都很简单，就是 explain [sql]...。

obclient> explain select t1.customerNumber, t1.contactFirstName, t1.contactLastName, t2.orderNumber, t2.status, t2.comments, t3.orderNumber, t3.productCode, t3.quantityOrdered  
from customers t1 join orders t2 on (t1.customerNumber=t2.customerNumber) join orderdetails t3 on (t2.orderNumber=t3.orderNumber and t2.customerNumber=t3.customerNumber)  
where t1.customerNumber=363\G

不过同样的sql，在mysql租户里执行计划略有不同

备注：

1. 用了t2和t3的merge join，不是很合理，估计是跟数据量有关。实际执行性能也不差。
2. 可以用HINT 修改执行计划，如下：

内部视图

OceanBase提供了非常丰富的内部视图，方便运维和开发了解内部原理、性能诊断等等。

gv$视图(仿ORACLE)

MySQL [oceanbase]> show tables like 'gv$%';+------------------------------------+| Tables_in_oceanbase (gv$%) |+------------------------------------+| gv$concurrent_limit_sql || gv$database || gv$latch || gv$lock_wait_stat || gv$memory || gv$memstore || gv$memstore_info || gv$obrpc_incoming || gv$obrpc_outgoing || gv$outline || gv$partition || gv$partition_audit || gv$plan_cache_plan_explain || gv$plan_cache_plan_stat || gv$plan_cache_stat || gv$server_memstore || gv$session_event || gv$session_longops || gv$session_wait || gv$session_wait_history || gv$sesstat || gv$sql || gv$sql_audit || gv$sql_monitor || gv$sql_plan_monitor || gv$sql_plan_statistics || gv$sysstat || gv$system_event || gv$table || gv$tenant || gv$tenant_memstore_allocator_info || gv$tenant_sequence_object || gv$unit || gv$unit_load_balance_event_history |+------------------------------------+34 rows in set (0.01 sec)

视图详细定义可以查看官网(oceanbase.alipay.com/docs)。
其中视图gv$sql_audit是OceanBase全量SQL运行日志的。详情参见前文《阿里数据库性能诊断的利器——SQL全量日志》。

OceanBase特有视图(__all_)

MySQL [oceanbase]> show tables like '__all_%';+--------------------------------------+| Tables_in_oceanbase (__all_%) |+--------------------------------------+| __all_acquired_snapshot || __all_build_index_param || __all_charset || __all_clog_history_info |<<<省略>>>| __all_unit || __all_unit_config || __all_unit_load_history || __all_user || __all_user_history || __all_zone |+--------------------------------------+106 rows in set (0.00 sec)

其中视图__all_rootservice_event_history可以查看内部事件日志。详情参见前面文章《OceanBase数据库实践入门——了解总控服务》。

select *from __all_rootservice_event_history horder by h.gmt_create desc limit 100;

备注：

1. 通过这个sql可以观察到资源分配、分区负载均衡(迁移)、leader副本切换的过程等等。

参数和变量

参数(Parameter)变更

OceanBase的参数变更影响的是集群级别的设置，只能在sys租户下变更。操作方式非常类似ORACLE的参数变更。

alter system set migrate_concurrency=20;
alter system set server_data_copy_out_concurrency=20;
alter system set server_data_copy_in_concurrency=20;
show parameters where name in ('migrate_concurrency','data_copy_concurrency','server_data_copy_out_concurrency','server_data_copy_in_concurrency');

变量(variables)变更

OceanBase的变量变更影响的是租户(实例)的设置，只能在具体租户下变更(sys租户也可以)，影响范围是当前租户。这点跟MySQL运维操作非常类似。

set global ob_query_timeout=20000000;
show global variables like '%timeout%';

总结

OceanBase的SQL兼容性既支持MySQL(5.6)又支持ORACLE，在运维操作上部分功能很像ORACLE，部分功能又像MySQL。所以传统ORACLE/MySQL DBA在接触OceanBase过程中会很容易入手。

推荐阅读

• OceanBase数据库实践入门——手动搭建OceanBase集群
• OceanBase数据库实践入门——了解总控服务
• 阿里数据库性能诊断的利器——SQL全量日志
• 阿里数据库性能诊断的利器——SQL执行干预