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

Posted on Edited on In Views:

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

  1. 打开dbgen目录。 
    1
    cd dbgen
  2. 复制makefile文件。 
    1
    cp makefile.suite Makefile
  3. 修改Makefile文件中的CC、DATABASE、MACHINE、WORKLOAD等参数定义。 打开Makefile文件。 修改CC、DATABASE、MACHINE、WORKLOAD参数的定义。 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    ################
    ## 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退出并保存。
  4. 修改tpcd.h文件，并添加新的宏定义。 打开tpcd.h文件。 vim tpcd.h 添加如下宏定义。 
    1
    2
    3
    4
    5
    6
    7
    8
    #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退出并保存。
  5. 对文件进行编译。 
    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 1. 将qgen与dists.dss复制到queries目录下。 
1
2
cp qgen queries
cp dists.dss queries
2. 使用以下脚本生成查询。 在queries 目录下，创建脚本gen.sh 
1
2
3
4
5
#!/usr/bin/bash
for i in {1..22}
do  
  ./qgen -d $i -s 100 > db"$i".sql
done
1
./gen.sh
3. 查询sql 进行调整 
1
dos2unix *
去掉生成文件中的"limit -1", 去掉day 后面的(3), 以q1 为例， sql 如下

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
-- 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;              ---  去掉这行

加载数据

  1. 下载加载脚本 

    1
    2
    3
    4
    5
    6
    7
    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 的步骤

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- 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

  1. 按照mysql 客户端 
    1
    yum install mysql -y
  2. 开始加载 
    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 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
{

    "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 文件即可