l_shipdate
l_shipdate
修改第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(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
附件:tpch初始化、自动化测试脚本压缩包。
备注:本文档部分参考古雷、王洪权整理的资料,感谢二位 :)