AnalyticDB:TPC-H testing

Preparations

Before you run the TPC-H benchmark test set in an AnalyticDB for MySQL environment on Alibaba Cloud, complete the following preparations.

1. Create a cluster.

2. Configure a whitelist for the cluster.

3. Create a database account in the cluster.

4. To connect to the cluster over the Internet, apply for a public endpoint.

Create test tables

Create eight data tables and one view in AnalyticDB for MySQL for performance testing.

SET ADB_CONFIG native_engine_opt_enabled = true;
SET ADB_CONFIG rc_ddl_engine_rewrite_xuanwuv2 = true;

Create the test tables

• Create a test database. If you have already created one, skip this step.

  CREATE DATABASE IF NOT EXISTS tpch; 

• NATION table

  CREATE TABLE nation (
    n_nationkey INT NOT NULL COMMENT '',
    n_name VARCHAR NOT NULL COMMENT '',
    n_regionkey INT NOT NULL COMMENT '',
    n_comment VARCHAR COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (n_nationkey)
  ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

• REGION table

  CREATE TABLE region (
    r_regionkey INT NOT NULL COMMENT '',
    r_name VARCHAR NOT NULL COMMENT '',
    r_comment VARCHAR COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (r_regionkey)
  ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

• PART table

  CREATE TABLE part (
    p_partkey INT NOT NULL COMMENT '',
    p_name VARCHAR NOT NULL COMMENT '',
    p_mfgr VARCHAR NOT NULL COMMENT '',
    p_brand VARCHAR(10) NOT NULL COMMENT '',
    p_type VARCHAR NOT NULL COMMENT '',
    p_size INT NOT NULL COMMENT '',
    p_container VARCHAR(10) NOT NULL COMMENT '',
    p_retailprice DOUBLE NOT NULL COMMENT '',
    p_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (p_partkey)
  ) DISTRIBUTED BY HASH (p_partkey) INDEX_ALL = 'Y' compression='lz4hc';

• SUPPLIER table

  CREATE TABLE supplier (
    s_suppkey INT NOT NULL COMMENT '',
    s_name VARCHAR NOT NULL COMMENT '',
    s_address VARCHAR NOT NULL COMMENT '',
    s_nationkey INT NOT NULL COMMENT '',
    s_phone VARCHAR(15) NOT NULL COMMENT '',
    s_acctbal DOUBLE NOT NULL COMMENT '',
    s_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (s_suppkey)
  ) DISTRIBUTED BY HASH (s_suppkey) INDEX_ALL = 'Y' compression='lz4hc';

• PARTSUPP table

  CREATE TABLE partsupp (
    ps_partkey INT NOT NULL COMMENT '',
    ps_suppkey INT NOT NULL COMMENT '',
    ps_availqty INT NOT NULL COMMENT '',
    ps_supplycost DOUBLE NOT NULL COMMENT '',
    ps_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (ps_partkey, ps_suppkey)
  ) DISTRIBUTED BY HASH (ps_partkey) INDEX_ALL = 'Y' compression='lz4hc';

• CUSTOMER table

  CREATE TABLE customer (
    c_custkey INT NOT NULL COMMENT '',
    c_name VARCHAR NOT NULL COMMENT '',
    c_address VARCHAR NOT NULL COMMENT '',
    c_nationkey INT NOT NULL COMMENT '',
    c_phone VARCHAR(15) NOT NULL COMMENT '',
    c_acctbal DOUBLE NOT NULL COMMENT '',
    c_mktsegment VARCHAR(10) NOT NULL COMMENT '',
    c_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (c_custkey)
  ) DISTRIBUTED BY HASH (c_custkey) INDEX_ALL = 'Y' compression='lz4hc';

• ORDERS table

  CREATE TABLE orders (
    o_orderkey BIGINT NOT NULL COMMENT '',
    o_custkey INT NOT NULL COMMENT '',
    o_orderstatus VARCHAR(1) NOT NULL COMMENT '',
    o_totalprice DOUBLE NOT NULL COMMENT '',
    o_orderdate DATE NOT NULL COMMENT '',
    o_orderpriority VARCHAR(15) NOT NULL COMMENT '',
    o_clerk VARCHAR(15) NOT NULL COMMENT '',
    o_shippriority INT NOT NULL COMMENT '',
    o_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    KEY idx_o_custkey (o_custkey),
    KEY idx_o_orderdate (o_orderdate),
    KEY idx_o_orderkey (o_orderkey),
    KEY idx_o_orderstatus (o_orderstatus)
  ) DISTRIBUTED BY HASH (o_orderkey) PARTITION BY VALUE(date_format(o_orderdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';

• LINEITEM table

  CREATE TABLE lineitem (
    l_orderkey BIGINT NOT NULL COMMENT '',
    l_partkey INT NOT NULL COMMENT '',
    l_suppkey INT NOT NULL COMMENT '',
    l_linenumber INT NOT NULL COMMENT '',
    l_quantity DOUBLE NOT NULL COMMENT '',
    l_extendedprice DOUBLE NOT NULL COMMENT '',
    l_discount DOUBLE NOT NULL COMMENT '',
    l_tax DOUBLE NOT NULL COMMENT '',
    l_returnflag VARCHAR(1) NOT NULL COMMENT '',
    l_linestatus VARCHAR(1) NOT NULL COMMENT '',
    l_shipdate DATE NOT NULL COMMENT '',
    l_commitdate DATE NOT NULL COMMENT '',
    l_receiptdate DATE NOT NULL COMMENT '',
    l_shipinstruct VARCHAR(25) NOT NULL COMMENT '',
    l_shipmode VARCHAR(10) NOT NULL COMMENT '',
    l_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    KEY idx_l_orderkey (l_orderkey),
    KEY idx_l_partkey (l_partkey),
    KEY idx_l_receiptdate (l_receiptdate),
    KEY idx_l_returnflag (l_returnflag),
    KEY idx_l_shipdate (l_shipdate),
    KEY idx_l_shipinstruct (l_shipinstruct),
    KEY idx_l_shipmode (l_shipmode),
    KEY idx_l_suppkey (l_suppkey),
    clustered key clustered_index(`l_orderkey` ASC)
  ) DISTRIBUTED BY HASH  (l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL='N'COMPRESSION='lz4hc';

• REVENUE0 view

  CREATE VIEW `revenue0` AS
  SELECT
    `l_suppkey` supplier_no,
    `sum`(
      (
        `l_extendedprice` * (1 - `l_discount`)
      )
    ) total_revenue
  FROM
    lineitem
  WHERE
    (
      (`l_shipdate` >= DATE '1996-01-01')
      AND (
        `l_shipdate` < (
          DATE '1996-01-01' + INTERVAL '3' MONTH
        )
      )
    )
  GROUP BY
    `l_suppkey`;

Import data

Generate the TPC-H test data and import the 1000 GB test dataset into AnalyticDB for MySQL.

The following table lists the number of rows in each table of the TPC-H test dataset.

Appendix: TPC-H test set

The following 22 SQL queries are executed during the performance test.

• SQL1

  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;

• SQL2

  SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
  	, s_address, s_phone, s_comment
  FROM part, supplier, partsupp, 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'
  	AND ps_supplycost = (
  		SELECT min(ps_supplycost)
  		FROM partsupp, 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'
  	)
  ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
  LIMIT 100;

• SQL3

  SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) AS revenue
  	, o_orderdate, o_shippriority
  FROM customer, orders, lineitem
  WHERE c_mktsegment = 'BUILDING'
  	AND c_custkey = o_custkey
  	AND l_orderkey = o_orderkey
  	AND o_orderdate < DATE '1995-03-15'
  	AND l_shipdate > DATE '1995-03-15'
  GROUP BY l_orderkey, o_orderdate, o_shippriority
  ORDER BY revenue DESC, o_orderdate
  LIMIT 10;

• SQL4

  SELECT o_orderpriority, count(*) AS order_count
  FROM orders
  WHERE o_orderdate >= DATE '1993-07-01'
  	AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
  	AND EXISTS (
  		SELECT *
  		FROM lineitem
  		WHERE l_orderkey = o_orderkey
  			AND l_commitdate < l_receiptdate
  	)
  GROUP BY o_orderpriority
  ORDER BY o_orderpriority;

• SQL5

  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;

• SQL6

  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 0.06 - 0.01 AND 0.06 + 0.01
  	AND l_quantity < 24;

• SQL7

  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'
  ) shipping
  GROUP BY supp_nation, cust_nation, l_year
  ORDER BY supp_nation, cust_nation, l_year;

• SQL8

  SELECT o_year
  	, sum(CASE
  		WHEN nation = 'PERU' 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 part, supplier, lineitem, orders, customer, 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 = 'PROMO BURNISHED TIN'
  ) all_nations
  GROUP BY o_year
  ORDER BY o_year;

• SQL9

  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 part, supplier, lineitem, partsupp, orders, nation
  	WHERE s_suppkey = l_suppkey
  		AND ps_suppkey = l_suppkey
  		AND ps_partkey = l_partkey
  		AND p_partkey = l_partkey
  		AND o_orderkey = l_orderkey
  		AND s_nationkey = n_nationkey
  		AND p_name LIKE '%green%'
  ) profit
  GROUP BY nation, o_year
  ORDER BY nation, o_year DESC;

• SQL10

  SELECT c_custkey, c_name
  	, sum(l_extendedprice * (1 - l_discount)) AS revenue
  	, c_acctbal, n_name, c_address, c_phone, c_comment
  FROM customer, orders, lineitem, nation
  WHERE c_custkey = o_custkey
  	AND l_orderkey = o_orderkey
  	AND o_orderdate >= DATE '1993-09-01'
  	AND o_orderdate < DATE '1993-09-01' + INTERVAL '3' MONTH
  	AND l_returnflag = 'R'
  	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

• SQL11

  SELECT ps_partkey, sum(ps_supplycost * ps_availqty) AS value
  FROM partsupp, supplier, nation
  WHERE ps_suppkey = s_suppkey
  	AND s_nationkey = n_nationkey
  	AND n_name = 'GERMANY'
  GROUP BY ps_partkey
  HAVING sum(ps_supplycost * ps_availqty) > (
  	SELECT sum(ps_supplycost * ps_availqty) * 0.0000001000
  	FROM partsupp, supplier, nation
  	WHERE ps_suppkey = s_suppkey
  		AND s_nationkey = n_nationkey
  		AND n_name = 'GERMANY'
  )
  ORDER BY value DESC;

• SQL12

  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 ('TRUCK', 'AIR')
  	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;

• SQL13

  SELECT c_count, count(*) AS custdist
  FROM (
  	SELECT c_custkey, count(o_orderkey) AS c_count
  	FROM customer
  		LEFT JOIN orders
  		ON c_custkey = o_custkey
  			AND o_comment NOT LIKE '%special%requests%'
  	GROUP BY c_custkey
  ) AS c_orders (c_custkey, c_count)
  GROUP BY c_count
  ORDER BY custdist DESC, c_count DESC;

• SQL14

  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 lineitem, part
  WHERE l_partkey = p_partkey
  	AND l_shipdate >= DATE '1995-09-01'
  	AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH;

• SQL15

  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;

• SQL16

  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;

• SQL17

  SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
  FROM lineitem, part
  WHERE p_partkey = l_partkey
  	AND p_brand = 'Brand#23'
  	AND p_container = 'SM CAN'
  	AND l_quantity < (
  		SELECT 0.2 * avg(l_quantity)
  		FROM lineitem
  		WHERE l_partkey = p_partkey
  	);

• SQL18

  SELECT c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
  	, sum(l_quantity)
  FROM customer, orders, lineitem
  WHERE o_orderkey IN (
  		SELECT l_orderkey
  		FROM lineitem
  		GROUP BY l_orderkey
  		HAVING sum(l_quantity) > 300
  	)
  	AND c_custkey = o_custkey
  	AND o_orderkey = l_orderkey
  GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
  ORDER BY o_totalprice DESC, o_orderdate
  LIMIT 100;

• SQL19

  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#42'
  		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#45'
  		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');

• SQL20

  SELECT s_name, s_address
  FROM supplier, nation
  WHERE s_suppkey IN (
  		SELECT ps_suppkey
  		FROM partsupp
  		WHERE ps_partkey IN (
  				SELECT p_partkey
  				FROM part
  				WHERE p_name LIKE 'forest%'
  			)
  			AND ps_availqty > (
  				SELECT 0.5 * sum(l_quantity)
  				FROM lineitem
  				WHERE l_partkey = ps_partkey
  					AND l_suppkey = ps_suppkey
  					AND l_shipdate >= DATE '1994-01-01'
  					AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
  			)
  	)
  	AND s_nationkey = n_nationkey
  	AND n_name = 'CANADA'
  ORDER BY s_name;

• SQL21

  SELECT s_name, count(*) AS numwait
  FROM supplier, lineitem l1, orders, nation
  WHERE s_suppkey = l1.l_suppkey
  	AND o_orderkey = l1.l_orderkey
  	AND o_orderstatus = 'F'
  	AND l1.l_receiptdate > l1.l_commitdate
  	AND EXISTS (
  		SELECT *
  		FROM lineitem l2
  		WHERE l2.l_orderkey = l1.l_orderkey
  			AND l2.l_suppkey <> l1.l_suppkey
  	)
  	AND NOT EXISTS (
  		SELECT *
  		FROM lineitem l3
  		WHERE l3.l_orderkey = l1.l_orderkey
  			AND l3.l_suppkey <> l1.l_suppkey
  			AND l3.l_receiptdate > l3.l_commitdate
  	)
  	AND s_nationkey = n_nationkey
  	AND n_name = 'SAUDI ARABIA'
  GROUP BY s_name
  ORDER BY numwait DESC, s_name
  LIMIT 100;

• SQL22

  SELECT cntrycode, count(*) AS numcust, sum(c_acctbal) AS totacctbal
  FROM (
  	SELECT substring(c_phone FROM 1 FOR 2) AS cntrycode, c_acctbal
  	FROM customer
  	WHERE substring(c_phone FROM 1 FOR 2) IN (
  			'13',
  			'31',
  			'23',
  			'29',
  			'30',
  			'18',
  			'17'
  		)
  		AND c_acctbal > (
  			SELECT avg(c_acctbal)
  			FROM customer
  			WHERE c_acctbal > 0.00
  				AND substring(c_phone FROM 1 FOR 2) IN (
  					'13',
  					'31',
  					'23',
  					'29',
  					'30',
  					'18',
  					'17'
  				)
  		)
  		AND NOT EXISTS (
  			SELECT *
  			FROM orders
  			WHERE o_custkey = c_custkey
  		)
  ) custsale
  GROUP BY cntrycode
  ORDER BY cntrycode;