TPC-H对GBase 8s数据库基础能力测试

什么是 TPC-H 测试

1、 TPC- H 是针对数据库决策支持能力的测试基准，主要是评价服务器数据库的分析处理能力。测试数据库复杂查询的响应时间，以每小时执行的查询数作为度量指标。
2、 TPC-H 基准模型中定义了一个数据库模型，容量可以在 1GB~10000GB 的 8 个级别中选择。
3、 数据库中共有 8 张表，支持多用户并发执行复杂的动态查询，同时有并发执行表修改操作
4、 TPC-H 测试围绕 22 个 SELECT 语句展开，还有 2 条带有 insert 和 delete 程序段的更新流语句
5、 TPC-H 测试分解为 3 个子测试：数据装载测试、Power 测试和 Throughput 测试

TPC-H 安装配置

TPC-H软件获取位置是：http://tpc.org/tpc_documents_current_versions/current_specifications5.asp
本文本介绍使用的是(2.18.0_rc2版)：https://gbasedbt.com/dl/tpc-h/09f4c75d-a329-41ca-b96b-9cd61be5aad1-tpc-h-tool.zip
解压

unzip 09f4c75d-a329-41ca-b96b-9cd61be5aad1-tpc-h-tool.zip

进入2.18.0_rc2/dbgen，编译makefile文件

cd 2.18.0_rc2/dbgen
cp makefile.suite makefile
vim makefile

需要修改的内容约在103行

################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC      = gcc                   # Linux一般使用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 = INFORMIX             # 选用兼容的数据库类型，如果没有兼容的，需要自己在tpcd.h中定义
MACHINE  = LINUX                # 当前使用Linux
WORKLOAD = TPCH                 # 这里只有TPCH

执行编译 make

[gbasedbt@localhost dbgen]$ make
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o build.o build.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o driver.o driver.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bm_utils.o bm_utils.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rnd.o rnd.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o print.o print.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o load_stub.o load_stub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bcd2.o bcd2.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o speed_seed.o speed_seed.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o text.o text.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o permute.o permute.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rng64.o rng64.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o rng64.o -lm
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o qgen.o qgen.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o varsub.o varsub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DINFORMIX -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.o text.o bcd2.o permute.o speed_seed.o rng64.o -lm

TPC-H数据生成

生成1G数据

[gbasedbt@localhost dbgen]$ ./dbgen -vf -s 1
TPC-H Population Generator (Version 2.18.0)
Copyright Transaction Processing Performance Council 1994 - 2010
Generating data for suppliers table/
Preloading text ... 100%
done.
Generating data for customers tabledone.
Generating data for orders/lineitem tablesdone.
Generating data for part/partsupplier tablesdone.
Generating data for nation tabledone.
Generating data for region tabledone.

生成的数据，GBase 8s数据库可直接使用。

建立数据库表

使用GBase 8s数据库用户创建测试库tpch
使用dbgen目录下的dss.ddl文件中的语句创建8张表

[gbasedbt@localhost dbgen]$ cp dss.ddl dss.sql
[gbasedbt@localhost dbgen]$ dbaccess tpch dss.sql 

使用导数工具load/dbload或者是外部表导入以下表数据，数据量约为1G

[gbasedbt@localhost dbgen]$ ls -al *.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt  24346144 8月   2 15:29 customer.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt 759863287 8月   2 15:29 lineitem.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt      2224 8月   2 15:29 nation.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt 171952161 8月   2 15:29 orders.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt 118984616 8月   2 15:29 partsupp.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt  24135125 8月   2 15:29 part.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt       389 8月   2 15:29 region.tbl
-rw-rw-r-- 1 gbasedbt gbasedbt   1409184 8月   2 15:29 supplier.tbl

如：

load from customer.tbl insert into customer;
load from lineitem.tbl insert into lineitem;
load from nation.tbl insert into nation;
load from orders.tbl insert into orders;
load from partsupp.tbl insert into partsupp;
load from part.tbl insert into part;
load from region.tbl insert into region;
load from supplier.tbl insert into supplier;

根据数据库实际SQL语法修改dss.ri（主键和外键关系）

[gbasedbt@localhost dbgen]$ cp dss.ri dss_ri.sql
[gbasedbt@localhost dbgen]$ vi dss_ri.sql 

如：
原有
-- For table NATION
ALTER TABLE TPCD.NATION
ADD PRIMARY KEY (N_NATIONKEY);

ALTER TABLE TPCD.NATION
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION;
改成
-- For table NATION
ALTER TABLE NATION
ADD CONSTRAINT PRIMARY KEY (N_NATIONKEY);

ALTER TABLE NATION
ADD CONSTRAINT FOREIGN KEY (N_REGIONKEY) references REGION;

创建关系

[gbasedbt@localhost dbgen]$ dbaccess tpch dss_ri.sql

SQL执行

SQL语句创建
进入queries目录下，复制dbgen目录下的dists.dss和qgen到当前目录下

