关于TCP-H benchmark

The TPC-H Benchmark is a popular one for comparing database vendors. It's possible to run the TPC-H data set on Postgresql without having a formal testing kit (although there is DBT-3,a work in progress to provide a full kit). The results have generally been disappointing,for reasons that aren't necessarily relevant in the real world. Postgresql is missing some of the things needed to do well on this benchmark,whereas proprietary database vendors are so focused on it they will "game" TPC-H runs (add optimizations specifically aimed at it) to make absolutely sure they do well.

安装

下载页面地址：http://www.tpc.org/tpch/specs.asp

安装数据库

Version:：9.3.4
Admin_user：postgres

安装tpch

解压：

[root@tpchopt]#unziptpch_2_16_0v1.zip

设置makefile：

[root@tpchopt]#cdtpch_2_15_0/dbgen/
[root@tpchdbgen]#cpmakefile.suitemakefile
[root@tpchdbgen]#vimakefile
CC=gcc
DATABASE=ORACLE
MACHINE=LINUX
WORKLOAD=TPCH

编译：

[root@tpchdbgen]#make

生成测试数据文件：

[root@tpchdbgen]#./dbgen-s1
[root@tpchdbgen]#ls*.tbl
customer.tbllineitem.tblnation.tblorders.tblpartsupp.tblpart.tblregion.tblsupplier.tbl

修改数据脚本，转换为csv格式：

（去掉每行最后一个”|”号）

[root@tpchdbgen]#foriin`ls*.tbl`;dosed's/|$//'$i>${i/tbl/csv};echo$i;done;
customer.tbl
lineitem.tbl
nation.tbl
orders.tbl
partsupp.tbl
part.tbl
region.tbl
supplier.tbl

修改约束脚本dss.ri：

（1）去掉”CONNECTTOTPCD;”
（2）去掉对象前的”TPCD.”
（3）去掉外键名称
（4）去掉”COMMITWORK;”

测试

创建测试库

[postgres@tpch~]$createdbtpch

创建表

[postgres@tpch~]$psql-f/opt/tpch_2_15_0/dbgen/dss.ddltpch
CREATETABLE
CREATETABLE
CREATETABLE
CREATETABLE
CREATETABLE
CREATETABLE
CREATETABLE
CREATETABLE

载入数据

[postgres@tpch~]$catload.sql
COPYnationFROM'/opt/tpch_2_15_0/dbgen/nation.csv'WITH(FORMATcsv,DELIMITER'|');
COPYregionFROM'/opt/tpch_2_15_0/dbgen/region.csv'WITH(FORMATcsv,DELIMITER'|');
COPYpartFROM'/opt/tpch_2_15_0/dbgen/part.csv'WITH(FORMATcsv,DELIMITER'|');
COPYsupplierFROM'/opt/tpch_2_15_0/dbgen/supplier.csv'WITH(FORMATcsv,DELIMITER'|');
COPYpartsuppFROM'/opt/tpch_2_15_0/dbgen/partsupp.csv'WITH(FORMATcsv,DELIMITER'|');
COPYcustomerFROM'/opt/tpch_2_15_0/dbgen/customer.csv'WITH(FORMATcsv,DELIMITER'|');
COPYordersFROM'/opt/tpch_2_15_0/dbgen/orders.csv'WITH(FORMATcsv,DELIMITER'|');
COPYlineitemFROM'/opt/tpch_2_15_0/dbgen/lineitem.csv'WITH(FORMATcsv,DELIMITER'|');
[postgres@tpch~]$psql-fload.sqltpch
COPY25
COPY5
COPY200000
COPY10000
COPY800000
COPY150000
COPY1500000
COPY6001215

添加约束

[postgres@tpch~]$psql-f/opt/tpch_2_15_0/dbgen/dss.ritpch
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE
ALTERTABLE

生成查询脚本

[root@tpchdbgen]#vimgen_query_sql.sh
#!/bin/sh
#generatequeriesfromquerytemplateswithqgen
DIR=.
mkdir$DIR/finals
cp$DIR/queries/*.sql$DIR
forFILEin$(find$DIR-maxdepth1-name"[0-9]*.sql")
do
DIGIT=$(echo$FILE|tr-cd'[[:digit:]]')
./qgen$DIGIT>$DIR/finals/$DIGIT.sql
done
rm*.sql

[root@tpchdbgen]#chmod+xgen_query_sql.sh
[root@tpchdbgen]#./gen_query_sql.sh
[root@tpchdbgen]#lsfinals/
10.sql12.sql14.sql16.sql18.sql1.sql21.sql2.sql4.sql6.sql8.sql
11.sql13.sql15.sql17.sql19.sql20.sql22.sql3.sql5.sql7.sql9.sql

参考文档

http://wiki.postgresql.org/wiki/TPC-H

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

http://dsl.serc.iisc.ernet.in/projects/PICASSO/picasso_download/doc/Installation/tpch.htm

https://github.com/tvondra/pg_tpch

http://ifthiskills.me/?p=588

http://blog.csdn.net/leixingbang1989/article/details/8766047