OceanBase 对外技术输出。欢迎关注个人公众号:obpilot
2019年08月
2019年07月
2019年06月
-------------------------
[font=-apple-system, system-ui, "][2019-03-18 16:15:33.921698] ERROR [LIB] start_daemon (utility.cpp:1316) [9635][Y0-0000000000000000] [lt=0] [font=-apple-system, system-ui, "]pid already exists [font=-apple-system, system-ui, "]BACKTRACE:0x2de73a9 0x2d7f9f7 0x53d7d9 0x2debaee 0x51c424 0x7f3080ed8445 0x53abc5
[font=-apple-system, system-ui, "]
[font=-apple-system, system-ui, "]这个错误也可以忽略。
SELECT @@version;
drop table if exists t_parttable;
create table t_parttable(
id bigint not null primary key,
name varchar(50) not NULL,
KEY name_ind(NAME) LOCAL
) DEFAULT CHARSET=utf8mb4 partition by hash(mod(id,1000)) partitions 8;
insert into t_parttable(id, name) values(1,'a'),(2,'A'),(3,'b'),(4,'B'),(5,'c'),(6,'C'),(7,'d'),(8,'D');
set session ob_query_timeout=1000000; select * from t_parttable where name='a';
explain select * from t_parttable where name='a';
select /*+read_consistency(weak)*/ * from t_parttable where name='a';
select * from t_parttable partition(p1);
select * from t_parttable partition(p2);
-------------------------
# 查看分区表的数据
SHOW CREATE TABLE db_bin.t_parttable;
SELECT /*+read_consistency(weak)*/ * FROM db_bin.t_parttable ORDER BY id ;
# 查看租户的资源单元(Unit)分布
SELECT tenant_name, unit_id,zone,svr_ip,max_cpu,round(max_memory/1024/1024/1024) max_mem_gb FROM gv$unit;
# 查看租户的负载均衡历史
SELECT str_to_date(h.gmt_create,'%Y-%m-%d %h:%i:%s') gmt_create, h.zone, t.database_name, t.tablegroup_name, t.table_name, t.part_num,h.partition_id,h.data_size, h.data_src_ip, h.dest_unit_id, h.dest_ip, h.result_code, h.COMMENT , h.rs_svr_ip
FROM gv$unit_load_balance_event_history h JOIN gv$TABLE t ON (h.table_id=t.table_id)
WHERE t.table_name NOT LIKE '%recycle%' AND t.database_name='db_bin'
ORDER BY gmt_create DESC LIMIT 100;
-------------------------
# 查看集群节点资源使用情况
SELECT svr_ip, s.zone, s.cpu_total, s.cpu_assigned, s.cpu_assigned_percent, round(s.mem_total/1024/1024/1024) mem_total_gb, round(s.mem_assigned/1024/1024/1024) mem_ass_gb, s.mem_assigned_percent
FROM __all_virtual_server_stat s
ORDER BY zone,svr_ip;
# 查看资源单元规格定义
SELECT NAME, max_cpu, min_cpu, round(max_memory/1024/1024/1024) max_mem_gb , round(min_memory/1024/1024/1024) min_mem_gb FROM __all_unit_config ORDER BY unit_config_id LIMIT 10;
# 查看已有租户及其资源池信息
SELECT t.tenant_name, p.name pool_name, c.name config_name, p.unit_count, p.zone_list, t.`locality`
FROM __all_resource_pool p JOIN __all_unit_config c ON (p.unit_config_id=c.unit_config_Id)
LEFT JOIN __all_tenant t ON (p.tenant_id=t.tenant_id)
WHERE p.NAME IN ('yq_Pool','app_pool') ORDER BY p.resource_pool_id;
-------------------------
# 清理已经创建的同名租户
DROP tenant IF EXISTS demo_t;
DROP resource pool demo_pool;
# 创建新的资源池(Resource Pool)
CREATE resource pool demo_pool unit='S0_unit_config', unit_num=2;
# 创建租户
CREATE tenant IF NOT EXISTS demo_t resource_pool_list=('demo_pool') SET VARIABLES ob_tcp_invited_nodes='%';
# 查看已有租户及其资源池信息
SELECT now() cur_time, str_to_date(t.gmt_create,'%Y-%m-%d %h:%i:%s') gmt_create, t.tenant_name, p.name pool_name, c.name config_name, p.unit_count, p.zone_list, t.`locality`
FROM __all_resource_pool p JOIN __all_unit_config c ON (p.unit_config_id=c.unit_config_Id)
LEFT JOIN __all_tenant t ON (p.tenant_id=t.tenant_id)
WHERE p.NAME IN ('demo_pool') ORDER BY p.resource_pool_id;
-------------------------
-------------------------
-------------------------
上面OB使用了6台机器。考虑到TiDB是用三台存储节点提供服务。于是我把OB租户缩容到三台机器再重新测试一下。
测试脚本不变。
测试结论也不变。SATA盘性能确实不怎么好。当数据量非常大的时候,TiKV的主机Pagecache命中率和OBServer的Block cache命中率都不高的时候,都会有很多随机磁盘IO,磁盘的性能间接都会影响二者的RT和吞吐。
纯读场景
读写混合
纯写场景
-------------------------
-------------------------
-------------------------
OceanBase在云栖社区上的公众号(https://yq.aliyun.com/teams/356)和论坛版块(https://bbs.aliyun.com/thread/439.html)
欢迎大家在论坛里讨论OceanBase技术问题。
-------------------------
-------------------------
-------------------------
-------------------------
Q: OceanBase里创建索引是立即生效吗?唯一索引呢?
A: 分场景。
-------------------------
## 查看冻结转储参数
SHOW parameters WHERE NAME IN ('enable_major_freeze','minor_freeze_times','freeze_trigger_percentage','major_freeze_duty_time')
AND svr_ip IN ('11.166.175.6');
## 触发冻结合并
ALTER system major freeze;
SELECT sleep(1);
## 查看冻结合并历史
SELECT str_to_date(h.gmt_create,'%Y-%m-%d %h:%i:%s') gmt_create, module, EVENT,name1,value1,name2,value2
FROM __all_rootservice_event_history h
WHERE h.module IN ('daily_merge','major_freeze', 'root_service')
ORDER BY gmt_create DESC LIMIT 50;