Benchmark performance with the Star Schema Benchmark

Step 1: Prepare the target instance

1. Prepare an instance.

   If you already have a target instance, verify that it meets the following requirements.

   If you do not have a target instance, you must create one.

   The instance used in this test meets the following requirements:

   • The SelectDB Core is version 4.1 or later.

     If you have an instance running a version earlier than 4.1, you must upgrade it. For more information, see Update the kernel version.

   • The specification must be 96-core 384 GB or higher. This test uses an instance with this specification.

   • The cluster cache space must be 1,200 GB or larger. This test uses 1,200 GB of cache space.

2. Set the streaming_load_max_mb parameter to its maximum value.

   The test tool uses Stream Load to upload data. Because the test dataset exceeds the default Stream Load limit of 10,240 MB, you must increase the BE parameter streaming_load_max_mb to its maximum value of 10,240,000 MB. For more information about how to modify parameters, see Parameter configuration.

3. Create a target database for the test data.

   If you already have a target database, skip this step.

   1. Connect to your instance. For more information, see Connect to an ApsaraDB for SelectDB instance by using a MySQL client.

   2. Create the database.

      This test uses a database named test_db. Run the following statement to create it.

      CREATE DATABASE test_db;

Step 2: Prepare the test server

Step 3: Ensure network connectivity

Ensure that the server where you install the SSB test tool can connect to your SelectDB instance.

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

   If the test server is an Alibaba Cloud ECS instance in the same VPC as the ApsaraDB for SelectDB instance, skip this step.

2. Add the IP address of the server to the ApsaraDB for SelectDB instance's IP address whitelist. For more information, see Configure an IP address whitelist.

Step 4: Learn about the test dataset

This test generates 1,000 GB of data using SSB and loads it into SelectDB for performance testing. The following table describes the tables in the 1,000 GB dataset.

Step 1: Log on to the target server

If your server is an Alibaba Cloud ECS instance, see Connection methods.

For other types of servers, see the corresponding product documentation.

Step 2: Download and install the SSB tool

1. Download the tool.

   This test uses Git to download the tool. Run the following command.

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

2. Build the tool.

   Run the following command to compile the tool.

   sh bin/build-ssb-dbgen.sh

Step 3: Generate the SSB dataset

From the tool's installation directory, run the script to generate the test dataset.

Syntax:

sh bin/gen-ssb-data.sh -s <yourAimDataNum>

Parameters:

yourAimDataNum:

• The size of the dataset for SSB to generate.

• Unit: GB

This medium-scale test generates a 1,000 GB (1 TB) dataset. Because this step can take a long time, we recommend running it in the background:

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

The execution log is written to the gen-ssb-data.log file in the tool's installation directory. Check this file to monitor progress.

The generated data files are stored in the ssb-data directory within the tool's bin directory. The data files have a .tbl extension.

Step 4: Create SSB test tables

1. Configure the SelectDB instance information

   Before you run the table creation script, configure your SelectDB instance information in the doris-cluster.conf file, located in the ssb-tools/conf/ directory under the test tool installation directory. 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 SSB tables located
   export DB='test_db'

   Parameters:

   Parameter	Description
   FE_HOST	The endpoint of the SelectDB instance. You can find the VPC or public endpoint in the Network Information section on the Instance Details page in the SelectDB console.
   FE_HTTP_PORT	The HTTP port of the SelectDB instance. You can find the HTTP port in the Network Information section on the Instance Details page in the SelectDB console.
   FE_QUERY_PORT	The MySQL protocol port. You can find it in the Network Information section on the Instance Details page in the SelectDB console.
   USER	The username for the SelectDB instance. When you create a SelectDB instance, an admin account is created by default.
   PASSWORD	The password for your SelectDB instance account. If you set USER to admin and have forgotten the password, you can reset it in the console.
   DB	The name of the target database on the SelectDB instance.

2. Create tables.

   In the tool's installation directory, run the following script to create the test tables in the target database on your SelectDB instance.

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

Step 5: Load data into SelectDB

In the tool's installation directory, run the following script to load all SSB and SSB-Flat data into SelectDB.

sh bin/load-ssb-data.sh

This medium-scale test loads a 1,000 GB (1 TB) dataset into SelectDB. Because this step can take a long time, we recommend running it in the background:

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

The execution log is written to the load-ssb-data.log file in the tool's installation directory. Check this file to monitor progress.

Step 6: Run the performance test

The SSB standard test evaluates database performance on complex star schema queries involving multi-table joins, aggregations, and filters.

The SSB-Flat test evaluates database performance on wide table structures and measures the impact of a denormalized data model on query efficiency.

