Benchmarking SelectDB with TPC-DS

Notes

• If you plan to use Git on the server to download the TPC-DS test tool, you must enable public network access.

  • For a new ECS instance: When you create the ECS instance, select Assign Public IPv4 Address for Public IP.

  • For an existing ECS instance without public network access: To enable public network access for the ECS instance, see Enable public bandwidth.

• The data files generated from the test dataset are approximately 1,000 GB. Ensure that the server has sufficient storage.

Step 1: Log in to the target server

If your server is an ECS instance, see Choose a connection method.

For other server types, see their product documentation for login instructions.

Step 2: Install the TPC-DS test tool

1. Download the tool.

   Run the following command to download the tool using Git.

   git clone https://github.com/apache/doris.git && cd ./doris/tools/tpcds-tools

2. Compile the tool.

   Run the following script to compile the tool.

   sh bin/build-tpcds-tools.sh

Step 3: Generate the TPC-DS test dataset

Run the script to generate the test dataset from the installation directory of the test tool.

Syntax:

sh bin/gen-tpcds-data.sh -s <dataSizeInGB>

Parameter description:

dataSizeInGB:

• Description: The size of the dataset to generate using TPC-DS.

• Unit: GB

This test generates a 1000 GB (1 TB) test dataset. This step can take a long time. We recommend running this task in the background:

nohup sh bin/gen-tpcds-data.sh -s 1000 > gen-tpcds-data.log 2>&1 &

The execution output is saved to the gen-tpcds-data.log file in the test tool's installation directory. You can check this file to verify that the process is running correctly.

The test dataset is saved as .dat files in the tpcds-data directory. This directory is located in the bin directory of the test tool's installation path.

Step 4: Create test tables in SelectDB

1. Configure SelectDB instance information.

   Before you execute the table creation script, you need to configure information about the SelectDB instance in the doris-cluster.conf file. This file is located in the tpcds-tools/conf/ directory of the test tool installation. The following is an example.

   # Any of FE host
   export FE_HOST='selectdb-cn-******.selectdbfe.rds.aliyuncs.com'
   # http_port in fe.conf
   export FE_HTTP_PORT=8080
   # query_port in fe.conf
   export FE_QUERY_PORT=9030
   # Doris username
   export USER='admin'
   # Doris password
   export PASSWORD='******'
   # The database where TPC-DS tables located
   export DB='test_db'

   Parameter description:

   Parameter	Description
   FE_HOST	The endpoint of the SelectDB instance. You can find the VPC endpoint or public endpoint from the Network Information section of the instance details page in the SelectDB console.
   FE_HTTP_PORT	The HTTP port of the SelectDB instance. The default port is 8080 for SelectDB. You can get the HTTP protocol port from the Network Information section of the instance details page in the SelectDB console.
   FE_QUERY_PORT	The MySQL port of the SelectDB instance. The default port is 9030 for SelectDB. You can get the MySQL protocol port from the Network Information section of the instance details page in the SelectDB console.
   USER	The account for the SelectDB instance. When you create a SelectDB instance, an admin account is created by default.
   PASSWORD	The password for the SelectDB instance account. If you are using the admin account and have forgotten the password, you can reset the admin password of the instance in the console.
   DB	The name of the database in the SelectDB instance to import data into.

2. Create tables.

   Run the following script in the test tool's installation directory to create the test tables. After the script completes, it creates the tables described in Step 4: Understand the test dataset in the target database of your SelectDB instance.

   sh bin/create-tpcds-tables.sh -s 1000

Step 5: Import data into SelectDB

Run the following script in the test tool's installation directory to import all TPC-DS test data into SelectDB.

sh bin/load-tpcds-data.sh

This test imports a 1000 GB (1 TB) test dataset into SelectDB. This step can take a long time. We recommend that you run this task in the background:

nohup sh bin/load-tpcds-data.sh > load-tpcds-data.log 2>&1 &

The execution output is saved to the load-tpcds-data.log file in the test tool's installation directory. You can check this file to monitor the import process.

Step 6: Test query performance

• Test batch query performance

  Important

  The larger the dataset, the longer the batch test takes, depending on the performance of your server.

  You can run the TPC-DS test SQL script to execute the test suite's SQL queries in batches.

  Syntax:

  sh bin/run-tpcds-queries.sh -s <dataSizeInGB>

  Parameter description:

  dataSizeInGB: Make sure the query runs against the correct data scale. This value must match the scale used for data generation (for example, if you used -s 1000, you must also use -s 1000 to run queries).

  After the script is complete, the console window displays the performance results for each SQL query in the test suite on SelectDB.

  This test queries a 1000 GB (1 TB) test dataset. This step can take a long time. We recommend that you run this task in the background:

  nohup sh bin/run-tpcds-queries.sh -s 1000 > run-tpcds-queries.log 2>&1 &

  The query results are saved to the run-tpcds-queries.log file in the test tool's installation directory. You can check this file to monitor the query process and view the final test results. For the test results from the 1000 GB dataset used in this document, see Test results.

• Test single query performance

  To test the performance of a single SQL query on SelectDB, follow these steps:

  1. Connect to the SelectDB instance. For instructions, see Connect to ApsaraDB for SelectDB with DMS.

  2. Execute the target SQL statement. The SQL statements are available in TPCDS-Query-SQL.

     Note

     The SQL statements in TPCDS-Query-SQL are the same as those executed by the batch test script.