本文将介绍如何使用 HyBench 对 TiDB 进行测试，并简述 HyBench 适配 TiDB 的注意事项。
Hybench 是一款由中国软件评测中心、清华大学联合牵头，多家公司共同研发的 HTAP 数据库基准测试工具。
TiDB 是一款兼容 MySQL 的数据库，Hybench 已在 Gitee 开源，支持 MySQL 数据库，通过修改 HyBench 源码以适配 TiDB。
前置需求
- 为方便演示，这里直接启动一个 TiDB 本地测试集群。
[root@rocky9 ~]# tiup playground display
tiup is checking updates for component playground ...
Starting component `playground`: /root/.tiup/components/playground/v1.14.1/tiup-playground display
Pid Role Uptime
--- ---- ------
7146 pd 4h4m9.520051218s
7164 tikv 4h3m23.583874294s
7320 tidb 4h3m10.59454483s
7332 tiflash 4h1m54.080597745s
- 准备 JDK 17，并配置环境变量。
[root@rocky9 ~]# java -version
openjdk version "17.0.9" 2023-10-17 LTS
OpenJDK Runtime Environment (Red_Hat-17.0.9.0.9-1) (build 17.0.9+9-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-17.0.9.0.9-1) (build 17.0.9+9-LTS, mixed mode, sharing)
[root@rocky9 ~]# env | grep -i java
JAVA_HOME=/usr/lib/jvm/java-17-openjdk-17.0.9.0.9-2.el9.x86_64
PATH=/root/.tiup/bin:/usr/lib/jvm/java-17-openjdk-17.0.9.0.9-2.el9.x86_64/bin:...
- 安装 Maven。
直接使用 dnf 安装即可，版本信息如下。
[root@rocky9 ~]# mvn --version
Apache Maven 3.6.3 (Red Hat 3.6.3-15)
Maven home: /usr/share/maven
Java version: 17.0.9, vendor: Red Hat, Inc., runtime: /usr/lib/jvm/java-17-openjdk-17.0.9.0.9-2.el9.x86_64
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "5.14.0-362.13.1.el9_3.x86_64", arch: "amd64", family: "unix"
<!-- dnf install java-17-openjdk java-17-openjdk-devel dnf install maven
vi ~/.bashrc export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-17.0.9.0.9-2.el9.x86_64 export PATH=$JAVA_HOME/bin:$PATH -->
运行测试
1. 修改数据库连接信息。
依据实际需求修改配置文件 conf/db.prop 中的 HOST, IP, USERNAME, PASSWORD 等信息。
2. 生成测试数据。
运行生成数据的命令：
./hybench -t gendata -c conf/db.prop
输出结果：
[root@rocky9 hybench]# ./hybench -t gendata -c conf/db.prop
2024-01-16 23:07:30 [main] INFO HyBench:324 - Hi~Bench, HyBench
2024-01-16 23:07:30 [main] INFO ConfigLoader:57 - ===============configuration==================
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - xapclient = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - at2_percent = 25
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - at1_percent = 35
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - apclient = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - xpRunMins = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - at4_percent = 15
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - apround = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - url = jdbc:mysql://192.168.8.92:4000/hybench?useUnicode=true&characterEncoding=utf-8
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - tpRunMins = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - at3_percent = 15
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - password =
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - tpclient = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - fresh_interval = 20
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - sf = 1x
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - classname = com.mysql.cj.jdbc.Driver
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - xtpclient = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - apRunMins = 1
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - at5_percent = 7
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - db = tidb
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - username = root
2024-01-16 23:07:30 [main] INFO ConfigLoader:59 - at6_percent = 3
2024-01-16 23:07:30 [main] INFO ConfigLoader:61 - ===============configuration==================
2024-01-16 23:07:30 [main] INFO ConfigLoader:62 -
This is a data generator of HyBench, Version 0.1
----------------
----------------
----------------
Data is generating...
----------------
----------------
----------------
WARNING: dir:/root/hybench/Data_1x not exists! will created.
Data generate not skipped!
Data is ready under the Data folder!
----------------
----------------
----------------
Data generation took 11831 ms
No autoloader, do nothing!
查看生成的数据文件：
[root@rocky9 Data_1x]# ll -h
total 11M
-rw-r--r-- 1 root root 1.4M Jan 16 23:07 checkingAccount.csv
-rw-r--r-- 1 root root 315K Jan 16 23:07 checking.csv
-rw-r--r-- 1 root root 305K Jan 16 23:07 company.csv
-rw-r--r-- 1 root root 3.6M Jan 16 23:07 customer.csv
-rw-r--r-- 1 root root 320K Jan 16 23:07 loanApps.csv
-rw-r--r-- 1 root root 411K Jan 16 23:07 loanTrans.csv
-rw-r--r-- 1 root root 1.4M Jan 16 23:07 savingAccount.csv
-rw-r--r-- 1 root root 3.2M Jan 16 23:07 transfer.csv
[root@rocky9 Data_1x]#
3. 初始化表结构。
运行生成表结构的命令：
./hybench -t sql -f conf/tidb/ddl_tidb.sql -c conf/db.prop
输出结果：
[root@rocky9 hybench]# ./hybench -t sql -f conf/tidb/ddl_tidb.sql -c conf/db.prop
2024-01-16 23:11:10 [main] INFO HyBench:324 - Hi~Bench, HyBench
...
2024-01-16 23:11:10 [main] INFO ConfigLoader:61 - ===============configuration==================
2024-01-16 23:11:10 [main] INFO ConfigLoader:62 -
2024-01-16 23:11:11 [main] INFO ExecSQL:58 - execute query:CREATE TABLE IF NOT EXISTS customer (
...
4. 初始化表索引。
运行生成表索引的命令：
./hybench -t sql -f conf/tidb/create_index_tidb.sql -c conf/db.prop
输出结果：
[root@rocky9 hybench]# ./hybench -t sql -f conf/tidb/create_index_tidb.sql -c conf/db.prop
2024-01-16 23:11:56 [main] INFO HyBench:324 - Hi~Bench, HyBench
...
2024-01-16 23:11:56 [main] INFO ConfigLoader:61 - ===============configuration==================
2024-01-16 23:11:56 [main] INFO ConfigLoader:62 -
2024-01-16 23:11:58 [main] INFO ExecSQL:58 - execute query:create index idx_loanapps_1 on loanapps ( applicantid );
...
5. 导入测试数据。
运行导入数据的命令：
./hybench -t sql -f conf/tidb/load_data_tidb.sql -c conf/db.prop
输出结果：
[root@rocky9 hybench]# ./hybench -t sql -f conf/tidb/load_data_tidb.sql -c conf/db.prop
2024-01-16 23:30:20 [main] INFO HyBench:324 - Hi~Bench, HyBench
...
2024-01-16 23:30:20 [main] INFO ConfigLoader:61 - ===============configuration==================
2024-01-16 23:30:20 [main] INFO ConfigLoader:62 -
2024-01-16 23:30:22 [main] INFO ExecSQL:58 - execute query:LOAD DATA LOCAL INFILE 'Data_1x/customer.csv' INTO TABLE customer FIELDS TERMINATED BY ',';
...
6. 连接数据库，查看库表信息。
mysql --comments --host 192.168.8.92 --port 4000 -u root
use hybench
show tables;
共导入 8 张表。
+-------------------+
| Tables_in_hybench |
+-------------------+
| checking |
| checkingAccount |
| company |
| customer |
| loanapps |
| loantrans |
| savingAccount |
| transfer |
+-------------------+
8 rows in set (0.01 sec)
7. 运行 TP 负载测试。
运行负载测试：
./hybench -t runtp -c conf/db.prop -f conf/tidb/stmt_tidb.toml
输出结果：
[root@rocky9 hybench]# ./hybench -t runtp -c conf/db.prop -f conf/tidb/stmt_tidb.toml
2024-01-16 23:38:57 [main] INFO HyBench:324 - Hi~Bench, HyBench
2024-01-16 23:38:57 [main] INFO ConfigLoader:57 - ===============configuration==================
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - xapclient = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - at2_percent = 25
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - at1_percent = 35
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - apclient = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - xpRunMins = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - at4_percent = 15
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - apround = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - url = jdbc:mysql://192.168.8.92:4000/hybench?useUnicode=true&characterEncoding=utf-8&allowLoadLocalInfile=true
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - tpRunMins = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - at3_percent = 15
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - password =
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - tpclient = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - fresh_interval = 20
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - sf = 1x
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - classname = com.mysql.cj.jdbc.Driver
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - xtpclient = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - apRunMins = 1
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - at5_percent = 7
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - db = tidb
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - username = root
2024-01-16 23:38:57 [main] INFO ConfigLoader:59 - at6_percent = 3
2024-01-16 23:38:57 [main] INFO ConfigLoader:61 - ===============configuration==================
2024-01-16 23:38:57 [main] INFO ConfigLoader:62 -
2024-01-16 23:38:58 [main] INFO HyBench:61 - Begin TP Workload
2024-01-16 23:38:58 [pool-3-thread-1] INFO Client:263 - Begin to run :TPClient, Test Duration is 1 mins
2024-01-16 23:39:04 [Thread-0] INFO Client:297 - Transaction 3 : max rt : 38.0 | min rt :23.0 | avg rt : 28.33 | 95% rt : 38.00 | 99% rt : 38.00
...
2024-01-16 23:39:04 [Thread-0] INFO Client:307 - Current 1/10 time TP TPS is 6.50
...
2024-01-16 23:39:58 [Thread-0] INFO Client:307 - Current 10/10 time TP TPS is 10.88
2024-01-16 23:39:58 [pool-3-thread-1] INFO Client:397 - Finished to execute TPClient
2024-01-16 23:39:58 [main] INFO HyBench:105 - TP Workload is done.
2024-01-16 23:39:58 [main] INFO HyBench:407 - Congs~ Test is done! Bye!
====================Test Summary========================
Test starts at 2024-01-16 23:38:58
Test ends at 2024-01-16 23:39:58
AP Concurrency is 0
TP Concurrency is 1
Total amount of TP Transaction is 654
TPS is 10.9
Query/Transaction response time(ms) histogram :
------------TP-------------------
TP Transaction 1 : max rt : 30.00 | min rt : 15.00 | avg rt : 21.28 | 95% rt : 30.00 | 99% rt : 30.00
TP Transaction 2 : max rt : 59.00 | min rt : 16.00 | avg rt : 27.83 | 95% rt : 59.00 | 99% rt : 59.00
TP Transaction 3 : max rt : 43.00 | min rt : 17.00 | avg rt : 24.08 | 95% rt : 41.25 | 99% rt : 43.00
TP Transaction 4 : max rt : 47.00 | min rt : 14.00 | avg rt : 24.52 | 95% rt : 41.40 | 99% rt : 47.00
TP Transaction 5 : max rt : 76.00 | min rt : 23.00 | avg rt : 37.18 | 95% rt : 72.55 | 99% rt : 76.00
TP Transaction 6 : max rt : 55.00 | min rt : 18.00 | avg rt : 29.35 | 95% rt : 54.30 | 99% rt : 55.00
TP Transaction 7 : max rt : 99.00 | min rt : 17.00 | avg rt : 32.33 | 95% rt : 99.00 | 99% rt : 99.00
TP Transaction 8 : max rt : 47.00 | min rt : 14.00 | avg rt : 25.90 | 95% rt : 46.20 | 99% rt : 47.00
TP Transaction 9 : max rt : 107.00 | min rt : 50.00 | avg rt : 70.00 | 95% rt : 99.75 | 99% rt : 107.00
TP Transaction 10 : max rt : 683.00 | min rt : 214.00 | avg rt : 393.02 | 95% rt : 617.40 | 99% rt : 683.00
TP Transaction 11 : max rt : 108.00 | min rt : 45.00 | avg rt : 66.57 | 95% rt : 93.00 | 99% rt : 108.00
TP Transaction 12 : max rt : 111.00 | min rt : 41.00 | avg rt : 64.20 | 95% rt : 98.20 | 99% rt : 111.00
TP Transaction 13 : max rt : 267.00 | min rt : 70.00 | avg rt : 122.98 | 95% rt : 219.45 | 99% rt : 267.00
TP Transaction 14 : max rt : 256.00 | min rt : 70.00 | avg rt : 116.97 | 95% rt : 236.20 | 99% rt : 256.00
TP Transaction 15 : max rt : 101.00 | min rt : 46.00 | avg rt : 67.52 | 95% rt : 99.40 | 99% rt : 101.00
TP Transaction 16 : max rt : 209.00 | min rt : 65.00 | avg rt : 102.05 | 95% rt : 205.90 | 99% rt : 209.00
TP Transaction 17 : max rt : 150.00 | min rt : 48.00 | avg rt : 71.58 | 95% rt : 116.50 | 99% rt : 150.00
TP Transaction 18 : max rt : 124.00 | min rt : 46.00 | avg rt : 69.42 | 95% rt : 103.00 | 99% rt : 124.00
====================Thank you!========================
8. 清理测试表数据。
运行清理脚本：
./hybench -t sql -f conf/dropTables.sql -c conf/db.prop
输出结果：
[root@rocky9 hybench]# ./hybench -t sql -f conf/dropTables.sql -c conf/db.prop
2024-01-16 23:43:07 [main] INFO HyBench:324 - Hi~Bench, HyBench
...
2024-01-16 23:43:07 [main] INFO ConfigLoader:61 - ===============configuration==================
2024-01-16 23:43:07 [main] INFO ConfigLoader:62 -
2024-01-16 23:43:09 [main] INFO ExecSQL:58 - execute query:drop table if exists customer;
...
注意事项
- HyBench 需要使用 JDK 17，这在 README 中有一处提及，全文搜索一下比较容易找到。
封装了java执行命令(需要配置jdk17)
- 目前，开源版本只支持1X、10x数据，后续在商业版本中会增加100x,1000x及更大规模的数据。
对应文件 src/main/resource/parameters.toml 中可以看到 [1x] 和 [10x]。
- 导入数据使用的 SQL 命令为
LOAD DATA LOCAL INFILE，是db.prop配置文件中，JDBC 连接串需要增加参数allowLoadLocalInfile=true。
总结
本文基于开源数据库压测软件 HyBench 对 TiDB 进行适配，并做简单测试。
项目地址：shawnyan/hybench
如果没有特殊需求，优先推荐使用 TiUP bench 组件对 TiDB 进行压测。