• Batch test the query performance

  SSB standard test

  Run the SSB standard test script to execute all SQL queries in the test suite.

  Syntax:

  sh bin/run-ssb-queries.sh -s <yourAimDataNum>

  Parameters:

  yourAimDataNum: Must match the scale used during data generation. For example, if you used -s 1000 to generate the data, use -s 1000 when running queries.

  When the script finishes, the console displays the performance results for each query on SelectDB.

  This medium-scale test uses a 1,000 GB (1 TB) dataset. Because this step can take a long time, we recommend running it in the background:

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

  For the complete SQL query list, see ssb-queries.

  The query results are written to the run-ssb-queries.log file in the tool's installation directory. For the 1,000 GB benchmark results, see Test results.

  SSB-flat test

  Run the SSB-Flat test script to execute all SQL queries in the test suite.

  Syntax:

  sh bin/run-ssb-flat-queries.sh -s <yourAimDataNum>

  Parameters:

  yourAimDataNum: Must match the scale used during data generation. For example, if you used -s 1000 to generate the data, use -s 1000 when running queries.

  When the script finishes, the console displays the performance results for each query on SelectDB.

  This medium-scale test uses a 1,000 GB (1 TB) dataset. Because this step can take a long time, we recommend running it in the background:

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

  For the complete SQL query list, see ssb-flat-queries.

  The query results are written to the run-ssb-flat-queries.log file in the tool's installation directory. For the 1,000 GB benchmark results, see Test results.

