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

Dataset

The test uses a 100 GB dataset (scale factor = 100). The three largest tables contain:

• LINEITEM: approximately 600 million rows

• ORDERS: 150 million rows

• PARTSUPP: 80 million rows

Node configurations

Stress test machine

The stress test runs on an Elastic Compute Service (ECS) instance of type ecs.g7.4xlarge (16 vCPUs, 64 GB memory).

Step 1: Create an ECS instance

Create an ECS instance to use as the stress test client.

Deploy the ECS instance in a virtual private cloud (VPC) and record the VPC name and ID. All database instances in this test must reside in the same VPC. Use a CentOS image and enable a public IP address for the instance.

Step 2: Create a PolarDB-X instance

Create a PolarDB-X instance in the same VPC as the ECS instance.

Add the internal IP address of the ECS instance to the whitelist of the PolarDB-X instance.

Step 3: Tune instance parameters

Adjust the compute node parameters of the PolarDB-X instance for optimal benchmark performance.

1. Set XPROTO_MAX_DN_CONCURRENT to 4000. For instructions, see Set parameters.

2. Connect to the PolarDB-X instance using the CLI and run the following SQL statements in a single session to disable logging, CPU profiling, and background 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. Open port 4121 in the security group of the ECS instance. For instructions, see Add a security group rule.

2. Log in to the ECS instance and run the following command to install Benchmark Boot:

   bash -c "$(curl -fsSL https://benchmark-boot.oss-cn-hangzhou.aliyuncs.com/setup.sh)"

   For more information, see Download and install Benchmark Boot.

3. Open http://<Public IP of ECS instance>:4121 in a browser. If the Benchmark Boot homepage loads, the installation is complete.

Step 5: Load test data

1. On the Benchmark Boot homepage, go to Stress Test > Database Connection > Configure Database Connection. Enter the host IP address, port, username, password, and TPC-H library name. Set the library creation mode to AUTO, and click Submit.

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

   After the import completes, Benchmark Boot automatically runs ANALYZE TABLE to collect optimizer statistics.

3. After the import completes, create CCIs on the primary PolarDB-X instance by running the following SQL statements. For more information, see Create and use a CCI.

   CCI creation takes approximately 45 minutes. Run show columnar index to check progress. For more information, see SHOW COLUMNAR INDEX.

   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;

Step 6: Add a read-only column store instance

Add a read-only column store instance to the primary PolarDB-X instance.

Add the internal IP address of the ECS instance to the whitelist of the read-only column store instance as well.

Step 7: Run the test

1. On the Benchmark Boot homepage, go to Stress Test > Database Connection > Configure Database Connection. Enter the TPC-H library name for the read-only column store instance, set the library creation mode to AUTO, and click Submit.

2. Go to Stress Test > TPCH > Run TPCH. Select All Queries, enter a task description, and click Submit to run all 22 TPC-H queries.

3. Go to Test Result > TPCH to view the results.

   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.