mysql -c -h192.168.50.59 -P2883 -u root@sys -p -Doceanbaseshow parameters like '%syslog%'# 语句级obclie
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;