使用 HyBench 测试 TiDB

使用 HyBench 测试 TiDB

ShawnYan Lv.6
  2024-01-01 10:26:16 2024-01-01 10:26:16 Created   2024-01-01 10:26:16 2024-01-01 10:26:16 Updated      2.5k Words  14 Mins  
img01.png

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

本文将介绍如何使用 HyBench 对 TiDB 进行测试，并简述 HyBench 适配 TiDB 的注意事项。

Hybench 是一款由中国软件评测中心、清华大学联合牵头，多家公司共同研发的 HTAP 数据库基准测试工具。

TiDB 是一款兼容 MySQL 的数据库，Hybench 已在 Gitee 开源，支持 MySQL 数据库，通过修改 HyBench 源码以适配 TiDB。

前置需求

  1. 为方便演示，这里直接启动一个 TiDB 本地测试集群。
1
2
3
4
5
6
7
8
9
[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
  1. 准备 JDK 17，并配置环境变量。
1
2
3
4
5
6
7
[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:...
  1. 安装 Maven。

直接使用 dnf 安装即可，版本信息如下。

1
2
3
4
5
6
[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"
02.png

运行测试

1. 修改数据库连接信息

依据实际需求修改配置文件 conf/db.prop 中的 HOST, IP, USERNAME, PASSWORD 等信息。

2. 生成测试数据

运行生成数据的命令：

1
./hybench -t gendata -c conf/db.prop

输出结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
[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!

查看生成的数据文件：

1
2
3
4
5
6
7
8
9
10
11
[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]#
03.png

3. 初始化表结构

运行生成表结构的命令：

1
./hybench -t sql -f conf/tidb/ddl_tidb.sql -c conf/db.prop

输出结果：

1
2
3
4
5
6
7
[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. 初始化表索引

运行生成表索引的命令：

1
./hybench -t sql -f conf/tidb/create_index_tidb.sql -c conf/db.prop

输出结果：

1
2
3
4
5
6
7
[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. 导入测试数据

运行导入数据的命令：

1
./hybench -t sql -f conf/tidb/load_data_tidb.sql -c conf/db.prop

输出结果：

1
2
3
4
5
6
7
[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. 连接数据库，查看库表信息

1
2
3
mysql --comments --host 192.168.8.92 --port 4000 -u root
use hybench
show tables;

共导入 8 张表。

1
2
3
4
5
6
7
8
9
10
11
12
13
+-------------------+
| Tables_in_hybench |
+-------------------+
| checking          |
| checkingAccount   |
| company           |
| customer          |
| loanapps          |
| loantrans         |
| savingAccount     |
| transfer          |
+-------------------+
8 rows in set (0.01 sec)

7. 运行 TP 负载测试

运行负载测试：

1
./hybench -t runtp -c conf/db.prop -f conf/tidb/stmt_tidb.toml

输出结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
[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. 清理测试表数据

运行清理脚本：

1
./hybench -t sql -f conf/dropTables.sql -c conf/db.prop

输出结果：

1
2
3
4
5
6
7
[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;
...

注意事项

  1. HyBench 需要使用 JDK 17，这在 README 中有一处提及，全文搜索一下比较容易找到。

    封装了java执行命令(需要配置jdk17)

  2. 目前，开源版本只支持1X、10x数据，后续在商业版本中会增加100x,1000x及更大规模的数据。

    对应文件 src/main/resource/parameters.toml 中可以看到 [1x] 和 [10x]。

  3. 导入数据使用的 SQL 命令为 LOAD DATA LOCAL INFILE，是 db.prop 配置文件中，JDBC 连接串需要增加参数 allowLoadLocalInfile=true。

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

总结

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

项目地址：shawnyan/hybench

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

logo.jpg
  • Title: 使用 HyBench 测试 TiDB
  • Author: ShawnYan
  • Created at: 2024-01-01 10:26:16
  • Updated at: 2024-01-01 10:26:16
  • Link: https://shawnyan.cn/2024/tidb/tidb-test-with-hybench/
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐阅读
ProxySQL v2.5.2 版本升级及新特性速览 ProxySQL v2.5.2 版本升级及新特性速览 ProxySQL v2.4.7 安装及新特性速览 ProxySQL v2.4.7 安装及新特性速览 About orzdba About orzdba
推荐阅读
ProxySQL v2.5.2 版本升级及新特性速览 ProxySQL v2.5.2 版本升级及新特性速览 ProxySQL v2.4.7 安装及新特性速览 ProxySQL v2.4.7 安装及新特性速览
 Comments
On this page
  1. 前置需求
  2. 运行测试
    1. 1. 修改数据库连接信息
    2. 2. 生成测试数据
    3. 3. 初始化表结构
    4. 4. 初始化表索引
    5. 5. 导入测试数据
    6. 6. 连接数据库，查看库表信息
    7. 7. 运行 TP 负载测试
    8. 8. 清理测试表数据
  3. 注意事项
  4. 总结