下载BenchmarkSQL并使用BenchmarkSQL查看OceanBase 的执行计划

一、什么是BenchmarkSQL

BenchmarkSQL是一个开源的数据库基准测试工具，可以用来评估数据库系统的性能，支持多种常见的数据库系统，包括EnterpriseDB、PostgreSQL、Oracle、Sybase、SQL Server、MySQL、HsqlDB、Derby/JavaDB和FireBird等。它的官网是：https://sourceforge.net/projects/benchmarksql/。

在国内的生产实践中，外键约束已经较少使用，但BenchmarkSQL的实现仍然广泛采用了外键约束，以符合TPC-C的要求。它的Order逻辑由4个SELECT、3个INSERT、2个UPDATE组成，和在微服务中不用匿名块和存储过程实现的逻辑高度匹配。

二、下载BenchmarkSQL

1. 点击此链接跳转官方网站下载：https://sourceforge.net/projects/benchmarksql/
   

2. 上传至linux
   

   这里使用的传输工具为：SecureCRT9
   安装步骤见此文：SecureCRT9汉化版安装
   也可关注文末公众号：数据探索者007 回复01获取

3. 减压文件

   [oceanbase@localhost ~]$ ls
   benchmarksql-5.0.zip  myoceanbase  mysql_test
   [oceanbase@localhost ~]$ unzip benchmarksql-5.0.zip 
   Archive:  benchmarksql-5.0.zip
   6036b15716cf8c142465ac8092d53a777d609684
      creating: benchmarksql-5.0/
    extracting: benchmarksql-5.0/.gitignore  
     inflating: benchmarksql-5.0/HOW-TO-RUN.txt  
     inflating: benchmarksql-5.0/README.md  
     inflating: benchmarksql-5.0/build.xml  
      creating: benchmarksql-5.0/doc/
      creating: benchmarksql-5.0/doc/src/
     inflating: benchmarksql-5.0/doc/src/TimedDriver.odt  
      creating: benchmarksql-5.0/lib/
    extracting: benchmarksql-5.0/lib/.gitignore  
     inflating: benchmarksql-5.0/lib/apache-log4j-extras-1.1.jar  
      creating: benchmarksql-5.0/lib/firebird/
     inflating: benchmarksql-5.0/lib/firebird/connector-api-1.5.jar  
     inflating: benchmarksql-5.0/lib/firebird/jaybird-2.2.9.jar  
     inflating: benchmarksql-5.0/lib/log4j-1.2.17.jar  
      creating: benchmarksql-5.0/lib/oracle/
    extracting: benchmarksql-5.0/lib/oracle/.gitignore  
     inflating: benchmarksql-5.0/lib/oracle/README.txt  
      creating: benchmarksql-5.0/lib/postgres/
     inflating: benchmarksql-5.0/lib/postgres/postgresql-9.3-1102.jdbc41.jar  
      creating: benchmarksql-5.0/run/
    extracting: benchmarksql-5.0/run/.gitignore  
     inflating: benchmarksql-5.0/run/funcs.sh  
     inflating: benchmarksql-5.0/run/generateGraphs.sh  
     inflating: benchmarksql-5.0/run/generateReport.sh  
     inflating: benchmarksql-5.0/run/log4j.properties  
      creating: benchmarksql-5.0/run/misc/
     inflating: benchmarksql-5.0/run/misc/blk_device_iops.R  
     inflating: benchmarksql-5.0/run/misc/blk_device_kbps.R  
     inflating: benchmarksql-5.0/run/misc/cpu_utilization.R  
     inflating: benchmarksql-5.0/run/misc/dirty_buffers.R  
     inflating: benchmarksql-5.0/run/misc/latency.R  
     inflating: benchmarksql-5.0/run/misc/net_device_iops.R  
     inflating: benchmarksql-5.0/run/misc/net_device_kbps.R  
     inflating: benchmarksql-5.0/run/misc/os_collector_linux.py  
     inflating: benchmarksql-5.0/run/misc/tpm_nopm.R  
     inflating: benchmarksql-5.0/run/props.fb  
     inflating: benchmarksql-5.0/run/props.ora  
     inflating: benchmarksql-5.0/run/props.pg  
     inflating: benchmarksql-5.0/run/runBenchmark.sh  
     inflating: benchmarksql-5.0/run/runDatabaseBuild.sh  
     inflating: benchmarksql-5.0/run/runDatabaseDestroy.sh  
     inflating: benchmarksql-5.0/run/runLoader.sh  
     inflating: benchmarksql-5.0/run/runSQL.sh  
      creating: benchmarksql-5.0/run/sql.common/
     inflating: benchmarksql-5.0/run/sql.common/buildFinish.sql  
     inflating: benchmarksql-5.0/run/sql.common/foreignKeys.sql  
     inflating: benchmarksql-5.0/run/sql.common/indexCreates.sql  
     inflating: benchmarksql-5.0/run/sql.common/indexDrops.sql  
     inflating: benchmarksql-5.0/run/sql.common/tableCreates.sql  
     inflating: benchmarksql-5.0/run/sql.common/tableDrops.sql  
     inflating: benchmarksql-5.0/run/sql.common/tableTruncates.sql  
      creating: benchmarksql-5.0/run/sql.firebird/
     inflating: benchmarksql-5.0/run/sql.firebird/extraHistID.sql  
      creating: benchmarksql-5.0/run/sql.oracle/
     inflating: benchmarksql-5.0/run/sql.oracle/extraHistID.sql  
      creating: benchmarksql-5.0/run/sql.postgres/
     inflating: benchmarksql-5.0/run/sql.postgres/buildFinish.sql  
     inflating: benchmarksql-5.0/run/sql.postgres/extraHistID.sql  
     inflating: benchmarksql-5.0/run/sql.postgres/tableCopies.sql  
      creating: benchmarksql-5.0/src/
      creating: benchmarksql-5.0/src/LoadData/
     inflating: benchmarksql-5.0/src/LoadData/LoadData.java  
     inflating: benchmarksql-5.0/src/LoadData/LoadDataWorker.java  
      creating: benchmarksql-5.0/src/OSCollector/
     inflating: benchmarksql-5.0/src/OSCollector/OSCollector.java  
      creating: benchmarksql-5.0/src/client/
     inflating: benchmarksql-5.0/src/client/jTPCC.java  
     inflating: benchmarksql-5.0/src/client/jTPCCConfig.java  
     inflating: benchmarksql-5.0/src/client/jTPCCConnection.java  
     inflating: benchmarksql-5.0/src/client/jTPCCRandom.java  
     inflating: benchmarksql-5.0/src/client/jTPCCTData.java  
     inflating: benchmarksql-5.0/src/client/jTPCCTerminal.java  
     inflating: benchmarksql-5.0/src/client/jTPCCUtil.java  
      creating: benchmarksql-5.0/src/jdbc/
     inflating: benchmarksql-5.0/src/jdbc/ExecJDBC.java  
   [oceanbase@localhost ~]$ ls
   benchmarksql-5.0  benchmarksql-5.0.zip  myoceanbase  mysql_test
   [oceanbase@localhost ~]$ 
   [oceanbase@localhost ~]$ tree benchmarksql-5.0
   benchmarksql-5.0
   ├── build.xml
   ├── doc
   │   └── src
   │       └── TimedDriver.odt
   ├── HOW-TO-RUN.txt
   ├── lib
   │   ├── apache-log4j-extras-1.1.jar
   │   ├── firebird
   │   │   ├── connector-api-1.5.jar
   │   │   └── jaybird-2.2.9.jar
   │   ├── log4j-1.2.17.jar
   │   ├── oracle
   │   │   └── README.txt
   │   └── postgres
   │       └── postgresql-9.3-1102.jdbc41.jar
   ├── README.md
   ├── run
   │   ├── funcs.sh
   │   ├── generateGraphs.sh
   │   ├── generateReport.sh
   │   ├── log4j.properties
   │   ├── misc
   │   │   ├── blk_device_iops.R
   │   │   ├── blk_device_kbps.R
   │   │   ├── cpu_utilization.R
   │   │   ├── dirty_buffers.R
   │   │   ├── latency.R
   │   │   ├── net_device_iops.R
   │   │   ├── net_device_kbps.R
   │   │   ├── os_collector_linux.py
   │   │   └── tpm_nopm.R
   │   ├── props.fb
   │   ├── props.ora
   │   ├── props.pg
   │   ├── runBenchmark.sh
   │   ├── runDatabaseBuild.sh
   │   ├── runDatabaseDestroy.sh
   │   ├── runLoader.sh
   │   ├── runSQL.sh
   │   ├── sql.common
   │   │   ├── buildFinish.sql
   │   │   ├── foreignKeys.sql
   │   │   ├── indexCreates.sql
   │   │   ├── indexDrops.sql
   │   │   ├── tableCreates.sql
   │   │   ├── tableDrops.sql
   │   │   └── tableTruncates.sql
   │   ├── sql.firebird
   │   │   └── extraHistID.sql
   │   ├── sql.oracle
   │   │   └── extraHistID.sql
   │   └── sql.postgres
   │       ├── buildFinish.sql
   │       ├── extraHistID.sql
   │       └── tableCopies.sql
   └── src
       ├── client
       │   ├── jTPCCConfig.java
       │   ├── jTPCCConnection.java
       │   ├── jTPCC.java
       │   ├── jTPCCRandom.java
       │   ├── jTPCCTData.java
       │   ├── jTPCCTerminal.java
       │   └── jTPCCUtil.java
       ├── jdbc
       │   └── ExecJDBC.java
       ├── LoadData
       │   ├── LoadData.java
       │   └── LoadDataWorker.java
       └── OSCollector
           └── OSCollector.java
   
   17 directories, 54 files
   [oceanbase@localhost ~]$ 
   

