使用TPC-H benchmark测试mysql数据库读写能力

TPC-H是TPC提供的一个benchmark，用来生成一堆虚构的数据，测试数据库的读写能力。

下载tpc-h测试工具tpc-h-tool_v2.17.3.zip

官网地址：http://www.tpc.org/

工具下载地址：http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp
文档地址：http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.17.3.pdf


下载的地址需要通过代理访问。下载的时候需要通过邮箱进行注册，每注册一次，只能进行一次下载。下载地址会通过邮件的形式发送到邮箱。

如果官网无法下载，可以在我的网盘里面下载

下载地址：
链接: https://pan.baidu.com/s/1G9GsDBONV6WDHLtIKls9Pw 提取码: cdta

模拟环境：centos7
1.解压文件：

[root@kdc soft]# unzip 8a14c643-bcc2-4ee6-8a6b-e2179e1449a3-tpc-h-tool.zip

2.切换目录

-bash-4.2$ cd 2.17.3/dbgen/

3.备份makefile.suite，也可以不备份。

-bash-4.2$ cp makefile.suite makefile

makefile文件主要是用来进行make编译的

4.修改makefile文件

-bash-4.2$ vim makefile

修改103~111行内容如下

103 CC      = gcc    #设定c语言编译器为gcc
104 # Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
105 #                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
106 # Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS, 
107 #                                  SGI, SUN, U2200, VMS, LINUX, WIN32 
108 # Current values for WORKLOAD are:  TPCH
109 DATABASE= MYSQL  #数据库格式为mysql
110 MACHINE = LINUX   #系统环境为linux
111 WORKLOAD = TPCH  #工作加载为tpch

5.修改tpcd.h（因为设定的数据库格式为mysql，所以需要进行此步骤，如果是上面支持的数据库格式，则不需要）

-bash-4.2$ vim 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

6.编译，生成dbgen

-bash-4.2$ make

7.生成tbl数据文件

-bash-4.2$ ./dbgen -s 1

-s 1表示生成1G的数据，如果之前生成过数据，可以通过make clean清理

8.修改初始化脚本

编译之后有dss.ddl和dss.ri两个脚本，dss.ddl是建表脚本，dss.ri脚本是建立主键外键关联关系。

因为TPC-H benchmark本身不支持mysql,所以需要对这两个脚本进行修改

修改dss.ddl

在行首添加以下语句

drop database tpch;
create database tpch;
use tpch;

修改dss.ri

-- Sccsid:     @(#)dss.ri	2.1.8.1
-- tpch Benchmark Version 8.0

-- CONNECT TO tpch;

use tpch;

-- ALTER TABLE tpch.REGION DROP PRIMARY KEY;
-- ALTER TABLE tpch.NATION DROP PRIMARY KEY;
-- ALTER TABLE tpch.PART DROP PRIMARY KEY;
-- ALTER TABLE tpch.SUPPLIER DROP PRIMARY KEY;
-- ALTER TABLE tpch.PARTSUPP DROP PRIMARY KEY;
-- ALTER TABLE tpch.ORDERS DROP PRIMARY KEY;
-- ALTER TABLE tpch.LINEITEM DROP PRIMARY KEY;
-- ALTER TABLE tpch.CUSTOMER DROP PRIMARY KEY;


-- For table REGION
ALTER TABLE tpch.REGION
ADD PRIMARY KEY (R_REGIONKEY);

-- For table NATION
ALTER TABLE tpch.NATION
ADD PRIMARY KEY (N_NATIONKEY);

ALTER TABLE tpch.NATION
-- ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references tpch.REGION;
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references tpch.REGION(R_REGIONKEY);


COMMIT WORK;

-- For table PART
ALTER TABLE tpch.PART
ADD PRIMARY KEY (P_PARTKEY);

COMMIT WORK;

-- For table SUPPLIER
ALTER TABLE tpch.SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);

ALTER TABLE tpch.SUPPLIER
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references tpch.NATION(N_NATIONKEY);

COMMIT WORK;

-- For table PARTSUPP
ALTER TABLE tpch.PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);

COMMIT WORK;

-- For table CUSTOMER
ALTER TABLE tpch.CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);

ALTER TABLE tpch.CUSTOMER
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references tpch.NATION(N_NATIONKEY);

COMMIT WORK;

-- For table LINEITEM
ALTER TABLE tpch.LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

COMMIT WORK;

-- For table ORDERS
ALTER TABLE tpch.ORDERS
ADD PRIMARY KEY (O_ORDERKEY);

COMMIT WORK;

-- For table PARTSUPP
ALTER TABLE tpch.PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references tpch.SUPPLIER(S_SUPPKEY);

COMMIT WORK;

ALTER TABLE tpch.PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references tpch.PART(P_PARTKEY);

COMMIT WORK;

-- For table ORDERS
ALTER TABLE tpch.ORDERS
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references tpch.CUSTOMER(C_CUSTKEY);

COMMIT WORK;

-- For table LINEITEM
ALTER TABLE tpch.LINEITEM
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references tpch.ORDERS(O_ORDERKEY);

COMMIT WORK;

ALTER TABLE tpch.LINEITEM
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references 
        tpch.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);

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 ;

COMMIT WORK;

9.建表

登录mysql，执行

mysql> \. path-to-tpc-h/dbgen/dss.ddl

注意\.之后有一个空格，前面是dss.ddl脚本的路径

执行成功之后，可用show databases查看数据库
会有一个tpch的数据库，use tpch；切换到tpch数据库下，show tables；可以看到8张表

customer ;
lineitem ;
nation   ;
orders   ;
part     ;
partsupp ;
region   ;
supplier ;

10.建立关联关系

mysql> \. path-to-tpc-h/dbgen/dss.ri

如果想查看关联关系是否建立成功，可以通过show create table customer/G查看

11.导入tbl脚本

在mysql命令行下执行

load data infile "Yourdirectory/part.tbl" into table part fields terminated by "|";
load data infile "Yourdirectory/region.tbl" into table region fields terminated by "|" lines terminated by "\r\n";
load data infile "Yourdirectory/nation.tbl" into table nation fields terminated by "|";
load data infile "Yourdirectory/customer.tbl" into table customer fields terminated by "|";
load data infile "Yourdirectory/supplier.tbl" into table supplier fields terminated by "|" lines terminated by "\r\n";
load data infile "Yourdirectory/orders.tbl" into table orders fields terminated by "|";
load data infile "Yourdirectory/partsupp.tbl" into table partsupp fields terminated by "|";
load data infile "Yourdirectory/lineitem.tbl" into table lineitem fields terminated by "|";

需要注意的是添加了关联关系，导入数据的顺序按照关联关系导入

12.查看导入结果

mysql> SHOW TABLE STATUS FROM tpch\G;