MySQL tpch测试工具简要手册.docx

MySQL tpch测试工具简要手册tpch是TPC(Transaction Processing Performance Council)组织提供的工具包。用于进行OLAP测试，以评估商业分析中决策支持系统（DSS）的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改，强调测试的是数据库、平台和I/O性能，关注查询能力。官网：/tpch下载地址：/tpch/spec/tpch_2_14_3.tgz 或 /tpch/spec/tpch_2_14_3.zip1、编译安装下载源码包，解压缩，然后：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_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生成工具生成初始化测试数据：rootimysql tpch# time ./dbgen -s 50TPC-H Population Generator (Version 2.9.0)Copyright Transaction Processing Performance Council 1994 - 2008real 192m43.897suser 37m45.398ssys 19m4.132srootimysql 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脚本：rootimysql 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 l_shipdate 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;修改第613行，所有的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