改造Benchmark SQL适配OceanBase开源版数据库跑TPC-C查看执行计划

改造Benchmark SQL5及SQL表性能调优适配OceanBase开源版数据库跑TPC-C测试

作者：马顺华

从事运维管理工作多年，目前就职于六棱镜（杭州）科技有限公司，熟悉运维自动化、OceanBase部署运维、MySQL 运维以及各种云平台技术和产品。并已获得OceanBase认证OBCA、OBCP证书。

前言

适配 Benchmark SQL5，由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，网络上大部分都是使用mysql数据库和oracle数据库测试Benchmark 。本节将详细介绍如何通过修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库。

 

什么是 TPC-C

TPC-C 是一个对 OLTP（联机交易处理）系统进行测试的规范，使用一个商品销售模型对 OLTP 系统进行测试。

数据库模型

在测试开始前，TPC-C Benchmark 规定了数据库的初始状态（也就是数据库中数据生成的规则），其中 ITEM 表中固定包含 10 万种商品，仓库的数量可进行调整，假设 WAREHOUSE 表中有 W 条记录，那么：

• STOCK 表中应有 W×10 万条记录（每个仓库对应 10 万种商品的库存数据）；
• DISTRICT 表中应有 W×10 条记录（每个仓库为 10 个地区提供服务）；
• CUSTOMER 表中应有 W×10×3000 条记录（每个地区有 3000 个客户）；
• HISTORY 表中应有 W×10×3000 条记录（每个客户一条交易历史）；
• ORDER 表中应有 W×10×3000 条记录（每个地区 3000 个订单），并且最后生成的 900 个订单将被添加到 NEW-ORDER 表中，每个订单随机生成 5~15 条 ORDER-LINE 记录。

在测试过程中，每一个地区（DISTRICT）都有一个对应的终端（Terminal），模拟为用户提供服务。在每个终端的生命周期内，要循环往复地执行各类事务，当终端执行完一个事务的周期后，就进入下一个事务的周期。

客户下单后，包含若干个订单明细（ORDER-LINE）的订单（ORDER）被生成，并被加入新订单（NEW-ORDER）列表。

客户对订单支付会产生交易历史（HISTORY）。 每个订单（ORDER）平均包含 10 条订单项（ORDER-LINE），其中 1% 需要从远程仓库中获取。这些就是 TPC-C 模型中的 9 个数据表。其中，仓库的数量 W 可以根据系统的实际情况进行调整，以使系统性能测试结果达到最佳。

事务类型

该 benchmark 包含 5 类事务：

• NewOrder：新订单请求

  从某一仓库中随机选取 5~15 件商品，创建新订单。其中 1% 的事务需要回滚（即 err）。一般来说新订单请求不可能超出全部事务请求的 45% 。

• Payment：订单付款

  更新客户账户余额，反映其支付情况。在全部事务请求中占比 43% 。

• OrderStatus：最近订单查询

  随机选择一个用户，查询其最近一条订单，显示该订单内的每个商品状态。在全部事务请求中占比 4% 。

• Delivery：配送

  模拟批处理交易，更新该订单用户的余额，把发货单从 neworder 中删除。在全部事务请求中占比 4% 。

• StockLevel : 库存

  缺货状态分析，在全部事务请求中占比 4% 。

安装环境部署版本

软件名	版本	安装方式	备注
java	1.8.0		BenchmarkSQL本身是使用Java语言编写的
BenchmarkSQL	5.0	git在线	压测工具BenchmarkSQL
obclient	2.0.0-2.el7.x86_64	yum安装	OBserver客户端工具
oceanbase-ce	3.1.3.el7.x86_64	yum安装	OBserver集群
Ant	1.9.4	yum安装	用来对BenchmarkSQL进行编译
mysql	5.7.16	yum安装	数据库

测试方案

• 使用 OBD 部署 OceanBase 数据库集群。ODP 和 TPC-C 单独部署在一台机器上, 作为客户端的压力机器。
• OceanBase 集群规模为 1：1：1。部署成功后，新建执行 TPC-C 测试的租户及用户（sys租户是管理集群的内置系统租户，请勿直接使用 sys 租户进行测试）。将租户的 primary_zone 设置为 RANDOM。RANDOM 表示新建表分区的 Leader 随机到这 3 台机器。

测试规格

warehouses=10
loadWorkers=10
terminals=10
runMins=10
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4

一、安装 Benchmark SQL

1、安装 Ant

按照以下步骤安装 Ant：

1. 下载 Ant。

   wget "http://archive.apache.org/dist/ant/binaries/apache-ant-1.10.6-bin.zip"
   

2. 设置环境变量。

   export ANT_HOME=xx/benchmarksql/apache-ant-1.10.6/
   

3. 检查是否安装成功。

   ant -version
   

   如果返回以下信息，则安装成功。

   Apache Ant(TM) version 1.10.6 compiled on May 2 2019
   

##

2、安装 Benchmark SQL5

