使用 BenmarkSQL 运行 TPC-C压测

1、下载BenmarkSQL并解压

GitHub - meiq4096/benchmarksql-5.0: benchmarksql-5.0 for oceanbase

编辑配置文件props.ob

db=oceanbase

driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver

conn=jdbc:oceanbase://127.0.0.1:2883/sbtest?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true

user=root@obmysql

password=Hello123

warehouses=1

loadWorkers=4

terminals=1

//To run specified transactions per terminal- runMins must equal zero

runTxnsPerTerminal=10

//To run for specified minutes- runTxnsPerTerminal must equal zero

runMins=0

//Number of total transactions per minute

limitTxnsPerMin=300

//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

//osCollectorSSHAddr=user@dbhost

osCollectorDevices=net_eth0 blk_sda

./runSQL.sh props.ob sql.oceanbase/tableCreates.sql

由于是mysql租户，执行报错

将oracle语法的建表语句修改为兼容mysql语法

cp tableCreates.sql tableCreates_mysql.sql

sed -i 's/varchar2/varchar/g' tableCreates_mysql.sql

./runSQL.sh props.ob sql.oceanbase/tableCreates_mysql.sql

加载测试数据

./runLoader.sh props.ob

开始性能测试

./runBenchmark.sh props.ob

查看进程

压测完成

2、查看执行计划

查看TOP5 SQL语句

SELECT trace_id,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 oceanbase.gv$ob_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 5;

查看SQL语句

select * from oceanbase.gv$ob_sql_audit where sql_id='F59A700FA168324279B0DBC25E19760F' limit 1\G;

explain查看语句的执行计划

explain SELECT count(*) AS low_stock FROM (    SELECT s_w_id, s_i_id, s_quantity         FROM bmsql_stock         WHERE s_w_id = 1 AND s_quantity < 20 AND s_i_id IN (            SELECT ol_i_id                 FROM bmsql_district                 JOIN bmsql_order_lr_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 = 1 AND d_id = 6         )     );

通过视图查询执行计划

select plan_line_id,operator,name,rows,cost from oceanbase.GV$OB_PLAN_CACHE_PLAN_EXPLAIN where SVR_IP='10.102.23.43' and SVR_PORT='2882' and tenant_id=1036 and plan_id=870;