TPC-DS benchmark

Overview

TPC-DS is a decision support benchmark formulated by the Transaction Processing Performance Council (TPC) to evaluate the performance of different systems in processing complex queries and analyzing large amounts of data.

TPC-DS is designed to simulate complex decisions in actual business. TPC-DS tests the performance of systems by running a series of complex queries and performing data operations. The queries include join queries, aggregate queries, sort queries, filter queries, and subqueries. These queries are run in various scenarios of different complexity, such as report generation, data mining, and online analytical processing (OLAP).

Prerequisites

The SelectDB instance is of V4.0 or later.

Preparations

• Perform the following steps to connect the server on which you want to install the TPC-DS test tool to the SelectDB instance:

  1. Apply for a public endpoint for the SelectDB instance. For more information, see Apply for or release a public endpoint.

     Skip this step if you want to install the TPC-DS test tool on an Elastic Compute Service (ECS) instance that resides in the same virtual private cloud (VPC) as the ApsaraDB for SelectDB instance.

  2. Add the IP address of the server on which you want to install the TPC-DS test tool to the whitelist of the ApsaraDB for SelectDB instance. For more information, see Configure an IP address whitelist.

• Make sure that MySQL is installed on the server on which you want to install the TPC-DS test tool.

  If you have not installed MySQL, run the following command to install MySQL:

  yum install mysql

• Prepare a destination database.

  Skip this step if a destination database is available.

  1. Connect to the ApsaraDB for SelectDB instance. For more information, see Connect to an ApsaraDB for SelectDB instance by using DMS.

  2. Execute the following statement to create a database:

     CREATE DATABASE <db_name>;

Test environment

• SelectDB database environment

  Item	Description

  Item	Description
  Region and zone	Zone K in the China (Hangzhou) region
  Specifications	64 CPU cores and 512 GB of memory
  Disk	800 GB high-performance Enterprise SSD (ESSD)
  ApsaraDB for SelectDB kernel version	4.0.3

• Client environment

  Item	Description

  Item	Description
  Device for downloading the test tool	An ECS instance. For more information about how to create an ECS instance, see Create an instance.
  Region and zone	China (Hangzhou)
  Instance type	ecs.g7.2xlarge
  Operating system	Ubuntu 22.04.1 LTS
  Network	The ECS instance resides in the same VPC as the ApsaraDB for SelectDB instance.

Procedure

Step 1: Install the TPC-DS test tool

1. Download the Business Foundation System test tool.

   wegt https://selectdb-customers-tools-bj.oss-cn-beijing.aliyuncs.com/yaochi_performance_tool.tar.gz

2. Decompress the tool file.

   tar -zxvf yaochi_performance_tool.tar.gz

3. In the test tool installation directory yaochi_performance_tool/tpcds-tools/bin/, run the following command to download and compile the tpcds-tools tool:

   bash build-tpcds-tools.sh

Step 2: Generate a TPC-DS test dataset

In the test tool installation directory yaochi_performance_tool/tpcds-tools/bin/, run the following command to generate a TPC-DS test dataset:

bash gen-tpcds-data.sh -s 1000

The generated TPC-DS test dataset is saved in a file suffixed by .dat in the tpcds-data directory.

Step 3: Create TPC-DS test tables

1. Configure the SelectDB instance.

   Before you create tables, you must configure the SelectDB instance in the doris-cluster.conf file. The file is stored in the test tool installation directory yaochi_performance_tool/tpcds-tools/conf/. The following table describes the parameters.

   Parameter	Description

   Parameter	Description
   FE_HOST	The endpoint that is used to access the SelectDB instance. You can view the VPC endpoint or public endpoint of the ApsaraDB for SelectDB instance in the Network Information section of the Instance Details page of the SelectDB instance.
   FE_HTTP_PORT	The HTTP port that is used to access the SelectDB instance. You can view the HTTP port of the ApsaraDB for SelectDB instance in the Network Information section of the Instance Details page of the SelectDB instance.
   FE_QUERY_PORT	The MySQL port that is used to access the ApsaraDB for SelectDB instance. You can view the MySQL port of the ApsaraDB for SelectDB instance in the Network Information section of the Instance Details page of the SelectDB instance.
   USER	The username that is used to log on to the SelectDB instance. By default, an admin account is created to log on to an SelectDB instance after it is created.
   PASSWORD	The password that is used to log on to the SelectDB instance. If you use the admin account to log on to your ApsaraDB for SelectDB instance but you forget the corresponding password, you can reset the password in the console.
   DB	The name of the database to which you want to import data in the ApsaraDB for SelectDB instance.

2. Create tables.

   In the test tool installation directory yaochi_performance_tool/tpcds-tools/bin/, run the following command to create tables for testing. After the command is run, the preceding tables in the test dataset are generated in the destination database in the SelectDB instance.

   bash create-tpcds-tables.sh -s 1000

Step 4: Import data to ApsaraDB for SelectDB

In the test tool installation directory yaochi_performance_tool/tpcds-tools/bin/, run the following command to import all data of the TPC-DS test dataset to SelectDB:

bash load-tpcds-data.sh

Step 5: Perform test queries

You can run multiple SQL queries at a time on the test dataset.

In the test tool installation directory yaochi_performance_tool/tpcds-tools/bin/, run the following command:

bash run-tpcds-queries.sh -s 1000

After the command is run, the performance metrics of each SQL query run on the SelectDB instance are displayed in the console. For more information about the test results, see the Test results section of this topic.

You can also run a specific SQL query to test the performance of SelectDB.

• Connect to the SelectDB instance. For more information, see Connect to an ApsaraDB for SelectDB instance by using DMS.

• Run the specified SQL query. Obtain the SQL statements used to run the specified SQL query on GitHub.

Test results

The following table describes the test results based on the TPC-DS 100 GB dataset and TPC-DS 1,000 GB dataset.