MySQL tpch Test Tool Brief Manual

TPCH is a toolkit provided by 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 complete set of business-oriented AD-HOC queries and concurrent data modifications that emphasize testing database, platform, and I/O performance, and focus on query capabilities.
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 package, unzip it, and then:

CP Makefile.suite 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 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

Then perform a make compilation and generate two executables after compilation:
Dbgen: Data Generation tool. You need to use this tool to generate TPCH related table data when testing using the INFINIDB official test script.
Qgen:sql Build Tool
To generate the initialization test data:

#先把sql脚本模板拷贝到dbgen所在目录下 [[Email protected] tpch]# cp -f queries/*.sql ./[[email  protected] tpch]# time ./dbgen -s 50TPC-H Population Generator  ( version 2.9.0) copyright transaction processing performance council 1994 -  2008real    192m43.897suser    37m45.398ssys      19m4.132s[[email protected] 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.tbl

The function of the Dbgen parameter-S is to specify the number of warehouses that 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 package does not fully apply to MySQL, some code needs to be modified.
Mr. Cheng Test SQL script:

[[email protected] 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, removing the latter (3):

L_shipdate l_shipdate

--------------------------------------Split Line--------------------------------------

http://zhishuedu.comTraining is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, with MySQL dba combat optimization and Python devops Development course, which is the most conscientious and The most quality training courses.

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1879756

MySQL tpch Test Tool Brief Manual