TPC-DS performance benchmark - E-MapReduce - Alibaba Cloud Documentation Center

TPC-DS

TPC-DS is an industry-standard benchmark developed by the Transaction Processing Performance Council (TPC), which defines specifications for data management systems and manages the publication of official benchmark results.

This benchmark builds 24 tables and runs 99 complex queries based on the TPC-DS specification. The primary metric is query response time — the elapsed time from query submission to result return.

Note

This benchmark is based on the TPC-DS specification but does not meet all official requirements. The results are not comparable to officially published TPC-DS results.

Prerequisites

Set up the required environment and prepare TPC-DS data before running the benchmark.

Note

Use a new instance for each test run to ensure accurate results. Do not use an instance that has been scaled up or down, as this can introduce variables that affect performance.

Step	Description
Create an ECS instance	Log on to Alibaba Cloud and create an ECS instance for data generation, data import into StarRocks, and running the benchmark client. Recommended specifications: Operating system: We recommend CentOS or Alibaba Cloud Linux. Data disk: An ESSD data disk is recommended. The required capacity depends on the dataset size. Region and VPC: Ensure the ECS instance and StarRocks instance are in the same region and VPC. Public IP address: A public IP address is required. For more information, see Create an instance.
Create a StarRocks instance	This benchmark uses a BE compute resource configuration of 8 CU (8 CPU cores and 32 GB of memory). Select a different configuration based on your business requirements. Log on to Alibaba Cloud, go to the EMR Serverless StarRocks page in the E-MapReduce console, and click Create Instance. For more information, see Create an instance.
Download and unzip the starrocks-benchmark toolkit	Log on to the ECS instance and run the following commands to download the benchmark toolkit. `sudo wget https://starrocks-oss.oss-cn-beijing.aliyuncs.com/public-access/starrocks-tpcds-benchmark-for-serverless.zip sudo yum install -y unzip unzip starrocks-tpcds-benchmark-for-serverless.zip`

Procedure

Step 1: Configure the benchmark toolkit

Log on to your ECS instance. For more information, see Connect to an instance.
Run the following command to go to the benchmark toolkit directory.
```
cd tpcds-poc-1.0
```

Run the vim conf/starrocks.conf command and configure the following parameters.

# for mysql cmd
mysql_host: fe-c-***-internal.starrocks.aliyuncs.com
mysql_port: 9030
mysql_user: admin
mysql_password: ****
database: tpcds

# cluster ports
http_port: 8030
be_heartbeat_port: 9050
broker_port: 8000

# benchmark config
scale_factor: 3
dataset_generate_path: /mnt/disk1/starrocks-tpcds-benchmark/datasets

The following table describes the parameters.

Parameter	Description
mysql_host	The internal endpoint of the Frontend (FE) for your StarRocks instance. You can find this on the Instance Details tab, in the FE Details section, under Internal Endpoint. Important We recommend that you do not use the public endpoint.
mysql_port	The service port of the FE for your StarRocks instance. Default: 9030. You can find this on the Instance Details tab, in the FE Details section, under Query Port.
mysql_user	The username for your StarRocks instance. Default: admin.
mysql_password	The password for the user specified in mysql_user.
database	The database name to create in the StarRocks instance. You can use the default value.
http_port	The HTTP port of the FE for your StarRocks instance. Default: 8030. You can find this on the Instance Details tab, in the FE Details section, under HTTP port.
scale_factor	The scale factor that controls the amount of data to generate, in GB. Default: 3. Important Ensure your cloud disk has enough capacity for the dataset.
dataset_generate_path	The storage path for the generated test dataset. Default: /mnt/disk1/starrocks-tpcds-benchmark/datasets.

Step 2: Run the benchmark

Run the following commands to start the automated TPC-DS benchmark. The script creates the database and tables, generates the dataset, loads the data, and executes the queries.

cd bin
sh run_tpcds.sh

You can also run specific parts of the benchmark:

Run only the queries.
```
sh run_tpcds.sh query
```
Regenerate the test data.
```
sh run_tpcds.sh gen_data
```
Reload data from existing data files, skipping the gen_data step.
```
sh run_tpcds.sh reload
```

Step 3: View the results

After the script finishes, the results appear in the terminal. The following example shows query response times for all 99 queries against the tpcds3 database.

Database queried: tpcds3
SQL		Time(ms)
Query01		34
Query02		36
Query03		26
Query04		57
Query05		40
Query06		29
Query07		35
Query08		33
Query09		31
Query10		33
Query11		45
Query12		26
Query13		31
Query14		91
Query15		29
Query16		50
Query17		36
Query18		33
Query19		31
Query20		32
Query21		32
Query22		33
Query23		67
Query24		39
Query25		32
Query26		36
Query27		32
Query28		32
Query29		33
Query30		37
Query31		46
Query32		32
Query33		52
Query34		33
Query35		35
Query36		30
Query37		31
Query38		37
Query39		33
Query40		36
Query41		34
Query42		25
Query43		31
Query44		30
Query45		27
Query46		33
Query47		50
Query48		36
Query49		49
Query50		38
Query51		30
Query52		26
Query53		32
Query54		32
Query55		27
Query56		49
Query57		41
Query58		37
Query59		37
Query60		48
Query61		32
Query62		42
Query63		34
Query64		130
Query65		29
Query66		87
Query67		31
Query68		33
Query69		40
Query70		34
Query71		29
Query72		32
Query73		28
Query74		40
Query75		48
Query76		28
Query77		40
Query78		36
Query79		29
Query80		120
Query81		36
Query82		28
Query83		39
Query84		26
Query85		37
Query86		28
Query87		32
Query88		46
Query89		32
Query90		28
Query91		32
Query92		35
Query93		29
Query94		65
Query95		57
Query96		27
Query97		29
Query98		25
Query99		38
All time(ms):	3799