BenchmarkSQL是通过jdbc连接各个数据库的。此次OceanBase的测试租户是Oracle类型，所以需要借用 lib/oracle 目录，然后把相关jar包一并放入其中。其中 oceanbase-client-*.jar 是OceanBase提供的，其他jar包可以从互联网获取。

1. 进入官网下载地址 MySQL :: Download Connector/J

2. 选择 （注：select Operating System这里一定要修改！！！）

   

3. 编译 Benchmark SQL。

进入 Benchmark SQL 解压后的目录，使用 Ant 编译 Benchmark SQL：

[root@CAIP131 benchmarksql]# ant
Buildfile: /soft/benchmarksql/build.xml

init:

compile:
    [javac] Compiling 11 source files to /soft/benchmarksql/build

dist:
      [jar] Building jar: /soft/benchmarksql/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 1 second

由于 Benchmark SQL5 不支持 OceanBase 数据库的 TPC-C 测试，本节将详细介绍如何通过修改 BenchMarkSQL5 部分源码支持 OceanBase 数据库。

二、适配Benchmark SQL

修改 benchmarksql-5.0/src/client/jTPCC.java 文件，增加 OceanBase 数据库相关内容。

[root@CAIP131 client]# vim jTPCC.java 

 

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;
       }

 

修改 benchmarksql-5.0/src/client/jTPCCConfig.java 文件，增加 OceanBase 数据库类型。

[root@CAIP131 client]# vim jTPCCConfig.java 

public final static int         DB_UNKNOWN = 0,
DB_FIREBIRD = 1,
DB_ORACLE = 2,
DB_POSTGRES = 3,
DB_OCEANBASE = 4;

 

 5.修改 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;

 

 6.重新编译修改后的源码。

