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

详细可以参考 tpch_reference

编译

下载源码包tpch

1. 打开dbgen目录。

   cd dbgen

2. 复制makefile文件。

   cp makefile.suite Makefile

3. 修改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退出并保存。
4. 修改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退出并保存。
5. 对文件进行编译。

   make

   编译完成后该目录下会生成两个可执行文件：

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

生成数据

生成测试数据

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

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

生成查询sql

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

   cp qgen queries
   cp dists.dss queries

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

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

./gen.sh

3. 查询sql 进行调整

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

加载数据

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 的步骤

2. 修改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

3. 按照mysql 客户端

   yum install mysql -y

4. 开始加载

   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 执行的次数， 会取平均值

}

运行脚本

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

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