专栏 - 使用 HyBench 测试 TiDB

no-alt

图片选自: https://asktug.com/t/topic/1020117

本文将介绍如何使用 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"

no-alt

运行测试

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]#

no-alt

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。
自 v7.4.0 起，TiDB 已经兼容 MySQL 8.0 的主要功能，推荐使用最新版本的 MySQL Connector/J 来连接 TiDB，因此源码工程依赖升级为 mysql-connector-j:8.2.0。

总结

本文基于开源数据库压测软件 HyBench 对 TiDB 进行适配，并做简单测试。

项目地址：shawnyan/hybench

如果没有特殊需求，优先推荐使用 TiUP bench 组件对 TiDB 进行压测。