Linux环境下使用TPC-H测试MySQL教程

MySQL 工作负载 环境搭建 TPC-H 安装教程
工作负载
发布日期:   2024-05-19
更新日期:   2024-05-19
文章字数:   3.1k
阅读时长:   13 分
阅读次数:  

TPC-H介绍

TPC-H 是 TPC（Transaction Processing Performance Council）组织发布的一套数据库测试规范，同样的还有很多其他的规范，例如Hammer DB、BenchMarkSQL、Sysbench、YCSB等等一样，都是提供了一套标准，然后供市面上的相关产品来执行这套标准，根据测试结果，从侧面展示产品的性能。

从 TPC 组织的官网 可以了解到，TCP 提供了一系列基准，据我了解，数据库基准测试大致可以分为两类：OLTP、OLAP。OLTP 类型中最流行的就是 TPC-C 测试基准，而 OLAP 中比较流行的则是TPC-H，而我们这次选择的就是TPC-H基准测试 。

根据TPC-H的基准规范可以知道，TPC-H 将海量数据存储在8张表中，并运行 22 个查询（Q1~Q22）来分析这些数据。其主要评价指标是各个查询的响应时间，即从提交查询到结果返回所需的时间，以及多个会话的吞吐量（每秒查询数量）。基准参考文档中介绍还介绍了，测试表与测试数据的详细信息，一些扩展数据以及执行规则等等，这是一份详细的规则清单以及说明。可以手动的根据基准规范文档来创建数据表、生成数据、执行查询语句。也可以使用TPC-H工具包来生成相应的数据表与数据，然后依次测试这22个查询。

接下来我会使用MySQL-8.0.36来测试TPC-H的相关基准。MySQL的安装请见之前的文章 Linux普通用户无root权限安装MySQL 8.0.36

TPC-H安装包的获取

  1. 首先进入官网：TPC-Homepage

  2. 进入工具包下载页面

    TPC-H工具包下载页面

  3. 进入 TPC-H 下载页面

    TPC-H下载页面

  4. 填写相应信息与验证，获取下载地址

  5. 获取成功，从邮件中获取下载地址

TPC-H 工具包的使用与介绍

查看工具包中的内容，了解 TPC-H 如何使用。

  • 安装包解压后，会有附带的 specification.docx 与 specification.pdf 这里面介绍了测试的相关标准，可以为手动测试提供参考。
  • TPC-H_Tools_v3.0.0/dbgen目录下的 READEME文件，介绍了工具相关用途。

TPC-H README文件目录

相关文件介绍

dss.ddl

该文件里面包含了 创建表的 8 条 SQL 语句。

dss.ri

这里是创建表约束的 SQL 语句，例如主键与外键。

/queries 文件夹

该文件夹下面是 22 条查询 SQL。

上一层的 /ref_data 文件夹

该文件夹里面存放了，填充数据表所需要的数据。

dbgen 与 qgen

dbgen 是用来生成与填充 符合基准的数据表的程序，来帮助我们简单的构建符合要求的数据表与数据。

qgen 是基于基准的查询程序，来帮助我们构建一个简单的基准实现。

以上两个可执行文件，均需要编译生成。

TPC-H工具包的编译与安装

准备与解压

将下载得到的安装包上传到服务器上，重命名后解压：

unzip tpc-h.zip

将文件夹放到你自己想要的位置

修改部分文件

  • 后续大部分的 修改、编译、执行等操作都在 mysql-tpc-h/dbgen 目录下完成
cd mysql-tpc-h/dbgen

修改makefile文件

该文件约束了 编译 后可执行文件的部分条件以及需要的依赖。

cp makefile.suite makefile
vim makefile
# 修改 103 109 110 111 这四行的内容


CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS, 
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32 
# Current values for WORKLOAD are:  TPCH
# 这儿的 DATABASE 字段 指明了一些常用的商业数据库 如果写 MYSQL 需要在 tpcd.h 文件中添加部分宏定义
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH

修改 tpcd.h 文件

在该文件中添加以下信息：

#ifdef MYSQL
#define GEN_QUERY_PLAN ""
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE "use %s;\n"
#endif

编译

修改了上述文件后 ，我们直接在 /dbgen 目录下使用 make 命令，来生成可执行文件 dbgen 和 qgen。

TPC-H工具包数据的生成、导入与查询

生成 <table>.tbl 数据文件

可以使用 ./dbgen -h 命令查看，也可以查看 README 中对 dbgen 的相关介绍。

dbgen帮助信息

使用命令生成符合基准的8张表与相应的数据。由介绍可知， -s 后面是数据量的大小，若为 10 则生成 10GB 数据，若为100则生成100GB的数据。

-V ：显示进度消息

-f ：覆盖原始数据

-s ： 生成数据的总体规模

# 生成数据
./dbgen -vf -s 100

# 查看生成的数据
ls *.tbl

导入构建脚本文件

编译之后有dss.ddl和dss.ri两个脚本，dss.ddl是建表脚本，dss.ri脚本是建立主键外键关联关系。

