- （必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
- （必选）分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
参考文档：
https://www.oceanbase.com/docs/community-observer-cn-10000000000901541
TPC-C&BenmarkSQL是什么？
TPC：Transaction process performance Council事务处理性能委员会，一个非盈利性质的组织，专门负责制定计算机事务处理能力测试标准。
TPC-C:TPC制定的C号标准。数据库性能测试的标杆。用来衡量在线事务处理的基准
TPC-C模型是模拟一个商品批发公司的销售模型，这个模型涵盖了一个批发公司面向客户对一系列商品进行销售的过程，这包括管理订单，管理库存，管理账号收支等操作。这些操作涉及到仓库、商品、客户、订单等概念，围绕这些概念，构造了数据表格，以及相应的数据库操作。
TPC-C的批发操作在概念上包括5个操作，新订单操作、付款操作、订单状态操作、发货操作，库存操作，这些操作一起构成了完整的批发流程，其中新订单操作是用来衡量tpmC的核心，也就是数据库系统每分钟所能处理的交易数量。下面，我们将详细讲述TPCC的数据库设计与特点，以及各个操作的组成
TPC-H：针对OLAP的测试。
TPC-C：针对OLTP的测试。
Benchmarksql则是一款符合TPC-C基准压力测试工具。
一、安装BenmarkSQL
下载地址
https://sourceforge.net/projects/benchmarksql
测试环境需要有 Java 运行环境，且版本不低于 V1.8.0。
Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，需要修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库。
unzip benchmarksql-5.0.zip
cd benchmarksql-5.0
1.修改benchmarksql-5.0/src/client/jTPCC.java，添加OceanBase内容
if (iDB.equals("firebird"))
dbType = DB_FIREBIRD;
else if (iDB.equals("oracle"))
dbType = DB_ORACLE;
else if (iDB.equals("postgres"))
dbType = DB_POSTGRES;
else if (iDB.equals("oceanbase"))
dbType = DB_OCEANBASE;
else
{
log.error("unknown database type '" + iDB + "'");
return;
}
2.修改benchmarksql-5.0/src/client/jTPCCConfig.java
public final static int
DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
DB_OCEANBASE = 4;
3.修改benchmarksql-5.0/src/client/jTPCCConnection.java，在 SQL 子查询增加 AS L 别名。
default:
stmtStockLevelSelectLow = dbConn.prepareStatement(
"SELECT count(*) AS low_stock FROM (" +
" SELECT s_w_id, s_i_id, s_quantity " +
" FROM bmsql_stock " +
" WHERE s_w_id = ? AND s_quantity < ? 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 = ? AND d_id = ? " +
" ) " +
" )AS L");
break;
4.重新编译修改后的源码。
cd benchmarksql-5.0
ant
[root@node1 benchmarksql-5.0]# ant
Buildfile: /soft/benchmarksql-5.0/build.xml
init:
compile:
[javac] Compiling 11 source files to /soft/benchmarksql-5.0/build
dist:
[mkdir] Created dir: /soft/benchmarksql-5.0/dist
[jar] Building jar: /soft/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
BUILD SUCCESSFUL
Total time: 2 seconds
5.修改benchmarksql-5.0/run/funcs.sh
function setCP()
{
case "$(getProp db)" in
firebird)
cp="../lib/firebird/*:../lib/*"
;;
oracle)
cp="../lib/oracle/*"
if [ ! -z "${ORACLE_HOME}" -a -d ${ORACLE_HOME}/lib ] ; then
cp="${cp}:${ORACLE_HOME}/lib/*"
fi
cp="${cp}:../lib/*"
;;
postgres)
cp="../lib/postgres/*:../lib/*"
;;
oceanbase)
cp="../lib/oceanbase/*:../lib/*"
;;
esac
myCP=".:${cp}:../dist/*"
export myCP
}
...省略
case "$(getProp db)" in
firebird|oracle|postgres|oceanbase)
;;
"") echo "ERROR: missing db= config option in ${PROPS}" >&2
exit 1
;;
*) echo "ERROR: unsupported database type 'db=$(getProp db)' in ${PROPS}" >&2
exit 1
;;
esac
6.修改benchmarksql-5.0/run/runDatabaseBuild.sh
AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
修改为：
AFTER_LOAD="indexCreates buildFinish"
二、修改配置文件
在run/下创建props.ob
vim run/props.ob
db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://192.168.40.111:2881/tpccdb?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true
user=root@my_tenant
password=Admin123
warehouses=10
loadWorkers=2
//fileLocation=/data/temp/
terminals=10
//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
osCollectorInterval=1
//osCollectorSSHAddr=user@dbhost
//osCollectorDevices=net_eth0 blk_sda
参数说明
db：指定数据库类型。此处保持和模板一致即可。
driver：驱动程序文件，推荐使用 MySQL 的 JDBC 驱动：mysql-connector-java-5.1.47，驱动下载地址。
·conn`：此处的 IP 建议填写 OceanBase Server 的 IP，端口为 OceanBase Server 部署端口，其他部分保持和模板一致。
user & password：根据环境中使用的用户名、租户名以及密码即可。如果环境中有多个 OceanBase 集群，则 user 的格式建议为 {user_name}@{tenant_name}#{cluster_name}。
warehouses：指定仓库数，仓库数决定性能测试的成绩。如果希望针对多节点的 OceanBase 集群进行测试，建议选择 1000 仓以上。如果机器配置有限，可以选择 100 仓进行测试。
loadWorkers：指定仓库数据加载时的并发。如果机器配置较高，该值可以设置大一些，例如 100。如果机器配置有限，该值需要设置小一些，如 10 并发。过高的并发可能会导致内存消耗太快，出现报错，导致数据加载需要重新进行。
terminals：指定性能压测时的并发数。建议并发数不要高于仓库数 * 10。否则，会有不必要的锁等待。在生产环境中，建议将此参数设置为最多 1000。在测试环境中，建议从 100 开始。
runMins：指定性能测试持续的时间。时间越久，越能考验数据库的性能和稳定性。建议不要少于 10 分钟，生产环境中机器建议不少于 1 小时
三、数据准备
创建测试库
obclient -h192.168.40.111 -P2883 -uroot@my_tenant -pAdmin123 -Doceanbase -A
create database tpccdb;
使用脚本创建测试表
./runSQL.sh props.ob sql.common/tableCreates.sql
报错：缺少mysql驱动
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-5.1.49.tar.gz
tar -zxvf mysql-connector-java-5.1.49.tar.gz
mkdir -p /soft/benchmarksql-5.0/lib/oceanbase/
cp /soft/mysql-connector-java-5.1.49/mysql-connector-java-5.1.49.jar /soft/benchmarksql-5.0/lib/oceanbase/
执行完成后，查看建表
obclient [oceanbase]> use tpccdb
Database changed
obclient [tpccdb]> show tables;
+------------------+
| Tables_in_tpccdb |
+------------------+
| 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.003 sec)
加载数据
./runLoader.sh props.ob
创建索引
cat sql.common/indexCreates.sql
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
四、开始测试
测试前可以先执行一次集群合并
需要以root登录到sys租户执行
obclient -h192.168.40.111 -P2883 -uroot -pAdmin123 -Doceanbase -A
ALTER SYSTEM MAJOR FREEZE tenant = my_tenant;
SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION;
#STATUS =IDLE表示合并完成
开始测试
./runBenchmark.sh props.ob
...
20:59:02,450 [Thread-9] INFO jTPCC : Term-00, 20:59:02,450 [Thread-9] INFO jTPCC : Term-00, 20:59:02,450 [Thread-9] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 7100.69
20:59:02,450 [Thread-9] INFO jTPCC : Term-00, Measured tpmTOTAL = 15819.71
20:59:02,450 [Thread-9] INFO jTPCC : Term-00, Session Start = 2024-03-14 20:49:02
20:59:02,450 [Thread-9] INFO jTPCC : Term-00, Session End = 2024-03-14 20:59:02
20:59:02,450 [Thread-9] INFO jTPCC : Term-00, Transaction Count = 158203
五、查看执行计划
查看top3的sql
[root@node1 run]# obclient -h192.168.40.111 -P2883 -uroot@my_tenant -pAdmin123 -Doceanbase -A
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 361
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:20:54)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> SELECT avg_exe_usec,tenant_id, svr_ip, svr_port, sql_id, plan_id FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT ORDER BY avg_exe_usec DESC LIMIT 3 ;
+--------------+-----------+----------------+----------+----------------------------------+---------+
| avg_exe_usec | tenant_id | svr_ip | svr_port | sql_id | plan_id |
+--------------+-----------+----------------+----------+----------------------------------+---------+
| 116533 | 1002 | 192.168.40.111 | 2882 | 6EFCCA331BC647571D2CF0D77051499E | 990 |
| 57704 | 1002 | 192.168.40.111 | 2882 | C6A7263F4E233857FFC175CF01581188 | 787 |
| 34275 | 1002 | 192.168.40.111 | 2882 | AEC8B9A89BF513683B92F47BEB42E931 | 742 |
+--------------+-----------+----------------+----------+----------------------------------+---------+
3 rows in set (0.013 sec)
obclient [oceanbase]>
查看第一条sql内容
select query_sql from oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT where sql_id='6EFCCA331BC647571D2CF0D77051499E';
| query_sql |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 10 AND s_quantity < 16 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 = 10 AND d_id = 8 ) ) AS L |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)
查看解释执行计划
sql前加上explain 关键字
obclient [oceanbase]> use tpccdb
Database changed
obclient [tpccdb]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 10 AND s_quantity < 16 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 = 10 AND d_id = 8 ) ) AS L ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================================================ |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |123 | |
| |1 |└─HASH RIGHT SEMI JOIN | |12 |122 | |
| |2 | ├─SUBPLAN SCAN |VIEW1 |12 |24 | |
| |3 | │ └─NESTED-LOOP JOIN | |12 |24 | |
| |4 | │ ├─TABLE GET |bmsql_district |1 |3 | |
| |5 | │ └─DISTRIBUTED TABLE RANGE SCAN|bmsql_order_line|1 |21 | |
| |6 | └─TABLE RANGE SCAN |bmsql_stock |423 |61 | |
| ================================================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT(*)]) |
| 1 - output(nil), filter(nil), rowset=256 |
| equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil) |
| 2 - output([VIEW1.ol_i_id]), filter(nil), rowset=256 |
| access([VIEW1.ol_i_id]) |
| 3 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| conds(nil), nl_params_([bmsql_district.d_next_o_id(:0)]), use_batch=false |
| 4 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), rowset=256 |
| access([bmsql_district.d_next_o_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_district.d_w_id], [bmsql_district.d_id]), range[10,8 ; 10,8], |
| range_cond([bmsql_district.d_w_id = 10], [bmsql_district.d_id = 8]) |
| 5 - output([bmsql_order_line.ol_i_id]), filter(nil), rowset=256 |
| access([bmsql_order_line.ol_i_id]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_number]), range(MIN ; MAX), |
| range_cond([bmsql_order_line.ol_w_id = 10], [bmsql_order_line.ol_d_id = 8], [bmsql_order_line.ol_o_id >= :0 - 20], [bmsql_order_line.ol_o_id < :0]) |
| 6 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_quantity < 16]), rowset=256 |
| access([bmsql_stock.s_i_id], [bmsql_stock.s_quantity]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([bmsql_stock.s_w_id], [bmsql_stock.s_i_id]), range(10,MIN ; 10,MAX), |
| range_cond([bmsql_stock.s_w_id = 10]) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
36 rows in set (0.730 sec)
obclient [tpccdb]>
查看真实执行计划
查看GV$OB_PLAN_CACHE_PLAN_EXPLAIN
obclient [oceanbase]> SELECT avg_exe_usec,tenant_id, svr_ip, svr_port, sql_id, plan_id FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT ORDER BY avg_exe_usec DESC LIMIT 3 ;
+--------------+-----------+----------------+----------+----------------------------------+---------+
| avg_exe_usec | tenant_id | svr_ip | svr_port | sql_id | plan_id |
+--------------+-----------+----------------+----------+----------------------------------+---------+
| 116533 | 1002 | 192.168.40.111 | 2882 | 6EFCCA331BC647571D2CF0D77051499E | 990 |
| 57704 | 1002 | 192.168.40.111 | 2882 | C6A7263F4E233857FFC175CF01581188 | 787 |
| 34275 | 1002 | 192.168.40.111 | 2882 | AEC8B9A89BF513683B92F47BEB42E931 | 742 |
+--------------+-----------+----------------+----------+----------------------------------+---------+
obclient [tpccdb]> SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,property
-> from oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN`
-> where tenant_id=1002 AND SVR_IP = '192.168.40.111' AND SVR_PORT=2882 AND plan_id=990;
+----------------+------------+--------------+-------------------------+------------------+------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| SVR_IP | plan_depth | plan_line_id | operator | name | rows | cost | property |
+----------------+------------+--------------+-------------------------+------------------+------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| 192.168.40.111 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 119 | NULL |
| 192.168.40.111 | 1 | 1 | PHY_HASH_JOIN | NULL | 8 | 119 | NULL |
| 192.168.40.111 | 2 | 2 | PHY_SUBPLAN_SCAN | NULL | 8 | 23 | NULL |
| 192.168.40.111 | 3 | 3 | PHY_NESTED_LOOP_JOIN | NULL | 8 | 23 | NULL |
| 192.168.40.111 | 4 | 4 | PHY_TABLE_SCAN | bmsql_district | 1 | 2 | table_rows:100, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_district] |
| 192.168.40.111 | 4 | 5 | PHY_TABLE_SCAN | bmsql_order_line | 1 | 20 | table_rows:2290118, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[bmsql_order_line] |
| 192.168.40.111 | 2 | 6 | PHY_TABLE_SCAN | bmsql_stock | 416 | 59 | table_rows:875175, physical_range_rows:416, logical_range_rows:416, index_back_rows:0, output_rows:416, avaiable_index_name[bmsql_stock] |
+----------------+------------+--------------+-------------------------+------------------+------+------+------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.023 sec)
obclient [tpccdb]>
394
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
