基于BenchmarkSQL的OceanBase数据库tpcc性能测试

原创已于 2025-05-15 19:21:05 修改 · 934 阅读

27 ·

CC 4.0 BY-SA版权

文章标签：

#oceanbase #数据库

于 2025-04-27 10:23:00 首次发布

OceanBase进阶篇专栏收录该内容

5 篇文章

订阅专栏

基于BenchmarkSQL的OceanBase数据库tpcc性能测试

安装BenchmarkSQL及其依赖
- 安装软件依赖
- 编译BenchmarkSQL
BenchmarkSQL props文件配置
数据库和测试表配置
BenchmarkSQL压测
重复测试流程梳理

安装BenchmarkSQL及其依赖

软件包	作用
benchmarksql-5.0.zip	tpcc性能测试
R-3.6.3.tar.gz	生成压测报告
mysql-connector-java-5.1.47	MySQL连接驱动

操作系统：

[root@localhost ~]# nkvers
############## Kylin Linux Version #################
Release:
Kylin Linux Advanced Server release V10 (Sword)

Kernel:
4.19.90-25.16.v2101.ky10.x86_64

Build:
Kylin Linux Advanced Server
release V10 (SP2) /(Sword)-x86_64-Build09/20210524
#################################################

安装软件依赖

检查JDK版本是否为1.8：

java -version

安装依赖软件包：

yum install -y glibc-common
yum install gcc glibc-headers gcc-c++ gcc-gfortran readline-devel libXt-devel pcre-devel libcurl libcurl-devel -y
yum install ncurses ncurses-devel autoconf automake zlib zlib-devel bzip2 bzip2-devel xz-devel -y

yum install -y numactl

报错信息：

[FAILED] glibc-common-2.28-49.p16.ky10.x86_64.rpm: No more mirrors to try - All mirrors were already tried without success                                                                                                                                                                                                 

The downloaded packages were saved in cache until the next successful transaction.
You can remove cached packages by executing 'yum clean packages'.
Error: Error downloading packages:
  Cannot download Packages/glibc-common-2.28-49.p16.ky10.x86_64.rpm: All mirrors were tried
  
#解决办法
yum install -y glibc-common

编译安装R语言：

yum install pango-devel pango libpng-devel cairo cairo-devel -y

tar -zxf R-3.6.3.tar.gz -C /opt
cd /opt/R-3.6.3
./configure 
make && make install

检查安装情况：

[root@primarydb benchmarksql]# R --version
R version 3.6.3 (2020-02-29) -- "Holding the Windsock"
Copyright (C) 2020 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)

安装ant工具：

yum install -y ant
ant -version

ant命令报错:

ant -version

#报错信息
Unable to locate tools.jar. Expected to find it in /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-1.h5.ky10.x86_64/lib/tools.jar

#解决办法
yum clean packages
yum install java-1.8.0-openjdk-devel -y

编译BenchmarkSQL

解压安装包：

[root@primarydb ~]# unzip -qo benchmarksql-5.0.zip -d /opt
[root@primarydb ~]# cd /opt/benchmarksql-5.0/

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

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

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

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

使用ant编译BenchmarkSQL：

[root@zcs024kvm benchmarksql-5.0]# ant
Buildfile: /opt/benchmarksql-5.0/build.xml

init:
    [mkdir] Created dir: /opt/benchmarksql-5.0/build

compile:
    [javac] Compiling 11 source files to /opt/benchmarksql-5.0/build

dist:
    [mkdir] Created dir: /opt/benchmarksql-5.0/dist
      [jar] Building jar: /opt/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar

BUILD SUCCESSFUL
Total time: 1 second

修改文件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

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

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

拷贝MySQL连接驱动。

mkdir benchmarksql-5.0/lib/oceanbase

unzip -qo mysql-connector-java-5.1.47.zip 
cp mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar benchmarksql-5.0/lib/oceanbase/

BenchmarkSQL props文件配置

benchmarksql-5.0/run/props.xxx是使用BenchmarkSQL进行性能测试的主要配置文件。

创建配置文件props.ob：

db=oceanbase
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://127.0.0.1:2881/tpccdb?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true
user=root@tpcc
password=******

warehouses=656
loadWorkers=80
//fileLocation=/data/temp/

terminals=100
//To run specified transactions per terminal- runMins must equal zero
runTxnsPerTerminal=0
//To run for specified minutes- runTxnsPerTerminal must equal zero
runMins=60
//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部署端口，其他部分保持和模板一致。tpccdb是数据库名。
user & password：根据环境中使用的用户名、租户名以及密码即可。如果环境中有多个OceanBase集群，则user的格式建议为{user_name}@{tenant_name}#{cluster_name}。
warehouses：指定仓库数，仓库数决定性能测试的成绩。如果希望针对多节点的OceanBase集群进行测试，建议选择1000仓以上。如果机器配置有限，可以选择100仓进行测试。每个仓库初始大小约100M。建议测试数据量为数据库服务器物理内存的2到5倍大小。
loadWorkers：指定仓库数据加载时的并发。如果机器配置较高，该值可以设置大一些，例如100。如果机器配置有限，该值需要设置小一些，如10并发。过高的并发可能会导致内存消耗太快，出现报错，导致数据加载需要重新进行。建议配置为CPU核数。
terminals：指定性能压测时的并发数。建议并发数不要高于仓库数 * 10，否则会有不必要的锁等待。在生产环境中，建议将此参数设置为最多1000。在测试环境中，建议从100开始。建议配置为CPU核数的2到6倍大小。
runMins：指定性能测试持续的时间。时间越久，越能考验数据库的性能和稳定性。建议不要少于10分钟，生产环境中机器建议不少于1小时。