4. 配置BenchmarkSQL
   使用如下命令进入props.ora 文件进行配置

   [oceanbase@localhost ~]$ vi benchmarksql-5.0/run/props.ora 
   

   具体配置如下：

   db=oracle
   driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
   conn=jdbc:oceanbase://192.168.66.110:2881/benchmark_sql?useUnicode=true&characterEncoding=utf-8
   user=user01@t1
   password=rootroot
   
   warehouses=1
   loadWorkers=4
   
   terminals=1
   //To run specified transactions per terminal- runMins must equal zero
   runTxnsPerTerminal=10
   //To run for specified minutes- runTxnsPerTerminal must equal zero
   runMins=0
   //Number of total transactions per minute
   limitTxnsPerMin=300
   
   //Set to true to run in 4.x compatible mode. Set to false to use the
   //entire configured database evenly.
   terminalWarehouseFixed=true
   
   //The following five values must add up to 100
   newOrderWeight=45
   paymentWeight=43
   orderStatusWeight=4
   deliveryWeight=4
   stockLevelWeight=4
   
   // Directory name to create for collecting detailed result data.
   // Comment this out to suppress.
   resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
   osCollectorScript=./misc/os_collector_linux.py
   osCollectorInterval=1
   //osCollectorSSHAddr=user@dbhost
   osCollectorDevices=net_eth0 blk_sda
   ~                                                                        
   ~                                                                        
   ~                                                                        
   ~                                                                        
   ~                                            
   

   

