PolarDB:TPC-H test for CCIs based on 100 GB of data

Background information

TPC-H is a benchmark that is widely used to evaluate the analytic query capabilities of databases. The benchmark is developed and released by Transaction Processing Performance Council (TPC). In the TPC-H benchmark, 22 complex SQL queries are performed on eight tables. Most queries contain joins on several tables, subqueries, and GROUP BY clauses.

Test design

Amount of test data

In this topic, 100 GB of data is used for the test, which indicates that the scalar factor is 100. The following list describes the amount of data in each major table:

• The LINEITEM table contains approximately 600 million rows of data.

• The ORDERS table contains 150 million rows of data.

• The PART_SUPP table contains 80 million rows of data.

Instance specifications for the TPC-H test

ECS instance type for stress testing

ecs.g7.4xlarge (16 vCPUs and 64 GB of memory)

Procedure

Step 1: Create an ECS instance for stress testing

Create an Elastic Compute Service (ECS) instance that is used to prepare data and perform stress testing.

Step 2: Create a PolarDB-X instance for stress testing

Create a PolarDB-X instance. For more information, see Create a PolarDB-X instance.

Step 3: Adjust instance parameters

1. Set the XPROTO_MAX_DN_CONCURRENT parameter to 4000. For more information, see Set parameters.

2. Connect to the PolarDB-X instance by using CLI and execute the following SQL statements in the same session to disable logging, CPU statistic sampling, and 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 Benchmark Boot

1. Enable port 4121 for the security group of the ECS instance. For more information, see Add a security group rule.

2. Log on to the 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. Access http://{Public IP address of the ECS instance}:4121 by using a browser. If the Benchmark Boot homepage appears, Benchmark Boot is installed.

Step 5: Prepare data

1. On the Benchmark Boot homepage, choose Stress Test > Database Connection > Configure Database Connection. On the page that appears, configure the host IP address, port, username, password, and TPC-H library, and set the library creation mode to AUTO to create a PolarDB-X connection. Then, click Submit.

2. Choose Stress Test > TPCH > Import TPC-H, set the size of the input dataset to 100 GB and the number of physical shards to 16, and then click Submit to import 100 GB of test data to the primary PolarDB-X instance.

   Note

   After the data is imported, Benchmark Boot automatically executes the analyze table statement to collect statistics.

3. After the data is imported, execute the following SQL statements on the primary PolarDB-X instance to create a CCI. For more information, see Create and use a CCI.

   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 execute the show columnar index statement to view the status of the CCI. For more information, see SHOW COLUMNAR INDEX.

   • It takes about 45 minutes to create a CCI.

Step 6: Add a read-only column store instance

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

Step 7: Run the TPC-H test

1. On the Benchmark Boot homepage, choose Stress Test > Database Connection > Configure Database Connection. On the page that appears, configure the TPC-H library name and set the library creation mode to AUTO to create a connection string for the read-only column store instance. Then, click Submit.

2. On the Benchmark Boot homepage, choose Stress Test > TPCH > Run TPCH. On the page that appears, select All Queries, specify a task description, and click Submit to execute the 22 query statements for the TPC-H test.

3. On the Benchmark Boot homepage, choose Test Result > TPCH to view the test results.

   Note

   A warm-up is required if the TPC-H test is run for the first time. We recommend that you run several rounds of the test to obtain stable results.

Test results

The following table describes the test results. Unit: seconds.