作者简介：李寅，软件测试工程师

1. 实验环境

1) Centos7.3；64G内存；16CPU; IP 10.137.32.197；

2. 参数设置

2.1 OS参数设置

sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ff>$x/rps_cpus; done'
sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"

2.2 SYS参数设置

mysql -uroot@sys#obdemo -pscadacom -h127.1 -P2883 -c -A oceanbase
alter system set enable_auto_leader_switch=false;
alter system set enable_one_phase_commit=false;
alter system set enable_monotonic_weak_read = true;
alter system set weak_read_version_refresh_interval='5s';
alter system _ob_minor_merge_schedule_interval='5s';
alter system set memory_limit_percentage = 90;  
alter system set memstore_limit_percentage = 55;
alter system set freeze_trigger_percentage = 70;
alter system set minor_freeze_times = 50;      
alter system set minor_warm_up_duration_time = 0;
alter system set merge_thread_count = 32;
alter system set minor_merge_concurrency = 8;
alter system set _mini_merge_concurrency = 4;

2.3 PROXY 参数

alter proxyconfig set proxy_mem_limited='4G'; --防止 oom，可根据实际环境动态调整
alter proxyconfig set enable_compression_protocol=false; --关闭压缩，降低 CPU 百分率
alter proxyconfig set work_thread_num=32; -- 调整工作线程数，寻找最优性能
alter proxyconfig set enable_compression_protocol=false;
alter proxyconfig set enable_metadb_used=false;
alter proxyconfig set enable_standby=false;
alter proxyconfig set enable_strict_stat_time=false;
alter proxyconfig set use_local_dbconfig=true;

2.4 租户设置

1. 查看当前集群资源使用情况：

select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free, mem_total/1024/1024/1024 mem_total_gb, mem_assigned/1024/1024/1024 mem_assign_gb, (mem_total-mem_assigned)/1024/1024/1024 mem_free_gb
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 t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, t2.max_memory/1024/1024/1024 max_mem_gb, 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
;

2. 根据集群剩余资源情况创建租户

create resource unit ly_test max_cpu=5 , max_memory='2g', max_iops=1280, max_disk_size='20g', max_session_num=1024, min_cpu=5, min_memory='2g', min_iops=512;
create resource pool ly_test_pool unit='ly_test', unit_num=1, zone_list=('zone1');
create tenant ly_tenant resource_pool_list=('ly_test_pool');
ALTER TENANT ly_tenant SET VARIABLES ob_tcp_invited_nodes='%';

mysql -uroot@ly_tenant -h127.1 -P2883 -c -A oceanbase
alter user root identified by 'scadacom';

3. 超时参数设置

set global ob_timestamp_service='GTS' ;
set global autocommit=ON;
set global ob_query_timeout=36000000000;
set global ob_trx_timeout=36000000000;
set global max_allowed_packet=67108864;
set global ob_sql_work_area_percentage=100;
set global parallel_max_servers=800;
set global parallel_servers_target=800;

4. 创建数据库

create database tpcc;
create user tpcc identified by 'scadacom';
grant all privileges on ly_tpcc.* to tpcc ;

3. BenchmarkSQL配置

2.1 下载BenchmarkSQL

从https://github.com/obpilot/benchmarksql-5.0下载benchmarksql

2.2 解压BenchmarkSQL

unzip benchmarksql-5.0-master.zip
cp -r benchmarksql-5.0-master /root/benchmarksql

2.3 编辑 /root/benchmarksql/run/props.ob如下所示：

2.4 运行建表语句

cd /root/benchmarksql/run
sh runSQL.sh props.ob sql.common/tableCreates.sql

这里出现报错，提示文件找不到，编辑runSQL.sh文件修改如下内容：

source /root/benchmarksql/run/funcs.sh $1

重新执行即可

2.5 装载数据

sh runLoader.sh props.ob

报错，修改runLoader.sh文件如下内容：

source /root/benchmarksql/run/funcs.sh $1

再次执行，成功

2.6 创建索引

create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

4. 执行性能测试

修改runBenchmark.sh文件如下：

sh runBenchmark.sh props.ob

pmC 8.04 ， 67秒一共运行了13个事务

5. TPC-C TOP SQL分析

5.1 查询TOP10 sql

mysql -uroot@ly_tenant#obdemo -pscadacom -h127.1 -P2883 -c -A oceanbase

SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time ,s.svr_ip,s.svr_port,s.tenant_id,s.plan_id
       FROM gv$sql_audit s
       WHERE 1=1
        and user_name='tpcc'
        and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 6000 MINUTE) )
       GROUP BY sql_id
       order by avg_elapsed_time desc limit 10;

5.1 对elapsed时间最长的前三条sql进行分析

SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time ,s.svr_ip,s.svr_port,s.tenant_id,s.plan_id
       FROM gv$sql_audit s
       WHERE 1=1
        and user_name='tpcc'
        and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 6000 MINUTE) )
       GROUP BY sql_id
       order by avg_elapsed_time desc limit 10;

select distinct query_sql from gv$sql_audit where sql_id='2D1366676CD073B5199DF5888FB3B0D9'\G;

select distinct query_sql from gv$sql_audit where sql_id='F86483FB17A84B0EA8381F4FA4EBA1AA'\G;

select distinct query_sql from gv$sql_audit where sql_id='3B6366A4BBB3F3DD91FEE90A15DF4C2A'\G;

SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1003 AND ip = '10.137.103.197' AND port=2882 AND plan_id=12;

use ly_tpcc;
explain INSERT INTO bmsql_config ( cfg_name, cfg_value) VALUES ('warehouses', '2');\G

6.总结

OceanBase 社区版入门到实战教程直播正在进行中~

快和小伙伴一起进群交流学习吧~

加入直播群方式一：

钉钉群号 3582 5151

加入直播群方式二：

扫码下方二维码加入