数据库和测试表配置

创建测试租户tpcc（参考OceanBase租户创建）。

TPCC测试租户必须是MySQL兼容类型（可以细分选择兼容MySQL5.7或MySQL8.0）。

在测试租户tpcc中，创建测试数据库tpccdb。

SQL> CREATE DATABASE tpccdb;

测试数据库连接：

mysql -h127.0.0.1 -P2881 -uroot@tpcc -p****** -A
obclient -h127.0.0.1 -P2881 -uroot@tpcc -p****** -A

创建测试表结构。

建表脚本如下，采用分区表方式创建，大部分表按照仓库ID做HASH分区。分区数取决于要测试的数据规模和机器数。如果集群只有1台或3台机器，分区数设置9个即可。如果是5000仓以上，或者集群中节点数较多，则分区数可以调整到99。

修改/opt/benchmarksql-5.0/run/sql.common/tableCreates_parts.sql。

CREATE TABLE bmsql_config (
cfg_name    varchar(30) primary key,
cfg_value   varchar(50)
);

-- drop tablegroup tpcc_group;
CREATE TABLEGROUP tpcc_group binding true partition by hash partitions 9;

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' partition by hash(w_id) partitions 9;

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' partition by hash(d_w_id) partitions 9;

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' partition by hash(c_w_id) partitions 9;


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' partition by hash(h_w_id) partitions 9;

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='tpcc_group' partition by hash(no_w_id) partitions 9;

CREATE TABLE bmsql_oorder (
   o_w_id       integer      not null,
   o_d_id       integer      not null,
   o_id         integer      not null,
   o_c_id       integer,
   o_carrier_id integer,
   o_ol_cnt     integer,
   o_all_local  integer,
   o_entry_d    timestamp,
   PRIMARY KEY (o_w_id, o_d_id, o_id)
)tablegroup='tpcc_group' partition by hash(o_w_id) partitions 9;

CREATE TABLE bmsql_order_line (
   ol_w_id         integer   not null,
   ol_d_id         integer   not null,
   ol_o_id         integer   not null,
   ol_number       integer   not null,
   ol_i_id         integer   not null,
   ol_delivery_d   timestamp,
   ol_amount       decimal(6,2),
   ol_supply_w_id  integer,
   ol_quantity     integer,
   ol_dist_info    char(24),
   PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
)tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 9;

CREATE TABLE bmsql_item (
   i_id     integer      not null,
   i_name   varchar(24),
   i_price  decimal(5,2),
   i_data   varchar(50),
   i_im_id  integer,
   PRIMARY KEY (i_id)
);

CREATE TABLE bmsql_stock (
   s_w_id       integer       not null,
   s_i_id       integer       not null,
   s_quantity   integer,
   s_ytd        integer,
   s_order_cnt  integer,
   s_remote_cnt integer,
   s_data       varchar(50),
   s_dist_01    char(24),
   s_dist_02    char(24),
   s_dist_03    char(24),
   s_dist_04    char(24),
   s_dist_05    char(24),
   s_dist_06    char(24),
   s_dist_07    char(24),
   s_dist_08    char(24),
   s_dist_09    char(24),
   s_dist_10    char(24),
   PRIMARY KEY (s_w_id, s_i_id)
)tablegroup='tpcc_group' use_bloom_filter=true partition by hash(s_w_id) partitions 9;

注意创建TABLEGROUP需要高权限用户。

BenchmarkSQL压测

装载测试数据

执行脚本创建测试表。

cd /opt/benchmarksql-5.0/run/
./runSQL.sh props.ob sql.common/tableCreates_parts.sql

执行脚本加载测试数据。

./runLoader.sh props.ob

加载数据时间会比较长，可以nohup后台执行：

nohup sh /opt/benchmarksql-5.0/run/runLoader.sh /opt/benchmarksql-5.0/run/props.ob > /home/obadmin/nohup.log 2>&1 &

tail -fn100 /home/obadmin/nohup.log

mv /home/obadmin/nohup.log /home/obadmin/runLoader.656whouses.log

数据加载完成后，手动创建两个索引。

[root@primarydb run]# cat sql.common/indexCreates.sql
CREATE INDEX bmsql_customer_idx1
  on  bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;

CREATE INDEX bmsql_oorder_idx1
  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

手动做一次Major Freeze。

