实践练习六：查看OceanBase 执行计划
- 实验说明
1.1 实验目标
1、使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
2、分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
3、对其中一条查询sql创建索引并查看解释执行计划和实际执行计划变更情况。
1.2 实验环境信息
本次部署使用环境如下：
- 宿主机Oracle Linux Server release 6.9 48核，320G内存 600G硬盘
- 虚拟机软件vbox VirtualBox-6.0.6
- 虚拟主机4台 8c32g，80G硬盘，/根目录50G
- centos7.6 iso 系统镜像（CentOS-7-x86_64-DVD-1810.iso）
1.3机器角色划分
| Zone | IP | hostname | RPC Port | Connect Port |
| Zone1 | 192.168.56.41 | ob32-2 | 2882 | 2881 |
| Zone2 | 192.168.56.42 | ob32-3 | 2882 | 2881 |
| Zone3 | 192.168.56.43 | ob32-4 | 2882 | 2881 |
| obproxy | 192.168.56.40 | ob32-1 | 2884 | 2883 |
| mysql and obclient客户端 | 192.168.56.40 | ob32-1 |
2.使用 BenmarkSQL 运行 TPC-C
2.1确认java版本
[root@ob32-1 ~]# java -version
openjdk version "1.8.0_181"
OpenJDK Runtime Environment (build 1.8.0_181-b13)
OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)
2.2 TPC-C简介
TPC-C是一个对OLTP(联机交易处理)系统进行测试的规范,使用一个商品销售模型对OLTP系统进行测试,其中包含五类事务:
neworder – 新订单的生成
payment – 订单付款
orderstatus – 最近订单查询
delivery – 配送
stocklevel – 库存缺货状态分析
在测试开始前,TPC-C Benchmark规定了数据库的初始状态,也就是数据库中数据生成的规则,其中ITEM表中固定包含10万种商品,仓库的数量可进行调整,假设WAREHOUSE表中有W条记录,那么:
STOCK 表中应有 W * 10 万条记录(每个仓库对应10万种商品的库存数据)
DISTRICT 表中应有 W * 10 条记录(每个仓库为10个地区提供服务)
CUSTOMER 表中应有 W * 10 * 3000 条记录(每个地区有3000个客户)
HISTORY 表中应有 W * 10 * 3000 条记录(每个客户一条交易历史)
ORDER 表中应有 W * 10 * 3000 条记录(每个地区3000个订单),并且最后生成的900个订单被添加到NEW-ORDER表中,每个订单随机生成5~15条ORDER-LINE记录.
我们将以1000 WAREHOUSE为例进行测试.
TPC-C使用tpmC值(Transactions per Minute)来衡量系统最大有效吞吐量(MQTh, Max Qualified Throughput),其中Transactions以NewOrder Transaction为准,即最终衡量单位为每分钟处理的新订单数.
2.3下载、配置 BenchmarkSQL
##下载BenchmarkSQL
git clone https://github.com/obpilot/benchmarksql-5.0.git或者直接网页下载压缩包都可以。
下载后查看压缩包大小：
[root@ob32-1 soft]# ls -lh benchmarksql-5.0-master.zip
-rw-r--r--. 1 root root 5.6M Mar 10 2023 benchmarksql-5.0-master.zip
解压缩：
[root@ob32-1 soft]# unzip benchmarksql-5.0-master.zip
[root@ob32-1 soft]# cd benchmarksql-5.0-master
[root@ob32-1 benchmarksql-5.0-master]# ll
total 28
drwxr-xr-x. 2 root root 4096 Feb 29 2020 build
-rwxr-xr-x. 1 root root 1130 Feb 29 2020 build.xml
drwxr-xr-x. 2 root root 34 Feb 29 2020 dist
drwxr-xr-x. 3 root root 17 Feb 29 2020 doc
-rwxr-xr-x. 1 root root 6376 Feb 29 2020 HOW-TO-RUN.txt
drwxr-xr-x. 5 root root 111 Feb 29 2020 lib
-rwxr-xr-x. 1 root root 5470 Feb 29 2020 README.md
drwxr-xr-x. 8 root root 4096 Feb 29 2020 run
drwxr-xr-x. 6 root root 67 Feb 29 2020 src
##配置benchmarksql
编辑/soft/benchmarksql-5.0-master/run/props.ob目录下, 修改配置文件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=root@obmytest#obdemo
password=rootPWD123
warehouses=5
loadWorkers=5
terminals=5
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=5
//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@ob32-1 run]# pwd
/soft/benchmarksql-5.0-master/run
2.4 创建数据库表
##先登陆到租户
obclient -h127.0.0.1 -P 2883 -u root@obmytest -prootPWD123 -c -A oceanbase
##为避免大事务超时,设置以下租户全局变量:
//show variables like '%ob_timestamp_service%';
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;
运行创建表语句
sh runSQL.sh props.ob sql.common/tableCreates.sql
报找不到funcs.sh文件,将funcs.sh设为绝对路径:
编辑一下runSQL.sh文件,更改内容如下:
#!/usr/bin/env bash
...
source /soft/benchmarksql-5.0-master/run/funcs.sh $1
再运行创建表的命令,表创建正常了,下面的脚本都需要做这样的更改 。
运行输出如下：
# ------------------------------------------------------------
# Loading SQL file sql.common/tableCreates.sql
# ------------------------------------------------------------
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
);
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
);
create sequence bmsql_hist_id_seq;
You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'sequence bmsql_hist_id_seq' at line 1
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp
);
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
);
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24)
);
##查看已经建好的10张表：
obclient [oceanbase]> use tpcc;
Database changed
obclient [tpcc]> show tables;
+------------------+
| Tables_in_tpcc |
+------------------+
| bmsql_config |
| bmsql_customer |
| bmsql_district |
| bmsql_history |
| bmsql_item |
| bmsql_new_order |
| bmsql_oorder |
| bmsql_order_line |
| bmsql_stock |
| bmsql_warehouse |
+------------------+
10 rows in set (0.016 sec)
2.5 加载测试数据和创建索引
##加载测试数据
[root@ob32-1 run]# sh runLoader.sh props.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
user=root@obmytest#obdemo
password=***********
warehouses=5
loadWorkers=5
fileLocation (not defined)
csvNullValue (not defined - using default 'NULL')
Worker 000: Loading ITEM
Worker 001: Loading Warehouse 1
Worker 002: Loading Warehouse 2
Worker 003: Loading Warehouse 3
Worker 004: Loading Warehouse 4
Worker 000: Loading ITEM done
Worker 000: Loading Warehouse 5
Worker 002: Loading Warehouse 2 done
Worker 004: Loading Warehouse 4 done
Worker 001: Loading Warehouse 1 done
Worker 003: Loading Warehouse 3 done
Worker 000: Loading Warehouse 5 done
##登陆到数据库,创建两个索引(表上之前是没有索引的)
obclient -h127.0.0.1 -uroot@obmytest -P2883 -prootPWD123 -c -A tpcc
create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;
Query OK, 0 rows affected (3.223 sec)
create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
Query OK, 0 rows affected (1.932 sec)
2.6 运行TPC-C测试
##测试前发起一次集群合并，以 root 登录到 sys 租户执行:
obclient -h192.168.56.40 -uroot@sys#obdemo -P2883 -prootPWD123 -c -A oceanbase
alter system major freeze;
##运行TPCC测试
##sh runBenchmark.sh props.ob
16:13:41,117 [main] INFO jTPCC : Term-00,
16:13:41,120 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
16:13:41,120 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
16:13:41,120 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
16:13:41,120 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
16:13:41,120 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
16:13:41,122 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
16:13:41,122 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
16:13:41,122 [main] INFO jTPCC : Term-00,
16:13:41,123 [main] INFO jTPCC : Term-00, db=oracle
16:13:41,123 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
16:13:41,123 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
16:13:41,123 [main] INFO jTPCC : Term-00, user=root@obmytest#obdemo
16:13:41,123 [main] INFO jTPCC : Term-00,
16:13:41,123 [main] INFO jTPCC : Term-00, warehouses=5
16:13:41,123 [main] INFO jTPCC : Term-00, terminals=5
16:13:41,124 [main] INFO jTPCC : Term-00, runMins=5
16:13:41,125 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
16:13:41,125 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
16:13:41,125 [main] INFO jTPCC : Term-00,
16:13:41,125 [main] INFO jTPCC : Term-00, newOrderWeight=45
16:13:41,125 [main] INFO jTPCC : Term-00, paymentWeight=43
16:13:41,125 [main] INFO jTPCC : Term-00, orderStatusWeight=4
16:13:41,125 [main] INFO jTPCC : Term-00, deliveryWeight=4
16:13:41,125 [main] INFO jTPCC : Term-00, stockLevelWeight=4
16:13:41,125 [main] INFO jTPCC : Term-00,
16:13:41,125 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
16:13:41,125 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
16:13:41,125 [main] INFO jTPCC : Term-00,
16:13:41,140 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2023-03-10_161341/run.properties
16:13:41,140 [main] INFO jTPCC : Term-00, created my_result_2023-03-10_161341/data/runInfo.csv for runID 6
16:13:41,140 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2023-03-10_161341/data/result.csv
16:13:41,141 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
16:13:41,141 [main] INFO jTPCC : Term-00, osCollectorInterval=1
16:13:41,141 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
16:13:41,141 [main] INFO jTPCC : Term-00, osCollectorDevices=null
16:13:41,204 [main] INFO jTPCC : Term-00,
16:13:41,501 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 149
16:13:41,501 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 84
16:13:41,501 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 7.55 Current tpmTOTAL: 312 Memory Usage: 36MB / 483MB
16:19:54,993 [Thread-5] INFO jTPCC : Term-00,
16:19:54,993 [Thread-5] INFO jTPCC : Term-00,
16:19:54,993 [Thread-5] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 3.53
16:19:54,994 [Thread-5] INFO jTPCC : Term-00, Measured tpmTOTAL = 7.55
16:19:54,994 [Thread-5] INFO jTPCC : Term-00, Session Start = 2023-03-10 16:13:41
16:19:54,994 [Thread-5] INFO jTPCC : Term-00, Session End = 2023-03-10 16:19:54
16:19:54,994 [Thread-5] INFO jTPCC : Term-00, Transaction Count = 46
##'2023-03-10 16:13:41' - '2023-03-10 16:19:54' 共 46个事务。
3.TPC-C TOP SQL分析
3.1第一个案例：查询TOP sql
登录系统租户查询：
obclient -h192.168.56.40 -uroot@sys#obdemo -P2883 -prootPWD123 -c -A oceanbase
1.查询TOP sql
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='root'
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;
-
- 第二个案例：对elapsed时间最长的前三条sql进行分析
##找出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;
##对elapsed时间最长的前三条sql进行分析
第一个sql_id:F4585305C4CB9B091C750826A7DEDD13对应了多个SQL
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='F4585305C4CB9B091C750826A7DEDD13';
第一个sql_id 有8个不同的文本，这8个文本的不同仅仅是传入参数不同，可以共享执行计划，
这个计划的plan_id 为3401，运行下面sql查询这条语句的实际执行计划；
SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain`
where tenant_id=1001 AND ip = '192.168.56.42' AND port=2882 AND plan_id=3401\G
##对第二条sql分析:
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='FC3FED8CCB2946DE54F1C5BA3656023C';
##查看plan_id=3398,对应的实际执行计划(已经看不到,重新执行一遍获取执行计划)
##SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from
oceanbase.`gv$plan_cache_plan_explain`
where tenant_id=1001 AND ip = '192.168.56.42' AND port=2882 AND plan_id=3398\G
##第一条SQL执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain`
where tenant_id=1001 AND ip = '192.168.56.42' AND port=2882 AND plan_id=3401;
##explain及extended获取执行计划以及两者的差异:
use tpcc;
MySQL [tpcc]> explain UPDATE bmsql_district SET d_ytd = d_ytd + 868.03 WHERE d_w_id = 4 AND d_id = 5\G;
##第二条SQL执行计划
MySQL [tpcc]> explain extended SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 4 AND d_id = 1 FOR UPDATE\G;
|
|
##对第三条sql进行分析:
obclient [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D
##对第三条sql进行分析:
obclient [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01';
##第三句SQL实际执行计划
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain`
where tenant_id=1001 AND ip = '192.168.56.42' AND port=2882 AND plan_id=3427;
##第三句SQL explain计划
use tpcc;
MySQL [tpcc]> explain SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04, s_dist_05, s_dist_06, s_dist_07, s_dist_08,
s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 5 AND s_i_id = 69842 FOR UPDATE\G;
3.3 第三个案例：创建索引查看执行计划变更
MySQL [tpcc]> show create table bmsql_item;
CREATE TABLE `bmsql_item` (
`i_id` int(11) NOT NULL,
`i_name` varchar(24) DEFAULT NULL,
`i_price` decimal(5,2) DEFAULT NULL,
`i_data` varchar(50) DEFAULT NULL,
`i_im_id` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE
TABLET_SIZE = 134217728 PCTFREE = 0
##未创建索引前查看的执行计划为全表扫
MySQL [tpcc]> explain select * from bmsql_item limit 1\G;
在bmsql_item建立一个索引：
create index bmsql_item_idx on bmsql_item (i_id) local;
再次查看执行计划，已经走索引了：
MySQL [tpcc]> explain select * from bmsql_item where i_id=1\G; 
MySQL [tpcc]> explain select * from bmsql_item where i_id=1\G;
##查看该sql对应的PLAN_ID
MySQL [oceanbase]> SELECT sql_id,query_sql,elapsed_time,execute_time, s.svr_ip, s.svr_port, s.tenant_id, s.plan_id FROM oceanbase.gv$sql_audit s order by request_time desc limit 1;
##查看该sql对应的实际执行计划，也是走了索引了。
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1001
AND ip = '192.168.56.42' AND port=2882 AND plan_id=3489 ;
4 配置过程中遇到的坑
4.1 运行创建表语句失败，报找不到funcs.sh文件
报错信息如下：
sh runSQL.sh props.ob sql.common/tableCreates.sql
报找不到funcs.sh文件,
解决办法：
1、将funcs.sh设为绝对路径:
编辑一下runSQL.sh文件,更改内容如下:
#!/usr/bin/env bash
...
source /soft/benchmarksql-5.0-master/run/funcs.sh $1
再运行创建表的命令,表创建正常了,下面的脚本都需要做这样的更改，比较麻烦，可采用第二种办法。
1、批量修改脚本，类似以下的写法：
sed -i 's/source funcs.sh/source \/soft\/benchmarksql-5.0-master\/run\/funcs.sh/g' runLoader.sh
- 将/soft/benchmarksql-5.0-master/run路径加入path环境变量
PATH=$PATH:/soft/benchmarksql-5.0-master/run:$HOME/bin
加入环境变量，然后 source ~/.bash_profile 使其生效。
5 实验总结
1.学习如何查看实际执行计划
2.学习理解执行计划中关键字的含义,以及为什么走这样的计划.
3.实验的难点是执行sysbench 测试。后面的查看执行计划，可以按照参考文档对照执行。
扫一扫
1699
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