因为TPC-H benchmark本身不支持MySQL；并且由于MySQL的表名、库名等默认都是严格区分大小写的，而 dss.ddl（建表脚本）与 dss.ri（表约束脚本）中使用的都是大写的数据库名与表名，但是提供的 22 条查询语句又使用的小写的表名。所以需要先修改这两个脚本。

修改 dss.dll

在文件开头添加以下语句：

DROP DATABASE IF EXISTS tpch;
CREATE DATABASE tpch;
USE tpch;

修改 dss.ri

可先备份一份，之后将内容修改为以下内容：

-- Sccsid: @(#)dss.ri 2.1.8.1
-- tpch Benchmark Version 8.0
-- CONNECT TO tpch;
use tpch;
-- ALTER TABLE tpch.REGION DROP PRIMARY KEY;
-- ALTER TABLE tpch.NATION DROP PRIMARY KEY;
-- ALTER TABLE tpch.PART DROP PRIMARY KEY;
-- ALTER TABLE tpch.SUPPLIER DROP PRIMARY KEY;
-- ALTER TABLE tpch.PARTSUPP DROP PRIMARY KEY;
-- ALTER TABLE tpch.ORDERS DROP PRIMARY KEY;
-- ALTER TABLE tpch.LINEITEM DROP PRIMARY KEY;
-- ALTER TABLE tpch.CUSTOMER DROP PRIMARY KEY;
-- For table REGION
ALTER TABLE tpch.REGION
ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE tpch.NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE tpch.NATION
-- ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references tpch.REGION;
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references tpch.REGION(R_REGIONKEY);
COMMIT WORK;
-- For table PART
ALTER TABLE tpch.PART
ADD PRIMARY KEY (P_PARTKEY);
COMMIT WORK;
-- For table SUPPLIER
ALTER TABLE tpch.SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE tpch.SUPPLIER
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references tpch.NATION(N_NATIONKEY);
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE tpch.PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
COMMIT WORK;
-- For table CUSTOMER
ALTER TABLE tpch.CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE tpch.CUSTOMER
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references tpch.NATION(N_NATIONKEY);
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE tpch.LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
COMMIT WORK;
-- For table ORDERS
ALTER TABLE tpch.ORDERS
ADD PRIMARY KEY (O_ORDERKEY);
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE tpch.PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references tpch.SUPPLIER(S_SUPPKEY);
COMMIT WORK;
ALTER TABLE tpch.PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references tpch.PART(P_PARTKEY);
COMMIT WORK;
-- For table ORDERS
ALTER TABLE tpch.ORDERS
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references tpch.CUSTOMER(C_CUSTKEY);
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE tpch.LINEITEM
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references tpch.ORDERS(O_ORDERKEY);
COMMIT WORK;
ALTER TABLE tpch.LINEITEM
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references
tpch.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
alter table CUSTOMER rename to customer ;
alter table LINEITEM rename to lineitem ;
alter table NATION rename to nation ;
alter table ORDERS rename to orders ;
alter table PART rename to part ;
alter table PARTSUPP rename to partsupp ;
alter table REGION rename to region ;
alter table SUPPLIER rename to supplier ;
COMMIT WORK;

建表

修改MySQL配置

在建表之前我们需要先修改MySQL的一些配置。一些版本的mysql对通过文件导入导出作了限制，默认不允许。

查看配置，执行以下MySQL命令：

SHOW VARIABLES LIKE "secure_file_priv";

如果value值为null，则为禁止；如果有文件夹目录，则只允许改目录下文件（测试子目录也不行）；如果为空，则不限制目录。

修改MySQL配置文件 my.cnf ，查看是否有 secure_file_priv= 这一行的内容，如果没有则需要手动添加。我这里是直接置空：

secure_file_priv=

表示不限制目录。等号一定要有，否则mysql无法启动。修改完配置文件后，重启MySQL生效。

生成数据表

开启MySQL服务，登录MySQL，使用命令 \. path-to-tpc-h/dbgen/dss.ddl 或者 source path-to-tpc-h/dbgen/dss.ddl 来使用修改后的 dss.dll文件创建数据表

导入 dss.dll 的命令

执行成功后查看数据库会发现有一个名叫tpch的数据库；切换到数据库下可以看到由8张表。

导入后结果展示

建立关联关系

与上述同理，使用命令 \. path-to-tpc-h/dbgen/dss.ri 或者 source path-to-tpc-h/dbgen/dss.ri 来使用修改后的 dss.dll文件创建表约束

如果想查看关联关系是否建立成功，可以通过命令 show create table customer\G; 查看

导入 tbl 数据文件

由于我要导入的数据很大，导入数据的时间会很长，我写了一个数据导入的脚本进行操作。

首先创建了一个 loaddata.sql 文件，该文件有所有导入数据所需的SQL命令。文件内容如下：

load data infile "/home/czhang/mysql-tpc-h/dbgen/part.tbl" into table part fields terminated by "|";
load data infile "/home/czhang/mysql-tpc-h/dbgen/region.tbl" into table region fields terminated by "|" lines terminated by "|\n";
load data infile "/home/czhang/mysql-tpc-h/dbgen/nation.tbl" into table nation fields terminated by "|";
load data infile "/home/czhang/mysql-tpc-h/dbgen/customer.tbl" into table customer fields terminated by "|";
load data infile "/home/czhang/mysql-tpc-h/dbgen/supplier.tbl" into table supplier fields terminated by "|" lines terminated by "|\n";
load data infile "/home/czhang/mysql-tpc-h/dbgen/orders.tbl" into table orders fields terminated by "|";
load data infile "/home/czhang/mysql-tpc-h/dbgen/partsupp.tbl" into table partsupp fields terminated by "|";
load data infile "/home/czhang/mysql-tpc-h/dbgen/lineitem.tbl" into table lineitem fields terminated by "|";

需要注意的是添加了关联关系，导入数据的顺序按照关联关系导入

之后编写bash脚本进行导入，脚本内容如下：

#!/bin/bash
mysql -u root -pPASSWORD tpch < /home/czhang/mysql-tpc-h/loaddata.sql

将 PASSWORD改成自己登录数据库的密码，数据库名和SQL命令文件路径改成自己的。

随后运行该脚本即可

导入完成后可运行以下SQL命令查看导入结果：

SHOW TABLE STATUS FROM tpch;

生成查询语句

前面介绍了 DBGEN ，是用来生成表与数据的，现在来介绍 QGEN ，这也是编译后生成的可执行文件，主要是用来生成查询 SQL 语句的，同样的，和dbgen一样，可以再README中查看该命令的介绍。

编写一个脚本用于生成查询语句，该脚本位于 dbgen文件夹下，脚本内容如下：

#! /bin/bash

# 拷贝 dists.dss (SQL语句格式化参考) 与 qgen (生成SQL的可执行文件) 到官方提供的 queries (22条未格式化的SQL语句) 目录
cp dists.dss qgen queries/
cd queries/

for i in {1..22}
do
  sed -i "/^:n./d" $i.sql
  ./qgen -d $i > d$i.sql
done

注意：在生成查询语句前，queries文件夹中的 22 条未格式化的SQL语句 模板文件不要改动，否则qgen会读取错误导致无法生成正确的查询语句文件

运行脚本后，可以看到现在已经有了 22 个SQL脚本，分别存放的是 22 条测试语句，但是这些测试语句还不能直接使用，需要做些许修改。

修改一些变量的内容：

# 将所有 limit -1 替换为 limit 1
sed -i '$s/limit -1/limit 1/' *.sql
# 去掉脚本中的 date 函数
sed -i 's/ date / /g' *.sql

然后需要手动修改一下 1.sql 、 13.sql 、15.sql 文件

################################# 1.sql ###########################
# 将 where 条件的内容修改 
# where
# 	 l_shipdate <= date '1998-12-01' - interval '90' day (3)

# 修改为 如下
where
	l_shipdate <= '1998-12-01' - interval '90' day
	
################################# 13.sql ##########################
# 加上字段别名 
#from
#	(
#		select
#			c_custkey,
#			count(o_orderkey)
from
	(
		select
			c_custkey,
			count(o_orderkey) as c_count
# 去除掉 表别名
#			c_custkey
#	) as c_orders (c_custkey, c_count)
#group by
#	c_count
			c_custkey
	) as c_orders
group by
	c_count
################################ 15.sql ############################
# 以下是修改后的文件
use tpch; 
with revenue0 (supplier_no, total_revenue) as
	(select
		l_suppkey,
		sum(l_extendedprice * (1 - l_discount))
	from
		lineitem
	where
		l_shipdate >= '1996-01-01'
		and l_shipdate < '1996-01-01' + interval '3' month
	group by
		l_suppkey)


select
	s_suppkey,
	s_name,
	s_address,
	s_phone,
	total_revenue
from
	supplier,
	revenue0
where
	s_suppkey = supplier_no
	and total_revenue = (
		select
			max(total_revenue)
		from
			revenue0
	)
order by
	s_suppkey;

至此，整个SQL就修改完成了

进行TPC-H基准测试

首先使用命令进行统一的修改。为每条 SQL 语句指名使用的数据库。

# 在个 SQL 文件的第一行 添加 use tpch; 的语句。
sed -i '1a use tpch; \n' *.sql

之后在 queries文件夹中运行命令：

for i in {1..22}; do mysql -u root -p123456 < d$i.sql; done > run.log 2>&1

即可开始基准测试。

Reference：

TPC-H在MemSQL的测试

linux环境下测试mysql+TPC-H（实测全流程：环境安装+导入数据+执行查询）

MySQL性能测试——tpch工具

文章作者: Antonio
文章链接: https://antoniozc666.github.io/2024/05/19/linux-huan-jing-xia-shi-yong-tpc-h-ce-shi-mysql-jiao-cheng/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Antonio !
MySQL 工作负载 环境搭建 TPC-H 安装教程
  目录