用户体验 | YashanDB 23.1 与 BenchmarkSQL 6 兼容性适配

YashanDB 2024-01-29

321

前言

YashanDB个人版开放后，陆续获得一大波「产品体验官」的关注和体验反馈，小崖在此把优秀的用户投稿文章分享给大家~今天分享的用户文章是《YashanDB 23.1与BenchmarkSQL 6 兼容性适配》（作者：严少安），欢迎大家一起交流。

以下为评测全文：

本文主要介绍 YashanDB 与 BenchmarkSQL 的兼容适配过程，以及演示简单测试过程。

YashanDB 基于原创内核架构，全面支持众核、RDMA、智能计算、云基础设施等面向未来的软硬件技术，采用异步并行、基于代价的事务调度、Raft、有界计算等创新技术和算法，满足企业级核心应用对高兼容、高性能、高扩展、高可用等全生命周期数据管理诉求。

BenchmarkSQL 是一款基于 JDBC 实现的 TPC-C 标准测试工具，支持主流关系型数据库，如：PostgreSQL,MySQL,MariaDB,Oracle 等。

文末放出本次兼容适配的成果物，欢迎大家下载使用。

概念介绍

YashanDB

崖山数据库系统 YashanDB 是深圳计算科学研究院完全自主研发设计的新型数据库系统，融入原创理论，支持单机/主备、共享集群、分布式等多种部署方式，覆盖 OLTP/HTAP/OLAP 交易和分析混合负载场景，为客户提供一站式的企业级融合数据管理解决方案。

YashanDB 个人版介质下载地址为：

https://download.yashandb.com/download

BenchmarkSQL

BenchmarkSQL 是用 Java 实现的，使用 JDBC 对 SQL 数据库进行压力测试。整个体系结构是一系列数据结构、队列和线程组，用于处理模拟终端、用户和应用程序线程。它的体系结构允许 BenchmarkSQL 驱动最多的 TPC-C 配置数千个仓库(称为缩放因子)，而不会压倒测试驱动程序本身的作业调度器。

目前，最新版本为 BenchmarkSQL 6.0，已兼容数据库: Oracle, PostgreSQL, Firebird, MariaDB, Transac-sql, Babelfish.

需要说明的是，YashanDB 官方文档中有专门一个章节介绍 TPC-C 测试，只是提及“请自行于BenchmarkSQL官网下载Benchmark SQL5”，版本陈旧，且没有给出下载链接和适配完成的成果物，建议文档迭代更新。

本文所使用的 BenchmarkSQL 版本基于最新版，gh 地址为：

https://github.com/pgsql-io/benchmarksql

环境介绍

YashanDB 数据库

YashanDB 个人版

目前，YashanDB 数据库只开放了个人版下载，本文使用的版本如下：

[yashan@shawnyan ~]$ yasql sys/yasdb_123
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64


Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux


SQL> select * from v$version;


BANNER                                                           VERSION_NUMBER    
---------------------------------------------------------------- ----------------- 
Personal Edition Release 23.1.1.100 x86_64                       23.1.1.100       


1 row fetched.

这里不再介绍如何安装，具体步骤可参考我之前的文章：

「YashanDB个人版体验」YashanDB 个人版安装初体验

https://www.modb.pro/db/1719038517697929216

「YashanDB个人版体验」定制 Dockerfile 构建 YashanDB 个人版镜像，并基于此搭建主备高可用

https://www.modb.pro/db/1727742975546564608

YashanDB JDBC

目前 YashanDB 官方提供 JDBC, ODBC, C 三种驱动的下载，下载页面是：https://download.yashandb.com/download

需要注意的是，JDBC 并没有上传 Maven 仓库，已反馈给官方。如需 Java 开发，需要先下载到本地，然后自行引入，引入方式如下。

    <!-- YashanDB -->
    <!-- https://download.yashandb.com/download -->
    <dependency>
      <groupId>com.yashandb.jdbc</groupId>
      <artifactId>yashandb-jdbc</artifactId>
      <version>1.5.1</version>
      <scope>system</scope>
      <systemPath>${project.basedir}/lib/yashandb-jdbc-1.5.1.jar</systemPath>
    </dependency>

另外，关于 ODBC 的使用，可参考我之前的文章：

「YashanDB个人版体验」通过 ODBC 连接 YashanDB 和 MariaDB

https://www.modb.pro/db/1729697884671664128

BenchmarkSQL 测试工具

