1，系统信息
[root@db01 test]# cat /etc/redhat-release CentOS Linux release 7.8.2003 (Core)[root@db01 test]# free -h total used free shared buff/cache available Mem: 18G 1.2G 16G 2058M 4500M 0 Swap: 0B 0B 0B
2,benchmarksql下载
[root@db01 test]# git clone https://github.com/obpilot/benchmarksql-5.0.git Cloning into 'benchmarksql-5.0'... remote: Enumerating objects: 110, done. remote: Counting objects: 100% (110/110), done. remote: Compressing objects: 100% (90/90), done. remote: Total 110 (delta 14), reused 105 (delta 12), pack-reused 0 Receiving objects: 100% (110/110), 5.58 MiB | 3.45 MiB/s, done. Resolving deltas: 100% (14/14), done.
2.1 配置用户和密码
[root@db01 run]# cat 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@tpcc#tpccpassword=123456
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
2.2 初始化表结构
#修改数据类型varchar2 类型为varchar。
sed -i 's/varchar2/varchar/g' tableCreates.sql
./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sql
2.3 加载数据
#初始化数据
sh runLoader.sh props.ob
#创建索引../runSQL.sh props.ob sql.oceanbase/indexCreates.sql
[root@db01 sql.oceanbase]# cat indexCreates.sql 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;
2.4 检查数据
obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456 -Dtpcc
MySQL [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 |2.5 TPCC压力测试
[root@db01 run]#
[root@db01 run]# sh runBenchmark.sh props.ob
10:01:50,632 [main] INFO jTPCC : Term-00,
10:01:50,636 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:01:50,636 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
10:01:50,636 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:01:50,636 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
10:01:50,637 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
10:01:50,639 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
10:01:50,639 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
10:01:50,639 [main] INFO jTPCC : Term-00,
10:01:50,639 [main] INFO jTPCC : Term-00, db=oracle
10:01:50,639 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
10:01:50,640 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpccdb?useUnicode=true&characterEncoding=ut f-8
10:01:50,640 [main] INFO jTPCC : Term-00, user=tpcc@my_tenant#obcluster
10:01:50,640 [main] INFO jTPCC : Term-00,
10:01:50,640 [main] INFO jTPCC : Term-00, warehouses=2
10:01:50,640 [main] INFO jTPCC : Term-00, terminals=5
10:01:50,645 [main] INFO jTPCC : Term-00, runMins=5
10:01:50,645 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
10:01:50,645 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
10:01:50,645 [main] INFO jTPCC : Term-00,
10:01:50,645 [main] INFO jTPCC : Term-00, newOrderWeight& 3.TOPsql 分析
3.1 TOP SQL查询
obclient -uroot@tpcc -h127.0.0.1 -P2881 -p123456
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;*********************** 1. row ***************************
sql_id: 9D06D688727D06D688727D0FE0F
count(*): 1
avg_elapsed_time: 640719
avg_exec_time: 635456
svr_ip: 127.0.0.1
svr_port: 2882
tenant_id: 1002
plan_id: 56
*************************** 2. row ***************************
sql_id: 2FBC9F1F98550EBEB55213E728Y
count(*): 1
avg_elapsed_time: 633977
avg_exec_time: 632120
svr_ip: 127.0.0.1
svr_port: 2882
tenant_id: 1002
plan_id:92
*************************** 3. row ***************************
sql_id: 71C241661E66D0F999BFCFF53F4
count(*): 1
avg_elapsed_time: 456054
avg_exec_time: 454233
svr_ip: 127.0.0.1
svr_port: 2882
tenant_id: 1002
plan_id: 86
3.2 执行计划查询
plan_cache_plan_explain查看真是执行的执行计划。
explain查看解释 SQL 语句的执行计划，并非真正执行。
gv$plan_cache_plan_explain
整个集群中plan cache中plan的基本信息解释。
该视图仅支持 get 操作，查询时需要指定 IP、PORT、TENANT_ID、PLAN_ID 字段。
v$plan_cache_plan_explain
该视图仅支持 Get 操作，查询时需要指定
TENANT_ID、PLAN_ID字段
#查看执行计划
SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`gv$plan_cache_plan_explain` where tenant_id=1002 AND ip = '127.0.0.1' AND port=2882 AND plan_id=56\G;
*************************** 1. row ***************************
ip: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_SCALAR_AGGREGATE
name: NULL
rows: 1
cost: 811804
property: NULL
*************************** 2. row ***************************
ip: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_HASH_JOIN
name: NULL
rows: 2
cost: 811804
property: NULL
*************************** 3. row ***************************
ip: 127.0.0.1
plan_depth: 2
plan_line_id: 2
operator: PHY_SUBPLAN_SCAN
name: NULL
rows: 1
cost: 612151
property: NULL
*************************** 4. row ***************************
ip: 127.0.0.1
plan_depth: 3
plan_line_id: 3
operator: PHY_NESTED_LOOP_JOIN
name: NULL
rows: 1
cost: 612151
property: NULL
*************************** 5. row ***************************
ip: 127.0.0.1
plan_depth: 4
plan_line_id: 4
operator: PHY_TABLE_SCAN
name: bmsql_order_line
rows: 40
cost: 612101
property: table_rows:404006, physical_range_rows:600330, logical_range_rows:404006, index_back_rows:0, output_rows:39, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453797, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:173262), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:404006, physical_rc:427068)]
*************************** 6. row ***************************
ip: 127.0.0.1
plan_depth: 4
plan_line_id: 5
operator: PHY_MATERIAL
name: NULL
rows: 1
cost: 50
property: NULL
*************************** 7. row ***************************
ip: 127.0.0.1
plan_depth: 5
plan_line_id: 6
operator: PHY_TABLE_SCAN
name: bmsql_district
rows: 1
cost: 50
property: table_rows:20, physical_range_rows:20, logical_range_rows:20, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1100611139453792, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:0), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:20, physical_rc:20)]
*************************** 8. row ***************************
ip: 127.0.0.1
plan_depth: 2
plan_line_id: 7
operator: PHY_TABLE_SCAN
name: bmsql_stock
rows: 91
cost: 199621
property: table_rows:91148, physical_range_rows:200114, logical_range_rows:91148, index_back_rows:0, output_rows:90, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453798, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:96654), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:91148, physical_rc:103460)]
8 rows in set (0.005 sec)#解释 SQL 语句的执行计划
该语句用于解释 SQL 语句的执行计划，可以是 SELECT、DELETE、INSERT、REPLACE 或 UPDATE 语句。
EXPLAIN 与 DESCRIBE、DESC 互为同义词。explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock
WHERE s_w_id = 2 AND s_quantity < 13 AND s_i_id IN (
SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id
AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 8))\G;
MySQL [tpccdb]> explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock
-> WHERE s_w_id = 2 AND s_quantity < 13 AND s_i_id IN (
-> SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id
-> AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 2 AND d_id = 8))\G;
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |812132|
|1 | HASH RIGHT SEMI JOIN| |2 |812131|
|2 | SUBPLAN SCAN |VIEW1 |1 |612420|
|3 | NESTED-LOOP JOIN | |1 |612419|
|4 | TABLE SCAN |bmsql_order_line|37 |612369|
|5 | MATERIAL | |1 |51 |
|6 | TABLE SCAN |bmsql_district |1 |51 |
|7 | TABLE SCAN |bmsql_stock |86 |199683|
============================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(*)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)])
1 - output([1]), filter(nil),
equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil)
2 - output([VIEW1.ol_i_id]), filter(nil),
access([VIEW1.ol_i_id])
3 - output([bmsql_order_line.ol_i_id]), filter(nil),
conds([bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20], [bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id]), nl_params_(nil)
4 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 8]),
access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0)
5 - output([bmsql_district.d_next_o_id]), filter(nil)
6 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 8], [bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]),
access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_next_o_id]), partitions(p0)
7 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_w_id = 2], [bmsql_stock.s_quantity < 13]),
access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0)
1 row in set (0.016 sec)
最后修改时间：2022-02-14 14:51:55
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
