PolarDB:TPC-H tests

Data set

The test uses 100 GB of data (scale factor = 100). The following table shows the row counts for the three largest tables.

Test environment

Step 1: Create an ECS instance for stress testing

Create an Elastic Compute Service (ECS) instance to generate data and run the stress test. The disk size must be larger than 200 GB, because the generated CSV data sets are stored locally.

Step 2: Create a PolarDB-X instance

1. Create a PolarDB-X instance.

   Note The PolarDB-X instance and the ECS instance must be in the same VPC.

2. Create a database named tpch_100g. For details, see Create a database.

   CREATE DATABASE tpch_100g;

3. Create the eight TPC-H tables in tpch_100g. All large tables use dbpartition and tbpartition (four table partitions each). Small reference tables (nation and region) use broadcast.

   CREATE TABLE `customer` (
     `c_custkey` int(11) NOT NULL,
     `c_name` varchar(25) NOT NULL,
     `c_address` varchar(40) NOT NULL,
     `c_nationkey` int(11) NOT NULL,
     `c_phone` varchar(15) NOT NULL,
     `c_acctbal` decimal(15,2) NOT NULL,
     `c_mktsegment` varchar(10) NOT NULL,
     `c_comment` varchar(117) NOT NULL,
     PRIMARY KEY (`c_custkey`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`c_custkey`) tbpartition by hash(`c_custkey`) tbpartitions 4;
   
   CREATE TABLE `lineitem` (
     `l_orderkey` bigint(20) NOT NULL,
     `l_partkey` int(11) NOT NULL,
     `l_suppkey` int(11) NOT NULL,
     `l_linenumber` bigint(20) 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` varchar(1) NOT NULL,
     `l_linestatus` varchar(1) NOT NULL,
     `l_shipdate` date NOT NULL,
     `l_commitdate` date NOT NULL,
     `l_receiptdate` date NOT NULL,
     `l_shipinstruct` varchar(25) NOT NULL,
     `l_shipmode` varchar(10) NOT NULL,
     `l_comment` varchar(44) NOT NULL,
     KEY `IDX_LINEITEM_SUPPKEY` (`l_suppkey`),
     KEY `IDX_LINEITEM_PARTKEY` (`l_partkey`),
     KEY `IDX_LINEITEM_SHIPDATE` (`l_shipdate`),
     PRIMARY KEY (`l_orderkey`,`l_linenumber`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by RIGHT_SHIFT(`l_orderkey`,6) tbpartition by RIGHT_SHIFT(`l_orderkey`,6) tbpartitions 4;
   
   CREATE TABLE `orders` (
     `o_orderkey` bigint(20) NOT NULL,
     `o_custkey` int(11) NOT NULL,
     `o_orderstatus` varchar(1) NOT NULL,
     `o_totalprice` decimal(15,2) NOT NULL,
     `o_orderdate` date NOT NULL,
     `o_orderpriority` varchar(15) NOT NULL,
     `o_clerk` varchar(15) NOT NULL,
     `o_shippriority` bigint(20) NOT NULL,
     `o_comment` varchar(79) NOT NULL,
     PRIMARY KEY (`O_ORDERKEY`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by RIGHT_SHIFT(`O_ORDERKEY`,6) tbpartition by RIGHT_SHIFT(`O_ORDERKEY`,6) tbpartitions 4;
   
   CREATE TABLE `part` (
     `p_partkey` int(11) NOT NULL,
     `p_name` varchar(55) NOT NULL,
     `p_mfgr` varchar(25) NOT NULL,
     `p_brand` varchar(10) NOT NULL,
     `p_type` varchar(25) NOT NULL,
     `p_size` int(11) NOT NULL,
     `p_container` varchar(10) NOT NULL,
     `p_retailprice` decimal(15,2) NOT NULL,
     `p_comment` varchar(23) NOT NULL,
     PRIMARY KEY (`p_partkey`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`p_partkey`) tbpartition by hash(`p_partkey`) tbpartitions 4;
   
   CREATE TABLE `partsupp` (
     `ps_partkey` int(11) NOT NULL,
     `ps_suppkey` int(11) NOT NULL,
     `ps_availqty` int(11) NOT NULL,
     `ps_supplycost` decimal(15,2) NOT NULL,
     `ps_comment` varchar(199) NOT NULL,
     KEY `IDX_PARTSUPP_SUPPKEY` (`PS_SUPPKEY`),
     PRIMARY KEY (`ps_partkey`,`ps_suppkey`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`ps_partkey`) tbpartition by hash(`ps_partkey`) tbpartitions 4;
   
   CREATE TABLE `supplier` (
     `s_suppkey` int(11) NOT NULL,
     `s_name` varchar(25) NOT NULL,
     `s_address` varchar(40) NOT NULL,
     `s_nationkey` int(11) NOT NULL,
     `s_phone` varchar(15) NOT NULL,
     `s_acctbal` decimal(15,2) NOT NULL,
     `s_comment` varchar(101) NOT NULL,
     PRIMARY KEY (`s_suppkey`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`s_suppkey`) tbpartition by hash(`s_suppkey`) tbpartitions 4;
   
   CREATE TABLE `nation` (
     `n_nationkey` int(11) NOT NULL,
     `n_name` varchar(25) NOT NULL,
     `n_regionkey` int(11) NOT NULL,
     `n_comment` varchar(152) DEFAULT NULL,
     PRIMARY KEY (`n_nationkey`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 broadcast;
   
   CREATE TABLE `region` (
     `r_regionkey` int(11) NOT NULL,
     `r_name` varchar(25) NOT NULL,
     `r_comment` varchar(152) DEFAULT NULL,
     PRIMARY KEY (`r_regionkey`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 broadcast;

Step 3: Tune instance parameters

Adjust the following parameters on the compute node to optimize stress testing performance. For details, see Parameter settings.

1. Set XPROTO_MAX_DN_CONCURRENT and XPROTO_MAX_DN_WAIT_CONNECTION to 4000.

2. Connect to the PolarDB-X instance using a command-line client and run the following statements in the same session to enable massively parallel processing (MPP) and disable logging and CPU statistical sampling:

   set GLOBAL RECORD_SQL = false;
   set GLOBAL ENABLE_HTAP=true;
   set GLOBAL ENABLE_MASTER_MPP=true;
   set GLOBAL MPP_METRIC_LEVEL = 0;
   set GLOBAL ENABLE_CPU_PROFILE = false;
   set GLOBAL ENABLE_SORT_AGG=false;
   set GLOBAL MPP_PARALLELISM=192;
   set GLOBAL GROUP_PARALLELISM=8;

Step 4: Prepare test data

1. Download and decompress the tpchData.tar.gz package, then open params.conf:

   tar xzvf tpchData.tar.gz
   cd tpchData/
   vi params.conf

2. In params.conf, set the PolarDB-X connection details:

   Placeholder	Description
   {HOST}	PolarDB-X instance hostname
   {PORT}	PolarDB-X connection port
   {USER}	Database username
   {PASSWORD}	Database password

   #!/bin/bash
   
   ### remote generating directory
   export remoteGenDir=./
   
   ### target path
   export targetPath=../tpch/tpchRaw
   
   ### cores per worker, default value is 1
   export coresPerWorker=`cat /proc/cpuinfo| grep "processor"| wc -l`
   
   ### threads per worker, default value is 1
   export threadsPerWorker=`cat /proc/cpuinfo| grep "processor"| wc -l`
   #export threadsPerWorker=1
   
   export hint=""
   
   
   export insertMysql="mysql -h{HOST} -P{PORT} -u{USER} -p{PASSWORD} -Ac --local-infile tpch_100g -e"

   Replace the following placeholders: To speed up data generation, set threadsPerWorker to the number of vCPUs on the ECS instance.

3. In the tpchData/workloads directory, confirm that tpch.workload.100.lst is present. This file controls data generation for a 100 GB data set (scale factor = 100). The third column sets how many segments the generation process splits into—increase it if resources are limited.

4. Generate 100 GB of data:

   cd datagen
   sh generateTPCH.sh 100

   The generated files appear in tpch/tpchRaw/SF100/:

   ls ../tpch/tpchRaw/SF100/
   customer  lineitem  nation  orders  part  partsupp  region  supplier

5. Import the data into PolarDB-X:

   cd ../loadTpch
   sh loadTpch.sh 100

6. Verify data integrity by checking row counts for all eight tables:

   MySQL [tpch_100g]> select (select count(*) from customer) as customer_cnt,
    (select count(*)  from lineitem) as lineitem_cnt,
    (select count(*)  from nation) as nation_cnt,
    (select count(*)  from orders) as order_cnt,
    (select count(*) from part) as part_cnt,
    (select count(*) from partsupp) as partsupp_cnt,
    (select count(*) from region) as region_cnt,
    (select count(*) from supplier) as supplier_cnt;
   +--------------+--------------+------------+-----------+----------+--------------+------------+--------------+
   | customer_cnt | lineitem_cnt | nation_cnt | order_cnt | part_cnt | partsupp_cnt | region_cnt | supplier_cnt |
   +--------------+--------------+------------+-----------+----------+--------------+------------+--------------+
   |     15000000 |    600037902 |         25 | 150000000 | 20000000 |     80000000 |          5 |      1000000 |
   +--------------+--------------+------------+-----------+----------+--------------+------------+--------------+

7. Collect table statistics for the query optimizer:

   analyze table customer;
   analyze table lineitem;
   analyze table nation;
   analyze table orders;
   analyze table part;
   analyze table partsupp;
   analyze table region;
   analyze table supplier;

Step 5: Run the stress test

1. Download and decompress the tpch-queries.tar.gz test script package:

   tar xzvf tpch-queries.tar.gz

2. Run all 22 queries and record the elapsed time for each:

   cd tpch-queries
   'time' -f "%e" sh all_query.sh {HOST} {USER} {PASSWORD} {DB} {PORT}

   Each query runs once. The recorded time is the actual wall-clock elapsed time in seconds.