BenchmarkSQL对OceanBase开源版3.1.2性能测试调优探索

原创 shunwah 2022-04-06

1870

BenchmarkSQL对OceanBase开源版3.1.2性能测试调优探索

此内容为参加“OceanBase 性能挑战季开启 | 第三期技术征文大赛等你来战！“欢迎大家踊跃参与，活动详情：https://www.modb.pro/db/388053

作者：马顺华

从事运维管理工作多年，目前就职于六棱镜（杭州）科技有限公司，熟悉运维自动化、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。

机器信息

机器类型	主机配置	备注
OS	Centos 7.4
中控机 /OBD	CPU:8C	内存：16G
目标机器 /OBserver	CPU:8C	内存：32G
系统盘 /	dev/vda 50G	LVS分区、文件系统：EXT4
数据盘 /data	dev/vdb 100G	GPT分区、文件系统：xfs
事务日志盘 /redo	dev/vdc 100G	GPT分区、文件系统：xfs

机器角色划分

角色	机器IP	备注
OBD	127.20.0.131	中控机
OBserver	127.20.0.120	{2881,2882}, {3881,3882} zone1
	127.20.0.121	{2881,2882}, {3881,3882} zone2
	127.20.0.122	{2881,2882}, {3881,3882} zone3
OBproxy	127.20.0.120	{2883,2884} 反向代理
	127.20.0.121	{2883,2884} 反向代理
	127.20.0.122	{2883,2884} 反向代理
OBAgent	127.20.0.120	监控采集框架默认端口 8088、8089
	127.20.0.121	监控采集框架默认端口 8088、8089
	127.20.0.122	监控采集框架默认端口 8088、8089
OBclient	127.20.0.131	OB命令行客户端
BenchmarkSQL	127.20.0.131	BenchmarkSQL客户端

安装环境部署版本

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

一、服务器初始化设置

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
下载方法一：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
```
将下载（benchmarksql-5.0.zip）的zip包，上传到测试服务器。
解压 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类型、OB类型略过该步骤）

[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 -h127.1 -uroot@sys#obce-demo -p#### -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

MySQL [oceanbase]> CREATE RESOURCE POOL pooltpcc UNIT = 'unittpcc', UNIT_NUM = 1,ZONE_LIST = ('zone1', 'zone2', 'zone3');
Query OK, 0 rows affected (0.018 sec)

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 -h127.1 -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 '####';
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 -h127.1 -utpcc@tenanttpcc#obce-demo -P2883 -p#### -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 -h127.1 -uroot@sys#obce-demo -p#### -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 -h127.1 -utpcc@tenanttpcc#obce-demo -P2883 -p#### -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 | 127.1  |     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 | 127.10 |     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 | 127.11 |     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 | 127.1  |     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 | 127.1  |     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 | 127.1  |     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 -h127.1 -utpcc@tenanttpcc#obce-demo -P2883 -p#### -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 -h127.1 -uroot@sys#obce-demo -p#### -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 |
+--------------+------+-----------------+--------+
| 127.1 | 2883 | 2884            | active |
| 127.1 | 2883 | 2884            | active |
| 127.1 | 2883 | 2884            | active |
+--------------+------+-----------------+--------+
succeed

本次集群是通过OBD部署集群， OBD 重启集群的时候，默认重启了所有组件（包括 OBSERVER 和 OBPROXY ）。所以通过 -c 命令指定重启具体的组件obproxy。

四、配置OceanBase测试参数文件

编辑初始化配置，编辑 /root/benchmarksql-5.0/run/props.ob

[root@CAIP131 benchmarksql-5.0]# 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://127.1: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 -h127.1 -utpcc@tenanttpcc#obce-demo -P2883 -p#### -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 -h127.1 -uroot@sys#obce-demo -p!!!# -P2883 -A -c oceanbase

MySQL [oceanbase]> grant select on oceanbase.* to obtest identified by '###';

b)创建python脚本

dooba 脚本在 /home/admin/oceanbase/bin/ ，是 python 脚本

c)观察租户整体性能视图

[admin@CAIP120 bin]$ python dooba.py -h 127.1 -uobtest@sys#obce-demo -P2883 -p###

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 -h127.1 -utpcc@tenanttpcc#obce-demo -P2883 -p#### -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 -h127.1 -uroot@sys#obce-demo -p#### -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

[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技能。在座的都是青年才俊，有句话是这样说，我要向大家学习。欢迎大家在文章评论区反馈留言和我交流学习。

最后修改时间：2022-04-14 15:58:15

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

BenchmarkSQL对OceanBase开源版3.1.2性能测试调优探索