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

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).