【数据库】性能测试--tpch

---

前言

数据库性能测试工具dbgen

---

一、dbgen工具

1.工具安装

下载：

http://www.tpc.org/tpch/

解压工具
编辑makefile

cd dbgen/
cp makefile.suite makefile  #复制makefile.suite到makefile
vim makefile
CC      = gcc    #小写
DATABASE= ORACLE
MACHINE = LINUX
WORKLOAD = TPCH

执行make

[test@localhost dbgen]$ make
gcc  -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o build.o build.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o driver.o driver.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bm_utils.o bm_utils.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rnd.o rnd.c
.
.
.

2.生成测试数据

[test@localhost dbgen]$ ./dbgen -vf -s 1  #1GB数据
[test@localhost dbgen]$ ll -h *.tbl
-rw-rw-r--. 1 test test  24M Jun 25 05:43 customer.tbl
-rw-rw-r--. 1 test test 725M Jun 25 05:43 lineitem.tbl
-rw-rw-r--. 1 test test 2.2K Jun 25 05:43 nation.tbl
-rw-rw-r--. 1 test test 164M Jun 25 05:43 orders.tbl
-rw-rw-r--. 1 test test 114M Jun 25 05:43 partsupp.tbl
-rw-rw-r--. 1 test test  24M Jun 25 05:43 part.tbl
-rw-rw-r--. 1 test test  389 Jun 25 05:43 region.tbl
-rw-rw-r--. 1 test test 1.4M Jun 25 05:43 supplier.tbl

参数说明：
-v：详细信息
-f：覆盖之前的文件
-s：生成数据大小，单位GB

3.处理数据

去除生成的数据文件中每一行最后一个竖线

sed -i 's/|$//g' customer.tbl
sed -i 's/|$//g' lineitem.tbl
sed -i 's/|$//g' nation.tbl
sed -i 's/|$//g' orders.tbl
sed -i 's/|$//g' partsupp.tbl
sed -i 's/|$//g' part.tbl
sed -i 's/|$//g' region.tbl
sed -i 's/|$//g' supplier.tbl

4.生成表结构

dbgen生成数据表结构文件为：dss.ddl

./Server/bin/ksql -U system -p 54321 -d test -f /home/test/jp/tools/dbgen/dss.ddl
test=# \dt+
                     List of relations
 Schema |   Name   | Type  | Owner  |  Size   | Description
--------+----------+-------+--------+---------+-------------
 public | customer | table | system | 0 bytes |
 public | lineitem | table | system | 0 bytes |
 public | nation   | table | system | 0 bytes |
 public | orders   | table | system | 0 bytes |
 public | part     | table | system | 0 bytes |
 public | partsupp | table | system | 0 bytes |
 public | region   | table | system | 0 bytes |
 public | supplier | table | system | 0 bytes |
(8 rows)

5.导入数据

./Server/bin/ksql -U system -p 54321 -d test -c "copy customer from '/home/test/jp/tools/dbgen/customer.tbl' with delimiter '|';"
./Server/bin/ksql -U system -p 54321 -d test -c "copy lineitem from '/home/test/jp/tools/dbgen/lineitem.tbl'  with delimiter '|';"
./Server/bin/ksql -U system -p 54321 -d test -c "copy nation from '/home/test/jp/tools/dbgen/nation.tbl  '    with delimiter '|';"
./Server/bin/ksql -U system -p 54321 -d test -c "copy orders from '/home/test/jp/tools/dbgen/orders.tbl  '    with delimiter '|';"
./Server/bin/ksql -U system -p 54321 -d test -c "copy partsupp from '/home/test/jp/tools/dbgen/partsupp.tbl'  with delimiter '|';"
./Server/bin/ksql -U system -p 54321 -d test -c "copy part from '/home/test/jp/tools/dbgen/part.tbl    '      with delimiter '|';"
./Server/bin/ksql -U system -p 54321 -d test -c "copy region from '/home/test/jp/tools/dbgen/region.tbl  '    with delimiter '|';"
./Server/bin/ksql -U system -p 54321 -d test -c "copy supplier from '/home/test/jp/tools/dbgen/supplier.tbl'  with delimiter '|';"

test=# \dt+
                       List of relations
 Schema |   Name   | Type  | Owner  |    Size    | Description
