MySQL TPCH测试工具简要手册

标签: MySQL FAQ MySQL优化 MySQL基础知识 硬件相关 运维相关 | 发表时间:2012-12-21 12:43 | 作者:yejr
出处:http://imysql.com

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
# 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

附件: tpch初始化、自动化测试脚本压缩包
备注:本文档部分参考古雷、王洪权整理的资料,感谢二位 :)

相关 [mysql tpch 测试] 推荐:

MySQL TPCH测试工具简要手册

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

SAS vs SSD对比测试MySQL tpch性能

- - MySQL 中文网 -
早先已经做过一次OLTP性能对比测试: SAS vs SSD各种模式下MySQL TPCC OLTP对比测试结果,这次做OLAP对比测试. SSD设备相应的性能提升比例:.

mysql 5.6 online ddl 测试

- - CSDN博客数据库推荐文章
     在生产环境中,因为业务需求,改动mysql表结构,特别是数据量特别大的时候,几百万甚至几千万的数据,mysql online ddl操作非常让mysql dba揪心. 5.6版本支持online ddl.    为什么mysql ddl操作让dba揪心呢. 因为mysql ddl操作内部原理是这样的:.

MySQL基准测试---sysbench

- - 博客园_首页
 一、 在对网站整体性能进行benchmark时,可以使用多种工具,比如大名鼎鼎的ab(Apache bench),http_load等工具. 这里我们不关注他们的使用,如果你想了解,可以去 这里.   重点来说MySQL的基准测试如何进行,也有很多种工具来供我们选择,比如mysqlslap、sysbench、Super Smack等,其中mysqlslap的使用MySQL官网给出了介绍,Super Smack是服务器压力测试强有力的工具,那么sysbench便是我们进行MySQL基准测试的很漂亮的工具了.

MySQL 5.6 测试之 Replication(主从复制)

- - MySQL 中文网 -
MySQL 5.6测试之Replication. MySQL 5.6版本相比以前新增了很多令人激动的特性,简要介绍见: 转:MySQL 5.6新特性. 性能方面已经做过测试了,详细请见: MySQL 5.6 vs MariaDB 5.5 vs Percona(5.5 & 5.6) 之TPCC性能测试.

Mysql压力测试shell脚本

- - CSDN博客数据库推荐文章
转载请注明,来自: http://blog.csdn.net/skyman_2001. Mysql自带了压力测试工具mysqlslap,所以我们可以不用自己编写程序来测试Mysql读取的压力. 上面脚本的意思是每隔100ms循环做这样的事:模拟100个mysql客户端,对数据库test的表test执行200次插入(number-of-queries = concurrency * 每个mysql客户端的查询次数,所以这里的每个mysql客户端的查询次数是2次),迭代10次.

MySQL Cluster-备份恢复初步测试

- - CSDN博客数据库推荐文章
在  http://blog.csdn.net/mchdba/article/details/10544585  的第八小节里面进行扩展测试. 8.1 sql节点上面录入数据:.  8.2 管理节点上面,开始备份.  8.3 再次插入几条数据(为了保持和正式环境尽可能接近,在插入数据中间穿插了flush logs操作!).

MySQL 5.6.17/Percona5.6.16/MariaDB 10.0.11/OneSQL 5.6.16 TpmC测试

- - MySQL中文网 - 叶金荣的技术和生活
近日花了点时间对几个分支版本进行对比测试,包括了:MySQL 5.6.17、Percona5.6.16、MariaDB 10.0.11、OneSQL 5.6.16. 测试工具: tpcc-mysql. 测试Warehouse数: 10/100. 并发线程数: 64 ~ 1920. 内核:2.6.32-358.el6.x86_64.

MySQL 5.6 vs MySQL 5.5 性能对比初步测试结果

- - ITeye博客
在MySQL 推出第一个5.6的GA版本后,我们对5.6进行了简单的性能对比测试. 测试的基本思路是在同一台服务器上(保证硬件环境完全一样),先后安装MySQL 5.6和5.5,使用sysbench工具进行同样的压力测试,对比结果. 服务器配置:8核CPU+16G内存的HP360服务器. 测试压力:sysbench的oltp的性能测试,测试表数据量5000万.

sysbench测试MySQL服务器性能(cpu,io,内存,mysql等)

- - CSDN博客数据库推荐文章
Sysbench的安装请参考http://blog.csdn.net/mchdba/article/details/8951289. sysbench采用寻找最大素数的方式来测试CPU的性能. 首先生成需要的测试文件,文件总大小1000M,16个并发线程,随机读写模式. 执行完后会在当前目录下生成一堆小文件.