分析TPC-C 查看 OceanBase 执行计划

分析TPC-C 查看 OceanBase 执行计划

oceanbase部署请查看历史文章

https://www.modb.pro/db/324460 使用OBD自动部署三节点OceanBase文档

https://www.modb.pro/db/328171 OceanBase手动部署三节点OBserver文档

https://www.modb.pro/db/322997 OceanBase 单节点手动部署OB文档

https://www.modb.pro/db/250245 Docker单节点自动化部署OB集群

目的

本次练习目的掌握 OceanBase 的执行计划查看方法，包括 explain 命令和查看实际执行计划。

练习内容

请记录并分享下列内容：

• （必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
• （必选）分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
• （可选）使用 OceanBase 的 Outline 对 其中一条 SQL 进行限流（限制并发为 1 ）。
• （可选）导入 TPC-H schema 和 数据，数据量不用太大 100M 即可。查看 TPC-H 5条 SQL 的解析执行计划和实际执行计划。

机器信息

机器划分

实验采用OBD部署3节点OBserver172.20.2.120查看 OceanBase 执行计划

一、服务器初始化设置

登录sys

[admin@CAIP120 ~]$ obclient -h172.20.2.120 -uroot@tenantsixlens#ob_cluster -pPwd123# -P2883 -c -A oceanbase

1、设置以下租户全局变量：防止事务超时

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;

2、开启SQL审计

在租户里开启或关闭 SQL 审计功能

MySQL [oceanbase]> set global ob_enable_sql_audit = on;

默认已开启

MySQL [oceanbase]> show variables like 'ob_enable_sql_audit';

3、创建测试数据库

create database tpcc;
create user tpcc identified by 'Pwd123#';
grant all privileges on tpcc.* to tpcc ;

4、下载 JDBC 驱动

https://help.aliyun.com/document_detail/212815.html

将 oceanbase-client-1.1.10.jar 驱动文件复制到lib目录

cp oceanbase-client-1.1.10.jar lib/oceanbase-client-1.1.10.jar

将OceanBase的jdbc驱动放入该目录/root/benchmarksql-5.0/lib/oceanbase-client-1.1.10.jar；经过上面的操作，benchmarksql便可以找到jdbc驱动了。 

二、使用 BenchmarkSQL 运行 TPC-C 测试

benchmarksql 开源项目是对TPC-C标准的实现。下面使用 BenchmarkSQL 运行 TPC-C 进行 5~10 并发场景测试。

1、下载 BenchmarkSQL

[root@CAIP120 ~]# git clone https://github.com/obpilot/benchmarksql-5.0.git

2、创建 benchmarksql 配置文件

编辑 /root/benchmarksql-5.0/run/props.ob 目录下,编辑后的内容如下:

[root@CAIP120 run]# vim props.ob

db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://172.20.2.120:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=tpcc@tenantsixlens#ob_cluster
password=Pwd123#

warehouses=2
loadWorkers=2

terminals=2
//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

3、执行建表脚本

运行创建表语句

[admin@CAIP120 run]$ sh runSQL.sh props.ob sql.common/tableCreates.sql
runSQL.sh: line 14: source: funcs.sh: file not found

报错，提示找不到funcs.sh文件，环境变量有问题，修改脚本runSQL.sh文件，修改如下：

[root@CAIP120 run]# vim runSQL.sh

编辑内容如下：

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

再运行创建表的命令，表创建正常了

[root@CAIP120 run]# ./runSQL.sh props.ob sql.common/tableCreates.sql

三、测试数据

3.1 导入数据

[root@CAIP120 run]# sh runLoader.sh props.ob
runLoader.sh: line 8: source: funcs.sh: file not found

报错，修改runLoader.sh配置

[root@CAIP120 run]# vim runLoader.sh

编辑一下runLoader.sh文件，修改内容如下：

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

再次导入数据

[root@CAIP120 run]# ./runLoader.sh props.ob

运行时间比较长，需要等待

3.2 创建索引

登陆到tpcc数据库

[root@CAIP120 run]# obclient -h172.20.2.120 -utpcc@tenantsixlens#ob_cluster -pPwd123# -P2883 -c -A tpcc

创建两个索引(表上之前是没有索引的)

MySQL [tpcc]> create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;

MySQL [tpcc]> create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

3.3 执行性能测试

[root@CAIP120 run]# ./runBenchmark.sh props.ob

四、TPC-C TOP SQL分析

4.1 查询TOP10 sql(root执行)

[root@CAIP120 run]# obclient -h172.20.2.120 -uroot@tenantsixlens#ob_cluster -pPwd123# -P2883 -c -A oceanbase

执行查询TOP10

MySQL [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;

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

MySQL [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 request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
    ->                     GROUP BY sql_id
    ->                    order by avg_elapsed_time desc limit 3;

查询第一条SQL

select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01';

查询第二条SQL

select distinct query_sql from gv$sql_audit where sql_id='01089E30C23A11C88E71A78F687ABE25';

查询第三条SQL

select distinct query_sql from gv$sql_audit where sql_id='E1F2BDA1D7391B757859ED3704E5AFB7';

4.3 分析执行计划

租户tpcc登录

[root@CAIP120 run]# obclient -h172.20.2.120 -utpcc@tenantsixlens#ob_cluster -pPwd123# -P2883 -c -A tpcc

explain 命令查看执行计划

MySQL [tpcc]> explain SELECT c_data    FROM bmsql_customer    WHERE c_w_id = 1 AND c_d_id = 9 AND c_id = 511;\G

MySQL [tpcc]> explain UPDATE bmsql_district    SET d_ytd = d_ytd + 479.86    WHERE d_w_id = 1 AND d_id = 10\G;

MySQL [tpcc]> 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;

MySQL [tpcc]> explain UPDATE bmsql_warehouse    SET w_ytd = w_ytd + 479.86    WHERE w_id = 1\G;

MySQL [tpcc]> explain SELECT w_name, w_street_1, w_street_2, w_city,       w_state, w_zip    FROM bmsql_warehouse    WHERE w_id = 1 \G;

MySQL [tpcc]> 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;

MySQL [tpcc]> 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;

MySQL [tpcc]> 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;

MySQL [tpcc]> 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;