-
- 使用 BenmarkSQL 运行 TPC-C ,并发数不用很高,5~10 并发即可(根据机器资源)
- 安装BenmarkSQL
- 使用 BenmarkSQL 运行 TPC-C ,并发数不用很高,5~10 并发即可(根据机器资源)
- 下载
wget https://jaist.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
- 配置
Vi /root/benchmarksql-5.0/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=test2@test2#admin
password=123456
warehouses=2
loadWorkers=1
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
-
-
- BenmarkSQL测试
- 测试前环境变量
- BenmarkSQL测试
-
obclient -h127.0.0.1 -P 2883 -u root@test#admin -p'123456qwe' -A -c
##设置以下租户全局变量:
//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;
-
-
-
- 普通租户登陆
-
-
obclient -h192.168.0.28 -P2883 -uTPCC@test2#admin:1673835228 -p'' -A
cd /root/benchmarksql-5.0/run
-
-
-
- 运行创建表语句
-
-
sh runSQL.sh props.ob sql.common/tableCreates.sql
注意:报找不到funcs.sh文件,将funcs.sh设为绝对路径:
编辑一下runSQL.sh文件,更改内容如下:
#!/usr/bin/env bash
...
source /root/benchmarksql-5.0/run/funcs.sh $1
-
-
-
- 装载数据
-
-
sh runLoader.sh props.ob
注意:runLoader.sh: line 8: source: funcs.sh: file not found
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://192.168.0.28:2883/TPCC?useUnicode=true&characterEncoding=utf-8
user=TPCC@test2#admin:1673835228
password=***********
warehouses=2
loadWorkers=1
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 000: Loading ITEM done
Worker 000: Loading Warehouse 4
Worker 001: Loading Warehouse 5 done
-
-
-
- 运行TPCC测试
-
-
sh runBenchmark.sh props.ob
注意:runBenchmark.sh: line 15: source: funcs.sh: file not found
添加绝对路径
##'2023-09-22 14:57:13' - '2023-09-22 14:59:31' 共 112个事务
-
- 分析 TPC-C TOP SQL,并查看 3条 SQL 的 解析执行计划 和 实际执行计划
- 查询top sql
- 分析 TPC-C TOP SQL,并查看 3条 SQL 的 解析执行计划 和 实际执行计划
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 request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 10;
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 sql_id in (' EC179230CBDD37CAC3BADEA5713AC139', '7B47FAE6C6C70D16D32B3E912D6E3181', 'D6235A02E65BE670CE68B91F7A5C959A','CB488C587A02856883A6EDDE6C927CC2', '03F777AFD4ECAA7C533613F17CAFBFA0', 'C9DA44EF8FBAFC40450B9FFB3FB9728B','7A51C5E6187094C97E3A40039ADD5B18','5E7BB584DF5C05299DA510C9E4BF0DCC','0C70BC079EF2DA1C30DD72C485CB287D','697000E8BE07C49E601175E1850D9398' ) GROUP BY sql_id order by avg_elapsed_time desc limit 10;
-
-
- 第一个 sql案例
- 解析执行计划
- 第一个 sql案例
-
select query_sql from gv$sql_audit where sql_id='130658322FDC78779AF90493CA8E5187';
-
-
-
- 实际执行计划
-
-
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.0.28' AND port=2882 AND plan_id=1071
;
-
-
- 第二个 sql案例
- 解析执行计划
- 第二个 sql案例
-
SELECT sql_id,query_sql,elapsed_time,execute_time, s.svr_ip, s.svr_port, s.tenant_id, s.plan_id FROM gv$sql_audit s order by request_time desc limit 2;
select query_sql from gv$sql_audit where sql_id='740A337A45DF23CEEB4BE9BE718D2511';
-
-
-
- 实际执行计划
-
-
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.0.28' AND port=2882 AND plan_id=1070;
-
-
- 第三个 sql案例
- 解析执行计划
- 第三个 sql案例
-
select query_sql from gv$sql_audit where sql_id='935A382CCE70D4D7E03EEB5F2EC48C76';
-
-
-
- 实际执行计划
-
-
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.0.28' AND port=2882 AND plan_id=1071;