DB性能测试-常用3套件-手把手一步一步跑TPCH

Posted on 2020-06-22 In Database , 性能测试 Views: Symbols count in article: 1.8k Reading time ≈ 7 mins.

性能测试 -- DB性能测试-常用3套件-手把手一步一步跑TPCH

Abstract

把过去的写的一篇笔记分享一下，数据库最常用的测试三套件， sysbench – oltp 测试， tpch – olap 测试， tpcc – 事务性能测试。
本文手把手一步一步 run TPCH, 即使从来没有跑过数据库的，也可以直接上手运行TPCH, 本文以运行TPCH on MySQL, 如果读者想要运行tpch 到postgres 或者其他的数据，可以先参考本博文，然后基于本博文，再到github上搜索相应数据库的TPCH 库即可。

整个过程，分为

介绍
编译
数据生成
数据加载
性能测试
表结构介绍

介绍

TPC现有的测试标准为：TPC-E、TPC-C、TPC-H、TPC-App。根据这4个测试基准，目前TPC主要包括的4个技术小组委员会：TPC-E 技术小组委员会、TPC-C 技术小组委员会、TPC-H技术小组委员会、TPC-App技术小组委员会。前期TPC使用过但目前已经停止使用的测试标准有：TPC-A、TPC-B（数据库处理能力测试标准）、TPC-D、TPC-R（决策支持系统测试标准，类TPC-H）、TPC-W（Web处理能力测试标准）。

TPC-H（商业智能计算测试）是美国交易处理效能委员会(TPC,Transaction Processing Performance Council) 组织制定的用来模拟决策支持类应用的一个测试集.目前,在学术界和工业界普遍采用它来评价决策支持技术方面应用的性能. 这种商业测试可以全方位评测系统的整体商业计算综合能力，对厂商的要求更高，同时也具有普遍的商业实用意义，目前在银行信贷分析和信用卡分析、电信运营分析、税收分析、烟草行业决策分析中都有广泛的应用。

TPC-H 基准测试是由 TPC-D(由 TPC 组织于 1994 年指定的标准,用于决策支持系统方面的测试基准)发展而来的.TPC-H 用 3NF 实现了一个数据仓库,共包含 8 个基本关系,其数据量可以设定从 1G~~3T 不等。TPC-H 基准测试包括 22 个查询(Q1~~Q22),其主要评价指标是各个查询的响应时间,即从提交查询到结果返回所需时间.TPC-H 基准测试的度量单位是每小时执行的查询数( QphH@size)，其中 H 表示每小时系统执行复杂查询的平均次数，size 表示数据库规模的大小,它能够反映出系统在处理查询时的能力.TPC-H 是根据真实的生产运行环境来建模的,这使得它可以评估一些其他测试所不能评估的关键性能参数.总而言之,TPC 组织颁布的TPC-H 标准满足了数据仓库领域的测试需求,并且促使各个厂商以及研究机构将该项技术推向极限。

详细可以参考 tpch_reference

编译

下载源码包tpch
tpch_download

打开dbgen目录。
1
cd dbgen
复制makefile文件。
1
cp makefile.suite Makefile

修改Makefile文件中的CC、DATABASE、MACHINE、WORKLOAD等参数定义。
打开Makefile文件。
修改CC、DATABASE、MACHINE、WORKLOAD参数的定义。

################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, ORACLE,
#                                  SQLSERVER, SYBASE, TDAT (Teradata)
# 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
MACHINE = LINUX
WORKLOAD = TPCH

按ECS键，然后输入:wq退出并保存。

修改tpcd.h文件，并添加新的宏定义。
打开tpcd.h文件。
vim 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

按ECS键，然后输入:wq退出并保存。

对文件进行编译。
1
make
编译完成后该目录下会生成两个可执行文件：

dbgen：数据生成工具。在使用InfiniDB官方测试脚本进行测试时，需要用该工具生成tpch相关表数据。
qgen：SQL生成工具。生成初始化测试查询，由于不同的seed生成的查询不同，为了结果的可重复性，请使用附件提供的22个查询。

生成数据

生成测试数据

可以生成tpch 10g 也可以100g，甚至1TB, 本例以100g 为例， 100g 的记录数在6亿条左右，和普通一家中小型公司的大表规格差不多

1
2
3

./dbgen -s 100
mkdir tpch100
mv *.tbl tpch100

生成查询sql

