TPC-H benchmark

Overview

TPC-H is a decision support benchmark consisting of a suite of business-oriented ad hoc queries and concurrent data modifications. The data that is used has broad industry relevance. This benchmark testing uses a series of query operations to assess the performance of the database system in processing complex queries and data mining tasks. The performance index reported by TPC-H is called a TPC-H composite query performance index per hour (QphH@Size), which reflects the capability of the system to process queries in multiple aspects. These aspects include the database size chosen when executing the query, the query processing capability when the query is submitted by a single stream, and the query throughput when the query is submitted by many concurrent users.

Test environment

• 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 enhanced SSD (ESSD)
  ApsaraDB for SelectDB kernel version	3.0.6

• Client environment

  Item	Description

  Item	Description
  Device for downloading the test tool	An Elastic Compute Service (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 virtual private cloud (VPC) where the ApsaraDB for SelectDB instance resides.

Test sets

In this test, the TPC-H 100 GB test set and TPC-H 500 GB test set are generated and imported into SelectDB for testing. The following table describes the tables in the TPC-H 100 GB test set.

Test procedure

This section describes the test procedure. To obtain the scripts that you need to use during the test, download the Business Foundation System test tool.

Step 1: Install the unzip utility

1. Install the unzip utility by running the following command:

   sudo apt install unzip

2. After the installation is complete, check whether the unzip utility is successfully installed by running the following command:

   unzip --help

Step 2: Download and install the TPC-H data generation tool

After you download the package of the Business Foundation System test tool, decompress the package and go to the ./yaochi_performance_tool/tpch-tools/bin/ directory. Run the build-tpch-dbgen.sh script to download and compile the tpch-dbgen tool.

tar -zxvf yaochi_performance_tool.tar.gz
cd ./yaochi_performance_tool/tpch-tools/bin
bash build-tpch-dbgen.sh

After the tpch-dbgen tool is downloaded and compiled, the binary file named dbgen is generated in the TPC-H_Tools_v3.0.0/ directory.

Step 3: Generate a TPC-H test set

Run the gen-tpch-data.sh script in the ./yaochi_performance_tool/tpch-tools/bin/ directory to generate a TPC-H test set. The following sample code provides an example:

cd ./yaochi_performance_tool/tpch-tools/bin
bash gen-tpch-data.sh

Data is generated in the tpch-data/ directory, suffixed by .tbl. By default, the total file size is about 100 GB. It takes several minutes to 1 hour to generate the test set.

Step 4: Create tables

1. Prepare the doris-cluster.conf file.

   Before you run the data import script, you need to specify the information for connecting to the test database in the doris-cluster.conf file. The file is stored in the tpch-tools/conf/ directory. You need to specify the endpoint, HTTP port number, username, and password that are used to connect to the test cluster and the database to which data is to be imported.

   Note

   To obtain the VPC endpoint or public endpoint and HTTP port number of an ApsaraDB for SelectDB instance, perform the following steps: Log on to the ApsaraDB for SelectDB console. On the Instances page, find the instance that you want to test and click its ID to go to the Instance Details page. On the Instance Details page, view the values of the VPC Endpoint or Public Endpoint parameter and the HTTP Port parameter in the Network Information section.

   export FE_HOST="xxx"
   export FE_HTTP_PORT="8080"
   export FE_QUERY_PORT="9030"
   export USER="root"
   export PASSWORD='xxx'
   export DB="tpch1"

2. Create TPC-H tables.

   Run the create-tpch-tables.sh script in the ./yaochi_performance_tool/tpch-tools/bin/ directory to automatically create test tables.

   bash create-tpch-tables.sh

Step 5: Import data

Run the load-tpch-data.sh script in the ./yaochi_performance_tool/tpch-tools/bin/ directory to import the data of a TPC-H test set.

bash ./load-tpch-data.sh

Step 6: Verify the imported data

If the preceding steps and parameter settings are followed, the number of imported rows should be the same as that specified in the Number of rows column in the Test sets section of this topic.

SELECT  COUNT(*) FROM lineitem;
SELECT  COUNT(*) FROM orders;
SELECT  COUNT(*) FROM partsupp;
SELECT  COUNT(*) FROM part;
SELECT  COUNT(*) FROM customer;
SELECT  COUNT(*) FROM supplier;
SELECT  COUNT(*) FROM nation;
SELECT  COUNT(*) FROM region;
SELECT  COUNT(*) FROM revenue0;

Step 7: Perform test queries

• Run the query script.

  Run the following command to perform test queries:

  ./run-tpch-queries.sh

  For more information about the test SQL statements, see TPCH-Query-SQL.

  Note

  The query optimizer and statistics features of SelectDB still have room for improvement. Therefore, some queries are rewritten in this TPC-H test to adapt to the execution framework of SelectDB. This does not affect the accuracy of the results.

• Execute a single SQL statement.

  The following sample code shows the SQL statements that are used in this test. You can also obtain the latest test query statements from the code repository. For more information about the latest test query statements, see TPC-H test query statements.

  --ENV config
  SET GLOBAL experimental_enable_nereids_planner=true;
  SET GLOBAL experimental_enable_pipeline_engine=true;
  SET GLOBAL enable_runtime_filter_prune=false;
  SET GLOBAL runtime_filter_wait_time_ms=10000;
  SET GLOBAL enable_fallback_to_original_planner=false;
  SET GLOBAL query_timeout=1000;
  
  --Q1
  SELECT 
      l_returnflag,
      l_linestatus,
      sum(l_quantity) AS sum_qty,
      sum(l_extendedprice) AS sum_base_price,
      sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
      sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
      avg(l_quantity) AS avg_qty,
      avg(l_extendedprice) AS avg_price,
      avg(l_discount) AS avg_disc,
      count(*) AS count_order
  FROM
      lineitem
  WHERE
      l_shipdate <= date '1998-12-01' - interval '90' day
  GROUP BY
      l_returnflag,
      l_linestatus
  ORDER BY
      l_returnflag,
      l_linestatus;
  
  --Q2
  SELECT
      s_acctbal,
      s_name,
      n_name,
      p_partkey,
      p_mfgr,
      s_address,
      s_phone,
      s_comment
  FROM
      partsupp join
      (
          SELECT
              ps_partkey AS a_partkey,
              min(ps_supplycost) AS a_min
          FROM
              partsupp,
              part,
              supplier,
              nation,
              region
          WHERE
              p_partkey = ps_partkey
              AND s_suppkey = ps_suppkey
              AND s_nationkey = n_nationkey
              AND n_regionkey = r_regionkey
              AND r_name = 'EUROPE'
              AND p_size = 15
              AND p_type LIKE '%BRASS'
          GROUP BY a_partkey
      ) AONps_partkey = a_partkey AND ps_supplycost=a_min ,
      part,
      supplier,
      nation,
      region
  WHERE
      p_partkey = ps_partkey
      AND s_suppkey = ps_suppkey
      AND p_size = 15
      AND p_type like '%BRASS'
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND r_name = 'EUROPE'
  
  ORDER BY
      s_acctbal DESC,
      n_name,
      s_name,
      p_partkey
  limit 100;
  
  --Q3
  SELECT l_orderkey,
      sum(l_extendedprice * (1 - l_discount)) AS revenue,
      o_orderdate,
      o_shippriority
  FROM
      (
          SELECT l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority, o_custkey FROM
          lineitem JOIN orders
          WHERE l_orderkey = o_orderkey
          AND o_orderdate < date '1995-03-15'
          AND l_shipdate > date '1995-03-15'
      ) t1 JOIN customer c 
     ONc.c_custkey = t1.o_custkey
      WHERE c_mktsegment = 'BUILDING'
  GROUP BY
      l_orderkey,
      o_orderdate,
      o_shippriority
  ORDER BY
      revenue DESC,
      o_orderdate
  limit 10;
  
  --Q4
  SELECT o_orderpriority,
      count(*) AS order_count
  FROM
      (
          SELECT
              *
          FROM
              lineitem
          WHERE l_commitdate < l_receiptdate
      ) t1
      RIGHT semi JOIN orders
     ONt1.l_orderkey = o_orderkey
  WHERE
      o_orderdate >= date '1993-07-01'
      AND o_orderdate < date '1993-07-01' + interval '3' month
  GROUP BY
      o_orderpriority
  ORDER BY
      o_orderpriority;
  
  --Q5
  SELECT n_name,
      sum(l_extendedprice * (1 - l_discount)) AS revenue
  FROM
      customer,
      orders,
      lineitem,
      supplier,
      nation,
      region
  WHERE
      c_custkey = o_custkey
      AND l_orderkey = o_orderkey
      AND l_suppkey = s_suppkey
      AND c_nationkey = s_nationkey
      AND s_nationkey = n_nationkey
      AND n_regionkey = r_regionkey
      AND r_name = 'ASIA'
      AND o_orderdate >= date '1994-01-01'
      AND o_orderdate < date '1994-01-01' + interval '1' year
  GROUP BY
      n_name
  ORDER BY
      revenue DESC;
  
  --Q6
  SELECT sum(l_extendedprice * l_discount) AS revenue
  FROM
      lineitem
  WHERE
      l_shipdate >= date '1994-01-01'
      AND l_shipdate < date '1994-01-01' + interval '1' year
      AND l_discount between .06 - 0.01 AND .06 + 0.01
      AND l_quantity < 24;
  
  --Q7
  SELECT supp_nation,
      cust_nation,
      l_year,
      sum(volume) AS revenue
  FROM
      (
          SELECT
              n1.n_name AS supp_nation,
              n2.n_name AS cust_nation,
              extract(year FROM l_shipdate) AS l_year,
              l_extendedprice * (1 - l_discount) AS volume
          FROM
              supplier,
              lineitem,
              orders,
              customer,
              nation n1,
              nation n2
          WHERE
              s_suppkey = l_suppkey
              AND o_orderkey = l_orderkey
              AND c_custkey = o_custkey
              AND s_nationkey = n1.n_nationkey
              AND c_nationkey = n2.n_nationkey
              AND (
                  (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')
                  or (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
              )
              AND l_shipdate between date '1995-01-01' AND date '1996-12-31'
      ) AS shipping
  GROUP BY
      supp_nation,
      cust_nation,
      l_year
  ORDER BY
      supp_nation,
      cust_nation,
      l_year;
  
  --Q8
  
  SELECT o_year,
      sum(case
          when nation = 'BRAZIL' then volume
          else 0
      end) / sum(volume) AS mkt_share
  FROM
      (
          SELECT
              extract(year FROM o_orderdate) AS o_year,
              l_extendedprice * (1 - l_discount) AS volume,
              n2.n_name AS nation
          FROM
              lineitem,
              orders,
              customer,
              supplier,
              part,
              nation n1,
              nation n2,
              region
          WHERE
              p_partkey = l_partkey
              AND s_suppkey = l_suppkey
              AND l_orderkey = o_orderkey
              AND o_custkey = c_custkey
              AND c_nationkey = n1.n_nationkey
              AND n1.n_regionkey = r_regionkey
              AND r_name = 'AMERICA'
              AND s_nationkey = n2.n_nationkey
              AND o_orderdate between date '1995-01-01' AND date '1996-12-31'
              AND p_type = 'ECONOMY ANODIZED STEEL'
      ) AS all_nations
  GROUP BY
      o_year
  ORDER BY
      o_year;
  
  --Q9
  SELECT nation,
      o_year,
      sum(amount) AS sum_profit
  FROM
      (
          SELECT
              n_name AS nation,
              extract(year FROM o_orderdate) AS o_year,
              l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
          FROM
              lineitem JOIN ordersONo_orderkey = l_orderkey
              join[shuffle] partONp_partkey = l_partkey
              join[shuffle] partsuppONps_partkey = l_partkey
              join[shuffle] supplierONs_suppkey = l_suppkey
              join[broadcast] nationONs_nationkey = n_nationkey
          WHERE
              ps_suppkey = l_suppkey AND 
              p_name like '%green%'
      ) AS profit
  GROUP BY
      nation,
      o_year
  ORDER BY
      nation,
      o_year DESC;
  
  --Q10
  SELECT c_custkey,
      c_name,
      sum(t1.l_extendedprice * (1 - t1.l_discount)) AS revenue,
      c_acctbal,
      n_name,
      c_address,
      c_phone,
      c_comment
  FROM
      customer,
      (
          SELECT o_custkey,l_extendedprice,l_discount FROM lineitem, orders
          WHERE l_orderkey = o_orderkey
          AND o_orderdate >= date '1993-10-01'
          AND o_orderdate < date '1993-10-01' + interval '3' month
          AND l_returnflag = 'R'
      ) t1,
      nation
  WHERE
      c_custkey = t1.o_custkey
      AND c_nationkey = n_nationkey
  GROUP BY
      c_custkey,
      c_name,
      c_acctbal,
      c_phone,
      n_name,
      c_address,
      c_comment
  ORDER BY
      revenue DESC
  limit 20;
  
  --Q11
  SELECT ps_partkey,
      sum(ps_supplycost * ps_availqty) AS value
  FROM
      partsupp,
      (
      SELECT s_suppkey
      FROM supplier, nation
      WHERE s_nationkey = n_nationkey AND n_name = 'GERMANY'
      ) B
  WHERE
      ps_suppkey = B.s_suppkey
  GROUP BY
      ps_partkey having
          sum(ps_supplycost * ps_availqty) > (
              SELECT
                  sum(ps_supplycost * ps_availqty) * 0.000002
              FROM
                  partsupp,
                  (SELECT s_suppkey
                   FROM supplier, nation
                   WHERE s_nationkey = n_nationkey AND n_name = 'GERMANY'
                  ) A
              WHERE
                  ps_suppkey = A.s_suppkey
          )
  ORDER BY
      value DESC;
  
  --Q12
  SELECT l_shipmode,
      sum(case
          when o_orderpriority = '1-URGENT'
              or o_orderpriority = '2-HIGH'
              then 1
          else 0
      end) AS high_line_count,
      sum(case
          when o_orderpriority <> '1-URGENT'
              AND o_orderpriority <> '2-HIGH'
              then 1
          else 0
      end) AS low_line_count
  FROM
      orders,
      lineitem
  WHERE
      o_orderkey = l_orderkey
      AND l_shipmode in ('MAIL', 'SHIP')
      AND l_commitdate < l_receiptdate
      AND l_shipdate < l_commitdate
      AND l_receiptdate >= date '1994-01-01'
      AND l_receiptdate < date '1994-01-01' + interval '1' year
  GROUP BY
      l_shipmode
  ORDER BY
      l_shipmode;
  
  --Q13
  SELECT c_count,
      count(*) AS custdist
  FROM
      (
          SELECT
              c_custkey,
              count(o_orderkey) AS c_count
          FROM
              orders RIGHT outer JOIN customer on
                  c_custkey = o_custkey
                  AND o_comment not like '%special%requests%'
          GROUP BY
              c_custkey
      ) AS c_orders
  GROUP BY
      c_count
  ORDER BY
      custdist DESC,
      c_count DESC;
  
  --Q14
  SELECT 100.00 * sum(case
          when p_type like 'PROMO%'
              then l_extendedprice * (1 - l_discount)
          else 0
      end) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
  FROM
      part,
      lineitem
  WHERE
      l_partkey = p_partkey
      AND l_shipdate >= date '1995-09-01'
      AND l_shipdate < date '1995-09-01' + interval '1' month;
  
  --Q15
  SELECT s_suppkey,
      s_name,
      s_address,
      s_phone,
      total_revenue
  FROM
      supplier,
      revenue0
  WHERE
      s_suppkey = supplier_no
      AND total_revenue = (
          SELECT
              max(total_revenue)
          FROM
              revenue0
      )
  ORDER BY
      s_suppkey;
  
  --Q16
  SELECT p_brAND,
      p_type,
      p_size,
      count(distinct ps_suppkey) AS supplier_cnt
  FROM
      partsupp,
      part
  WHERE
      p_partkey = ps_partkey
      AND p_brAND <> 'BrAND#45'
      AND p_type not like 'MEDIUM POLISHED%'
      AND p_size in (49, 14, 23, 45, 19, 3, 36, 9)
      AND ps_suppkey not in (
          SELECT
              s_suppkey
          FROM
              supplier
          WHERE
              s_comment like '%Customer%Complaints%'
      )
  GROUP BY
      p_brAND,
      p_type,
      p_size
  ORDER BY
      supplier_cnt DESC,
      p_brAND,
      p_type,
      p_size;
  
  --Q17
  SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
  FROM
      lineitem JOIN [broadcast]
      part p1ONp1.p_partkey = l_partkey
  WHERE
      p1.p_brAND = 'BrAND#23'
      AND p1.p_container = 'MED BOX'
      AND l_quantity < (
          SELECT
              0.2 * avg(l_quantity)
          FROM
              lineitem JOIN [broadcast]
              part p2ONp2.p_partkey = l_partkey
          WHERE
              l_partkey = p1.p_partkey
              AND p2.p_brAND = 'BrAND#23'
              AND p2.p_container = 'MED BOX'
      );
  
  --Q18
  SELECT c_name,
      c_custkey,
      t3.o_orderkey,
      t3.o_orderdate,
      t3.o_totalprice,
      sum(t3.l_quantity)
  FROM
  customer join
  (
    SELECT * FROM
    lineitem join
    (
      SELECT * FROM
      orders left semi join
      (
        SELECT
            l_orderkey
        FROM
            lineitem
        GROUP BY
            l_orderkey having sum(l_quantity) > 300
      ) t1
     ONo_orderkey = t1.l_orderkey
    ) t2
   ONt2.o_orderkey = l_orderkey
  ) t3
  on c_custkey = t3.o_custkey
  GROUP BY
      c_name,
      c_custkey,
      t3.o_orderkey,
      t3.o_orderdate,
      t3.o_totalprice
  ORDER BY
      t3.o_totalprice DESC,
      t3.o_orderdate
  limit 100;
  
  --Q19
  SELECT sum(l_extendedprice* (1 - l_discount)) AS revenue
  FROM
      lineitem,
      part
  WHERE
      (
          p_partkey = l_partkey
          AND p_brAND = 'BrAND#12'
          AND p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
          AND l_quantity >= 1 AND l_quantity <= 1 + 10
          AND p_size between 1 AND 5
          AND l_shipmode in ('AIR', 'AIR REG')
          AND l_shipinstruct = 'DELIVER IN PERSON'
      )
      or
      (
          p_partkey = l_partkey
          AND p_brAND = 'BrAND#23'
          AND p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
          AND l_quantity >= 10 AND l_quantity <= 10 + 10
          AND p_size between 1 AND 10
          AND l_shipmode in ('AIR', 'AIR REG')
          AND l_shipinstruct = 'DELIVER IN PERSON'
      )
      or
      (
          p_partkey = l_partkey
          AND p_brAND = 'BrAND#34'
          AND p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
          AND l_quantity >= 20 AND l_quantity <= 20 + 10
          AND p_size between 1 AND 15
          AND l_shipmode in ('AIR', 'AIR REG')
          AND l_shipinstruct = 'DELIVER IN PERSON'
      );
  
  --Q20
  SELECT s_name, s_address FROM
  supplier left semi join
  (
      SELECT * FROM
      (
          SELECT l_partkey,l_suppkey, 0.5 * sum(l_quantity) AS l_q
          FROM lineitem
          WHERE l_shipdate >= date '1994-01-01'
              AND l_shipdate < date '1994-01-01' + interval '1' year
          GROUP BY l_partkey,l_suppkey
      ) t2 join
      (
          SELECT ps_partkey, ps_suppkey, ps_availqty
          FROM partsupp left semi JOIN part
         ONps_partkey = p_partkey AND p_name like 'forest%'
      ) t1
     ONt2.l_partkey = t1.ps_partkey AND t2.l_suppkey = t1.ps_suppkey
      AND t1.ps_availqty > t2.l_q
  ) t3
  on s_suppkey = t3.ps_suppkey
  join nation
  WHERE s_nationkey = n_nationkey
      AND n_name = 'CANADA'
  ORDER BY s_name;
  
  --Q21
  SELECT s_name, count(*) AS numwait
  FROM
    lineitem l2 RIGHT semi join
    (
      SELECT * FROM
      lineitem l3 RIGHT anti join
      (
        SELECT * FROM
        orders JOIN lineitem l1ONl1.l_orderkey = o_orderkey AND o_orderstatus = 'F'
        join
        (
          SELECT * FROM
          supplier JOIN nation
          WHERE s_nationkey = n_nationkey
            AND n_name = 'SAUDI ARABIA'
        ) t1
        WHERE t1.s_suppkey = l1.l_suppkey AND l1.l_receiptdate > l1.l_commitdate
      ) t2
     ONl3.l_orderkey = t2.l_orderkey AND l3.l_suppkey <> t2.l_suppkey  AND l3.l_receiptdate > l3.l_commitdate
    ) t3
   ONl2.l_orderkey = t3.l_orderkey AND l2.l_suppkey <> t3.l_suppkey 
  
  GROUP BY
      t3.s_name
  ORDER BY
      numwait DESC,
      t3.s_name
  limit 100;
  
  --Q22
  with tmp AS (SELECT
                      avg(c_acctbal) AS av
                  FROM
                      customer
                  WHERE
                      c_acctbal > 0.00
                      AND substring(c_phone, 1, 2) in
                          ('13', '31', '23', '29', '30', '18', '17'))
  
  SELECT cntrycode,
      count(*) AS numcust,
      sum(c_acctbal) AS totacctbal
  FROM
      (
      SELECT
              substring(c_phone, 1, 2) AS cntrycode,
              c_acctbal
          FROM
               orders RIGHT anti JOIN customer cON o_custkey = c.c_custkey JOIN tmpONc.c_acctbal > tmp.av
          WHERE
              substring(c_phone, 1, 2) in
                  ('13', '31', '23', '29', '30', '18', '17')
      ) AS custsale
  GROUP BY
      cntrycode
  ORDER BY
      cntrycode;

Test results

The following table describes the test results based on the TPC-H 100 GB test set and TPC-H 500 GB test set.