## Thanks to George Ma, who shared a copy and HOW TO for TPC-H in one of his bug report which I'm using here. ## Build TPC-H ### Download TPC-H [https://github.com/gregrahn/tpch-kit](https://github.com/gregrahn/tpch-kit) ### Build dbgen ```shell # 1. cd tpch-kit/dbgen # 2. # vi makefile.suite vi Makefile ################ ## CHANGE NAME OF ANSI COMPILER HERE ################ CC = gcc # Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata) # SQLSERVER, SYBASE, ORACLE, VECTORWISE, POSTGRESQL # Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, MACOS # SGI, SUN, U2200, VMS, LINUX, WIN32 # Current values for WORKLOAD are: TPCH DATABASE = MYSQL // From POSTGRESQL to MYSQL MACHINE = LINUX WORKLOAD = TPCH # 3. # add MySQL defination somewhere vi 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 /* MYSQL */ # 4. cd tpch-kit/dbgen make clean make ``` ### Use dbgen to generate data ```shell ./dbgen --help # 10G data ./dbgen -s 2 # view data ls -lh | grep tbl ~/work/binaries/utils/tpch-kit-master/dbgen]$ ls -lh | grep tbl -rw-r--r-- 1 umshastr common 47M Oct 21 14:52 customer.tbl -rw-r--r-- 1 umshastr common 1.5G Oct 21 14:52 lineitem.tbl -rw-r--r-- 1 umshastr common 2.2K Oct 21 14:52 nation.tbl -rw-r--r-- 1 umshastr common 327M Oct 21 14:52 orders.tbl -rw-r--r-- 1 umshastr common 227M Oct 21 14:52 partsupp.tbl -rw-r--r-- 1 umshastr common 46M Oct 21 14:52 part.tbl -rw-r--r-- 1 umshastr common 384 Oct 21 14:52 region.tbl -rw-r--r-- 1 umshastr common 2.7M Oct 21 14:52 supplier.tbl ## Run TPC-H ### DDL dss.ddl ```sql -- Sccsid: @(#)dss.ddl 2.1.8.1 DROP DATABASE IF EXISTS tpch; CREATE DATABASE tpch; USE tpch; CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL, N_NAME CHAR(25) NOT NULL, N_REGIONKEY INTEGER NOT NULL, N_COMMENT VARCHAR(152)); CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL, R_NAME CHAR(25) NOT NULL, R_COMMENT VARCHAR(152)); CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL, P_NAME VARCHAR(55) NOT NULL, P_MFGR CHAR(25) NOT NULL, P_BRAND CHAR(10) NOT NULL, P_TYPE VARCHAR(25) NOT NULL, P_SIZE INTEGER NOT NULL, P_CONTAINER CHAR(10) NOT NULL, P_RETAILPRICE DECIMAL(15,2) NOT NULL, P_COMMENT VARCHAR(23) NOT NULL ); CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL, S_NAME CHAR(25) NOT NULL, S_ADDRESS VARCHAR(40) NOT NULL, S_NATIONKEY INTEGER NOT NULL, S_PHONE CHAR(15) NOT NULL, S_ACCTBAL DECIMAL(15,2) NOT NULL, S_COMMENT VARCHAR(101) NOT NULL); CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL, PS_SUPPKEY INTEGER NOT NULL, PS_AVAILQTY INTEGER NOT NULL, PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL ); CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL, C_NAME VARCHAR(25) NOT NULL, C_ADDRESS VARCHAR(40) NOT NULL, C_NATIONKEY INTEGER NOT NULL, C_PHONE CHAR(15) NOT NULL, C_ACCTBAL DECIMAL(15,2) NOT NULL, C_MKTSEGMENT CHAR(10) NOT NULL, C_COMMENT VARCHAR(117) NOT NULL); CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, O_TOTALPRICE DECIMAL(15,2) NOT NULL, O_ORDERDATE DATE NOT NULL, O_ORDERPRIORITY CHAR(15) NOT NULL, O_CLERK CHAR(15) NOT NULL, O_SHIPPRIORITY INTEGER NOT NULL, O_COMMENT VARCHAR(79) NOT NULL); CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL, L_PARTKEY INTEGER NOT NULL, L_SUPPKEY INTEGER NOT NULL, L_LINENUMBER INTEGER NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG CHAR(1) NOT NULL, L_LINESTATUS CHAR(1) NOT NULL, L_SHIPDATE DATE NOT NULL, L_COMMITDATE DATE NOT NULL, L_RECEIPTDATE DATE NOT NULL, L_SHIPINSTRUCT CHAR(25) NOT NULL, L_SHIPMODE CHAR(10) NOT NULL, L_COMMENT VARCHAR(44) NOT NULL); ``` dss.ri ```sql -- Sccsid: @(#)dss.ri 2.1.8.1 -- tpch Benchmark Version 8.0 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(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); COMMIT WORK; 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; ``` ### Load Data loaddata.sql ```sql USE tpch; SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/part.tbl' INTO TABLE part FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/region.tbl' INTO TABLE region FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; LOAD DATA LOCAL INFILE '/home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'; SET FOREIGN_KEY_CHECKS=1; ################################################################################### ###################################### 9.1.0 ###################################### ################################################################################### ## Source build /export/home/tmp/ushastry/src]$ wget -Omysql-9.1.0.tar.gz https://dev.mysql.com/get/Downloads/MySQL-9.1/mysql-9.1.0.tar.gz tar -zxvf mysql-9.1.0.tar.gz ############################################################################ ###################################### without patch ############################################################################ scl enable devtoolset-11 bash MYSQL_VERSION="Bug116309_910" TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION rm -rf bld/ mkdir bld && cd bld rm -rf CMakeCache.txt /home/umshastr/work/binaries/utils/cmake-3.28.1/bin/cmake .. -DBUILD_CONFIG=mysql_release -DWITH_SSL=system -DWITH_UNIT_TESTS=0 -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja ninja ninja install cd $TARGET BugNumber=116309 rm -rf $BugNumber/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910]$ bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 9.1.0 Source distribution Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \q Bye [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910]$ bin/mysql -uroot -S/tmp/mysql.sock < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ddl [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910]$ bin/mysql -uroot -S/tmp/mysql.sock < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ri [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910]$ bin/mysql -uroot -S/tmp/mysql.sock --local-infile=1 < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/loaddata.sql [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910]$ /export/home/tmp/ushastry/src/Bug116309_910]$ time bin/mysql -uroot -S/tmp/mysql.sock tpch < 16.sql *************************** 1. row *************************** EXPLAIN: -> Sort: supplier_cnt DESC, part.P_BRAND, part.P_TYPE, part.P_SIZE (actual time=21828..21830 rows=24525 loops=1) -> Stream results (cost=10.3e+9 rows=3.54e+6) (actual time=489..21797 rows=24525 loops=1) -> Group aggregate: count(distinct partsupp.PS_SUPPKEY) (cost=10.3e+9 rows=3.54e+6) (actual time=489..21772 rows=24525 loops=1) -> Nested loop antijoin (cost=3.12e+9 rows=31.2e+9) (actual time=489..21606 rows=237868 loops=1) -> Nested loop inner join (cost=315732 rows=1.57e+6) (actual time=411..21386 rows=237992 loops=1) -> Sort: part.P_BRAND, part.P_TYPE, part.P_SIZE (cost=43535 rows=395303) (actual time=410..425 rows=59498 loops=1) -> Filter: ((part.P_BRAND <> 'Brand#34') and (not((part.P_TYPE like 'LARGE BRUSHED%'))) and (part.P_SIZE in (48,19,12,4,41,7,21,39))) (cost=43535 rows=395303) (actual time=4.34..354 rows=59498 loops=1) -> Table scan on part (cost=43535 rows=395303) (actual time=4.34..270 rows=400000 loops=1) -> Covering index lookup on partsupp using PRIMARY (PS_PARTKEY = part.P_PARTKEY) (cost=0.728 rows=3.97) (actual time=0.351..0.352 rows=4 loops=59498) -> Single-row index lookup on using (s_suppkey = partsupp.PS_SUPPKEY) (cost=6783..6783 rows=1) (actual time=747e-6..747e-6 rows=521e-6 loops=237992) -> Materialize with deduplication (cost=6780..6780 rows=19841) (actual time=78.1..78.1 rows=10 loops=1) -> Filter: (supplier.S_SUPPKEY is not null) (cost=2208 rows=19841) (actual time=6.17..78 rows=10 loops=1) -> Filter: (supplier.S_COMMENT like '%Customer%Complaints%') (cost=2208 rows=19841) (actual time=6.17..78 rows=10 loops=1) -> Table scan on supplier (cost=2208 rows=19841) (actual time=3.34..68.2 rows=20000 loops=1) ############################################################################ ###################################### with patch ############################################################################ -- Apply patch on top of 9.1.0 scl enable devtoolset-11 bash MYSQL_VERSION="Bug116309_910_patched" TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION rm -rf bld/ mkdir bld && cd bld rm -rf CMakeCache.txt /home/umshastr/work/binaries/utils/cmake-3.28.1/bin/cmake .. -DBUILD_CONFIG=mysql_release -DWITH_SSL=system -DWITH_UNIT_TESTS=0 -DCMAKE_INSTALL_PREFIX=$TARGET -G Ninja ninja ninja install cd $TARGET BugNumber=116309 rm -rf $BugNumber/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910_patched]$ bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 9.1.0 Source distribution Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \q Bye [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910_patched]$ bin/mysql -uroot -S/tmp/mysql.sock < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ddl [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910_patched]$ bin/mysql -uroot -S/tmp/mysql.sock < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/dss.ri [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910_patched]$ bin/mysql -uroot -S/tmp/mysql.sock --local-infile=1 < /home/umshastr/work/binaries/utils/tpch-kit-master/dbgen/loaddata.sql [umshastr@support-cluster03:/export/home/tmp/ushastry/src/Bug116309_910_patched]$ /export/home/tmp/ushastry/src/Bug116309_910_patched]$ time bin/mysql -uroot -S/tmp/mysql.sock tpch < 16.sql *************************** 1. row *************************** EXPLAIN: -> Sort: supplier_cnt DESC, part.P_BRAND, part.P_TYPE, part.P_SIZE (actual time=11129..11131 rows=24525 loops=1) -> Stream results (actual time=11005..11112 rows=24525 loops=1) -> Group aggregate: count(distinct partsupp.ps_suppkey) (actual time=11005..11107 rows=24525 loops=1) -> Sort: part.P_BRAND, part.P_TYPE, part.P_SIZE (actual time=11005..11023 rows=237868 loops=1) -> Stream results (cost=2.33e+9 rows=11.6e+9) (actual time=14.4..10773 rows=237868 loops=1) -> Hash antijoin (``.s_suppkey = partsupp.PS_SUPPKEY) (cost=2.33e+9 rows=11.6e+9) (actual time=14.4..10702 rows=237868 loops=1) -> Nested loop inner join (cost=241707 rows=587446) (actual time=0.124..10645 rows=237992 loops=1) -> Filter: ((part.P_BRAND <> 'Brand#34') and (not((part.P_TYPE like 'LARGE BRUSHED%'))) and (part.P_SIZE in (48,19,12,4,41,7,21,39))) (cost=40591 rows=158363) (actual time=0.108..255 rows=59498 loops=1) -> Table scan on part (cost=40591 rows=395902) (actual time=0.102..146 rows=400000 loops=1) -> Covering index lookup on partsupp using PRIMARY (PS_PARTKEY = part.P_PARTKEY) (cost=0.899 rows=3.71) (actual time=0.173..0.174 rows=4 loops=59498) -> Hash -> Table scan on (cost=6597..6847 rows=19797) (actual time=14.2..14.2 rows=10 loops=1) -> Materialize with deduplication (cost=6597..6597 rows=19797) (actual time=14.2..14.2 rows=10 loops=1) -> Filter: (supplier.S_SUPPKEY is not null) (cost=2036 rows=19797) (actual time=0.387..14.2 rows=10 loops=1) -> Filter: (supplier.S_COMMENT like '%Customer%Complaints%') (cost=2036 rows=19797) (actual time=0.386..14.2 rows=10 loops=1) -> Table scan on supplier (cost=2036 rows=19797) (actual time=0.0489..4.78 rows=20000 loops=1) real 0m11.142s user 0m0.003s sys 0m0.003s