[root@CAIP131 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:
      [jar] Building jar: /soft/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 1 second

[root@CAIP131 benchmarksql]# ant
Buildfile: /soft/benchmarksql/build.xml

init:

compile:
    [javac] Compiling 11 source files to /soft/benchmarksql/build

dist:
      [jar] Building jar: /soft/benchmarksql/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 0 seconds

 

 7.在 benchmarksql-5.0/run 目录下，创建文件 prop.oceanbase。

db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://172.20.2.120:2883/tpcc?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=30000000
user=tpcc@tenantdemo#obce_demo
password=Pwd123#
warehouses=10
loadWorkers=10
terminals=10
database=tpcc
//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
//The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec
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

 

 prop.oceanbase 中的参数说明：

• JDBC 连接串：conn=jdbc:mysql://x.x.x.x(ip):xx(port)/xxxx(dbname)?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000

• rewriteBatchedStatements：

  • 参数非常重要，会严重影响导数据效率，不可以忽略。
  • 如果导数据较慢，可以用对应租户登录上去通过 show full processlist 检查是否开启。
  • new order 事务中也用到了 batch update，因此导数和 benchmark 阶段都需要开启。

• 并发数量（terminals）：10，MySQL 租户配置下并发需要结合具体配置动态调整。

• useLocalSessionState：是否使用 autocommit，read_only 和 transaction isolation 的内部值（jdbc 端的本地值），建议设置为 true，如果设置为 false，则需要发语句到远端请求，增加发送请求频次，影响性能。

• warehouses/loadWorkers 这两项用于设置压测数据量，可以适当调整。

• numTerminals > 0 && numTerminals <= 10*numWarehouses，terminals 的范围需要在这个区间内。

1. 修改文件：benchmarksql-5.0/run/funcs.sh，添加 OceanBase 数据库类型。

   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
   

   

    

2. 添加 mysql java connector 驱动，推荐 mysql-connector-java-5.1.47.jar。

   [root@CAIP131 benchmarksql-5.0]# mkdir lib/oceanbase
   [root@CAIP131 benchmarksql-5.0]# cp mysql-connector-java-5.1.47.jar lib/oceanbase/
   

   

    

3. 修改 benchmarksql-5.0/run/runDatabaseBuild.sh。

   AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
   # 修改为：
   AFTER_LOAD="indexCreates buildFinish"
   

   原配置

   

    改配置

    

4.  删除 benchmarksql-5.0/run/sql.common 目录下 sql 文件的 sequence。

[root@CAIP131 sql.common]# ls
buildFinish.sql  foreignKeys.sql  indexCreates.sql  indexDrops.sql  tableCreates.sql  tableDrops.sql  tableTruncates.sql

tableCreates.sql 中去掉。

[root@CAIP131 sql.common]# vim tableCreates.sql 

create sequence bmsql_hist_id_seq;

 tableDrops.sql 中去掉。

[root@CAIP131 benchmarksql-5.0]# vim run/sql.common/tableDrops.sql

drop sequence bmsql_hist_id_seq;

 

 三、改造 BenchMarkSQL5 中的 SQL。

1. [root@CAIP131 benchmarksql-5.0]# cp run/sql.common/tableCreates.sql  run/sql.common/tableCreates.sql-bak
   [root@CAIP131 benchmarksql-5.0]# vim run/sql.common/tableCreates.sql
   

   备份并重写 benchmarksql-5.0/run/sql.common/tableCreates.sql。

   建表脚本通常放在 run/sql.common 下或者其他指定目录下。建表脚本可以选择非分区表方案和分区表方案。

   1、非分区表

   create table bmsql_config (
   cfg_name    varchar(30) primary key,
   cfg_value   varchar(50)
   );
   create tablegroup tpcc_group ;
   create table bmsql_warehouse (
   w_id        integer   not null,
   w_ytd       decimal(12,2),
   w_tax       decimal(4,4),
   w_name      varchar(10),
   w_street_1  varchar(20),
   w_street_2  varchar(20),
   w_city      varchar(20),
   w_state     char(2),
   w_zip       char(9),
   primary key(w_id)
   )tablegroup=tpcc_group;
   create table bmsql_district (
   d_w_id       integer       not null,
   d_id         integer       not null,
   d_ytd        decimal(12,2),
   d_tax        decimal(4,4),
   d_next_o_id  integer,
   d_name       varchar(10),
   d_street_1   varchar(20),
   d_street_2   varchar(20),
   d_city       varchar(20),
   d_state      char(2),
   d_zip        char(9),
   PRIMARY KEY (d_w_id, d_id)
   )tablegroup=tpcc_group ;
   create table bmsql_customer (
   c_w_id         integer        not null,
   c_d_id         integer        not null,
   c_id           integer        not null,
   c_discount     decimal(4,4),
   c_credit       char(2),
   c_last         varchar(16),
   c_first        varchar(16),
   c_credit_lim   decimal(12,2),
   c_balance      decimal(12,2),
   c_ytd_payment  decimal(12,2),
   c_payment_cnt  integer,
   c_delivery_cnt integer,
   c_street_1     varchar(20),
   c_street_2     varchar(20),
   c_city         varchar(20),
   c_state        char(2),
   c_zip          char(9),
   c_phone        char(16),
   c_since        timestamp,
   c_middle       char(2),
   c_data         varchar(500),
   PRIMARY KEY (c_w_id, c_d_id, c_id)
   )tablegroup=tpcc_group ;
   create table bmsql_history (
   hist_id  integer,
   h_c_id   integer,
   h_c_d_id integer,
   h_c_w_id integer,
   h_d_id   integer,
   h_w_id   integer,
   h_date   timestamp,
   h_amount decimal(6,2),
   h_data   varchar(24)
   )tablegroup=tpcc_group ;
   create table bmsql_new_order (
   no_w_id  integer   not null ,
   no_d_id  integer   not null,
   [detached from 1248.pts-0.obce-0000]
   [root@obce-0000 run]# cat sql.common/tableCreates.sql
   create table bmsql_config (
   cfg_name    varchar(30) primary key,
   cfg_value   varchar(50)
   );
   
   create tablegroup tpcc_group ;
   
   create table bmsql_warehouse (
   w_id        integer   not null,
   w_ytd       decimal(12,2),
   w_tax       decimal(4,4),
   w_name      varchar(10),
   w_street_1  varchar(20),
   w_street_2  varchar(20),
   w_city      varchar(20),
   w_state     char(2),
   w_zip       char(9),
   primary key(w_id)
   )tablegroup=tpcc_group;
   
   create table bmsql_district (
   d_w_id       integer       not null,
   d_id         integer       not null,
   d_ytd        decimal(12,2),
   d_tax        decimal(4,4),
   d_next_o_id  integer,
   d_name       varchar(10),
   d_street_1   varchar(20),
   d_street_2   varchar(20),
   d_city       varchar(20),
   d_state      char(2),
   d_zip        char(9),
   PRIMARY KEY (d_w_id, d_id)
   )tablegroup=tpcc_group ;
   
   create table bmsql_customer (
   c_w_id         integer        not null,
   c_d_id         integer        not null,
   c_id           integer        not null,
   c_discount     decimal(4,4),
   c_credit       char(2),
   c_last         varchar(16),
   c_first        varchar(16),
   c_credit_lim   decimal(12,2),
   c_balance      decimal(12,2),
   c_ytd_payment  decimal(12,2),
   c_payment_cnt  integer,
   c_delivery_cnt integer,
   c_street_1     varchar(20),
   c_street_2     varchar(20),
   c_city         varchar(20),
   c_state        char(2),
   c_zip          char(9),
   c_phone        char(16),
   c_since        timestamp,
   c_middle       char(2),
   c_data         varchar(500),
   PRIMARY KEY (c_w_id, c_d_id, c_id)
   )tablegroup=tpcc_group ;
   
   
   create table bmsql_history (
   hist_id  integer,
   h_c_id   integer,
   h_c_d_id integer,
   h_c_w_id integer,
   h_d_id   integer,
   h_w_id   integer,
   h_date   timestamp,
   h_amount decimal(6,2),
   h_data   varchar(24)
   )tablegroup=tpcc_group ;
   
   create table bmsql_new_order (
   no_w_id  integer   not null ,
   no_d_id  integer   not null,
   no_o_id  integer   not null,
   PRIMARY KEY (no_w_id, no_d_id, no_o_id)
   )tablegroup=tp