MYSQL-TPCH Test Tool Brief manual _mysql

TPCH is a toolkit provided by the TPC (Transaction processing performance Council) organization. Used for OLAP testing to assess the performance of decision support systems (DSS) in Business Analytics. It includes a suite of business-oriented AD-HOC queries and concurrent data modifications, emphasizing the testing of databases, platforms, and I/O performance, and the ability to query.
Official website: http://www.tpc.org/tpch
Download Address: http://www.tpc.org/tpch/spec/tpch_2_14_3.tgz or Http://www.tpc.org/tpch/spec/tpch_2_14_3.zip

1. Compile and install

Download the source package, unzip it, and then:
CP Makefile.suite Makefile

Modify the CC, DATABASE, MACHINE, workload definitions in the Makefile file:
################
# # change NAME of ANSI COMPILER here
### #############
cc      = gcc
# current values for DATABASE Are:informix, DB2, ORACLE,
#                                   SQL Server, SYBASE, Tdat (Teradata)
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#    &nbs p;                              SGI, SUN, U2200, VMS, LINUX, WIN32
# current values for Workload are:  tpch
database= MYSQL
MACHINE = LINUX
Workload = tpch

Modify the Tpcd.h file to add a few lines of macro definition:
#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
The make compilation is then performed and two executables are generated when the compilation is completed:
dbgen: Data Generation tool. When testing with the INFINIDB official test script, you need to generate TPCH related table data using the tool.
qgen:sql Build Tools
To generate initialization test data:
[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 1.2G Sep 15:23 customer.tbl
-rw-r--r--1 root 1.4G Sep 15:23 lineitem.tbl
-rw-r--r--1 root 2.2K Sep 15:23 nation.tbl
-rw-r--r--1 root 317M Sep 15:23 orders.tbl
-rw-r--r--1 root 504K Sep 15:23 partsupp.tbl
-rw-r--r--1 root 464K Sep 15:23 part.tbl
-rw-r--r--1 root root 389 Sep 15:23 region.tbl
-rw-r--r--1 root 69M Sep 15:23 supplier.tbl
The role of the Dbgen parameter-S is to specify the number of warehouses to generate test data, the recommended benchmark value is set above 100, and in my test environment, it is generally set to 1000.
Since the TPCH Initialization Library table script in the source pack does not fully apply to MySQL, you need to modify some of the code.
Mister into Test SQL script:
[Root@imysql tpch]#./qgen | Sed-e ' s/\r//' > Queries/tpch_queries.sql

Then use VIM to open the Tpch_queries.sql script and make the following global substitutions:
:%s/;\nlimit/limit/g
:%s/limit-1/limit 1/g

Search for all statements similar to the following, remove the rear (3):
L_shipdate <= Date ' 1998-12-01 '-Interval ' Day (3)
=>
L_shipdate <= Date ' 1998-12-01 '-interval ' day '

Further modify the No. 369 line near:
Count (O_orderkey)
=>
Count (O_orderkey) as C_count

Modify the No. 376 line or so
) as C_orders (C_custkey, C_count)
=>
) as C_orders

Modify near line No. 431:
Drop view revenue0 limit 1;
=>
Drop View revenue0;

Finally, the large query SQL script is split into 23 separate SQL query scripts, from Tpch_01.sql ~ Tpch_23.sql.

2, initialization of the library table

The database table initialization script provided by TPCH has some minor problems that need to be modified:
DSS.DDL–DSS Library Initialization DDL script
DSS.RI–DSS data Table CREATE INDEX, foreign key script
The Dss.ddl script needs to add a few lines:
Drop database tpch;
Create Database tpch;
Use tpch;

The Dss.ri script needs to be modified in several places:
Modify the 4th line or so:
CONNECT to TPCD;
=>
Use tpch;

Modify line 6th to 13th with all the SQL annotation characters "--" followed by a space:
--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;