三、使用BenchmarkSQL查看OceanBase 的执行计划

1. 下载ant
   
   设置ant环境

   [root@localhost ~]# vi ~/.bash_profile 
   [root@localhost ~]# source /etc/profile
   [root@localhost ~]# ant -version
   Apache Ant(TM) version 1.9.4 compiled on November 5 2018
   

   
   在对应目录下编译BenchmarkSQL

   [oceanbase@localhost ~]$ cd benchmarksql-5.0
   [oceanbase@localhost benchmarksql-5.0]$ ant
   Buildfile: /home/oceanbase/benchmarksql-5.0/build.xml
   
   init:
       [mkdir] Created dir: /home/oceanbase/benchmarksql-5.0/build
   
   compile:
       [javac] Compiling 11 source files to /home/oceanbase/benchmarksql-5.0/build
   
   dist:
       [mkdir] Created dir: /home/oceanbase/benchmarksql-5.0/dist
         [jar] Building jar: /home/oceanbase/benchmarksql-5.0/dist/BenchmarkSQL-5.0.jar
   
   BUILD SUCCESSFUL
   Total time: 2 seconds
   

2. 准备Oceanbase驱动文件
   下载 JDBC 驱动，BenchmarkSQL 是通过 JDBC 连接各个数据库的。此次 OceanBase 的测试租户是 MySQL 类型，所以需要把相关 Jar 包一并放入其中

   Oceanbase驱动文件
   
   下载好后放在benchmarksql-5.0/lib/下

3. 创建建表sql文件
   在benchmarksql-5.0/run/sql.common/目录下，使用如下语句：

   vi benchmarksql-5.0/run/sql.common/createTables.sql
   

   createTables.sql文件内容如下：

   CREATE TABLE students (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       age INT,
       gender ENUM('Male', 'Female')
   );
   CREATE TABLE courses (
       id INT PRIMARY KEY,
       name VARCHAR(50)
   );
   CREATE TABLE student_courses (
       student_id INT,
       course_id INT,
       grade INT,
       FOREIGN KEY (student_id) REFERENCES students(id),
       FOREIGN KEY (course_id) REFERENCES courses(id)
   );
   

4. 修改runSQL.sh文件

   [oceanbase@localhost ~]$ cd benchmarksql-5.0/run/
   [oceanbase@localhost run]$ pwd
   /home/oceanbase/benchmarksql-5.0/run
   [oceanbase@localhost run]$ cd
   [oceanbase@localhost ~]$ vi benchmarksql-5.0/run/runSQL.sh 
   [oceanbase@localhost ~]$ 
   

   source /home/oceanbase/benchmarksql-5.0/run/fu1ncs.sh $
   

   

5. 执行测试：

   [oceanbase@localhost run]$ sh runSQL.sh props.ora sql.common/createTables.sql
   # ------------------------------------------------------------
   # Loading SQL file sql.common/createTables.sql
   # ------------------------------------------------------------
   CREATE TABLE students (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   age INT,
   gender ENUM('Male', 'Female')
   );
   CREATE TABLE courses (
   id INT PRIMARY KEY,
   name VARCHAR(50)
   );
   CREATE TABLE student_courses (
   student_id INT,
   course_id INT,
   grade INT,
   FOREIGN KEY (student_id) REFERENCES students(id),
   FOREIGN KEY (course_id) REFERENCES courses(id)
   );
   [oceanbase@localhost run]$ 
   [oceanbase@localhost run]$
   

   ./runBenchmark.sh props.ora
   

   

---

更多精彩文章可扫码关注公主号查看：