📖 在开始性能测试之前，建议您先登录到对应租户做一次集群合并（major freeze），获得更好的测试结果。您可以通过如下的方式手动触发合并，这个过程并不是必须的。

obclient[oceanbase]> ALTER SYSTEM MAJOR FREEZE;

当看到如下查询返回IDLE时，表示合并完成。

MySQL [oceanbase]> SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION;
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
| TENANT_ID | ZONE  | BROADCAST_SCN       | LAST_SCN            | LAST_FINISH_TIME           | START_TIME                 | STATUS |
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
|         1 | zone1 | 1664503499339325817 | 1664503499339325817 | 2022-09-30 10:05:19.442115 | 2022-09-30 10:04:59.369976 | IDLE   |
|      1002 | zone1 |                   1 |                   1 | 1970-01-01 08:00:00.000000 | 1970-01-01 08:00:00.000000 | IDLE   |
+-----------+-------+---------------------+---------------------+----------------------------+----------------------------+--------+
2 rows in set

TPC-C压测（固定事务数量）

修改props.ob中的测试模式为固定事务数量：

runTxnsPerTerminal=10
runMins=0

使用runBenchmark.sh脚本进行测试：

[root@primarydb run]# ./runBenchmark.sh props.ob

执行事务的总数等于terminals与runTxnsPerTerminal的乘积。

后台执行：

nohup sh /opt/benchmarksql-5.0/run/runBenchmark.sh /opt/benchmarksql-5.0/run/props.ob > /home/obadmin/nohup.log 2>&1 &

tail -fn100 /home/obadmin/nohup.log

mv /home/obadmin/nohup.log /home/obadmin/runBenchmark.656whouses.log

TPC-C压测（固定时长）

修改props.ob中的测试模式为固定时长：

runTxnsPerTerminal=0
runMins=60

生成测试报告

使用runBenchmark.sh脚本来生成测试报告（需要安装R语言）：

[root@primarydb run]# ls my_result_2025-04-21_171901/
data  run.properties

[root@primarydb run]# ./generateReport.sh my_result_2025-04-21_171901/

[root@primarydb run]# ls my_result_2025-04-21_171901/
blk_vdb_iops.png  cpu_utilization.png  dirty_buffers.png  net_eth0_iops.png  report.html     tpm_nopm.png
blk_vdb_kbps.png  data                 latency.png        net_eth0_kbps.png  run.properties

[root@primarydb run]# zip -r ob_tpcc_0421_01.zip my_result_2025-04-21_171901/

其中，report.html是生成的测试报告，png文件是报告中包含的图片。

销毁测试数据后重新加载测试：

[root@primarydb run]# ./runDatabaseDestroy.sh props.ob
[root@primarydb run]# ./runSQL.sh props.ob sql.common/tableCreates_parts.sql
[root@primarydb run]# ./runLoader.sh props.ob
# 添加索引：sql.common/indexCreates.sql
[root@primarydb run]# ./runBenchmark.sh props.ob

重复测试流程梳理

📖总结一下，多次测试的流程如下：

##### 0. DESTROY DATA

cd /opt/benchmarksql-5.0/run/
./runDatabaseDestroy.sh props.ob.656whouses

##### 1. CREATE TABLE

./runSQL.sh props.ob.656whouses sql.common/tableCreates_parts.sql

##### 2. LOAD DATA

nohup sh /opt/benchmarksql-5.0/run/runLoader.sh /opt/benchmarksql-5.0/run/props.ob.656whouses > /home/obadmin/nohup.log 2>&1 &

chmod 644 /home/obadmin/nohup.log
tail -fn100 /home/obadmin/nohup.log

cp /home/obadmin/nohup.log /home/obadmin/runLoader.656whouses.log

##### 3. CREATE INDEX

use tpccdb;

CREATE INDEX bmsql_customer_idx1
  on  bmsql_customer (c_w_id, c_d_id, c_last, c_first) local;

CREATE INDEX bmsql_oorder_idx1
  on  bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;

##### 4. NAJOR FREEZE

select database_name,table_id,table_name,round(occupy_size/1024/1024,3) occupy_size_mb, 
round(required_size/1024/1024,3) required_size_mb
from oceanbase.DBA_OB_TABLE_SPACE_USAGE where database_name='tpccdb';

ALTER SYSTEM MAJOR FREEZE;
SELECT * FROM oceanbase.CDB_OB_ZONE_MAJOR_COMPACTION;

##### 5. RUN BENCHMARK

nohup sh /opt/benchmarksql-5.0/run/runBenchmark.sh /opt/benchmarksql-5.0/run/props.ob.656whouses > /home/obadmin/nohup.log 2>&1 &

chmod 644 /home/obadmin/nohup.log
tail -fn100 /home/obadmin/nohup.log

cp /home/obadmin/nohup.log /home/obadmin/runBenchmark.656whouses.log

##### 6. GENERATE REPORT

cd /opt/benchmarksql-5.0/run/
./generateReport.sh my_result_2025-04-21_171901/
zip -r ob_tpcc_0421_01.zip my_result_2025-04-21_171901/

以上。