作者简介：马顺华，从事运维管理工作多年，目前就职于六棱镜（杭州）科技有限公司，熟悉运维自动化、OceanBase 部署运维、MySQL 运维以及各种云平台技术和产品。并已获得 OceanBase 认证 OBCA 、OBCP 证书。
前言
网络上测试 OceanBase 性能的方法有很多，不过如果想对比 OceanBase 数据库跟统数据库、其它分布式数据库产品的性能差异，还需要找到一个适用的测试方案。BenchmarkSQL 内嵌了 TPC-C 测试脚本，也支持很多数据库，如 PostgreSQL 、Oracle 和 MySQL 等。本文章向大家介绍使用压测工具 BenchmarkSQL 对 OceanBase 开源版3.1.2性能测试调优，主要包括 BenchmarkSQL 数据库基准测试工具使用、应用技巧、基本知识点总结和需要注意事项，需要的同学可以参考一下。
测试准备
- 先安装 JDK、Ant、Benchmark SQL、OceanBase 数据库与 OBClient ，
- 本次测试采用 OBD 部署 OBServer 集群，集群部署规模为1-1-1安装方法与步骤按照官方文档或之前发表文章执行，
https://www.modb.pro/db/324460 使用 OBD 自动部署三节点 OceanBase 文档
- 修改 BenchMarkSQL5 部分源码，修改及编译构建方法按照官方文档和网络搜索执行，
- 测试目标：创建测试租户 Tenanttpcc、测试用户 TPCC。
机器信息
机器角色划分
安装环境部署版本
一、服务器初始化设置
1、安装 Openjdk
首先安装 Java 开发环境，本文涉及到的操作在 Java 1.8.0 环境下测试通过。
[root@CAIP131 ~]# yum install java-1.8.0-openjdk -y
[root@CAIP131 ~]# yum install java-1.8.0-openjdk-devel.x86_64 -y
检查 Java 是否安装好
[root@CAIP131 ~]# java -version java version "1.8.0_201" Java(TM) SE Runtime Environment (build 1.8.0_201-b09) Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
2、安装 Apache的 Ant 工具。
Apache Ant ,是一个将软件编译、测试、部署等步骤联系在一起加以自动化的一个工具。用于编译 Benchmark SQL。
配置 Apache-Ant 的环境变量：
[root@CAIP131 ant-1.9.4]# vim ~/.bash_profile
#在脚本最后处添加以下内容
export APACH_HOME=/usr/share/doc/ant-1.9.4
export PATH=${ANT_HOME}/bin:$PATH
设置完之后，如果要使环境变量立即生效，需要通过输入命令：source /etc/profile，重新加载配置文件。最后，通过 Ant --version 查看安装的 Ant 版本，验证安装成功。
[root@CAIP131 ~]# source ~/.bash_profile [root@CAIP131 ~]# ant -version Apache Ant(TM) version 1.9.4 compiled on November 5 2018
到这里，Ant 已经安装完成，可以开始使用了！
3、安装 Benchmark SQL
按照以下步骤安装 Benchmark SQL：
在 sourceforge 网站下载 Benchmark SQL
1.下载方法一 https://sourceforge.net/projects/benchmarksql/
下载二方法 https://github.com/obpilot/benchmarksql-5.0
下载方法三 通过 GIT 下载
[root@CAIP131 opt]# git clone https://github.com/obpilot/benchmarksql-5.0.git
2.将下载（BenchmarkSQL-5.0.zip）的 zip 包，上传到测试服务器。
3.解压 Benchmark SQL。
[root@CAIP131 opt]# unzip benchmarksql-5.0.zip
进入目录
[root@CAIP131 opt]# cd benchmarksql-5.0/ [root@CAIP131 benchmarksql-5.0]# ls build.xml doc HOW-TO-RUN.txt lib README.md run src
4、准备 OceanBase 驱动文件
下载 JDBC 驱动，BenchmarkSQL 是通过 JDBC 连接各个数据库的。此次 OceanBase 的测试租户是 MySQL 类型，所以需要把相关 Jar 包一并放入其中。
https://help.aliyun.com/document_detail/212815.html
[root@CAIP131 lib] /opt/benchmarksql-5.0/lib
将下载好的 Oceanbase - Client-1.1.10.Jar 驱动文件复制到 lib 目录 BenchmarkSQL-5.0/lib/OceanBase-client-1.1.10.Jar；经过上面的操作，BenchmarkSQL 便可以找到 JDBC 驱动了。 
编译 BenchmarkSQL -5.0工具，生成 Jar文件（用于 MySQL 类型、OceanBase 类型略过该步骤）
[root@CAIP131 benchmarksql-5.0]# ant
Buildfile: /opt/benchmarksql-5.0/build.xml
init:
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
二、资源池-租户-用户-创建及测试
测试目标：创建测试租户 Tenanttpcc、测试用户 TPCC
1、查询系统资源总计资源( SYS 租户登录)
[admin@CAIP131 ~]$ obclient -h172.20.2.120 -uroot@sys#obce-demo -pPwd2012# -P2883 -A -c oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]>
1）查询资源
MySQL [oceanbase]> SELECT svr_ip,svr_port, cpu_total, mem_total/1024/1024/1024, disk_total/1024/1024/1024, zone FROM __all_virtual_server_stat; +--------------+----------+-----------+--------------------------+---------------------------+-------+ | svr_ip | svr_port | cpu_total | mem_total/1024/1024/1024 | disk_total/1024/1024/1024 | zone | +--------------+----------+-----------+--------------------------+---------------------------+-------+ | 172.20.2.120 | 2882 | 14 | 20.000000000000 | 50.000000000000 | zone1 | | 172.20.2.121 | 2882 | 14 | 20.000000000000 | 50.000000000000 | zone2 | | 172.20.2.122 | 2882 | 14 | 20.000000000000 | 50.000000000000 | zone3 | +--------------+----------+-----------+--------------------------+---------------------------+-------+ 3 rows in set (0.007 sec)
2）查询租户已分配资源：
MySQL [oceanbase]> SELECT sum(c.max_cpu), sum(c.max_memory)/1024/1024/1024 FROM __all_resource_pool as a, __all_unit_config AS c WHERE a.unit_config_id=c.unit_config_id; +----------------+----------------------------------+ | sum(c.max_cpu) | sum(c.max_memory)/1024/1024/1024 | +----------------+----------------------------------+ | 5 | 6.000000000000 | +----------------+----------------------------------+ 1 row in set (0.010 sec) MySQL [oceanbase]>
2、创建普通测试租户：Unittpcc
1）创建资源单元（4c/8g/50G）：UNIT=unittpcc
MySQL [oceanbase]> CREATE RESOURCE UNIT unittpcc max_cpu = 4, max_memory = '8G', min_memory = '8G', max_iops = 100000, min_iops = 100000, max_session_num = 30000, max_disk_size = '50G'; Query OK, 0 rows affected (0.011 sec)
2）查看新创建的资源单元：unittpcc
MySQL [oceanbase]> SELECT unit_config_id,name,max_cpu,min_cpu,max_memory,min_memory,max_disk_size FROM __all_unit_config; +----------------+-----------------+---------+---------+------------+------------+---------------+ | unit_config_id | name | max_cpu | min_cpu | max_memory | min_memory | max_disk_size | +----------------+-----------------+---------+---------+------------+------------+---------------+ | 1 | sys_unit_config | 5 | 2.5 | 6442450944 | 5368709120 | 53687091200 | | 1003 | unittpcc | 4 | 4 | 8589934592 | 8589934592 | 53687091200 | +----------------+-----------------+---------+---------+------------+------------+---------------+ 2 rows in set (0.002 sec)
3）创建资源池：POOL=pooltpcc
4）创建租户：tenant=tenanttpcc
MySQL [oceanbase]> create tenant tenanttpcc resource_pool_list=('pooltpcc'), charset=utf8mb4, replica_num=3, zone_list('zone1', 'zone2', 'zone3'), primary_zone=RANDOM, locality='F@zone1,F@zone2,F@zone3' set variables ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%';
Query OK, 0 rows affected (1.687 sec)
5）查看创建成功的租户：Tenanttpcc
MySQL [oceanbase]> select tenant_id,tenant_name,primary_zone from __all_tenant; +-----------+-------------+-------------------+ | tenant_id | tenant_name | primary_zone | +-----------+-------------+-------------------+ | 1 | sys | zone1;zone2,zone3 | | 1003 | tenanttpcc | RANDOM | +-----------+-------------+-------------------+ 2 rows in set (0.001 sec)
3、配置用户
1）登录新创建的 Tenanttpcc 租户（默认租户密码为空）：
[admin@CAIP131 ~]$ obclient -h172.20.2.120 -uroot@tenanttpcc#obce-demo -P2883 -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]>
2）创建用户并设置密码：TPCC
MySQL [oceanbase]> CREATE USER 'tpcc' IDENTIFIED BY '123456#'; Query OK, 0 rows affected (0.059 sec) MySQL [oceanbase]>
3）查看创建成功的用户：TPCC
MySQL [oceanbase]> SELECT user FROM mysql.user; +------------+ | user | +------------+ | root | | ORAAUDITOR | | tpcc | +------------+ 3 rows in set (0.050 sec)
4）设置用户 TPCC 的授权
MySQL [oceanbase]> grant all on *.* to 'tpcc' WITH GRANT OPTION; Query OK, 0 rows affected (0.023 sec)
5）查看用户授权
MySQL [oceanbase]> show grants for tpcc; +--------------------------------------------------------------+ | Grants for tpcc@% | +--------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'tpcc' WITH GRANT OPTION | | GRANT ALL PRIVILEGES ON `tpcc`.* TO 'tpcc' WITH GRANT OPTION | +--------------------------------------------------------------+ 2 rows in set (0.008 sec)
6）新用户 TPCC 登录测试
[admin@CAIP131 ~]$ obclient -h172.20.2.120 -utpcc@tenanttpcc#obce-demo -P2883 -p123456# -c -A oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]>
三、OBServer
性能优化参数修改
1、内存参数调优
BenchmarkSQL会加载大量数据，短时间内对OceanBase内存消耗速度会很快，因此需要针对内存冻结合并和限流参数做一些调优。在sys租户执行：
obclient -h172.20.2.120 -uroot@sys#obce-demo -pPwd2012# -P2883 -A -c oceanbase
MySQL [oceanbase]> ALTER SYSTEM SET enable_merge_by_turn=FALSE;
duration='10m' tenant='tenanttpcc';
show parameters where name in ('minor_freeze_times','freeze_trigger_percentage');Query OK, 0 rows affected (0.065 sec)
MySQL [oceanbase]> ALTER SYSTEM set minor_freeze_times=100;
Query OK, 0 rows affected (0.042 sec)
MySQL [oceanbase]> ALTER SYSTEM set freeze_trigger_percentage=70;
Query OK, 0 rows affected (0.039 sec)
MySQL [oceanbase]> ALTER SYSTEM set writing_throttling_trigger_percentage=70 tenant='tenanttpcc';
Query OK, 0 rows affected (0.024 sec)
MySQL [oceanbase]> ALTER SYSTEM set writing_throttling_maximum_duration='10m' tenant='tenanttpcc';
Query OK, 0 rows affected (0.013 sec)
MySQL [oceanbase]> show parameters where name in ('minor_freeze_times','freeze_trigger_percentage');
+-------+----------+--------------+----------+---------------------------+-----------+-------+---------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+--------------+----------+---------------------------+-----------+-------+---------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
| zone2 | observer | 172.20.2.121 | 2882 | minor_freeze_times | NULL | 100 | specifies how many minor freezes should be triggered between two major freezes. Range: [0, 65535] | TENANT | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 172.20.2.121 | 2882 | freeze_trigger_percentage | NULL | 70 | the threshold of the size of the mem store when freeze will be triggered. Range: (0, 100) | TENANT | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 172.20.2.122 | 2882 | minor_freeze_times | NULL | 100 | specifies how many minor freezes should be triggered between two major freezes. Range: [0, 65535] | TENANT | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 172.20.2.122 | 2882 | freeze_trigger_percentage | NULL | 70 | the threshold of the size of the mem store when freeze will be triggered. Range: (0, 100) | TENANT | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 172.20.2.120 | 2882 | minor_freeze_times | NULL | 100 | specifies how many minor freezes should be triggered between two major freezes. Range: [0, 65535] | TENANT | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 172.20.2.120 | 2882 | freeze_trigger_percentage | NULL | 70 | the threshold of the size of the mem store when freeze will be triggered. Range: (0, 100) | TENANT | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+---------------------------+-----------+-------+---------------------------------------------------------------------------------------------------+---------+---------+---------+-------------------+
6 rows in set (0.016 sec)
注意：业务租户限流参数的修改是在 SYS 租户里，需要指定相应的租户名例：Tenant='Tenanttpcc'。
2、租户调优
注意：业务租户限流参数的修改是在 SYS 租户里，需要指定相应的租户名。然后查看确认需要到业务租户里。
在业务租户执行：
obclient -h172.20.2.120 -utpcc@tenanttpcc#obce-demo -P2883 -p123456# -c -A
MySQL [(none)]> SHOW parameters WHERE name IN ('writing_throttling_trigger_percentage','writing_throttling_maximum_duration');
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
| zone1 | observer | 172.20.2.120 | 2882 | writing_throttling_maximum_duration | NULL | 10m | maximum duration of writing throttling(in minutes), max value is 3 days | TRANS | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 172.20.2.120 | 2882 | writing_throttling_trigger_percentage | NULL | 70 | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0, 100]. setting 100 means turn off writing limit | TRANS | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 172.20.2.121 | 2882 | writing_throttling_maximum_duration | NULL | 10m | maximum duration of writing throttling(in minutes), max value is 3 days | TRANS | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 172.20.2.121 | 2882 | writing_throttling_trigger_percentage | NULL | 70 | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0, 100]. setting 100 means turn off writing limit | TRANS | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 172.20.2.122 | 2882 | writing_throttling_maximum_duration | NULL | 10m | maximum duration of writing throttling(in minutes), max value is 3 days | TRANS | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 172.20.2.122 | 2882 | writing_throttling_trigger_percentage | NULL | 70 | the threshold of the size of the mem store when writing_limit will be triggered. Rang:(0, 100]. setting 100 means turn off writing limit | TRANS | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+---------------------------------------+-----------+-------+------------------------------------------------------------------------------------------------------------------------------------------+---------+--------+---------+-------------------+
6 rows in set (0.052 sec)
3、业务租户参数修改
OceanBase 跟 Oracle/MySQL 相比，会有个默认 SQL 超时和事务超时机制。这个可能会导致后面查看修改数据的 SQL 报错。所以先修改一下这些参数。
MySQL [(none)]> set global recyclebin=off; Query OK, 0 rows affected (0.005 sec) MySQL [(none)]> set global ob_query_timeout=1000000000; Query OK, 0 rows affected (0.035 sec) MySQL [(none)]> set global ob_trx_idle_timeout=1200000000; Query OK, 0 rows affected (0.031 sec) MySQL [(none)]> set global ob_trx_timeout=1000000000; Query OK, 0 rows affected (0.045 sec)
4、配置 SQL 审计
查看SQL审计开关 MySQL [(none)]> show variables like 'ob_enable_sql_audit'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | ob_enable_sql_audit | ON | +---------------------+-------+ 1 row in set (0.003 sec) MySQL [(none)]>
开启 SQL 审计
MySQL [(none)]> set global ob_enable_sql_audit = on; Query OK, 0 rows affected (0.004 sec)
5、创建测试数据库
obclient -h172.20.2.120 -utpcc@tenanttpcc#obce-demo -P2883 -p123456# -c -A MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | +--------------------+ 4 rows in set (0.010 sec) MySQL [(none)]> create database tpcc; Query OK, 1 row affected (0.041 sec) MySQL [(none)]> use tpcc; Database changed MySQL [tpcc]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | | tpcc | +--------------------+ 5 rows in set (0.008 sec) MySQL [tpcc]> show tables; Empty set (0.004 sec)
6、OBProxy 性能优化
OBProxy 是 OceanBase 的访问代理，其内部一些参数也可能影响性能。( SYS 租户登录)
[root@CAIP131 run]# obclient -h172.20.2.120 -uroot@sys#obce-demo -pPwd2012# -P2883 -A -c oceanbase Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 25 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]>
OBProxy 配置修改，如下面的压缩参数对 CPU 有一定消耗，测试时可以关闭。
MySQL [oceanbase]> alter proxyconfig set enable_compression_protocol=False; Query OK, 0 rows affected (0.003 sec) MySQL [oceanbase]> show proxyconfig like 'enable_compression_protocol'; +-----------------------------+-------+-------------------------------------------------------------+-------------+---------------+ | name | value | info | need_reboot | visible_level | +-----------------------------+-------+-------------------------------------------------------------+-------------+---------------+ | enable_compression_protocol | False | if enabled, proxy will use compression protocol with server | false | USER | +-----------------------------+-------+-------------------------------------------------------------+-------------+---------------+ 1 row in set (0.001 sec)
该参数修改后，需要重启 OBProxy 进程（切换 Admin 用户执行）
[admin@CAIP131 ~]$ obd cluster restart obce-demo -c obproxy Get local repositories and plugins ok Open ssh connection ok Stop obproxy ok succeed Get local repositories and plugins ok Open ssh connection ok Load cluster param plugin ok Cluster status check ok Check before start obproxy ok Start obproxy ok obproxy program health check ok Connect to obproxy ok +------------------------------------------------+ | obproxy | +--------------+------+-----------------+--------+ | ip | port | prometheus_port | status | +--------------+------+-----------------+--------+ | 172.20.2.120 | 2883 | 2884 | active | | 172.20.2.121 | 2883 | 2884 | active | | 172.20.2.122 | 2883 | 2884 | active | +--------------+------+-----------------+--------+ succeed
本次集群是通过 OBD 部署集群， OBD 重启集群的时候，默认重启了所有组件（包括 OBSERVER 和 OBPROXY ）。所以通过 -c 命令指定重启具体的组件OBProxy。
四、配置 OceanBase 测试参数文件
编辑初始化配置，编辑 /root/benchmarksql-5.0/run/props.ob
cd run/ [root@CAIP131 run]# ls funcs.sh log4j.properties props.ob runBenchmark.sh runLoader.sh sql.firebird sql.postgres generateGraphs.sh misc props.ora runDatabaseBuild.sh runSQL.sh sql.oceanbase generateReport.sh props.fb props.pg runDatabaseDestroy.sh sql.common sql.oracle
Vim prop.ob 修改文件夹内创建 prop.ob 文件，编辑后的内容如下:
集群格式：TPCC@Tenanttpcc#obce-demo
[root@CAIP131 run]# vim props.ob
原 prop.ob 文件
db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@oracle0_85#obv22_stable password=123456 warehouses=2 loadWorkers=2 terminals=2 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=1 //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
修改后的 prop.ob 文件
db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://172.20.2.120:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@tenanttpcc#obce-demo password=123456# warehouses=10 loadWorkers=10 terminals=10 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal runMins=10 //Number of total transactions per minute limitTxnsPerMin=10 //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 osCollectorInterval1 ~
此次 OceanBase 的测试租户是 Oracle 类型，需要修改 DB 类型为 Oracle
注意：
a. 仓库数( warehouses )决定了数据量。正式的压测仓库数一般在10000以上。
b. loadworkers 数决定了数据加载的性能。如果 OceanBase 租户资源很小(尤其是内存资源)，那加载速度也不要太快；否则容易把租户内存打爆。
c. 并发数 ( terminals ) 是后期做 TPC-C 测试的客户端并发数。这个每次测试都可以调整，以方便观察不同压力下的性能。
d. 压测时间 ( runMin ) 是每次测试时间，越长测试结果越好且稳定。因为有时候数据访问有个预热过程，效果会体现在内存命中率上。
e. runMins 和 runTxnsPerTerminal 这两个参数指定了两种运行方式，前者是按照指定运行时间执行，以时间为标准；后者以指定每个终端的事务数为标准执行。两者不能同时生效，必须有一个设定为0。
五、创建 BenchmarkSQL 相关表
1、配置脚本
目录下有脚本，直接修改 tableCreate.sql 脚本，该 SQL 脚本不需要直接执行。
1）进入 BenchmarkSQL 客户端目录
默认 Benchmarksql-5.0/run/sql.oceanbase 目录
[root@CAIP131 run]# cd sql.oceanbase/ [root@CAIP131 sql.oceanbase]# ls indexCreates.sql tableCreates3.sql tableCreates.sqlls
2）修改 tableCreate.sql：
[root@CAIP131 sql.oceanbase]# vim tableCreates.sql
create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); 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) ); 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) ); 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) ); create sequence bmsql_hist_id_seq; 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) ); create table bmsql_new_order ( no_w_id integer not null, no_d_id integer not null, no_o_id integer not null ); 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 ); 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) ); 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 ); 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) );
a. 建表语句中的分区数目可以根据实际情况调整，跟集群节点数有关。如果集群是3台(1-1-1)，建议是6个或6的倍数；这样方便后期弹性伸缩测试的时候能尽可能保证每个节点上的分区数均衡。
b. 上面 bmsql_item 使用了【复制表】功能，在租户的所有节点上都会有一个副本。当然主副本始终只有一个。
c. 建表语句不包含非主键索引，是为了后面加载数据性能更快。
2、建表：在 run 目录执行
[root@CAIP131 run]# ./runSQL.sh props.ob ./sql.oceanbase/tableCreates.sql # ------------------------------------------------------------ # Loading SQL file ./sql.oceanbase/tableCreates.sql # ------------------------------------------------------------ create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); 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) ); 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) ); 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) ); create sequence bmsql_hist_id_seq; You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'sequence bmsql_hist_id_seq' at line 1 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) ); create table bmsql_new_order ( no_w_id integer not null, no_d_id integer not null, no_o_id integer not null ); 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 ); 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) ); 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 ); 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) );
3、查看建好的表( TPCC 用户查看)
obclient -h172.20.2.120 -utpcc@tenanttpcc#obce-demo -P2883 -p123456# -c -A tpcc MySQL [tpcc]> show tables; +------------------+ | Tables_in_tpcc | +------------------+ | 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.006 sec)
4、加载数据
1）开始加载数据
[root@CAIP131 run]# ./runLoader.sh props.ob Starting BenchmarkSQL LoadData driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://172.20.2.120:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@tenanttpcc#obce-demo password=*********** warehouses=10 loadWorkers=10 fileLocation (not defined) csvNullValue (not defined - using default 'NULL') Worker 000: Loading ITEM Worker 001: Loading Warehouse 1 Worker 002: Loading Warehouse 2 Worker 003: Loading Warehouse 3 Worker 004: Loading Warehouse 4 Worker 005: Loading Warehouse 5 Worker 006: Loading Warehouse 6 Worker 007: Loading Warehouse 7 Worker 008: Loading Warehouse 8 Worker 009: Loading Warehouse 9 Worker 000: Loading ITEM done Worker 000: Loading Warehouse 10 Worker 002: Loading Warehouse 2 done Worker 006: Loading Warehouse 6 done Worker 005: Loading Warehouse 5 done Worker 001: Loading Warehouse 1 done Worker 007: Loading Warehouse 7 done Worker 004: Loading Warehouse 4 done Worker 003: Loading Warehouse 3 done Worker 008: Loading Warehouse 8 done Worker 009: Loading Warehouse 9 done Worker 000: Loading Warehouse 10 done
此处运行时间比较长，需要等待
2）观察数据加载性能（ SYS 租户登录）
MySQL [oceanbase]> SELECT tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb , freeze_cnt , round((active/freeze_trigger),2) freeze_pct, round(total/mem_limit, 2) mem_usage FROM `gv$memstore` WHERE tenant_id IN (1001) ORDER BY tenant_id, ip; Empty set (0.007 sec)
为了对数据写入速度进行观察，可以在 SYS 租户下反复执行下面 SQL，主要是观察增量内存增速和增量内存总量，以及是否接近总增量内存限制。
3）观察租户整体性能
使用 OceanBase 自带的命令行监控脚本
Dooba 可以观察很方便实时观察 OceanBase 租户性能。
a)创建基本用户
在 SYS 租户创建一个只读的账户，能查看系统视图。不建议是 Root 账。（ SYS 租户登录）
[root@CAIP131 ~]# obclient -h172.20.2.120 -uroot@sys#obce-demo -pPwd2012# -P2883 -A -c oceanbase MySQL [oceanbase]> grant select on oceanbase.* to obtest identified by '123456';
b)创建 Python 脚本
dooba 脚本在 /home/admin/oceanbase/bin/ ，是 python 脚本
c)观察租户整体性能视图
[admin@CAIP120 bin]$ python dooba.py -h 172.20.2.120 -uobtest@sys#obce-demo -P2883 -p123456
Dooba 进去后，默认是 SYS 租户。按字母小写 'c' ，选择业务租户。按数字'1'查看帮助，数字'2'查看租户总览，数字'3'查看租户的机器性能信息，按 TAB 切换当前焦点，按字母小写 'd' 删除当前 TAB，按字母大写 R 恢复所有 TAB。总览里的 NET TAB 没有意义可以删除以节省屏幕空间。
OceanBase 的 SQL 诊断，建议关注 租户的
QPS（每秒 SQL 请求数，包括 SELECT 、INSERT、UPDATE、DELETE ）以及其 RT（SQL 执行耗时）、TPS（每秒事务数，跟 ORACLE 一致 ）以及其 RT（事务提交延时）。此外，关注这些指标在 OceanBase 集群节点上的性能信息。 OceanBase 集群的性能瓶颈不会首先在 IO ，而更容易在内存，其次是 CPU 。所以还需要关注 每秒内存的变化。具体是指增量内存的使用情况。
5、建索引
索引很少，就2条。由于相关表是分区表，可以建全局索引或者本地索引。我们建本地索引。
修改 benchmarksql/run/sql.mysql/indexCreates.sql
[root@CAIP131 sql.oceanbase]# pwd /root/soft/benchmarksql-5.0/run/sql.oceanbase [root@CAIP131 sql.oceanbase]# vim 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;
开始建索引。OceanBase 建索引很快就会返回，索引构建是异步的。
[root@CAIP131 run]# ./runSQL.sh props.ob ./sql.oceanbase/indexCreates.sql
6、数据校验
检查一下各个表的数据量（ TPCC 用户登录）
[root@CAIP131 run]# obclient -h172.20.2.120 -utpcc@tenanttpcc#obce-demo -P2883 -p123456# -c -A tpcc Welcome to the OceanBase. Commands end with ; or \g. Your MySQL connection id is 1496 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_CONFIG; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.021 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_WAREHOUSE; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.019 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_DISTRICT; +----------+ | count(*) | +----------+ | 100 | +----------+ 1 row in set (0.034 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_CUSTOMER; +----------+ | count(*) | +----------+ | 300000 | +----------+ 1 row in set (0.153 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_HISTORY; +----------+ | count(*) | +----------+ | 300000 | +----------+ 1 row in set (0.201 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_NEW_ORDER; +----------+ | count(*) | +----------+ | 90000 | +----------+ 1 row in set (0.078 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_OORDER; +----------+ | count(*) | +----------+ | 300000 | +----------+ 1 row in set (0.243 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_ORDER_LINE; +----------+ | count(*) | +----------+ | 2997520 | +----------+ 1 row in set (1.027 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_ITEM; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.075 sec) MySQL [tpcc]> select /*+ parallel(16) read_consistency(weak) */ count(*) from TPCC.BMSQL_STOCK; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.663 sec)
六、运行 BenchmarkSQL TPC-C 测试
1、OceanBase 内存冻结与合并
前面加载了大量数据，OceanBase 的增量都在内存中，需要做一次 major freeze 以释放增量内存。这个事件分两步。一是冻结操作，这个很快。二是合并操作，这个跟增量数据量有关，通常要几分钟或者几十分钟。每次重复测试的时候都建议做一次 Major freeze 事件以释放内存，弊端就是随后测试中内存数据访问又需要一个预热过程。
1）观察内存增量使用情况( SYS 租户)
[root@CAIP131 run]# obclient -h172.20.2.120 -uroot@sys#obce-demo -pPwd2012# -P2883 -A -c oceanbase
select tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb, freeze_cnt, round(total/mem_limit,2) total_pct
from `gv$memstore` where tenant_id>1001 order by tenant_id;
MySQL [oceanbase]> select tenant_id, ip, round(active/1024/1024) active_mb, round(total/1024/1024) total_mb, round(freeze_trigger/1024/1024) freeze_trg_mb, round(mem_limit/1024/1024) mem_limit_mb, freeze_cnt, round(total/mem_limit,2) total_pct
-> from `gv$memstore` where tenant_id>1001 order by tenant_id;
+-----------+--------------+-----------+----------+---------------+--------------+------------+-----------+
| tenant_id | ip | active_mb | total_mb | freeze_trg_mb | mem_limit_mb | freeze_cnt | total_pct |
+-----------+--------------+-----------+----------+---------------+--------------+------------+-----------+
| 1003 | 172.20.2.120 | 2839 | 2842 | 2867 | 4096 | 0 | 0.69 |
| 1003 | 172.20.2.121 | 136 | 138 | 2867 | 4096 | 1 | 0.03 |
| 1003 | 172.20.2.122 | 2821 | 2822 | 2867 | 4096 | 0 | 0.69 |
+-----------+--------------+-----------+----------+---------------+--------------+------------+-----------+
3 rows in set (0.025 sec)
2）发起内存 major freeze 事件
MySQL [oceanbase]> ALTER SYSTEM major freeze; Query OK, 0 rows affected (0.013 sec)
3） 观察合并进度
观察合并事件
SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip
FROM __all_rootservice_event_history
WHERE 1 = 1 AND module IN ('daily_merge')
ORDER BY gmt_create DESC
LIMIT 100;
MySQL [oceanbase]> SELECT DATE_FORMAT(gmt_create, '%b%d %H:%i:%s') gmt_create_ , module, event, name1, value1, name2, value2, rs_svr_ip
-> FROM __all_rootservice_event_history
-> WHERE 1 = 1 AND module IN ('daily_merge')
-> ORDER BY gmt_create DESC
-> LIMIT 100;
+----------------+-------------+----------------------+---------------+--------+--------------------------+-------------+--------------+
| gmt_create_ | module | event | name1 | value1 | name2 | value2 | rs_svr_ip |
+----------------+-------------+----------------------+---------------+--------+--------------------------+-------------+--------------+
| Apr05 16:40:52 | daily_merge | start_merge | zone | zone3 | global_broadcast_version | 3 | 172.20.2.120 |
| Apr05 16:40:52 | daily_merge | start_merge | zone | zone2 | global_broadcast_version | 3 | 172.20.2.120 |
| Apr05 16:40:52 | daily_merge | start_merge | zone | zone1 | global_broadcast_version | 3 | 172.20.2.120 |
| Apr05 16:40:52 | daily_merge | set_zone_merging | zone | zone3 | | | 172.20.2.120 |
| Apr05 16:40:52 | daily_merge | set_zone_merging | zone | zone2 | | | 172.20.2.120 |
| Apr05 16:40:52 | daily_merge | set_zone_merging | zone | zone1 | | | 172.20.2.120 |
| Apr05 16:40:52 | daily_merge | merging | merge_version | 3 | zone | global_zone | 172.20.2.120 |
| Apr05 13:51:07 | daily_merge | global_merged | version | 2 | | | 172.20.2.120 |
| Apr05 13:50:57 | daily_merge | all_partition_merged | merge_version | 2 | zone | zone2 | 172.20.2.120 |
| Apr05 13:50:57 | daily_merge | merge_succeed | merge_version | 2 | zone | zone2 | 172.20.2.120 |
| Apr05 13:50:47 | daily_merge | all_partition_merged | merge_version | 2 | zone | zone3 | 172.20.2.120 |
| Apr05 13:50:47 | daily_merge | merge_succeed | merge_version | 2 | zone | zone3 | 172.20.2.120 |
| Apr05 13:50:47 | daily_merge | all_partition_merged | merge_version | 2 | zone | zone1 | 172.20.2.120 |
| Apr05 13:50:47 | daily_merge | merge_succeed | merge_version | 2 | zone | zone1 | 172.20.2.120 |
| Apr05 13:50:04 | daily_merge | start_merge | zone | zone3 | global_broadcast_version | 2 | 172.20.2.120 |
| Apr05 13:50:04 | daily_merge | start_merge | zone | zone2 | global_broadcast_version | 2 | 172.20.2.120 |
| Apr05 13:50:04 | daily_merge | start_merge | zone | zone1 | global_broadcast_version | 2 | 172.20.2.120 |
| Apr05 13:50:04 | daily_merge | set_zone_merging | zone | zone3 | | | 172.20.2.120 |
| Apr05 13:50:04 | daily_merge | set_zone_merging | zone | zone2 | | | 172.20.2.120 |
| Apr05 13:50:04 | daily_merge | set_zone_merging | zone | zone1 | | | 172.20.2.120 |
| Apr05 13:50:04 | daily_merge | merging | merge_version | 2 | zone | global_zone | 172.20.2.120 |
+----------------+-------------+----------------------+---------------+--------+--------------------------+-------------+--------------+
21 rows in set (0.006 sec)
观察合并进度
MySQL [oceanbase]> select ZONE,svr_ip,major_version,ss_store_count ss_sc, merged_ss_store_count merged_ss_sc, modified_ss_store_count modified_ss_sc, date_format(merge_start_time, "%h:%i:%s") merge_st, date_format(merge_finish_time,"%h:%i:%s") merge_ft, merge_process
-> from `__all_virtual_partition_sstable_image_info` s
-> order by major_version, zone, svr_ip ;
+-------+--------------+---------------+-------+--------------+----------------+----------+----------+---------------+
| ZONE | svr_ip | major_version | ss_sc | merged_ss_sc | modified_ss_sc | merge_st | merge_ft | merge_process |
+-------+--------------+---------------+-------+--------------+----------------+----------+----------+---------------+
| zone1 | 172.20.2.120 | 2 | 1350 | 1350 | 94 | 01:50:06 | 01:50:36 | 100 |
| zone2 | 172.20.2.121 | 2 | 1350 | 1350 | 94 | 01:50:04 | 01:50:47 | 100 |
| zone3 | 172.20.2.122 | 2 | 1350 | 1350 | 94 | 01:50:05 | 01:50:38 | 100 |
| zone1 | 172.20.2.120 | 3 | 1350 | 1350 | 66 | 04:40:54 | 04:41:51 | 50 |
| zone2 | 172.20.2.121 | 3 | 1350 | 1350 | 66 | 04:40:54 | 04:41:40 | 50 |
| zone3 | 172.20.2.122 | 3 | 1350 | 1350 | 66 | 04:40:53 | 04:41:32 | 50 |
+-------+--------------+---------------+-------+--------------+----------------+----------+----------+---------------+
6 rows in set (0.080 sec)
2、跑 TPC-C 测试
1）运行测试程序
在 BenchmarkSQL 客户端，执行 BenchmarkSQL 程序对数据库服务器进行压力测试。
[root@CAIP131 run]# ./runBenchmark.sh props.ob
2）性能监控
注意：
这个监控界面重点关注 QPS/TPS、以及相应的 RT、增量内存的增量和总量占比等。此外还能看出测试过程中还是有不少物理读 IO。
这个监控界面里的重点看各个节点的 QPS 和 TPS 分布，以及远程 SQL 的数量占总 QPS 的比例( SRC/SLC )。TPC-C 业务定义会有约1%的远程仓库交易事务，在 OceanBase 里这个交易又有一定概率是分布式事务。
3）TPC-C 报告
运行结束后会生成结果。
[root@CAIP131 run]# ./runBenchmark.sh props.ob 21:46:54,553 [main] INFO jTPCC : Term-00, 21:46:54,554 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 21:46:54,555 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 21:46:54,555 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 21:46:54,555 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 21:46:54,555 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 21:46:54,556 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 21:46:54,556 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 21:46:54,556 [main] INFO jTPCC : Term-00, 21:46:54,556 [main] INFO jTPCC : Term-00, db=oracle 21:46:54,556 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver 21:46:54,557 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://172.20.2.120:2883/tpcc?useUnicode=true&characterEncoding=utf-8 21:46:54,557 [main] INFO jTPCC : Term-00, user=tpcc@tenanttpcc#obce-demo 21:46:54,557 [main] INFO jTPCC : Term-00, 21:46:54,557 [main] INFO jTPCC : Term-00, warehouses=10 21:46:54,557 [main] INFO jTPCC : Term-00, terminals=10 21:46:54,558 [main] INFO jTPCC : Term-00, runMins=10 21:46:54,558 [main] INFO jTPCC : Term-00, limitTxnsPerMin=10 21:46:54,558 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 21:46:54,558 [main] INFO jTPCC : Term-00, 21:46:54,558 [main] INFO jTPCC : Term-00, newOrderWeight=45 21:46:54,558 [main] INFO jTPCC : Term-00, paymentWeight=43 21:46:54,558 [main] INFO jTPCC : Term-00, orderStatusWeight=4 21:46:54,558 [main] INFO jTPCC : Term-00, deliveryWeight=4 21:46:54,558 [main] INFO jTPCC : Term-00, stockLevelWeight=4 21:46:54,558 [main] INFO jTPCC : Term-00, 21:46:54,558 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 21:46:54,559 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 21:46:54,559 [main] INFO jTPCC : Term-00, 21:46:54,572 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-04-05_214654/run.properties 21:46:54,572 [main] INFO jTPCC : Term-00, created my_result_2022-04-05_214654/data/runInfo.csv for runID 6 21:46:54,573 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-04-05_214654/data/result.csv 21:46:54,574 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 21:46:54,574 [main] INFO jTPCC : Term-00, osCollectorInterval=1 21:46:54,574 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 21:46:54,574 [main] INFO jTPCC : Term-00, osCollectorDevices=null 21:46:54,642 [main] INFO jTPCC : Term-00, 21:46:54,855 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 195 21:46:54,855 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 86 21:46:54,855 [maiTerm-00, Running Average tpmTOTAL: 10.54 Current tpmTOTAL: 744 Memory Usage: 49MB / 241MB 21:57:55,106 [Thread-7] INFO jTPCC : Term-00, 21:57:55,106 [Thread-7] INFO jTPCC : Term-00, 21:57:55,106 [Thread-7] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 4.54 21:57:55,107 [Thread-7] INFO jTPCC : Term-00, Measured tpmTOTAL = 10.09 21:57:55,107 [Thread-7] INFO jTPCC : Term-00, Session Start = 2022-04-05 21:46:55 21:57:55,107 [Thread-7] INFO jTPCC : Term-00, Session End = 2022-04-05 21:57:55 21:57:55,107 [Thread-7] INFO jTPCC : Term-00, Transaction Count = 110
4)生成运行文件
运行同时还生成了一个文件夹（my_result_2022-04-05_214654）
[root@CAIP131 run]# ls benchmarksql-error.log log4j.properties props.ob runDatabaseBuild.sh sql.common sql.postgres funcs.sh misc props.ora runDatabaseDestroy.sh sql.firebird generateGraphs.sh my_result_2022-04-05_214654 props.pg runLoader.sh sql.oceanbase generateReport.sh props.fb runBenchmark.sh runSQL.sh sql.oracle
3、输出 Html 以及图形
1）安装 R 语言
下载安装 R 需要先安装2个依赖的 Rpm 包
[root@CAIP131 soft]# yum install texinfo-tex -y [root@CAIP131 soft]# yum install libjpeg-turbo -y yum install R
输出 html 汇总结果示例：
$ ./generateReport.sh 结果路径 # 结果路径是（runBenchmark.sh测试结果的路径）
执行完成后 runBenchmark.sh 后会在 run 目录下生成一个结果目录，执行如下的命令可以生成报表
2）执行输出 html 汇总：
[root@CAIP131 run]# ./generateReport.sh my_result_2022-04-05_214654/
3）将输出的 html 文件下载到 Windows 本地即可查看
流量指标 ( Throughput，简称 tpmC) 按照 TPC 的定义，流量指标描述了系统在执行 Payment、Order-status、Delivery、Stock-Level 这四种交易的同时，每分钟可以处理多少个 New-Order 交易。所有交易的响应时间必须满足 TPC-C 测试规范的要求。 流量指标值越大越好！
性价比( Price/Performance，简称 Price/tpmC ) 即测试系统价格（指在美国的报价）与流量指标的比值。 性价比越小越好！
遇到的问题1
通过 gip下载的 Benchmarksql-5.0 工具已经默认编译，不用在进行编译，略过该步骤
编译 Benchmarksql-5.0工具，生成 Jar 文件报错
[root@CAIP131 benchmarksql-5.0]# ant
报错，JAVA 变量未设置
配置 Apache-Ant 的环境变量：
[root@CAIP131 ant-1.9.4]# vim ~/.bash_profile
#添加以下内容
export APACH_HOME=/usr/share/doc/ant-1.9.4
export PATH=${ANT_HOME}/bin:$PATH
设置完之后，如果要使环境变量立即生效，需要通过输入命令：source /etc/profile，重新加载配置文件。最后，通过 ant --version 查看安装的 Ant 版本，验证安装成功。
[root@CAIP131 ~]# source ~/.bash_profile [root@CAIP131 ~]# ant -version Apache Ant(TM) version 1.9.4 compiled on November 5 2018
到这里，Ant 已经安装完成，可以开始使用了！
再次编译 BenchmarkSQL-5.0 工具，生成 Jar 文件，未报错
[root@CAIP131 benchmarksql-5.0]# ant
Buildfile: /opt/benchmarksql-5.0/build.xml
init:
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
问题2
通过 gip 下载的 Benchmarksql-5.0工具已经适配 MySQL ，不用在设置，略过该步骤
建表：在 run 目录执行时报错
[root@CAIP131 run]# ./runSQL.sh props.ob ./sql.common/tableCreates.sql ERROR: unsupported database type 'db=mysql' in props.ob
之前 Ant 编译会编译出一个版本 BenchmarkSQL-5.0/dist/BenchmarkSQL-5.0.Jar，但是该版本并不支持 MySQL 的 TPC-C 测试，需要做如下的修改。
修改 BenchmarkSQL5.0 源码，增加对 MySQL 的支持 修改 funcs.sh 脚本
vim run/funcs.sh
原 Funcs.sh 文件
修改后 Funcs.sh 文件
- 修改 BenchmarkSQL 源码 （1）修改 benchmarksql-5.0/src/client/jTPCC.java，增加 MySQL 相关部分，如下所示：
原 JTPCC.java 文件
修改后的 JTPCC.java 文件
else if (iDB.equals("mysql"))
dbType = DB_UNKNOWN;
修改 JTPCCConnection.java
[root@CAIP131 client]# vim jTPCCConnection.java
原 JTPCCConnection.java
修改后的 JTPCC.java 文件
重新 Ant
[root@CAIP131 benchmarksql-5.0]# ant
修改相关脚本，支持 MySQL （1）修改 文件：benchmarksql-5.0/run/funcs.sh，添加 MySQL 数据库类型。
修改前
修改后
（3）修改 Benchmarksql-5.0/run/runDatabaseBuild.sh，去掉 extraHistID AFTER_LOAD="indexCreates foreignKeys extraHistID buildFinish"
修改为：AFTER_LOAD="indexCreates foreignKeys buildFinish"
问题3
安装 R 语言报错
[root@CAIP131 soft]# yum install R -y yum命令出现Loaded plugins: fastestmirror
配置 Yum 的源
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
安装 R 正常
yum -y install R
写在最后
BenchmarkSQL 测试场景和方法虽然简单，但实际测试中可能会遇到一些问题。分析这些问题也可以了解 OceanBase 的特点。以上就是通过 BenchmarkSQL跑 TPC-C 测试程序的完整过程，感兴趣的同学也可以按照上述步骤体验。我也是初学者，还在不断探索中，希望学习到更多的 Oceanbase 技能。 在座的都是青年才俊，有句话是这样说，我要向大家学习。欢迎大家在文章评论区反馈留言和我交流学习。
最后的最后，有任何问题都可以和我们联系。
联系我们
欢迎广大 OceanBase 爱好者、用户和客户随时与我们联系、反馈，方式如下：
社区版官网论坛
社区版项目网站提 Issue
