MySQL-tpch 测试工具简要手册

tpch是TPC(Transaction Processing Performance
Council)组织提供的工具包。用于进行OLAP测试,以评估商业分析中决策支持系统的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改,强调测试的是数据库、平台和I/O性能,关注查询能力。官网:下载地址:_2_14_3.tgz
或 _2_14_3.zip

1、编译安装

下载源码包,解压缩,然后:cp makefile.suite makefile

修改makefile文件中的CC、DATABASE、MACHINE、WORKLOAD等定义:##################
CHANGE NAME OF ANSI COMPILER HERE################CC =
gcc# Current values for DATABASE are: INFORMIX, DB2, ORACLE,#
SQLSERVER, SYBASE, TDAT (Teradata)# Current values for MACHINE are:
ATT, DOS, HP, IBM, ICL, MVS,# SGI, SUN, U2200, VMS, LINUX, WIN32#
Current values for WORKLOAD are: TPCHDATABASE= MYSQLMACHINE =
LINUXWORKLOAD = TPCH

修改tpcd.h文件,增加几行宏定义:#ifdef MYSQL#define GEN_463.com,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 50TPC-H Population Generator (Version
2.9.0)Copyright Transaction Processing Performance Council 1994 – 2008

real 192m43.897suser 37m45.398ssys 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.tbldbgen参数
-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 CUSTOMERrename to customer ;alter table LINEITEMrename to
lineitem ;alter table NATIONrename to nation ;alter table ORDERSrename
to orders ;alter table PARTrename to part ;alter table PARTSUPPrename to
partsupp ;alter table REGIONrename to region ;alter table SUPPLIERrename
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(), 2012/12/14##PATH=$PATH:/usr/local/binexport PATH. ~/.bash_profile  /dev/null 2&1exec 3&1 4&2 1 tpch-benchmark-olap-`date +'%Y%m%d%H%M%S'`.log 2&1I=1II=3while [ $I -le $II ]doN=1T=23while [ $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手册。

相关文章