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

  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退出并保存。

  1. 修改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退出并保存。

  1. 对文件进行编译。
    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
  1. 查询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
    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 的步骤

  1. 修改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 文件即可

文章目录
  1. 1. Abstract
  2. 2. 介绍
  3. 3. 编译
  4. 4. 生成数据
    1. 4.1. 生成测试数据
    2. 4.2. 生成查询sql
  5. 5. 加载数据
  6. 6. 开始测试
本站PV 本站UV 本页PV