MySQL-tpch test tool brief manual, mysql-tpch Test Tool

MySQL-tpch test tool brief manual, mysql-tpch Test Tool

Tpch is a toolkit provided by the TPC (Transaction Processing Performance councer) organization. Used for OLAP testing to evaluate the performance of the Decision Support System (DSS) in business analysis. It includes a complete set of Commercial ad-hoc queries and concurrent data modifications. It focuses on testing the database, platform, and I/O performance and focuses on the query capability.
Official Website: http://www.tpc.org/tpch
: 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 code package, decompress it, and then:
Cp makefile. suite makefile

Modify definitions of CC, DATABASE, MACHINE, and WORKLOAD in makefile:
################
# 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

Modify the tpcd. h file and add several macro definitions:
# 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
Then execute make compilation. After compilation, two executable files are generated:
Mongodbgen: a data generation tool. When using the InfiniDB official test script for testing, you need to use this tool to generate tpch-related table data.
 Qgen: SQL Generation Tool
Generate initialization test data:
[Root @ imysql tpch] # time./dbgen-s 50
TPC-H Population Generator (Version 2.9.0)
Copyright Transaction Processing Performance couneller 1994-2008

Real 192m43. 897 s
User 37m45. 398 s
Sys 19m4. 132 s

[Root @ imysql tpch] # ls-lh * tbl
-Rw-r -- 1 root 1.2G Sep 21 :23 customer. tbl
-Rw-r -- 1 root 1.4G Sep 21 :23 lineitem. tbl
-Rw-r -- 1 root 2.2 K Sep 21 :23 nation. tbl
-Rw-r -- 1 root 317 M Sep 21 :23 orders. tbl
-Rw-r -- 1 root 504 K Sep 21 :23 partsupp. tbl
-Rw-r -- 1 root 464 K Sep 21 :23 part. tbl
-Rw-r -- 1 root 389 Sep 21 :23 region. tbl
-Rw-r -- 1 root 69 M Sep 21 :23 supplier. tbl
The dbgen parameter-s is used to specify the number of warehouses for generating test data. We recommend that you set the reference value to 100 or more. In my test environment, it is generally set to 1000.
Because the tpch initialization library and table scripts in the source code package do not fully apply to MySQL, You need to modify some code.
Mr Cheng 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 perform the following global replications:
: % S/; \ nlimit/limit/g
: % S/limit-1/limit 1/g

Search for all statements similar to the following and remove the following (3 ):
Rochelle shipdate <= date '2017-12-01 '-interval '000000' day (3)
=>
Rochelle shipdate <= date '2017-12-01 '-interval '000000' day

Next, modify the line 10:
Count (o_orderkey)
=>
Count (o_orderkey) as c_count

Modify about 376th rows
) As c_orders (c_custkey, c_count)
=>
) As c_orders

Modify the vicinity of row 431st:
Drop view revenue0 limit 1;
=>
Drop view revenue0;

Finally, the large query SQL script is split into 23 independent SQL query scripts, from tpch_01. SQL ~ Tpch_23. SQL.

2. initialize the database table

The database table initialization script provided by tpch has some minor issues and needs to be modified:
Dss. ddl-DSS database initialization DDL script
Dss. ri-DSS data table creation index and foreign key script
Add several lines to the dss. ddl script:
Drop database tpch;
Create database tpch;
Use tpch;

The dss. ri script must be modified in the following ways:
Modify about 4th rows:
Connect to tpcd;
=>
Use tpch;

Modify 6th ~ In line 13, all the SQL comments "--" are 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 row 25th:
Add foreign key NATION_FK1 (N_REGIONKEY) references TPCD. REGION;
=>
Add foreign key NATION_FK1 (N_REGIONKEY) references TPCD. REGION (R_REGIONKEY );

Modify row 40th:
Add foreign key SUPPLIER_FK1 (S_NATIONKEY) references TPCD. NATION;
=>
Add foreign key SUPPLIER_FK1 (S_NATIONKEY) references TPCD. NATION (N_NATIONKEY );

Modify row 55th:
Add foreign key CUSTOMER_FK1 (C_NATIONKEY) references TPCD. NATION;
=>
Add foreign key CUSTOMER_FK1 (C_NATIONKEY) references TPCD. NATION (N_NATIONKEY );

Modify row 73rd:
Add foreign key PARTSUPP_FK1 (PS_SUPPKEY) references TPCD. SUPPLIER;
=>
Add foreign key PARTSUPP_FK1 (PS_SUPPKEY) references TPCD. SUPPLIER (S_SUPPKEY );

Modify row 78th:
Add foreign key PARTSUPP_FK2 (PS_PARTKEY) references TPCD. PART;
=>
Add foreign key PARTSUPP_FK2 (PS_PARTKEY) references TPCD. PART (P_PARTKEY );

Modify row 84th:
Add foreign key ORDERS_FK1 (O_CUSTKEY) references TPCD. CUSTOMER;
=>
Add foreign key ORDERS_FK1 (O_CUSTKEY) references TPCD. CUSTOMER (C_CUSTKEY );

Modify row 90th:
Add foreign key LINEITEM_FK1 (L_ORDERKEY) references TPCD. ORDERS;
=>
Add foreign key LINEITEM_FK1 (L_ORDERKEY) references TPCD. ORDERS (O_ORDERKEY );

Modify row 96th:
TPCD. PARTSUPP;
=>
TPCD. PARTSUPP (PS_PARTKEY, PS_SUPPKEY );

In addition, because the table name generated by tpch is in upper case, you need to modify the table name to lower case, so add a few more rows:
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, and the test database table is initialized. Next, you can import the data.
Note that if binlog is enabled, it is best to disable binlog before importing. Otherwise, an error message indicating that the value exceeds max_binlog_cache_size is displayed. If binlog cannot be disabled, you need to split the import file into multiple small files and then import the file.
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. Run the tpch test.
Next we can perform the tpch test. Execute 23 SQL queries one by one. Restart the MySQL instance before each execution to ensure that the memory buffer is clean.
The following is a simple cyclic test script:

#! /Bin/sh #### execute tpch OLAP test #### writed by yejr (http://imysql.com), 2012/12/14 # PATH = $ PATH:/usr/local/binexport 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> & 1I = 1II = 3 while [$ I-le $ II] doN = 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

Attachment: tpch initialization, automated testing script package and word manual.