[gbasedbt@localhost dbgen]$ cd queries/
[gbasedbt@localhost queries]$ cp ../dists.dss ./ 
[gbasedbt@localhost queries]$ cp ../qgen ./

根据22个SQL文件模板{1…22}.sql，生成具体的SQL语句Q{1…22}.sql

[gbasedbt@localhost queries]$ for i in {1..22}
> do
>   ./qgen -d $i > Q${i}.sql
> done

根据数据库实际语法修改语法
可能需要修改的地方包括：
1，SQL文件的最后一行需要删除或者注释（sed -i ‘$d’ Q*.sql）
2，DATE类型写法
3，INTERVAL类型写法
4，extract函数（sed -i ‘s/extract(year from /year(/g’ Q*.sql’）

编写测试脚本，调用22条SQL语句

#!/bin/bash
# filename: tpch.sh
dbaccess tpch Q1.sql >> /dev/null
dbaccess tpch Q2.sql >> /dev/null
dbaccess tpch Q3.sql >> /dev/null
dbaccess tpch Q4.sql >> /dev/null
dbaccess tpch Q5.sql >> /dev/null
dbaccess tpch Q6.sql >> /dev/null
dbaccess tpch Q7.sql >> /dev/null
dbaccess tpch Q8.sql >> /dev/null
dbaccess tpch Q9.sql >> /dev/null
dbaccess tpch Q10.sql >> /dev/null
dbaccess tpch Q11.sql >> /dev/null
dbaccess tpch Q12.sql >> /dev/null
dbaccess tpch Q13.sql >> /dev/null
dbaccess tpch Q14.sql >> /dev/null
dbaccess tpch Q15.sql >> /dev/null
dbaccess tpch Q16.sql >> /dev/null
dbaccess tpch Q17.sql >> /dev/null
dbaccess tpch Q18.sql >> /dev/null
dbaccess tpch Q19.sql >> /dev/null
dbaccess tpch Q20.sql >> /dev/null
dbaccess tpch Q21.sql >> /dev/null
dbaccess tpch Q22.sql >> /dev/null

执行测试脚本

[gbasedbt@localhost queries]$ nohup ./tpch.sh &

执行完成后，查看各个SQL的执行时间
注：Q15含有额外的创建视图和删除视图语句

[gbasedbt@localhost queries]$ grep 'Elapsed time:' nohup.out 
Elapsed time: 18.646 sec
Elapsed time: 0.624 sec
Elapsed time: 29.788 sec
Elapsed time: 11.668 sec
Elapsed time: 14.041 sec
Elapsed time: 1.243 sec
Elapsed time: 25.292 sec
Elapsed time: 75.645 sec
Elapsed time: 17.696 sec
Elapsed time: 3.155 sec
Elapsed time: 6.368 sec
Elapsed time: 19.843 sec
Elapsed time: 4.097 sec
Elapsed time: 0.005 sec
Elapsed time: 2.500 sec
Elapsed time: 0.002 sec
Elapsed time: 1.942 sec
Elapsed time: 0.886 sec
Elapsed time: 59.137 sec
Elapsed time: 605.902 sec
Elapsed time: 7.333 sec
Elapsed time: 83.765 sec
Elapsed time: 3.385 sec

附：22个SQL语句的功能解析

Q01 统计查询
Q02 WHERE条件中，使用子查询(=)
Q03 多表关联统计查询，并统计(SUM)
Q04 WHERE条件中，使用子查询(EXISTS)，并统计(COUNT)
Q05 多表关联查询(=)，并统计(SUM)
Q06 条件(BETWEEN AND)查询，并统计(SUM)
Q07 带有FROM子查询，从结果集中统计(SUM)
Q08 带有FROM多表子查询，从结果集中的查询列上带有逻辑判断(WHEN THEN ELSE)的统计(SUM)
Q09 带有FROM多表子查询，查询表中使用函数(EXTRACT)，从结果集中统计(SUM)
Q10 多表条件查询(>=, <)，并统计(SUM)
Q11 在GROUP BY中使用比较条件(HAVING >)，比较值从子查询中查出
Q12 带有逻辑判断(WHEN AND/ WHEN OR)的查询，并统计(SUM)
Q13 带有FROM子查询，子查询中使用外联结
Q14 使用逻辑判断(WHEN ELSE)的查询
Q15 使用视图和表关联查询
Q16 在WHERE子句中使用子查询，使用IN/ NOT IN判断条件，并统计(COUNT)
Q17 在WHERE子句中使用子查询，使用<比较，使用了AVG函数
Q18 在WHERE子句中使用IN条件从子查询结果中比较
Q19 多条件比较查询
Q20 WHERE条件子查询(三层)
Q21 在WHERE条件中使用子查询，使用EXISTS和NOT EXISTS判断
Q22 在WHERE条件中使用判断子查询、IN、NOT EXISTS，并统计(SUM、COUNT)查询结果