MySQLtpch 测试工具简要手册
tpch是TPC(Transaction Processing Performance Council)组织提供的工具包。用于进行OLAP测试，以评估商业分析中决策支持系统（DSS）的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改，强
<p>tpch是TPC(Transaction Processing Performance Council)组织提供的工具包。用于进行OLAP测试，以评估商业分析中决策支持系统（DSS）的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改，强调测试的是数据库、平台和I/O性能，关注查询能力。官网：http://www.tpc.org/tpch 下载地址：http://www.tpc.org/tpch/spec/tpch_2_14_3.tgz 或 http://www.tpc.org/tpch/spec/tpch_2_14_3.zip
1、编译安装
下载源码包，解压缩，然后： cp makefile.suite makefile
修改makefile文件中的CC、DATABASE、MACHINE、WORKLOAD等定义： ################ ## CHANGE NAME OF ANSI COMPILER HERE ################ CC = gcc 1. Current values for DATABASE are: INFORMIX, DB2, ORACLE, # SQLSERVER, SYBASE, TDAT (Teradata) 1. Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, # SGI, SUN, U2200, VMS, LINUX, WIN32 1. Current values for WORKLOAD are: TPCH DATABASE= MYSQL MACHINE = LINUX WORKLOAD = TPCH
修改tpcd.h文件，增加几行宏定义： #ifdef MYSQL #define GEN_QUERY_PLAN "" #define START_TRAN "START TRANSACTION" #define END_TRAN "COMMIT" #define SET_OUTPUT "" #define SET_ROWCOUNT "limit %d;\n" #define SET_DBASE "use %s;\n" #endif 然后执行make编译，编译完毕后会生成两个可执行文件： dbgen：数据生成工具。在使用InfiniDB官方测试脚本进行测试时，需要用该工具生成tpch相关表数据。 qgen：SQL生成工具 生成初始化测试数据： [root@imysql tpch]# time ./dbgen -s 50 TPC-H Population Generator (Version 2.9.0) Copyright Transaction Processing Performance Council 1994 - 2008
real 192m43.897s user 37m45.398s sys 19m4.132s
[root@imysql tpch]# ls -lh *tbl -rw-r--r-- 1 root root 1.2G Sep 21 15:23 customer.tbl -rw-r--r-- 1 root root 1.4G Sep 21 15:23 lineitem.tbl -rw-r--r-- 1 root root 2.2K Sep 21 15:23 nation.tbl -rw-r--r-- 1 root root 317M Sep 21 15:23 orders.tbl -rw-r--r-- 1 root root 504K Sep 21 15:23 partsupp.tbl -rw-r--r-- 1 root root 464K Sep 21 15:23 part.tbl -rw-r--r-- 1 root root 389 Sep 21 15:23 region.tbl -rw-r--r-- 1 root root 69M Sep 21 15:23 supplier.tbl dbgen参数 -s 的作用是指定生成测试数据的仓库数，建议基准值设定在100以上，在我的测试环境中，一般都设定为1000。 由于源码包中自带的tpch初始化库表脚本并不能完全适用MySQL，需要修改部分代码。 先生成测试SQL脚本： [root@imysql tpch]# ./qgen | sed -e 's/\r//' > queries/tpch_queries.sql
而后用vim打开tpch_queries.sql脚本，进行下面几次全局替换： :%s/;\nlimit/ limit/g :%s/limit -1/limit 1/g
搜索所有类似下面的语句，去掉后面的 (3)： l_shipdate <= date '1998-12-01' - interval '106' day (3) => l_shipdate <= date '1998-12-01' - interval '106' day
再修改第369行附近： count(o_orderkey) => count(o_orderkey) as c_count
修改第376行左右 ) as c_orders (c_custkey, c_count) => ) as c_orders
修改第431行附近： drop view revenue0 limit 1; => drop view revenue0;
最后把大的查询SQL脚本拆分成23个独立的SQL查询脚本，分别从tpch_01.sql ~ tpch_23.sql。
2、初始化库表
tpch提供的数据库表初始化脚本有些小问题，需要进行修改： dss.ddl – DSS库初始化DDL脚本 dss.ri – DSS数据表创建索引、外键脚本 dss.ddl脚本需要增加几行： drop database tpch; create database tpch; use tpch;
dss.ri脚本需要修改几个地方： 修改第4行左右： CONNECT TO TPCD; => Use tpch;
修改第6~13行，所有的SQL注释符 “--” 后面再加一个空格： -- ALTER TABLE TPCD.REGION DROP PRIMARY KEY; -- ALTER TABLE TPCD.NATION DROP PRIMARY KEY; -- ALTER TABLE TPCD.PART DROP PRIMARY KEY; -- ALTER TABLE TPCD.SUPPLIER DROP PRIMARY KEY; -- ALTER TABLE TPCD.PARTSUPP DROP PRIMARY KEY; -- ALTER TABLE TPCD.ORDERS DROP PRIMARY KEY; -- ALTER TABLE TPCD.LINEITEM DROP PRIMARY KEY; -- ALTER TABLE TPCD.CUSTOMER DROP PRIMARY KEY;
修改第25行： ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION; => ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY);
修改第40行： ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION; => ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION(N_NATIONKEY);
修改第55行： ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION; => ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION(N_NATIONKEY);
修改第73行： ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER; => ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER(S_SUPPKEY);
修改第78行： ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART; => ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART(P_PARTKEY);
修改第84行： ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER; => ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER(C_CUSTKEY);
修改第90行： ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references TPCD.ORDERS; => ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references TPCD.ORDERS(O_ORDERKEY);
修改第96行： TPCD.PARTSUPP; => TPCD.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
另外，由于tpch生成的表名是大写的，需要修改下表名成小写的，因此再增加几行： use tpch;
alter table CUSTOMER rename to customer ; alter table LINEITEM rename to lineitem ; alter table NATION rename to nation ; alter table ORDERS rename to orders ; alter table PART rename to part ; alter table PARTSUPP rename to partsupp ; alter table REGION rename to region ; alter table SUPPLIER rename to supplier ;
3、导入数据 测试数据生成了，测试库表也初始化完了，接下来就可以开始导入数据了。 需要注意下，如果开启了binlog，在导入前最好先关闭binlog，否则会提示超出max_binlog_cache_size的错误提示，如果不能关闭binlog，则需要把导入文件切分成多个小文件再导入。 myqsl -e "LOAD DATA INFILE 'path/dbgen/customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';" myqsl -e "LOAD DATA INFILE 'path/dbgen/orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|';" myqsl -e "LOAD DATA INFILE 'path/dbgen/lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|';" myqsl -e "LOAD DATA INFILE 'path/dbgen/nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|';" myqsl -e "LOAD DATA INFILE 'path/dbgen/partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|';" myqsl -e "LOAD DATA INFILE 'path/dbgen/part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|';" myqsl -e "LOAD DATA INFILE 'path/dbgen/region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|';" myqsl -e "LOAD DATA INFILE 'path/dbgen/supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|';"
4、执行tpch测试接下来就可以进行tpch测试了，逐个执行23个查询SQL脚本即可，每次执行前都要重启下MySQL实例，确保每次的内存缓冲区都是干净的。 简单循环测试脚本如下：
#!/bin/sh
执行tpch OLAP测试
writed by yejr(http://imysql.com), 2012/12/14
PATH=$PATH:/usr/local/bin export PATH
. ~/.bash_profile > /dev/null 2>&1
exec 3>&1 4>&2 1>> tpch-benchmark-olap-date +'%Y%m%d%H%M%S'.log 2>&1
I=1
II=3
while [ $I -le $II ]
do
N=1
T=23
while [ $N -lt $T ]
do
if [ $N -lt 10 ] ; then
NN='0'$N
else
NN=$N
fi
echo "query $NN starting"
/etc/init.d/mysql restart
time mysql -f tpch < ./queries/tpch_${NN}.sql
echo "query $NN ended!"
N=expr $N + 1
done
I=expr $I + 1
Done
附件：tpch初始化、自动化测试脚本压缩包与word手册。
