全面测试TiDB性能 使用HyBench实战报告

图片选自: 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"

运行测试

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 in] 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 [llowLoadLocalInfile=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 进行压测。