一、TPC-H介绍
TPC Benchmark H(TPC-H)是一个决策支持的基准,它由一系列面向商务应用的查询和并行数据修改组成。基准里选择的查询和组成数据库的数据在商业上都具有广泛的代表性并且易于实现。TPC-H基准阐明了决策支持系统的三个方面:
分析大量的数据,执行高复杂度的查询,回答关键的、经常需要回答的商业问题。
通过在可控环境下执行一些列针对标准数据库的查询,TPC-H评估各种决策支持系统的性能。
下图说明了TPC-H所对应的商业环境,并且突出了TPC-H测试和其他TPC测试的根本区别。
TPC-H的schema定义如下:
TPC-H数据库是由8个单独的基本表构成,这些表的列与列之间的关系如上图所示。
8个基本表的定义如下:
PART表定义 | ||
列名 | 数据类型需求 | 注释 |
P_PARTKEY | identifier | 填充SF*200,000 |
P_NAME | variable text, size 55 | |
P_MFGR | fixed text, size 25 | |
P_BRAND | fixed text, size 10 | |
P_TYPE | variable text, size 25 | |
P_SIZE | integer | |
P_CONTAINER | fixed text, size 10 | |
P_RETAILPRICE | decimal | |
P_COMMENT | variable text, size 23 | |
主键: P_PARTKEY | ||
SUPPLIER表定义 | ||
列名 | 数据类型需求 | 注释 |
S_SUPPKEY | identifier | 填充SF*10,000 |
S_NAME | fixed text, size 25 | |
S_ADDRESS | variable text, size 40 | |
S_NATIONKEY | Identifier | 外键: N_NATIONKEY |
S_PHONE | fixed text, size 15 | |
S_ACCTBAL | decimal | |
S_COMMENT | variable text, size 101 | |
主键: S_SUPPKEY | ||
PARTSUPP表定义 | ||
列名 | 数据类型需求 | 注释 |
PS_PARTKEY | Identifier | 外键: P_PARTKEY |
PS_SUPPKEY | Identifier | 外键: S_SUPPKEY |
PS_AVAILQTY | integer | |
PS_SUPPLYCOST | Decimal | |
PS_COMMENT | variable text, size 199 | |
主键: PS_PARTKEY, PS_SUPPKEY | ||
CUSTOMER表定义 | ||
列名 | 数据类型需求 | 注释 |
C_CUSTKEY | Identifier | 填充SF*150,000 |
C_NAME | variable text, size 25 | |
C_ADDRESS | variable text, size 40 | |
C_NATIONKEY | Identifier | 外键:N_NATIONKEY |
C_PHONE | fixed text, size 15 | |
C_ACCTBAL | Decimal | |
C_MKTSEGMENT | fixed text, size 10 | |
C_COMMENT | variable text, size 117 | |
主键: C_CUSTKEY | ||
ORDERS表定义 | ||
列名 | 数据类型需求 | 注释 |
O_ORDERKEY | Identifier | 填充SF*1,500,000 |
O_CUSTKEY | Identifier | 外键: C_CUSTKEY |
O_ORDERSTATUS | fixed text, size 1 | |
O_TOTALPRICE | Decimal | |
O_ORDERDATE | Date | |
O_ORDERPRIORITY | fixed text, size 15 | |
O_CLERK | fixed text, size 15 | |
O_SHIPPRIORITY | Integer | |
O_COMMENT | variable text, size 79 | |
主键: O_ORDERKEY | ||
LINEITEM表定义 | ||
列名 | 数据类型需求 | 注释 |
L_ORDERKEY | identifier | 外键: O_ORDERKEY |
L_PARTKEY | identifier | 外键: P_PARTKEY和L_SUPPKEY的组合引用 (PS_PARTKEY, PS_SUPPKEY) |
L_SUPPKEY | Identifier | 外键: S_SUPPKEY和L_PARTKEY的组合引用(PS_PARTKEY, PS_SUPPKEY) |
L_LINENUMBER | integer | |
L_QUANTITY | decimal | |
L_EXTENDEDPRICE | decimal | |
L_DISCOUNT | decimal | |
L_TAX | decimal | |
L_RETURNFLAG | fixed text, size 1 | |
L_LINESTATUS | fixed text, size 1 | |
L_SHIPDATE | date | |
L_COMMITDATE | date | |
L_RECEIPTDATE | date | |
L_SHIPINSTRUCT | fixed text, size 25 | |
L_SHIPMODE | fixed text, size 10 | |
L_COMMENT | variable text size 44 | |
主键: L_ORDERKEY, L_LINENUMBER | ||
NATION表定义 | ||
列名 | 数据类型需求 | 注释 |
N_NATIONKEY | identifier | 填充25个国家 |
N_NAME | fixed text, size 25 | |
N_REGIONKEY | identifier | 外键: R_REGIONKEY |
N_COMMENT | variable text, size 152 | |
主键: N_NATIONKEY | ||
REGION表定义 | ||
列名 | 数据类型需求 | 注释 |
R_REGIONKEY | identifier | 填充5个区域 |
R_NAME | fixed text, size 25 | |
R_COMMENT | variable text, size 152 | |
主键: R_REGIONKE |
二、TPC-H数据生成
如果-s(scale factor)是1或者10的时候可以不对数据文件进行切割,如果scale factor是100或者100以上,为了加快数据load时间,可以对大表文件进行切割,比如lineitem表、orders表等。下面以100G数据,即scale factor 100进行演示。
1. 下载TPC-H V3.0工具包tpc-h-tool-v3.zip
http://www.tpc.org/tpc_documents_current_versions/download_programs/tools-download-request5.asp?bm_type=TPC-H&bm_vers=3.0.0&mode=CURRENT-ONLY
2. 解压工具包:unzip tpc-h-tool-v3.zip
3. cp makefile.suite Makefile
修改Makefile如下:
CC = 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 = KINGBASE
MACHINE = LINUX
WORKLOAD = TPCH
同时在tpcd.h中添加KINGBASE相关的宏定义:
#ifdef KINGBASE
#define GEN_QUERY_PLAN "EXPLAIN"
#define START_TRAN "BEGIN TRANSACTION"
#define END_TRAN "COMMIT;"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "LIMIT %d\n"
#define SET_DBASE ""
#endif
4. 执行make,编译生成dbgen和qgen可执行文件
5. 执行下面的脚本自动生成TPC-H数据
脚本生成表数据:
#!/bin/bash
#generate region data
./dbgen -vf -s 100 -T r
#generate nation data
./dbgen -vf -s 100 -T n
#generate customer data
./dbgen -vf -s 100 -T c
#generate supplier data
./dbgen -vf -s 100 -T s
#generate part data
./dbgen -vf -s 100 -T P
#generate partsupp data
for i in {1..5}
do
./dbgen -vf -s 100 -T S -C 5 -S $i
done
#generate orders data
for i in {1..10}
do
./dbgen -vf -s 100 -T O -C 10 -S $i
done
#generate lineitem data
for i in {1..20}
do
./dbgen -vf -s 100 -T L -C 20 -S $i
done
三、 TPC-H SQL语句生成
执行下面的脚本自动生成TPC-H查询语句:
#!/usr/bin/bash
export DSS_QUERY=/512/TPC-H_Tools_v3.0.0/dbgen/queries
for i in {1..22}
do
./qgen -d $i -s 100 > query"$i".sql
done
生成的语句:
[subi@steven54 query]$ ls -ltr query*
-rw-r--r-- 1 root root 577 Dec 27 16:34 query1.sql
-rw-r--r-- 1 root root 738 Dec 27 16:34 query2.sql
-rw-r--r-- 1 root root 456 Dec 27 16:34 query3.sql
-rw-r--r-- 1 root root 400 Dec 27 16:34 query4.sql
-rw-r--r-- 1 root root 535 Dec 27 16:34 query5.sql
-rw-r--r-- 1 root root 288 Dec 27 16:34 query6.sql
-rw-r--r-- 1 root root 862 Dec 27 16:34 query7.sql
-rw-r--r-- 1 root root 841 Dec 27 16:34 query8.sql
-rw-r--r-- 1 root root 650 Dec 27 16:34 query9.sql
-rw-r--r-- 1 root root 571 Dec 27 16:34 query10.sql
-rw-r--r-- 1 root root 553 Dec 27 16:34 query11.sql
-rw-r--r-- 1 root root 639 Dec 27 16:34 query12.sql
-rw-r--r-- 1 root root 392 Dec 27 16:34 query13.sql
-rw-r--r-- 1 root root 374 Dec 27 16:34 query14.sql
-rw-r--r-- 1 root root 573 Dec 27 16:34 query15.sql
-rw-r--r-- 1 root root 530 Dec 27 16:34 query16.sql
-rw-r--r-- 1 root root 326 Dec 27 16:34 query17.sql
-rw-r--r-- 1 root root 495 Dec 27 16:34 query18.sql
-rw-r--r-- 1 root root 1020 Dec 27 16:34 query19.sql
-rw-r--r-- 1 root root 645 Dec 27 16:34 query20.sql
-rw-r--r-- 1 root root 713 Dec 27 16:34 query21.sql
-rw-r--r-- 1 root root 705 Dec 27 16:34 query22.sql
[subi@steven54 query]$
四、金仓数据库自动化测试TPC-H脚本
搭建金仓数据库服务器,创建完表信息后首先对步骤二生成的tbl文件进行处理,删除最后的'|':
for i in `ls *.tbl*`
do
sed -i 's/|$//' $i
done
如果更改了文件后缀,比如nation.sql之类的,将tbl替换成sql即可。
数据文件处理完成后可执行脚本自动load数据到金仓数据库:
if [ $1 -gt 1 ]; then
for i in $( seq 1 $1)
do
echo "Copy" $2 "FROM /512/TPC-H_Tools_v3.0.0/dbgen/"$2".tbl.$i WITH DELIMITER AS '|';" > test.sql
./ksql -p port -d database_name -f test.sql
done
else
echo echo "Copy" $2 "FROM /512/TPC-H_Tools_v3.0.0/dbgen/"$2".tbl WITH DELIMITER AS '|';" > test.sql
./ksql -p port -d database_name -f test.sql
fi
自动load完数据后可以进行TPC-H的自动化测试,查看自动化执行的结果:
for i in {1..22}
do
echo "\timing" > test.sql
echo "\i /512/ TPC-H_Tools_v3.0.0/dbgen/query$i.sql" >> test.sql
./ksql -p port -d database_name -f test.sql > /home/test/result_$i
done
如果想看计划树,可以在query的语句前加explain analyze,计划树就会保存在每个语句对应的result中。后续主要继续集成自动化测试脚本,使其更加自动化,尽量减少人工工作量。
【更多人大金仓数据库信息,详见https://help.kingbase.com.cn/】