MySQL tpch test tool Manual

From old leaves, original http://imysql.com/2012/12/21/tpch-for-mysql-manual.html

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:  TPCHDATABASE= MYSQLMACHINE = LINUXWORKLOAD = 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:
• Dbgen: 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:

# First copy the SQL Script Template to the directory where dbgen is located [root @ imysql tpch] # cp-F Queries /*. SQL. /[root @ imysql tpch] # Time. /dbgen-s 50tpc-h population generator (version 2.9.0) Copyright transaction processing performance couneller 1994-2008 real 192m43. 897 suser 37m45. 398 SSYS 19m4. 132 s [root @ imysql tpch] # ls-LH * TBL-RW-r -- 1 Root 1.2G Sep 21 customer. TBL-RW-r -- 1 Root 1.4G Sep 21 15:23 lineitem. TBL-RW-r -- 1 Root 2.2 k Sep 21 nation. TBL-RW-r -- 1 Root 317 m Sep 21 :23 orders. TBL-RW-r -- 1 Root 504 K Sep 21 partsupp. TBL-RW-r -- 1 Root 464 K Sep 21 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
Modify the vicinity of row 369th:
count(o_orderkey)=>count(o_orderkey) as c_count
Modify the vicinity of row 376th:
) 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 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. 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
Attachment: Compressed tpch initialization and automated test scripts. Remarks: In this document, please refer to the documents prepared by gulei and Wang hongquan. Thank you for your support.