将qgen与dists.dss复制到queries目录下。
1
2
cp qgen queries
cp dists.dss queries

使用以下脚本生成查询。
在queries 目录下，创建脚本gen.sh

#!/usr/bin/bash
for i in {1..22}
do  
  ./qgen -d $i -s 100 > db"$i".sql
done

./gen.sh

查询sql 进行调整
1
dos2unix *
去掉生成文件中的”limit -1”, 去掉day 后面的(3), 以q1 为例， sql 如下

-- using default substitutions


select
        l_returnflag,
        l_linestatus,
        sum(l_quantity) as sum_qty,
        sum(l_extendedprice) as sum_base_price,
        sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
        sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
        avg(l_quantity) as avg_qty,
        avg(l_extendedprice) as avg_price,
        avg(l_discount) as avg_disc,
        count(*) as count_order
from
        lineitem
where
        l_shipdate <= date '1998-12-01' - interval '90' day (3)   --- 把(3) 去掉
group by
        l_returnflag,
        l_linestatus
order by
        l_returnflag,
        l_linestatus;
limit -1;              ---  去掉这行

加载数据

下载加载脚本

mkdir load
cd load
wget https://raw.githubusercontent.com/longdafeng/test/master/shell/tpch/load.sh ./
wget https://raw.githubusercontent.com/longdafeng/test/master/shell/tpch/polar.index.sh ./
chmod +x *
cp ../dss.ri  ../dss.ddl ./

因为这个测试里面带了polardb 的创建index 脚本，读者如果不使用polardb，则可以不用下载polar.index.sh，并修改load.sh 文件，去掉设置index 的步骤

修改dss.ri 脚本
dss.ri 主要是设置primary key 和foreign key

-- Sccsid:     @(#)dss.ri       2.1.8.1
-- TPCD Benchmark Version 8.0

CONNECT TO TPCD;           

--ALTER TABLE TPCD.REGION DROP PRIMARY KEY;
--ALTER TABLE TPCD.NATION DROP PRIMARY KEY;
--ALTER TABLE TPCD.PART DROP PRIMARY KEY;
--ALTER TABLE TPCD.SUPPLIER DROP PRIMARY KEY;
--ALTER TABLE TPCD.PARTSUPP DROP PRIMARY KEY;
--ALTER TABLE TPCD.ORDERS DROP PRIMARY KEY;
--ALTER TABLE TPCD.LINEITEM DROP PRIMARY KEY;
--ALTER TABLE TPCD.CUSTOMER DROP PRIMARY KEY;

把其中”CONNECT TO TPCD; “删除掉，把所有的”TPCD.” 给去除掉

如果不想修改dss.ri, 可以直接下载现成的dss.ri

按照mysql 客户端
1
yum install mysql -y

开始加载

1 2	nohup./load.sh hostxxx portxxx userxxx passwordxxx dbxxx > load.log 2>&1 & tail -f load.log

其中hostxxx 为db 地址
portxxx 为db 端口
userxxx 为用户名 — 需要提前创建好用户名，对于云上用户，还需要设置白名单，把机器ip 白名单设置进去
passwordxxx 为用户密码
dbxxx 为要创建的数据库名字，因为脚本会自动加载在“生成数据” 一节中创建的目录（我们例子中是tpch100），因此数据库名也必须是上一节生成数据创建的目录

开始测试

下载测试脚本 https://github.com/longdafeng/test/tree/master/python/tpch

配置配置文件example.cfg

{

    "host":"xxxx"                 // 数据库机器名
    "port":"3306"                 // 数据库端口号
    "username":"xxxxx"            // 数据库用户名
    "password":"xxxx"             // 数据库密码
    "database":"xxxxx"            // 数据库 库名
    //input dir
    "input_dir":"mysql"           // 查询sql 存放的目录，如果是测试mysql 系列，则这里是mysql， 如果是pg，则需要生成pg的查询sql
    
    //output_dir
    "output_dir":"polardb80"      // 打印日志的目录
    
    //mysql_setting, set mysql variable
    //"mysql_setting": "set max_parallel_degree=32;"
    "mysql_setting": ""

    //query per sql times
    "times_per_sql":"1"            // 每条sql 执行的次数， 会取平均值

}

运行脚本

1 2	nohup ./tpch.py -f example.cfg > run.log 2>&1 & tail -f run.log

最后进入配置文件“output_dir” 目录下，查看result 文件即可