作者简介：邹阳，数据库工程师。
本次练习是必选练习之一。
练习目的
本次练习目的掌握 OceanBase 的执行计划查看方法，包括 explain 命令和查看实际执行计划。
练习条件
有服务器，内存资源至少 12G*1 台，部署有 OceanBase 集群（单副本或三副本都可以）。
练习内容
请记录并分享下列内容：
（必选）1、使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
（必选）2、分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
一、环境信息
服务器信息
二、参数设置
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、OceanBase 参数
2.2.1 SYS 租户参数
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; obclient -h 172.16.100.21 -P 2883 -uroot@sys -p -c -A oceanbase
2.2.2 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.2.3 租户参数
创建租户资源
create resource unit test max_cpu=6 , max_memory='12g', max_iops=1280, max_disk_size='20g', max_session_num=1024, min_cpu=5, min_memory='10g', min_iops=512; create resource pool test_pool unit='test', unit_num=1, zone_list=('zone1'); create tenant test_tenant resource_pool_list=('test_pool'); ALTER TENANT test_tenant SET VARIABLES ob_tcp_invited_nodes='%'; obclient -h 127.0.0.1 -P 2883 -u root@test_tenant -p -c -A oceanbase alter user root identified by 'password';
数据库下租户设置，防止事务超时
obclient -h 127.0.0.1 -P 2883 -u root@test_tenant -p -c -A oceanbase
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;
create database tpcc;
obclient -h 127.0.0.1 -P 2883 -u root@test_tenant -p -c -A oceanbase create user tpcc identified by 'password'; grant all privileges on tpcc.* to tpcc ;
三、配置 BenchmarkSQL
3.1 解压安装
unzip benchmarksql-5.0-master.zip
[root@ob01 soft]# cp -r benchmarksql-5.0-master /root/benchmarksql/
3.2 配置
编辑 /root/benchmarksql/run/props.ob 目录下,编辑后的内容如下:
db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@test_tenant#obdemo password=password warehouses=2 loadWorkers=10 terminals=10 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=1 //Number of total transactions per minute limitTxnsPerMin=0 //Set to true to run in 4.x compatible mode. Set to false to use the //entire configured database evenly. terminalWarehouseFixed=true //The following five values must add up to 100 newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4 // Directory name to create for collecting detailed result data. // Comment this out to suppress. resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS osCollectorScript=./misc/os_collector_linux.py osCollectorInterval=1
[root@ob01 run]# /root/benchmarksql/run
3.3 运行创建表语句
sh runSQL.sh props.ob sql.common/tableCreates.sql
编辑一下 runSQL.sh 文件，更改内容如下：
source /root/benchmarksql/run/funcs.sh $1
原文件如下：
修改后
sh runSQL.sh props.ob sql.common/tableCreates.sql
四、测试
4.1 加载数据
sh runLoader.sh props.ob
也报错，修改方法看 二、配置中的修改方式。
source /root/benchmarksql/run/funcs.sh $1
原文件
修改后
这开始执行了，时间好长。
root@ob01 run]# sh runLoader.sh props.ob
跑了近一小时，跑完了。
4.2 创建索引
##登陆到数据库,创建两个索引(表上之前是没有索引的)
[root@ob01 run]# obclient -h 172.16.100.21 -utpcc@test_tenant -P2883 -p'password' -c -A tpcc
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.3 执行性能测试
sh runBenchmark.sh props.ob
报错，接着改。
source /root/benchmarksql/run/funcs.sh $1
原文件
修改后
五、TPC-C TOP SQL分析
5.1 查询TOP10 sql
obclient -h 172.16.100.21 -uroot@test_tenant -P2883 -p'password' -c -A oceanbase SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time FROM gv$sql_audit s WHERE 1=1 and user_name='tpcc' and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 10;
5.2 对 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 request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 3;
select distinct query_sql from gv$sql_audit where sql_id='5C9973474F2AF4CB0C20B54B9B49A2D7';
select distinct query_sql from gv$sql_audit where sql_id='B1F3DF2C7803B02C326BAB74BB140979';
select distinct query_sql from gv$sql_audit where sql_id='F594DCAC2224F0B0976DDD6A8525E6CB'\G;
5.3 分析执行计划
obclient -h 172.16.100.21 -utpcc@test_tenant -P2883 -p'password' -c -A tpcc
explain SELECT c_data FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 9 AND c_id = 511;\G
explain UPDATE bmsql_district SET d_ytd = d_ytd + 479.86 WHERE d_w_id = 1 AND d_id = 10\G;
explain SELECT d_name, d_street_1, d_street_2, d_city, d_state, d_zip FROM bmsql_district WHERE d_w_id = 1 AND d_id = 10\G;
explain UPDATE bmsql_warehouse SET w_ytd = w_ytd + 479.86 WHERE w_id = 1\G;
explain SELECT w_name, w_street_1, w_street_2, w_city, w_state, w_zip FROM bmsql_warehouse WHERE w_id = 1 \G;
explain SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 10 AND c_last = 'ANTIPRESABLE' ORDER BY c_first\G;
explain SELECT c_first, c_middle, c_last, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_credit, c_credit_lim, c_discount, c_balance FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 10 AND c_id = 643 FOR UPDATE\G;
explain UPDATE bmsql_customer SET c_balance = c_balance - 479.86, c_ytd_payment = c_ytd_payment + 479.86, c_payment_cnt = c_payment_cnt + 1 WHERE c_w_id = 1 AND c_d_id = 10 AND c_id = 643\G;
explain INSERT INTO bmsql_history ( h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data) VALUES (643, 10, 1, 10, 1, '2022-02-07 09:09:09.087', 479.86, 'seWDa94k x2iIRI')\G;
OceanBase 社区版入门到实战教程直播正在进行中~
快和小伙伴一起进群交流学习吧~
加入直播群方式一：
钉钉群号 3582 5151
加入直播群方式二：
扫码下方二维码加入
