AnalyticDB：Pengujian TPC-H

Persiapan

Sebelum menjalankan set pengujian benchmark TPC-H di lingkungan AnalyticDB for MySQL di Alibaba Cloud, selesaikan persiapan berikut.

1. Buat kluster.

2. Konfigurasikan daftar putih untuk kluster tersebut.

3. Buat akun database di dalam kluster.

4. Untuk menghubungkan ke kluster melalui Internet, ajukan titik akhir publik.

Buat tabel pengujian

Buat delapan tabel data dan satu view di AnalyticDB for MySQL untuk pengujian performa.

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

Buat tabel pengujian

• Buat database pengujian. Jika Anda telah membuatnya, lewati langkah ini.

  CREATE DATABASE IF NOT EXISTS tpch; 

• Tabel NATION

  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';

• Tabel REGION

  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';

• Tabel PART

  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';

• Tabel SUPPLIER

  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';

• Tabel PARTSUPP

  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';

• Tabel CUSTOMER

  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';

• Tabel ORDERS

  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';

• Tabel LINEITEM

  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';

• View REVENUE0

  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`;

Impor data

Hasilkan data uji TPC-H dan impor set data uji 1000 GB ke AnalyticDB for MySQL.

Tabel berikut mencantumkan jumlah baris di setiap tabel set data uji TPC-H.

Lampiran: Set pengujian TPC-H

22 kueri SQL berikut dijalankan selama pengujian performa.

• 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;