TPC-H tests

Prerequisites

Before you begin, make sure you have:

• An Alibaba Cloud account with permissions to create ECS and PolarDB-X instances

• Access to the same virtual private cloud (VPC) for all instances you create

Step 1: Create an ECS instance

Create an Elastic Compute Service (ECS) instance to use as the stress testing client.

Deploy the ECS instance in a VPC. Record the VPC name and ID—all database instances created in subsequent steps must reside in the same VPC.

Step 2: Create a PolarDB-X instance

1. Create a PolarDB-X instance. See Create a PolarDB-X instance.

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

2. Create a database named tpch_100g. See Create a database.

   CREATE DATABASE tpch_100g MODE = 'auto';

3. Create the TPC-H tables in tpch_100g. All major tables use 192 partitions; the nation and region tables use broadcast distribution.

   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 PARTITION BY KEY(`c_custkey`)
     PARTITIONS 192;
   
   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_PARTKEY` (`l_partkey`),
     KEY `IDX_SUPPKEY` (`l_suppkey`),
     KEY `IDX_LINEITEM_SHIPDATE` (`l_shipdate`),
     PRIMARY KEY (`l_orderkey`,`l_linenumber`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY KEY(`l_orderkey`)
     PARTITIONS 192;
   
   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 PARTITION BY KEY(`o_orderkey`)
     PARTITIONS 192;
   
   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 PARTITION BY KEY(`p_partkey`)
     PARTITIONS 192;
   
   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 PARTITION BY KEY(`ps_partkey`)
     PARTITIONS 192;
   
   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 PARTITION BY KEY(`s_suppkey`)
     PARTITIONS 192;
   
   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: Configure instance parameters

These parameters tune the PolarDB-X computing layer to enable MPP-based parallel execution and disable profiling overhead during the benchmark run. Apply them before importing data.

1. Set XPROTO_MAX_DN_CONCURRENT and XPROTO_MAX_DN_WAIT_CONNECTION to 4000 in the console. See Parameter settings.

2. Connect to the instance with a command-line client and run the following statements in a single session:

   Parameter	Value	Purpose
   RECORD_SQL	false	Disables SQL logging to reduce overhead
   ENABLE_HTAP	true	Enables hybrid transaction and analytic processing
   ENABLE_MASTER_MPP	true	Enables MPP query execution on the master node
   MPP_METRIC_LEVEL	0	Disables MPP metrics collection
   ENABLE_CPU_PROFILE	false	Disables CPU profiling
   ENABLE_SORT_AGG	false	Disables sort-based aggregation in favor of hash aggregation
   MPP_PARALLELISM	192	Sets MPP parallelism to match the number of table partitions
   GROUP_PARALLELISM	8	Sets per-group parallelism for query execution

   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;

   The following table describes each parameter:

Step 4: Import and verify data

Import data

1. Download Batch Tool and upload it to the ECS instance. See Use Batch Tool to export and import data.

2. Install Java on the ECS instance. For Alibaba Cloud Linux 3:

   yum install -y java-11-openjdk.x86_64

3. Import the 100 GB TPC-H dataset. Set your connection details in the variables below, then run the command:

   # Set your connection details
   HOST=<polardbx-host>      # Endpoint of the PolarDB-X instance
   PORT=<port>               # Port number
   USER=<username>           # Username
   PASSWORD=<password>       # Password
   
   java -Xmn8g -Xmx16g -jar batch-tool.jar \
     -h ${HOST} -P${PORT} -u ${USER} -p ${PASSWORD} \
     -D tpch_100g -o import -benchmark tpch -scale 100 \
     -s , -pro 1 -con 80 -minConn 81 -maxConn 81 -batchSize 500

Verify data integrity

Connect to the instance and run the following query to confirm row counts match the expected values:

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;

Expected output:

+--------------+--------------+------------+-----------+----------+--------------+------------+--------------+
| customer_cnt | lineitem_cnt | nation_cnt | order_cnt | part_cnt | partsupp_cnt | region_cnt | supplier_cnt |
+--------------+--------------+------------+-----------+----------+--------------+------------+--------------+
|     15000000 |    600037902 |         25 | 150000000 | 20000000 |     80000000 |          5 |      1000000 |
+--------------+--------------+------------+-----------+----------+--------------+------------+--------------+

Collect statistics

Run ANALYZE TABLE on all eight tables to generate the optimizer statistics needed for efficient query planning:

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 benchmark

1. Download and extract the query script package tpch-queries.tar.gz:

   tar xzvf tpch-queries.tar.gz

2. Run all 22 queries and record per-query execution time:

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