克隆 BenchmarkSQL 源码，并查看源码版本信息。

$ git lg
* 0ab7d4d - (HEAD -> yashandb, origin/yashandb) Support YashanDB. (4 hours ago) <ShawnYan>
* 727d5e9 - (origin/master, origin/HEAD, shawnyan, master) Add config parameters to run only on a range of warehouses (12 months ago) <Jan Wieck>

CentOS 操作系统

本文所使用的操作系统为 CentOS 7，具体版本信息如下：

[yashan@shawnyan ~]$ cat etc/redhat-release 
CentOS Linux release 7.9.2009 (Core)
[yashan@shawnyan ~]$ uname -a
Linux shawnyan 3.10.0-1160.95.1.el7.x86_64 #1 SMP Mon Jul 24 13:59:37 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

选择这个版本的主要原因是目前崖山数据库提供的安装包多为 el7 版本。当然，高版本 OS 环境也可以使用，如我先前做的测试就是在 Rocky Linux 9.3 环境下。

Java 环境

BenchmarkSQL 6 需要 JDK 1.8 环境，可通过 yum 直接安装。

yashan@shawnyan ~]$ rpm -qa | grep java-1.8
java-1.8.0-openjdk-1.8.0.392.b08-2.el7_9.x86_64
java-1.8.0-openjdk-headless-1.8.0.392.b08-2.el7_9.x86_64
java-1.8.0-openjdk-devel-1.8.0.392.b08-2.el7_9.x86_64
[yashan@shawnyan ~]$

安装完成后，查看版本：

[yashan@shawnyan ~]$ java -version
openjdk version "1.8.0_392"
OpenJDK Runtime Environment (build 1.8.0_392-b08)
OpenJDK 64-Bit Server VM (build 25.392-b08, mixed mode)
[yashan@shawnyan ~]$

Maven 编译工具

对于上古版本的 Java 项目，需要使用 Ant 作为编译工具，而 BenchmarkSQL 6 已经替换为使用 Maven 作为编译工具，可以通过 yum 直接安装：

[root@shawnyan ~]# yum info maven
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Installed Packages
Name        : maven
Arch        : noarch
Version     : 3.0.5
Release     : 17.el7
Size        : 1.6 M
Repo        : installed
From repo   : base-aliyun
Summary     : Java project management and project comprehension tool
URL         : http://maven.apache.org/
License     : ASL 2.0
Description : Maven is a software project management and comprehension tool. Based on the
            : concept of a project object model (POM), Maven can manage a project's build,
            : reporting and documentation from a central piece of information.


[root@shawnyan ~]# mvn -v
Apache Maven 3.0.5 (Red Hat 3.0.5-17)
Maven home: usr/share/maven
Java version: 1.8.0_392, vendor: Red Hat, Inc.
Java home: usr/lib/jvm/java-1.8.0-openjdk-1.8.0.392.b08-2.el7_9.x86_64/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "3.10.0-1160.95.1.el7.x86_64", arch: "amd64", family: "unix"
[root@shawnyan ~]#

但是，该版本太低，使用时会遇到报错。

