PolarDB:TPC-H test for clustered columnar index (100 GB)

Background

TPC-H is a decision support benchmark established by the Transaction Processing Performance Council (TPC) to evaluate the analytical query capabilities of databases. The TPC-H workload consists of eight tables and 22 complex SQL queries, most of which include table joins, subqueries, and GROUP BY aggregations.

Test design

Dataset size

This test uses a 100 GB dataset (Scale Factor = 100). The main tables include:

• LINEITEM: approximately 600 million rows

• ORDERS: 150 million rows

• PART_SUPP: 80 million rows

Test instance specifications

Node specification	Number of nodes	Dataset size
8c32g	2/4	100 GB
16c64g	2/3/4/6	100 GB

Stress test instance specification

ecs.g7.4xlarge (16 vCPU, 64 GB memory)

Procedure

Step 1: Prepare the stress test ECS

Prepare one ECS instance. You will use this instance for all subsequent operations, such as data import and running the stress test.

Step 2: Prepare the PolarDB-X instance

Create a PolarDB-X instance. For detailed instructions, see Create an instance.

Step 3: Adjust instance parameters

1. Set the XPROTO_MAX_DN_CONCURRENT parameter to 4000. For detailed instructions, see Parameter settings.

2. Connect to the PolarDB-X instance by using a command-line interface (CLI). In the same session, run the following SQL statements to disable SQL logging, CPU sampling, and automatic statistics collection.

   set global RECORD_SQL = false;
   set global MPP_METRIC_LEVEL = 0;
   set global ENABLE_CPU_PROFILE = false;
   set global ENABLE_BACKGROUND_STATISTIC_COLLECTION=false;
   set global ENABLE_STATISTIC_FEEDBACK=false;

Step 4: Install the Benchmark Boot tool

1. Open port 4121 in the security group of the stress testing ECS instance. For more information, see Add a security group rule.

2. Log on to the stress testing ECS instance and run the bash -c "$(curl -fsSL https://benchmark-boot.oss-cn-hangzhou.aliyuncs.com/setup.sh)" command to install Benchmark Boot. For more information, see Download and install Benchmark Boot.

3. In your browser, open http://{public IP address of the stress testing instance}:4121. The Benchmark Boot homepage appears, confirming a successful installation.

Step 5: Prepare the data

1. In the Benchmark Boot console, navigate to Stress Test > Database Connection > Configure Database Connection. Configure the PolarDB-X connection string (host IP address, port, username, and password). Enter the TPC-H database name, select AUTO for Database Creation Mode, and click Submit.

2. Navigate to Stress Test > TPCH > Import TPC-H. Set Submit to 100 and Submit to 16. Click Submit to import the 100 GB TPC-H dataset to the PolarDB-X primary instance.

   Note

   After the data is imported, Benchmark Boot automatically runs the ANALYZE TABLE statement to collect statistics.

3. After the 100 GB TPC-H dataset is imported, run the following SQL statements on the PolarDB-X primary instance to create clustered columnar indexes (CCIs). For more information, see Create and use CCIs.

   create clustered columnar index `nation_col_index` on nation(`n_nationkey`) partition by hash(`n_nationkey`) partitions 1;
   
   create clustered columnar index `region_col_index` on region(`r_regionkey`) partition by hash(`r_regionkey`) partitions 1;
   
   create clustered columnar index `customer_col_index` on customer(`c_custkey`) partition by hash(`c_custkey`) partitions 64;
   
   create clustered columnar index `part_col_index` on part(`p_size`) partition by hash(`p_partkey`) partitions 64;
   
   create clustered columnar index `partsupp_col_index` on partsupp(`ps_partkey`) partition by hash(`ps_partkey`) partitions 64;
   
   create clustered columnar index `supplier_col_index` on supplier(`s_suppkey`) partition by hash(`s_suppkey`) partitions 64;
   
   create clustered columnar index `orders_col_index` on orders(`o_orderdate`,`o_orderkey`) partition by hash(`o_orderkey`) partitions 64;
   
   create clustered columnar index `lineitem_col_index` on lineitem(`l_shipdate`,`l_orderkey`) partition by hash(`l_orderkey`) partitions 64;

   Note

   • You can run the SHOW COLUMNAR INDEX statement to view the current status of the CCIs. For more information, see SHOW COLUMNAR INDEX.

   • Creating the CCIs takes about 45 minutes.

Step 6: Add a read-only column store instance

Add a read-only column store instance to the PolarDB-X primary instance. For detailed instructions, see Add a read-only column store instance.

Step 7: Run the TPC-H test

1. In the Benchmark Boot console, navigate to Stress Test > Database Connection > Configure Database Connection. Configure the connection string for the read-only column store instance, enter the TPC-H database name, select AUTO for Database Creation Mode, and click Submit.

2. Navigate to Stress Test > TPCH > Run TPCH. Select Submit, enter a task description, and click Submit to run the 22 query statements of the TPC-H benchmark.

3. Navigate to Test Result > TPCH to view the final test results.

   Note

   The first benchmark run requires a warm-up. For stable results, we recommend running the test multiple times.

Test results

All execution times are in seconds (s).

Query	8c32g * 2	8c32g * 4	16c64g * 2	16c64g * 3	16c64g * 4	16c64g * 6
Q1	5.9	3.99	4.37	1.6	1.22	0.86
Q2	2.41	1.68	1.63	1.15	1.04	0.86
Q3	4.18	2.13	2.43	1.77	1.20	0.88
Q4	3.72	1.02	0.94	0.78	0.48	0.44
Q5	5.63	3.12	3.27	2.05	1.68	1.4
Q6	0.41	0.38	0.42	0.18	0.15	0.13
Q7	5.47	2.94	2.93	2.08	1.72	1.34
Q8	3.61	2.31	1.98	1.81	1.68	1.51
Q9	13.6	6.91	7.46	4.93	3.85	3.11
Q10	6.36	3.12	3.5	2.31	1.73	1.26
Q11	1.51	0.64	0.74	0.53	0.49	0.35
Q12	1.86	0.82	0.78	0.57	0.40	0.35
Q13	8.85	4.29	4.44	2.6	1.64	1.2
Q14	0.94	0.58	0.7	0.51	0.38	0.31
Q15	1.26	0.76	0.88	0.59	0.47	0.37
Q16	2.3	1	1.19	0.75	0.61	0.46
Q17	1.86	0.75	0.69	0.47	0.43	0.49
Q18	8.43	4.18	4.43	3.16	2.19	1.6
Q19	4.92	2.16	1.86	1.07	1.02	0.73
Q20	1.88	1.34	1.17	0.73	0.78	0.73
Q21	12.05	3.18	3.11	2.38	1.72	1.57
Q22	2.67	1.25	1.39	0.98	0.66	0.61
Total time	99.82	48.55	50.31	33.00	25.54	20.56