--------+----------+-------+--------+------------+-------------
 public | customer | table | system | 28 MB      |
 public | lineitem | table | system | 976 MB     |
 public | nation   | table | system | 8192 bytes |
 public | orders   | table | system | 216 MB     |
 public | part     | table | system | 32 MB      |
 public | partsupp | table | system | 274 MB     |
 public | region   | table | system | 8192 bytes |
 public | supplier | table | system | 1808 kB    |
(8 rows)

6.创建索引

对应工具脚本/dbgen/dss.ri（部分数据库不适配，需要手动修改），建议先插入数据再创建约束，否则可能出现异常。

-- For table REGION
ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);

-- For table NATION
ALTER TABLE NATION ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION ADD FOREIGN KEY (N_REGIONKEY) references REGION;

-- For table PART
ALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY);

-- For table SUPPLIER
ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER ADD FOREIGN KEY (S_NATIONKEY) references NATION;

-- For table PARTSUPP
ALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);

-- For table CUSTOMER
ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER ADD FOREIGN KEY (C_NATIONKEY) references NATION;

-- For table LINEITEM
ALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

-- For table ORDERS
ALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY);

-- For table PARTSUPP
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) references PART;

-- For table ORDERS
ALTER TABLE ORDERS ADD FOREIGN KEY (O_CUSTKEY) references CUSTOMER;

-- For table LINEITEM
ALTER TABLE LINEITEM ADD FOREIGN KEY (L_ORDERKEY)  references ORDERS;
ALTER TABLE LINEITEM ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) referencesPARTSUPP;

二、tpch测试

2.1表结构说明

TPC-H 测试标准模拟了一个零部件在线销售的系统，共定义了8个表：

表名称	说明
REGION	区域表
NATION	国家表
SUPPLIER	供应商表
PART	零部件表
PARTSUPP	零部件供应表
CUSTOMER	客户表
ORDERS	订单表
LINEITEM	订单明细表

2.2 22条查询语句说明

查询语句	说明	主要涉及业务
Q1	定价汇总报表查询	分组、排序、聚集操作并存的单表查询操作
Q2	最低成本供应商查询	带有排序、聚集操作、子查询并存的多表查询操作
Q3	运输优先级查询	分组、排序、聚集操作并存的三表查询操作
Q4	订单优先级查询	分组、排序、聚集操作、子查询并存的单表查询操作
Q5	本地供应商收入量查询	分组、排序、聚集操作、子查询并存的多表连接查询操作
Q6	预测收入变化查询	聚集操作的单表查询操作
Q7	批量出货查询	分组、排序、聚集、子查询操作并存的多表查询操作
Q8	全国市场份额查询	分组、排序、聚集、子查询操作并存的查询操作
Q9	产品类型利润度量查询	分组、排序、聚集、子查询操作并存的查询操作
Q10	退货报告查询	分组、排序、聚集操作并存的多表连接查询操作
Q11	库存价值查询	分组、排序、聚集、子查询操作并存的多表连接查询操作
Q12	运送方式和订单优先级查询	分组、排序、聚集操作并存的两表连接查询操作
Q13	客户分布查询	分组、排序、聚集、子查询、左外连接操作并存的查询操作
Q14	促销效果查询	分组、排序、聚集、子查询、左外连接操作并存的查询操作
Q15	顶级供应商查询	分排序、聚集、聚集子查询操作并存的普通表与视图的连接操作
Q16	零部件/供货商关系查询	分组、排序、聚集、去重、NOT IN子查询操作并存的两表连接操作
Q17	小额订单收入查询	聚集、聚集子查询操作并存的两表连接操作
Q18	大批量客户查询	分组、排序、聚集、IN子查询操作并存的三表连接操作
Q19	折扣收入查询	分组、排序、聚集、IN子查询操作并存的三表连接操作
Q20	潜在零部件促销查询	排序、聚集、IN子查询、普通子查询操作并存的两表连接操作
Q21	供应商留单等待查询	分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作

2.3 查询测试

使用shell脚本一次性执行22条查询语句

for ((i=1;i<=22;i++))
do
    echo $i
    time ./ksql -Usystem -W12345678ab -p54321 -f /home/test/$i.sql >> tpch_result.log
done