Modify Line 25th:
ADD FOREIGN KEY NATION_FK1 (n_regionkey) references tpcd. REGION;
=>
ADD FOREIGN KEY NATION_FK1 (n_regionkey) references tpcd. REGION (R_regionkey);

Modify Line 40th:
ADD FOREIGN KEY SUPPLIER_FK1 (s_nationkey) references tpcd. NATION;
=>
ADD FOREIGN KEY SUPPLIER_FK1 (s_nationkey) references tpcd. NATION (N_nationkey);

Modify Line 55th:
ADD FOREIGN KEY CUSTOMER_FK1 (c_nationkey) references tpcd. NATION;
=>
ADD FOREIGN KEY CUSTOMER_FK1 (c_nationkey) references tpcd. NATION (N_nationkey);

Modify Line 73rd:
ADD FOREIGN KEY PARTSUPP_FK1 (ps_suppkey) references tpcd. SUPPLIER;
=>
ADD FOREIGN KEY PARTSUPP_FK1 (ps_suppkey) references tpcd. SUPPLIER (S_suppkey);

Modify Line 78th:
ADD FOREIGN KEY partsupp_fk2 (ps_partkey) references tpcd. part;
=>
ADD FOREIGN KEY partsupp_fk2 (ps_partkey) references tpcd. Part (P_partkey);

Modify Line 84th:
ADD FOREIGN KEY ORDERS_FK1 (o_custkey) references tpcd. CUSTOMER;
=>
ADD FOREIGN KEY ORDERS_FK1 (o_custkey) references tpcd. CUSTOMER (C_custkey);

Modify Line 90th:
ADD FOREIGN KEY LINEITEM_FK1 (l_orderkey) references tpcd. ORDERS;
=>
ADD FOREIGN KEY LINEITEM_FK1 (l_orderkey) references tpcd. ORDERS (O_orderkey);

Modify Line 96th:
TPCD. Partsupp;
=>
TPCD. Partsupp (Ps_partkey,ps_suppkey);

Also, because the table name generated by TPCH is uppercase, you need to modify the following table name to lowercase, and add a few more lines:
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. Import Data
The test data is generated, the test library table is initialized, and then the data can be imported.
Need to be aware that if you turn on the Binlog, it is best to close the Binlog before the import, otherwise you will be prompted to exceed max_binlog_cache_size error prompts, if you can not close the binlog, you need to cut the import file into a number of small files and then import.
Myqsl-e "LOAD DATA INFILE ' path/dbgen/customer.tbl ' into the TABLE customer FIELDS terminated by ' | ';"
Myqsl-e "LOAD DATA INFILE ' path/dbgen/orders.tbl ' into the TABLE orders FIELDS terminated by ' | ';"
Myqsl-e "LOAD DATA INFILE ' path/dbgen/lineitem.tbl ' into the TABLE lineitem FIELDS terminated by ' | ';"
Myqsl-e "LOAD DATA INFILE ' path/dbgen/nation.tbl ' into the TABLE nation FIELDS terminated by ' | ';"
Myqsl-e "LOAD DATA INFILE ' path/dbgen/partsupp.tbl ' into the TABLE Partsupp FIELDS terminated by ' | ';"
Myqsl-e "LOAD DATA INFILE ' path/dbgen/part.tbl ' into the TABLE part FIELDS terminated by ' | ';"
Myqsl-e "LOAD DATA INFILE ' path/dbgen/region.tbl ' into the TABLE region FIELDS terminated by ' | ';"
Myqsl-e "LOAD DATA INFILE ' path/dbgen/supplier.tbl ' into the TABLE supplier FIELDS terminated by ' | ';"

4. Perform tpch test
then you can do the TPCH test, execute 23 query SQL scripts one at a time, restart the MySQL instance before each execution to ensure that each memory buffer is clean.
The simple loop test script is as follows:

#!/bin/sh
# # #
Execute tpch OLAP Test # # #
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]
Todo
N=1
t=23 while
[$N-lt $T]
does
 if [$N-lt] 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

Attachment: TPCH initialization, automated test script compression package and Word manual.