【数据库性能测试—TPC-H】

TPC-H 简介

TPC-H是一套针对数据库决策支持能力的测试基准，通过模拟数据库中与业务相关的复杂查询和并行的数据修改操作考察数据库的综合处理能力,获取数据库操作的响应时间和每小时执行的查询数指标(QphH@Size)。

TPC-H基准模型涉及22条复杂的select查询流语句和2条带有insert和delete程序段的更新流语句。

TPC-H性能测试分为Power测试和Throughout测试两种类型：
（1）Power测试是随机执行22条查询流中的一条测试流和2条更新流中的一条测
试流，考核指标为QppH@Size；
（2）Throughout测试执行的是多条查询流和一条更新流对数据库的混合操作，
考核指标是QthH@Size，

1. 数据库模型

TPC- H 主要目的是评价特定查询的决策支持能力，强调服务器在数据挖
掘、分析处理方面的能力。通过模拟零售商市场分析，构建一个数据仓
库。

表说明

part表示零件信息，主键为p_partkey，取值范围1~SF200000，与partsupp关联。
supplier表示供应商信息，主键为s_suppkey，取值范围1~SF10000，和partsupp、customer、nation关联。
partsupp表示供应商零件信息，主键为ps_partkey、ps_suppkey，与part、supplier、lineitem关联。
customer表示消费者信息，主键为c_custkey，取值范围1~SF150000，与orders表关联。
orders表示订单信息，主键为o_orderkey，取值范围1~SF1500000与lineitem表关联。
lineitem表示在售商品信息，主键为l_orderkey,l_linenumber，这是数据量最大的一张表。
nation表示国家信息，主键为n_nationkey，有25个国家，这是固定值。
region表示地区信息，主键为r_regionkey，固定有5个地区。

表关联说明

lineitem的l_orderkey跟orders表的o_orderkey是一一对应关系，订单上的商品都在lineitem表中，每个订单(l_orderkey)有1-7(l_linenumber)种商品，两张表数据量是1:(1-7)。

orders表的o_custkey信息都在customer表中，但不是一一对应关系，也就是说订单上的所有消费者信息都在customer表中，但不是所有消费者都购买了商品，大概2/3的消费者有订单。

part和partsupp中的partkey是一一对应关系，每个零件都有4个供应商，两张表数据量是1:4。

supplier和partsupp中的suppkey是一一对应关系，每个供应商供应80种零件，两张表数据量是1:80。

lineitem的partkey和suppkey都在partsupp中。每个region有5个nation，每个nation中，supplier和customer的比例大概为1:15。

2. 性能测试及衡量指标

性能测试方法

Power 单并发测试，单线程执行22条查询+ RF（INSERT +DELETE）;

Throughput多并发测试，N个查询线程+ 1个INSERT/DELETE 线程。

性能参数

TPC-H Power@Size： Power 单并发测试的测试结果
TPC-H Throughput@Size ：Throughput多并发测试的测试结果

价格/性能度量：TPC-H S/QphH

测试工具使用

测试工具可选择官网提供的工具进行测试。

下载与编译

官网下载地址：
https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp
在linux下编译流程如下：
a) 修改makefile文件适配数据库

cp makefile.suite makefile 
vim makefile

make
#生成dbgen、qgen两个可执行文件
#dbgen：数据生成工具。
#qgen：SQL生成工具。

生成数据

a. dbgen生成数据

/dbgen -s 100

参数-s 的作用是指定生成测试数据的仓库数（大概100g数据）。

b. dbgen生成更新、删除

./dbgen -s 100 -U 1

参数`-U的作用是指定生成更新、删除的文件个数。

c. qgen生成sql查询

vim create-select.sh
#脚本内容如下
#1 #!/usr/bin/bash 
#2 for i in {1..22} 
#3 do 
#4 ./qgen -d $i -s 1 > db"$i".sql 
#5 done

./create-select.sh

导入数据

a. 建表

#创建数据库并导入文件
create table tpch100g;
use tpch100g;
source ./dbgen/dss.ddl

b. 创建主键

vim primary.ri
#创建primary.ri文件并填入内容
ALTER TABLE region ADD PRIMARY KEY (R_REGIONKEY); 
ALTER TABLE nation ADD PRIMARY KEY (N_NATIONKEY); 
ALTER TABLE part ADD PRIMARY KEY (P_PARTKEY); 
ALTER TABLE supplier ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE partsupp ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY); 
ALTER TABLE customer ADD PRIMARY KEY (C_CUSTKEY); 
ALTER TABLE lineitem ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER); 
ALTER TABLE orders ADD PRIMARY KEY (O_ORDERKEY); 
ALTER TABLE add_orders ADD PRIMARY KEY (O_ORDERKEY); 
ALTER TABLE delete_orders ADD PRIMARY KEY (O_ORDERKEY); 
ALTER TABLE add_lineitem ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

use tpch100g;
source primary.ri

c. 注入数据

vim load_data.ddl
#
load data local INFILE 'customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|';
load data local INFILE 'region.tbl' INTO TABLE region FIELDS TERMINATED BY '|'; 
load data local INFILE 'nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|'; 
load data local INFILE 'supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|'; 
load data local INFILE 'part.tbl' INTO TABLE part FIELDS TERMINATED BY '|'; 
load data local INFILE 'partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|';
load data local INFILE 'orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|'; 
load data local INFILE 'lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';

load data local INFILE 'delete.1' INTO TABLE delete_orders FIELDS TERMINATED BY '|'; 
load data local INFILE 'orders.tabl.u1' INTO TABLE add_orders FIELDS TERMINATED BY '|'; 
load data local INFILE 'lineitem.tbl.u1' INTO TABLE add_lineitem FIELDS TERMINATED BY '|';
#

NOTE:注入报错-ERROR 1148 (42000): The used command is not allowed with this MySQL version
解决方法：以local-infile方式登陆

mysql --local-infile -u -p 

d. 创建外键

vim foreign.ri
#
ALTER TABLE nation ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references region(R_REGIONKEY); 
ALTER TABLE supplier ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references nation(N_NATIONKEY);
ALTER TABLE customer ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references nation(N_NATIONKEY); 
ALTER TABLE partsupp ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references supplier(S_SUPPKEY); 
ALTER TABLE partsupp ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references part(P_PARTKEY); 
ALTER TABLE orders ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references customer(C_CUSTKEY); 
ALTER TABLE lineitem ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references orders(O_ORDERKEY);
ALTER TABLE lineitem ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) referencespartsupp(PS_PARTKEY,PS_SUPPKEY);
#

性能测试

建立测试脚本test.sh进行测试

vim test.sh
#
连接数据库、执行22条查询语句和2条更新删除语句
#