• Test individual SQL queries

  To test the performance of a specific SQL query on SelectDB:

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

  2. Run the desired SQL query.

     SSB standard test

     You can find query statements in the SSB standard test queries directory.

     You can also run any of the following SQL queries used in this test.

     --Q1.1
     SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
     FROM lineorder, dates
     WHERE
         lo_orderdate = d_datekey
       AND d_year = 1993
       AND lo_discount BETWEEN 1 AND 3
       AND lo_quantity < 25;
     
     --Q1.2
     SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
     FROM lineorder, dates
     WHERE
         lo_orderdate = d_datekey
       AND d_yearmonth = 'Jan1994'
       AND lo_discount BETWEEN 4 AND 6
       AND lo_quantity BETWEEN 26 AND 35;
         
     --Q1.3
     SELECT
         SUM(lo_extendedprice * lo_discount) AS REVENUE
     FROM lineorder, dates
     WHERE
         lo_orderdate = d_datekey
       AND d_weeknuminyear = 6
       AND d_year = 1994
       AND lo_discount BETWEEN 5 AND 7
       AND lo_quantity BETWEEN 26 AND 35;
         
     --Q2.1
     SELECT SUM(lo_revenue), d_year, p_brand
     FROM lineorder, dates, part, supplier
     WHERE
         lo_orderdate = d_datekey
       AND lo_partkey = p_partkey
       AND lo_suppkey = s_suppkey
       AND p_category = 'MFGR#12'
       AND s_region = 'AMERICA'
     GROUP BY d_year, p_brand
     ORDER BY p_brand;
     
     --Q2.2
     SELECT SUM(lo_revenue), d_year, p_brand
     FROM lineorder, dates, part, supplier
     WHERE
         lo_orderdate = d_datekey
       AND lo_partkey = p_partkey
       AND lo_suppkey = s_suppkey
       AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'
       AND s_region = 'ASIA'
     GROUP BY d_year, p_brand
     ORDER BY d_year, p_brand;
     
     --Q2.3
     SELECT SUM(lo_revenue), d_year, p_brand
     FROM lineorder, dates, part, supplier
     WHERE
         lo_orderdate = d_datekey
       AND lo_partkey = p_partkey
       AND lo_suppkey = s_suppkey
       AND p_brand = 'MFGR#2239'
       AND s_region = 'EUROPE'
     GROUP BY d_year, p_brand
     ORDER BY d_year, p_brand;
     
     --Q3.1
     SELECT
         c_nation,
         s_nation,
         d_year,
         SUM(lo_revenue) AS REVENUE
     FROM customer, lineorder, supplier, dates
     WHERE
         lo_custkey = c_custkey
       AND lo_suppkey = s_suppkey
       AND lo_orderdate = d_datekey
       AND c_region = 'ASIA'
       AND s_region = 'ASIA'
       AND d_year >= 1992
       AND d_year <= 1997
     GROUP BY c_nation, s_nation, d_year
     ORDER BY d_year ASC, REVENUE DESC;
     
     --Q3.2
     SELECT
         c_city,
         s_city,
         d_year,
         SUM(lo_revenue) AS REVENUE
     FROM customer, lineorder, supplier, dates
     WHERE
         lo_custkey = c_custkey
       AND lo_suppkey = s_suppkey
       AND lo_orderdate = d_datekey
       AND c_nation = 'UNITED STATES'
       AND s_nation = 'UNITED STATES'
       AND d_year >= 1992
       AND d_year <= 1997
     GROUP BY c_city, s_city, d_year
     ORDER BY d_year ASC, REVENUE DESC;
     
     --Q3.3
     SELECT
         c_city,
         s_city,
         d_year,
         SUM(lo_revenue) AS REVENUE
     FROM customer, lineorder, supplier, dates
     WHERE
         lo_custkey = c_custkey
       AND lo_suppkey = s_suppkey
       AND lo_orderdate = d_datekey
       AND (
                 c_city = 'UNITED KI1'
             OR c_city = 'UNITED KI5'
         )
       AND (
                 s_city = 'UNITED KI1'
             OR s_city = 'UNITED KI5'
         )
       AND d_year >= 1992
       AND d_year <= 1997
     GROUP BY c_city, s_city, d_year
     ORDER BY d_year ASC, REVENUE DESC;
     
     --Q3.4
     SELECT
         c_city,
         s_city,
         d_year,
         SUM(lo_revenue) AS REVENUE
     FROM customer, lineorder, supplier, dates
     WHERE
         lo_custkey = c_custkey
       AND lo_suppkey = s_suppkey
       AND lo_orderdate = d_datekey
       AND (
                 c_city = 'UNITED KI1'
             OR c_city = 'UNITED KI5'
         )
       AND (
                 s_city = 'UNITED KI1'
             OR s_city = 'UNITED KI5'
         )
       AND d_yearmonth = 'Dec1997'
     GROUP BY c_city, s_city, d_year
     ORDER BY d_year ASC, REVENUE DESC;
     
     --Q4.1
     SELECT
         d_year,
         c_nation,
         SUM(lo_revenue - lo_supplycost) AS PROFIT
     FROM dates, customer, supplier, part, lineorder
     WHERE
         lo_custkey = c_custkey
       AND lo_suppkey = s_suppkey
       AND lo_partkey = p_partkey
       AND lo_orderdate = d_datekey
       AND c_region = 'AMERICA'
       AND s_region = 'AMERICA'
       AND (
                 p_mfgr = 'MFGR#1'
             OR p_mfgr = 'MFGR#2'
         )
     GROUP BY d_year, c_nation
     ORDER BY d_year, c_nation;
     
     --Q4.2
     SELECT
         d_year,
         s_nation,
         p_category,
         SUM(lo_revenue - lo_supplycost) AS PROFIT
     FROM dates, customer, supplier, part, lineorder
     WHERE
         lo_custkey = c_custkey
       AND lo_suppkey = s_suppkey
       AND lo_partkey = p_partkey
       AND lo_orderdate = d_datekey
       AND c_region = 'AMERICA'
       AND s_region = 'AMERICA'
       AND (
                 d_year = 1997
             OR d_year = 1998
         )
       AND (
                 p_mfgr = 'MFGR#1'
             OR p_mfgr = 'MFGR#2'
         )
     GROUP BY d_year, s_nation, p_category
     ORDER BY d_year, s_nation, p_category;
     
     --Q4.3
     SELECT
         d_year,
         s_city,
         p_brand,
         SUM(lo_revenue - lo_supplycost) AS PROFIT
     FROM dates, customer, supplier, part, lineorder
     WHERE
         lo_custkey = c_custkey
       AND lo_suppkey = s_suppkey
       AND lo_partkey = p_partkey
       AND lo_orderdate = d_datekey
       AND s_nation = 'UNITED STATES'
       AND (
                 d_year = 1997
             OR d_year = 1998
         )
       AND p_category = 'MFGR#14'
     GROUP BY d_year, s_city, p_brand
     ORDER BY d_year, s_city, p_brand;
     

     SSB-flat test

     You can find query statements in the SSB-Flat test queries directory.

     You can also run any of the following SQL queries used in this test.

     --Q1.1
     SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
     FROM lineorder_flat
     WHERE
         LO_ORDERDATE >= 19930101
         AND LO_ORDERDATE <= 19931231
         AND LO_DISCOUNT BETWEEN 1 AND 3
         AND LO_QUANTITY < 25;
     
     --Q1.2
     SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
     FROM lineorder_flat
     WHERE
         LO_ORDERDATE >= 19940101
       AND LO_ORDERDATE <= 19940131
       AND LO_DISCOUNT BETWEEN 4 AND 6
       AND LO_QUANTITY BETWEEN 26 AND 35;
     
     --Q1.3
     SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
     FROM lineorder_flat
     WHERE
         weekofyear(LO_ORDERDATE) = 6
       AND LO_ORDERDATE >= 19940101
       AND LO_ORDERDATE <= 19941231
       AND LO_DISCOUNT BETWEEN 5 AND 7
       AND LO_QUANTITY BETWEEN 26 AND 35;
     
     --Q2.1
     SELECT
         SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
         P_BRAND
     FROM lineorder_flat
     WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
     GROUP BY YEAR, P_BRAND
     ORDER BY YEAR, P_BRAND;
     
     --Q2.2
     SELECT
         SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
         P_BRAND
     FROM lineorder_flat
     WHERE
         P_BRAND >= 'MFGR#2221'
       AND P_BRAND <= 'MFGR#2228'
       AND S_REGION = 'ASIA'
     GROUP BY YEAR, P_BRAND
     ORDER BY YEAR, P_BRAND;
     
     --Q2.3
     SELECT
         SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
         P_BRAND
     FROM lineorder_flat
     WHERE
         P_BRAND = 'MFGR#2239'
       AND S_REGION = 'EUROPE'
     GROUP BY YEAR, P_BRAND
     ORDER BY YEAR, P_BRAND;
     
     --Q3.1
     SELECT
         C_NATION,
         S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
         SUM(LO_REVENUE) AS revenue
     FROM lineorder_flat
     WHERE
         C_REGION = 'ASIA'
       AND S_REGION = 'ASIA'
       AND LO_ORDERDATE >= 19920101
       AND LO_ORDERDATE <= 19971231
     GROUP BY C_NATION, S_NATION, YEAR
     ORDER BY YEAR ASC, revenue DESC;
     
     --Q3.2
     SELECT
         C_CITY,
         S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
         SUM(LO_REVENUE) AS revenue
     FROM lineorder_flat
     WHERE
         C_NATION = 'UNITED STATES'
       AND S_NATION = 'UNITED STATES'
       AND LO_ORDERDATE >= 19920101
       AND LO_ORDERDATE <= 19971231
     GROUP BY C_CITY, S_CITY, YEAR
     ORDER BY YEAR ASC, revenue DESC;
     
     --Q3.3
     SELECT
         C_CITY,
         S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
         SUM(LO_REVENUE) AS revenue
     FROM lineorder_flat
     WHERE
         C_CITY IN ('UNITED KI1', 'UNITED KI5')
       AND S_CITY IN ('UNITED KI1', 'UNITED KI5')
       AND LO_ORDERDATE >= 19920101
       AND LO_ORDERDATE <= 19971231
     GROUP BY C_CITY, S_CITY, YEAR
     ORDER BY YEAR ASC, revenue DESC;
     
     --Q3.4
     SELECT
         C_CITY,
         S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR,
         SUM(LO_REVENUE) AS revenue
     FROM lineorder_flat
     WHERE
         C_CITY IN ('UNITED KI1', 'UNITED KI5')
       AND S_CITY IN ('UNITED KI1', 'UNITED KI5')
       AND LO_ORDERDATE >= 19971201
       AND LO_ORDERDATE <= 19971231
     GROUP BY C_CITY, S_CITY, YEAR
     ORDER BY YEAR ASC, revenue DESC;
     
     --Q4.1
     SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
         C_NATION,
         SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
     FROM lineorder_flat
     WHERE
         C_REGION = 'AMERICA'
       AND S_REGION = 'AMERICA'
       AND P_MFGR IN ('MFGR#1', 'MFGR#2')
     GROUP BY YEAR, C_NATION
     ORDER BY YEAR ASC, C_NATION ASC;
     
     --Q4.2
     SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
         S_NATION,
         P_CATEGORY,
         SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
     FROM lineorder_flat
     WHERE
         C_REGION = 'AMERICA'
       AND S_REGION = 'AMERICA'
       AND LO_ORDERDATE >= 19970101
       AND LO_ORDERDATE <= 19981231
       AND P_MFGR IN ('MFGR#1', 'MFGR#2')
     GROUP BY YEAR, S_NATION, P_CATEGORY
     ORDER BY
         YEAR ASC,
         S_NATION ASC,
         P_CATEGORY ASC;
     
     --Q4.3
     SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
         S_CITY,
         P_BRAND,
         SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
     FROM lineorder_flat
     WHERE
         S_NATION = 'UNITED STATES'
       AND LO_ORDERDATE >= 19970101
       AND LO_ORDERDATE <= 19981231
       AND P_CATEGORY = 'MFGR#14'
     GROUP BY YEAR, S_CITY, P_BRAND
     ORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;