[root@shawnyan benchmarksql]# mvn 
[INFO] Scanning for projects...
[WARNING] 
[WARNING] Some problems were encountered while building the effective model for com.github.pgsql-io:benchmarksql:jar:6.0.0-SNAPSHOT
[WARNING] 'dependencies.dependency.systemPath' for com.yashandb.jdbc:yashandb-jdbc:jar should not point at files within the project directory, ${project.basedir}/lib/yashandb-jdbc-1.5.1.jar will be unresolvable by dependent projects @ line 91, column 16
[WARNING] 
[WARNING] It is highly recommended to fix these problems because they threaten the stability of your build.
[WARNING] 
[WARNING] For this reason, future Maven versions might no longer support building such malformed projects.
[WARNING] 
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building A TPC-C like test tool 6.0.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-resources-plugin/3.2.0/maven-resources-plugin-3.2.0.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-resources-plugin/3.2.0/maven-resources-plugin-3.2.0.pom (8 KB at 4.6 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/34/maven-plugins-34.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/34/maven-plugins-34.pom (11 KB at 27.5 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/34/maven-parent-34.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/34/maven-parent-34.pom (42 KB at 84.7 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/apache/23/apache-23.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/apache/23/apache-23.pom (18 KB at 44.4 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-resources-plugin/3.2.0/maven-resources-plugin-3.2.0.jar
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-resources-plugin/3.2.0/maven-resources-plugin-3.2.0.jar (33 KB at 61.9 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-compiler-plugin/3.8.0/maven-compiler-plugin-3.8.0.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-compiler-plugin/3.8.0/maven-compiler-plugin-3.8.0.pom (13 KB at 23.8 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/32/maven-plugins-32.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/32/maven-plugins-32.pom (11 KB at 38.2 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/32/maven-parent-32.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/32/maven-parent-32.pom (43 KB at 45.0 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/apache/20/apache-20.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/apache/20/apache-20.pom (16 KB at 33.8 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-compiler-plugin/3.8.0/maven-compiler-plugin-3.8.0.jar
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-compiler-plugin/3.8.0/maven-compiler-plugin-3.8.0.jar (61 KB at 47.3 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-surefire-plugin/2.10/maven-surefire-plugin-2.10.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-surefire-plugin/2.10/maven-surefire-plugin-2.10.pom (11 KB at 28.5 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/surefire/surefire/2.10/surefire-2.10.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/surefire/surefire/2.10/surefire-2.10.pom (12 KB at 32.1 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/20/maven-parent-20.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/20/maven-parent-20.pom (25 KB at 39.3 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/apache/9/apache-9.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/apache/9/apache-9.pom (15 KB at 29.5 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-surefire-plugin/2.10/maven-surefire-plugin-2.10.jar
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-surefire-plugin/2.10/maven-surefire-plugin-2.10.jar (30 KB at 47.8 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-jar-plugin/2.4/maven-jar-plugin-2.4.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-jar-plugin/2.4/maven-jar-plugin-2.4.pom (6 KB at 19.8 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/22/maven-plugins-22.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/22/maven-plugins-22.pom (13 KB at 27.3 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/21/maven-parent-21.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/21/maven-parent-21.pom (26 KB at 33.3 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/apache/10/apache-10.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/apache/10/apache-10.pom (15 KB at 20.6 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-jar-plugin/2.4/maven-jar-plugin-2.4.jar
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-jar-plugin/2.4/maven-jar-plugin-2.4.jar (34 KB at 42.5 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-dependency-plugin/3.0.0/maven-dependency-plugin-3.0.0.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-dependency-plugin/3.0.0/maven-dependency-plugin-3.0.0.pom (14 KB at 33.5 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/30/maven-plugins-30.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-plugins/30/maven-plugins-30.pom (10 KB at 27.4 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/30/maven-parent-30.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/maven-parent/30/maven-parent-30.pom (41 KB at 45.2 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/apache/18/apache-18.pom
Downloaded: https://repo.maven.apache.org/maven2/org/apache/apache/18/apache-18.pom (16 KB at 24.4 KB/sec)
Downloading: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-dependency-plugin/3.0.0/maven-dependency-plugin-3.0.0.jar
Downloaded: https://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-dependency-plugin/3.0.0/maven-dependency-plugin-3.0.0.jar (161 KB at 44.9 KB/sec)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 20.140s
[INFO] Finished at: Sat Dec 02 11:43:38 CST 2023
[INFO] Final Memory: 10M/254M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-resources-plugin:3.2.0:resources (default-resources) on project benchmarksql: The plugin org.apache.maven.plugins:maven-resources-plugin:3.2.0 requires Maven version 3.1.0 -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/PluginIncompatibleException
[root@shawnyan benchmarksql]#

工程里用到了插件【maven-resources-plugin:3.2.0】，索性我们就将 Maven 直接升级到最新版本【Maven 3.9.6】。

[root@shawnyan ~]# wget https://dlcdn.apache.org/maven/maven-3/3.9.6/binaries/apache-maven-3.9.6-bin.zip
--2023-12-02 12:03:07--  https://dlcdn.apache.org/maven/maven-3/3.9.6/binaries/apache-maven-3.9.6-bin.zip
Resolving dlcdn.apache.org (dlcdn.apache.org)... 151.101.2.132, 2a04:4e42::644
Connecting to dlcdn.apache.org (dlcdn.apache.org)|151.101.2.132|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 9513253 (9.1M) [application/zip]
Saving to: ‘apache-maven-3.9.6-bin.zip’


100%[==================================================================================================================================================================================================>] 9,513,253   66.7KB/s   in 1m 52s 


2023-12-02 12:04:59 (82.7 KB/s) - ‘apache-maven-3.9.6-bin.zip’ saved [9513253/9513253]


[root@shawnyan ~]# unzip apache-maven-3.9.6-bin.zip 
Archive:  apache-maven-3.9.6-bin.zip
...
[root@shawnyan bin]# ./mvn -v
Apache Maven 3.9.6 (bc0240f3c744dd6b6ec2920b3cd08dcc295161ae)
Maven home: opt/maven
Java version: 1.8.0_392, vendor: Red Hat, Inc., runtime: usr/lib/jvm/java-1.8.0-openjdk-1.8.0.392.b08-2.el7_9.x86_64/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "3.10.0-1160.95.1.el7.x86_64", arch: "amd64", family: "unix"

Python 环境

BenchmarkSQL 6 使用 Flask 作为 Web 框架，可通过浏览器进行配置、测试、查看结果，这里引入了 Python 环境。

[root@shawnyan ~]# python3 --version
Python 3.6.8

安装工程所需 Python 依赖。

pip3 install pip --upgrade
pip3 install -r requirements.txt

输出：

[root@centos7 resources]# pip3 install -r requirements.txt
Requirement already satisfied: Flask in usr/local/lib/python3.6/site-packages (from -r requirements.txt (line 4)) (2.0.3)
Requirement already satisfied: numpy in usr/local/lib64/python3.6/site-packages (from -r requirements.txt (line 5)) (1.19.5)
Requirement already satisfied: matplotlib in usr/local/lib64/python3.6/site-packages (from -r requirements.txt (line 6)) (3.3.4)
Requirement already satisfied: paho-mqtt in usr/local/lib/python3.6/site-packages (from -r requirements.txt (line 7)) (1.6.1)
Requirement already satisfied: jproperties in usr/local/lib/python3.6/site-packages (from -r requirements.txt (line 8)) (2.1.1)
Requirement already satisfied: click>=7.1.2 in usr/local/lib/python3.6/site-packages (from Flask->-r requirements.txt (line 4)) (8.0.4)
Requirement already satisfied: Werkzeug>=2.0 in usr/local/lib/python3.6/site-packages (from Flask->-r requirements.txt (line 4)) (2.0.3)
Requirement already satisfied: Jinja2>=3.0 in usr/local/lib/python3.6/site-packages (from Flask->-r requirements.txt (line 4)) (3.0.3)
Requirement already satisfied: itsdangerous>=2.0 in usr/local/lib/python3.6/site-packages (from Flask->-r requirements.txt (line 4)) (2.0.1)
Requirement already satisfied: pillow>=6.2.0 in usr/local/lib64/python3.6/site-packages (from matplotlib->-r requirements.txt (line 6)) (8.4.0)
Requirement already satisfied: python-dateutil>=2.1 in usr/local/lib/python3.6/site-packages (from matplotlib->-r requirements.txt (line 6)) (2.8.2)
Requirement already satisfied: cycler>=0.10 in usr/local/lib/python3.6/site-packages (from matplotlib->-r requirements.txt (line 6)) (0.11.0)
Requirement already satisfied: kiwisolver>=1.0.1 in usr/local/lib64/python3.6/site-packages (from matplotlib->-r requirements.txt (line 6)) (1.3.1)
Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in usr/local/lib/python3.6/site-packages (from matplotlib->-r requirements.txt (line 6)) (2.4.7)
Requirement already satisfied: six~=1.13 in /usr/local/lib/python3.6/site-packages (from jproperties->-r requirements.txt (line 8)) (1.15.0)
Requirement already satisfied: importlib-metadata in /usr/local/lib/python3.6/site-packages (from click>=7.1.2->Flask->-r requirements.txt (line 4)) (4.8.3)
Requirement already satisfied: MarkupSafe>=2.0 in /usr/local/lib64/python3.6/site-packages (from Jinja2>=3.0->Flask->-r requirements.txt (line 4)) (2.0.1)
Requirement already satisfied: dataclasses in /usr/local/lib/python3.6/site-packages (from Werkzeug>=2.0->Flask->-r requirements.txt (line 4)) (0.8)
Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.6/site-packages (from importlib-metadata->click>=7.1.2->Flask->-r requirements.txt (line 4)) (3.6.0)
Requirement already satisfied: typing-extensions>=3.6.4 in /usr/local/lib/python3.6/site-packages (from importlib-metadata->click>=7.1.2->Flask->-r requirements.txt (line 4)) (4.1.1)

兼容性适配

我已经将适配后的 BenchmarkSQL 上传到 Gitee:

https://gitee.com/shawnyan/benchmarksql4yas/

具体修改内容，可以从提交履历中一目了然，比如：

这里主要强调三点，

1.与 YashanDB 官方文档中介绍有差异，不再一一罗列修改了什么，而是直接提供适配后的源码。

2.由于 YashanDB 高度兼容 Oracle，所以在适配 BenchmarkSQL 过程中，主要以 Oracle 的部分为模板，建表语句是相同的。

3.BenchmarkSQL 使用的是 JDBC 连接数据库，使用时，需要修改配置文件的连接信息，模板如下：

db=yashandb
driver=com.yashandb.jdbc.Driver
application=Generic
conn=jdbc:yasdb://127.0.0.1:1688/yandb
user=yandb
password=123

适配完成后，再次编译源码，成功。

[root@shawnyan benchmarksql]# mvn
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding=UTF8
[INFO] Scanning for projects...
[WARNING] 
[WARNING] Some problems were encountered while building the effective model for com.github.pgsql-io:benchmarksql:jar:6.0.0-SNAPSHOT
[WARNING] 'dependencies.dependency.systemPath' for com.yashandb.jdbc:yashandb-jdbc:jar should not point at files within the project directory, ${project.basedir}/lib/yashandb-jdbc-1.5.1.jar will be unresolvable by dependent projects @ line 91, column 16
[WARNING] 
[WARNING] It is highly recommended to fix these problems because they threaten the stability of your build.
[WARNING] 
[WARNING] For this reason, future Maven versions might no longer support building such malformed projects.
[WARNING] 
[INFO] 
[INFO] ------------------< com.github.pgsql-io:benchmarksql >------------------
[INFO] Building A TPC-C like test tool 6.0.0-SNAPSHOT
[INFO]   from pom.xml
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- resources:3.2.0:resources (default-resources) @ benchmarksql ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Using 'UTF-8' encoding to copy filtered properties files.
[INFO] Copying 66 resources
[INFO] 
[INFO] --- compiler:3.8.0:compile (default-compile) @ benchmarksql ---
[INFO] Changes detected - recompiling the module!
[INFO] Compiling 19 source files to /root/benchmarksql/target/classes
[INFO] 
[INFO] --- resources:3.2.0:testResources (default-testResources) @ benchmarksql ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Using 'UTF-8' encoding to copy filtered properties files.
[INFO] skip non existing resourceDirectory /root/benchmarksql/src/test/resources
[INFO] 
[INFO] --- compiler:3.8.0:testCompile (default-testCompile) @ benchmarksql ---
[INFO] No sources to compile
[INFO] 
[INFO] --- surefire:3.2.2:test (default-test) @ benchmarksql ---
[INFO] No tests to run.
[INFO] 
[INFO] --- jar:2.4:jar (default-jar) @ benchmarksql ---
[INFO] Building jar: /root/benchmarksql/target/BenchmarkSQL.jar
[INFO] 
[INFO] --- dependency:3.0.0:copy-dependencies (copy-dependencies) @ benchmarksql ---
[INFO] Copying postgresql-42.2.19.jar to /root/benchmarksql/target/lib/postgresql-42.2.19.jar
[INFO] Copying jcc-11.5.5.0.jar to /root/benchmarksql/target/lib/jcc-11.5.5.0.jar
[INFO] Copying mysql-connector-java-8.0.23.jar to /root/benchmarksql/target/lib/mysql-connector-java-8.0.23.jar
[INFO] Copying yashandb-jdbc-1.5.1.jar to /root/benchmarksql/target/lib/yashandb-jdbc-1.5.1.jar
[INFO] Copying ant-1.8.2.jar to /root/benchmarksql/target/lib/ant-1.8.2.jar
[INFO] Copying protobuf-java-3.11.4.jar to /root/benchmarksql/target/lib/protobuf-java-3.11.4.jar
[INFO] Copying jaybird-4.0.3.java11.jar to /root/benchmarksql/target/lib/jaybird-4.0.3.java11.jar
[INFO] Copying mssql-jdbc-9.2.1.jre8.jar to /root/benchmarksql/target/lib/mssql-jdbc-9.2.1.jre8.jar
[INFO] Copying antlr4-runtime-4.7.2.jar to /root/benchmarksql/target/lib/antlr4-runtime-4.7.2.jar
[INFO] Copying log4j-api-2.14.1.jar to /root/benchmarksql/target/lib/log4j-api-2.14.1.jar
[INFO] Copying ojdbc8-21.1.0.0.jar to /root/benchmarksql/target/lib/ojdbc8-21.1.0.0.jar
[INFO] Copying ant-launcher-1.8.2.jar to /root/benchmarksql/target/lib/ant-launcher-1.8.2.jar
[INFO] Copying connector-api-1.5.jar to /root/benchmarksql/target/lib/connector-api-1.5.jar
[INFO] Copying checker-qual-3.5.0.jar to /root/benchmarksql/target/lib/checker-qual-3.5.0.jar
[INFO] Copying log4j-core-2.14.1.jar to /root/benchmarksql/target/lib/log4j-core-2.14.1.jar
[INFO] Copying mariadb-java-client-2.7.2.jar to /root/benchmarksql/target/lib/mariadb-java-client-2.7.2.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  9.218 s
[INFO] Finished at: 2023-12-03T20:15:17+08:00
[INFO] ------------------------------------------------------------------------
[root@shawnyan benchmarksql]#

编译成功后，可以看到生成了 target 文件夹。

[root@shawnyan target]# ll
total 108
-rw-r--r--  1 root root 100144 Dec  3 20:15 BenchmarkSQL.jar
drwxr-xr-x  3 root root     17 Dec  3 20:15 classes
drwxr-xr-x  3 root root     25 Dec  3 20:15 generated-sources
drwxr-xr-x  2 root root   4096 Dec  3 20:15 lib
drwxr-xr-x  2 root root     28 Dec  3 20:15 maven-archiver
drwxr-xr-x  3 root root     35 Dec  3 20:15 maven-status
drwxr-xr-x 13 root root   4096 Dec  3 20:23 run

到此，YashanDB 23 与 BenchmarkSQL 6 兼容性适配的相关内容已介绍完毕，下面做个小演示 Demo。

性能测试

测试步骤

创建测试用户

CREATE USER yandb IDENTIFIED BY 123;
GRANT DBA TO yandb;
GRANT SELECT ANY TABLE TO yandb;
GRANT CONNECT TO yandb;
GRANT CREATE PROCEDURE TO yandb;
GRANT CREATE SEQUENCE TO yandb;
GRANT CREATE SESSION TO yandb;
GRANT CREATE TABLE TO yandb;
GRANT CREATE TRIGGER TO yandb;
GRANT CREATE TYPE TO yandb;
GRANT UNLIMITED TABLESPACE TO yandb;

启动服务

前文提及了 BenchmarkSQL 已经提供了 Web 服务，可以通过浏览器进行访问操作，先来启动服务：

python3 /benchmarksql/run/FlaskService/main.py

导入配置文件

在浏览器中导入配置文件，具体内容参见工程文件 sample.yashandb.properties，注意数据库连接信息：

conn=jdbc:yasdb://127.0.0.1:1688/yandb
user=yandb
password=123

这里只是流程演示，所以调小了这两个参数，以节省时间：

warehouses=2
runMins=1

补充说明：

1.本文旨在打通 BanchmarkSQL 在 YashanDB 上的测试，而非正式压测，所以仓库数(warehouses)设置很低。

2.runMins 是压测时间，这里为了验证功能，所以只设定为1。

测试日志

测试过程中，产生了一些测试日志。

- Current/Last Job Output


# ------------------------------------------------------------
# Loading SQL file ./sql.yashandb/tableCreates.sql
# ------------------------------------------------------------
2023-12-03 20:19:02,472  INFO - create table bmsql_config (
cfg_name    varchar2(30) primary key,
cfg_value   varchar2(50)
);
...
# ------------------------------------------------------------
# Loading SQL file ./sql.common/storedProcedureCreates.sql
# ------------------------------------------------------------
2023-12-03 20:19:08,595  INFO - -- ----------------------------------------------------------------------
2023-12-03 20:19:08,605  INFO - -- This is a placeholder for databases, that don't support
2023-12-03 20:19:08,605  INFO - -- stored procedures or where the support is too limited to
2023-12-03 20:19:08,606  INFO - -- reasonably implement the TPCC transaction profiles.
2023-12-03 20:19:08,606  INFO - -- ----------------------------------------------------------------------
2023-12-03 20:19:10,823  INFO - Starting BenchmarkSQL LoadData
2023-12-03 20:19:10,832  INFO - 
2023-12-03 20:19:10,834  INFO - random initialized
2023-12-03 20:19:10,840  INFO - driver=com.yashandb.jdbc.Driver
2023-12-03 20:19:10,884  INFO - conn=jdbc:yasdb://127.0.0.1:1688/yandb
2023-12-03 20:19:10,884  INFO - user=yandb
2023-12-03 20:19:10,884  INFO - password=***********
...
# ------------------------------------------------------------
# Loading SQL file ./sql.common/indexCreates.sql
# ------------------------------------------------------------
2023-12-03 20:20:24,343  INFO - alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
...
# ------------------------------------------------------------
# Loading SQL file ./sql.common/foreignKeys.sql
# ------------------------------------------------------------
2023-12-03 20:20:29,379  INFO - alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
...
# ------------------------------------------------------------
# Loading SQL file ./sql.common/buildFinish.sql
# ------------------------------------------------------------
2023-12-03 20:21:07,512  INFO - -- ----
2023-12-03 20:21:07,521  INFO - -- Extra commands to run after the tables are created, loaded,
2023-12-03 20:21:07,521  INFO - -- indexes built and extra's created.
2023-12-03 20:21:07,522  INFO - -- ----

负载信息

测试过程中，YashanDB 和 BenchmarkSQL 的运行载荷都正常。

YashanDB 数据库日志

测试过程中，YashanDB 数据库也产生了若干日志。

tailf /home/yashan/yashandb/yasdb_data/log/run/run.log


2023-12-03 17:09:08.664 20590 [INFO] [USER] create user success, name: YANDB, id: 4, version: 0
2023-12-03 17:09:08.665 20590 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -6
2023-12-03 17:09:08.669 20590 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -500
2023-12-03 17:09:08.672 20590 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -24
2023-12-03 17:09:09.068 20590 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -19
2023-12-03 20:16:54.004 31354 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -505
2023-12-03 20:16:57.968 31354 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -55
2023-12-03 20:17:01.648 31354 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -35
2023-12-03 20:17:30.265 31354 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -60
2023-12-03 20:17:34.280 31354 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -73
2023-12-03 20:17:38.696 31354 [INFO] [ROLE] grant action succeed, granteeId: 4, granteeType: 1, granteeName: YANDB, grantRole: -48
2023-12-03 20:19:17.556 31598 [INFO] [REDO] switch redo file, new asn 3
2023-12-03 20:19:18.357 31598 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 3354 to 11816 
2023-12-03 20:19:26.645 31596 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 11816 to 26227 
2023-12-03 20:19:29.423 31606 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 26227 to 33482 
2023-12-03 20:19:29.525 31601 [INFO] [REDO] switch redo file, new asn 4
2023-12-03 20:19:37.339 31598 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 33482 to 59036 
2023-12-03 20:19:45.199 31611 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 59036 to 77321 
2023-12-03 20:19:52.848 31592 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 77321 to 97789 
2023-12-03 20:20:01.215 31601 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 97789 to 116250 
2023-12-03 20:20:01.743 31592 [INFO] [REDO] switch redo file, new asn 5
2023-12-03 20:20:09.891 31604 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 116250 to 142015 
2023-12-03 20:20:18.354 31592 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 142015 to 169831 
2023-12-03 20:20:24.967 31820 [INFO] [TABLESPACE] try to update instance 0 's space change lsn from 169831 to 180502

连接数据库查看 REDO 日志文件情况。

SQL> SELECT THREAD#,ID,NAME,BLOCK_SIZE,BLOCK_COUNT,USED_BLOCKS,SEQUENCE#,STATUS from V$LOGFILE;


THREAD#           ID NAME                                                               BLOCK_SIZE  BLOCK_COUNT  USED_BLOCKS    SEQUENCE# STATUS    
------- ------------ ---------------------------------------------------------------- ------------ ------------ ------------ ------------ --------- 
      1            0 /home/yashan/yashandb/yasdb_data/dbfiles/redo0                           4096        25600        25481            5 CURRENT  
      1            1 /home/yashan/yashandb/yasdb_data/dbfiles/redo1                           4096        25600        25549            2 INACTIVE 
      1            2 /home/yashan/yashandb/yasdb_data/dbfiles/redo2                           4096        25600        25600            3 INACTIVE 
      1            3 /home/yashan/yashandb/yasdb_data/dbfiles/redo3                           4096        25600        25600            4 INACTIVE 


4 rows fetched.