MySQL-tpch 测试工具简要手册
tpch是tpc(transaction processing performance council)组织提供的工具包。用于进行olap测试,以评估商业分析中决策支持系统(dss)的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改,强调测试的是数据库、平台和i/o性能,关注查询能力。
官网:
下载地址: 或
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: 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