oceanbase 学习

mysql -c -h192.168.50.59 -P2883 -u root@sys -p -Doceanbase

show parameters like '%syslog%'

# 语句级

obclient> SELECT /*+ trace_log=on */c1 FROM t1 LIMIT 2;

obclient> SHOW TRACE;

# Session 级

obclient> SET ob_enable_trace_log = 'ON';

obclient> SELECT COUNT(*) FROM t1;

obclient> SHOW TRACE;

select * from __all_server \G;

select * from __all_virtual_server_stat;

资源规格

select * from __all_unit_config;

select * from __all_unit;

enable_syslog_recycle=true;

max_syslog_file_count=;

syslog_level=

CREATE RESOURCE UNIT unit1 MAX_CPU 4, MIN_CPU 1, MAX_MEMORY '4G', MIN_MEMORY '1G', MAX_IOPS 128000, MIN_IOPS 128, MAX_DISK_SIZE '20G', MAX_SESSION_NUM 64;

alter resource unit unit1 max_cpu=2, min_cpu=1, max_memory='2G', min_memory='1G';

create resource pool pool1

unit = 'unit1',

unit_num = 1,

zone_list = ('zone1','zone2','zone3');

查看OB集群所有节点信息

select zone,svr_ip,svr_port,inner_port,with_rootserver,status,gmt_create from __all_server order by zone, svr_ip;

查看OceanBase集群可用资源情况。

select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free,

round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb,

round(disk_total/1024/1024/1024) disk_total_gb,

substr(a.build_version,1,6) version,usec_to_time(b.start_service_time) start_service_time

from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port)

order by a.zone, a.svr_ip;

查看创建的资源单元

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;

查看资源池，这里看到新创建的资源池my_pool_test还未归属租户

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;

drop tenant my_ora_tent PURGE;

3.创建租户

create tenant my_test_tent resource_pool_list=('pool1');

select tenant_id, tenant_name, zone_list, locality ,gmt_modified from __all_tenant;

create tenant mysql_tent resource_pool_list=('pool1'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';

ceanBase 2.x 支持oracle租户，创建oracle租户：

create tenant my_ora_tent resource_pool_list=('my_pool_test'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';

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

查看当前集群的租户信息

SELECT * FROM oceanbase.gv$tenant;

查看当前租户

SHOW TENANT;

SHOW VARIABLES LIKE 'ob_tcp_invited_nodes';

-- 查看租户当前会话

SHOW PROCESSLIST;

终止租户会话

KILL session_id;

-- 或者

KILL CONNECTION session_id;

ALTER TENANT tenant_name LOCK;

系统租户设置集群配置项的语法

ALTER SYSTEM SET mysql_port=8888 [PARAM_OPTS]

系统租户设置租户配置项的语法

ALTER SYSTEM SET sql_work_area='1G' [PARAM_OPTS] TENANT=all|TENANT_NAME|seed

租户设置租户配置项的语法

ALTER SYSTEM SET sql_work_area='2G' [PARAM_OPTS]

SELECT CLUSTER_ID, CLUSTER_NAME, CLUSTER_ROLE, CLUSTER_STATUS FROM V$OB_CLUSTER;

SELECT CLUSTER_ID, CLUSTER_NAME, CLUSTER_ROLE, CLUSTER_STATUS FROM V$OB_STANDBY_STATUS;

SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$OB_CLUSTER;