1 安装 benchmark

https://codeload.github.com/obpilot/benchmarksql-5.0/zip/refs/heads/master

unzip benchmarksql-5.0.zip

cp -r datax/plugin/reader/oceanbasev10reader/libs ./lib/oceanbase /

初始化配置文件

目录为 /opt/software/benchmarksql-5.0/run

vi runB enchmark.sh

#!/usr/bin/env bash

if [ $# -ne 1 ] ; then
    echo "usage: $(basename $0) PROPS_FILE" >&2
    exit 2
fi

SEQ_FILE="./.jTPCC_run_seq.dat"
if [ ! -f "${SEQ_FILE}" ] ; then
    echo "0" > "${SEQ_FILE}"
fi
SEQ=$(expr $(cat "${SEQ_FILE}") + 1) || exit 1
echo "${SEQ}" > "${SEQ_FILE}"

source /opt/software/benchmarksql-5.0/run/funcs.sh $1

setCP || exit 1

myOPTS="-Dprop=$1 -DrunID=${SEQ}"

java -cp "$myCP" $myOPTS jTPCC

vi props.ob

db=oracle
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
# 修改连接串
conn=jdbc:oceanbase:// 127.0.0.1:2883/tpcc ?useUnicode=true&characterEncoding=utf-8
user= tpcc
password= 111111

warehouses=2
loadWorkers=2

terminals=5
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=10
//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

创建 tpcc 数据库

obclient -h127.0.0.1 -uroot@ob_tent_ygb#obdemo -P2883 -p111111 -A -c

create database tpcc;

create user tpcc identified by ‘ 111111 ’;

grant all privileges on tpcc.* to tpcc ;

运行建表语句

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

插入数据

sh runLoader.sh props.ob

执行压测

sh runBenchmark.sh props.ob

查看执行计划

SELECT /*+ PARALLEL(15)*/ avg_exe_usec,tenant_id,plan_id, svr_ip, svr_port, sql_id,db_id,query_sql FROM oceanbase.gv$plan_cache_plan_stat where

db_id='18446744073709551615' ORDER BY avg_exe_usec DESC LIMIT 3;

查看SQL 执行计划结果不是很清楚需要再研究

1.

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 = ' 127.0.0.1 ' AND port=2882 AND plan_id= 295 \G;

2.

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 = ' 127.0.0.1 ' AND port=2882 AND plan_id= 278 \G;

3.

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 = ' 127.0.0.1 ' AND port=2882 AND plan_id= 287 \G;

ITPub博客

实践练习六：查看 OceanBase 执行计划