AnalyticDB：Uji TPC-H

Persiapan

Sebelum menjalankan dan menguji kueri TPC-H di AnalyticDB for MySQL, selesaikan langkah-langkah berikut:

1. Buat kluster AnalyticDB for MySQL.

2. Konfigurasikan Daftar putih alamat IP untuk kluster tersebut.

3. Buat akun basis data untuk kluster tersebut.

4. Untuk terhubung ke kluster melalui Internet, ajukan Titik akhir publik.

Buat tabel uji

Anda harus membuat delapan tabel dan satu Tampilan 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 uji

• 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)
  ) DISTRIBUTED BY HASH (l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';

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

Bangun set data uji TPC-H dan impor 1.000 GB data uji TPC-H ke AnalyticDB for MySQL.

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

Lampiran: Kueri uji TPC-H

Pengujian performa mencakup 22 kueri SQL berikut.

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