LoginSignup
4
4
@hmatsu47

東京リージョンにやってきた MySQL HeatWave on AWS を試す (2) サンプルクエリ実行編

Last updated at Posted at 2023-07-23

こちらの記事の続きです。

前回の記事で AWS 上に作成した MySQL DB・HeatWave Cluster にサンプルデータベースを構築し、分析クエリを流して HeatWave なしの場合と比較してみます。

2023/7/23 時点の情報です。
今後、サンプルデータの提供内容や有無、利用手順、実行結果などが変わる可能性があります。

試す内容

こちらのチュートリアルに沿って、サンプルデータベースを構築して分析クエリの処理時間を比較します。

あらかじめ EC2 に MySQL Client と MySQL Shell をインストールしておきます。

サンプルデータベース構築

対象は MySQL HeatWave on AWS ですが、基本的な手順は OCI 上の MySQL HeatWave と同じです。

サンプルデータをダウンロード・展開

まず EC2 でサンプルデータをダウンロードし、展開します。

$  wget https://objectstorage.ap-osaka-1.oraclecloud.com/p/seAq8Kgd4TyUqlv5M5qObMJwvsluhCPyOuHOn1L_t4HQYUle2DV-KdFeK44MS7yQ/n/idazzjlcjqzj/b/workshop/o/heatwave_workshop.zip
--2023-07-22 14:01:10--  https://objectstorage.ap-osaka-1.oraclecloud.com/p/seAq8Kgd4TyUqlv5M5qObMJwvsluhCPyOuHOn1L_t4HQYUle2DV-KdFeK44MS7yQ/n/idazzjlcjqzj/b/workshop/o/heatwave_workshop.zip
Resolving objectstorage.ap-osaka-1.oraclecloud.com (objectstorage.ap-osaka-1.oraclecloud.com)... 134.70.112.3
Connecting to objectstorage.ap-osaka-1.oraclecloud.com (objectstorage.ap-osaka-1.oraclecloud.com)|134.70.112.3|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 348382849 (332M) [application/x-zip-compressed]
Saving to: ‘heatwave_workshop.zip’

100%[======================================================================================================================>] 348,382,849 59.1MB/s   in 5.4s   

2023-07-22 14:01:15 (61.3 MB/s) - ‘heatwave_workshop.zip’ saved [348382849/348382849]

heatwave_workshop.zipを展開します。

$  unzip heatwave_workshop.zip
Archive:  heatwave_workshop.zip
   creating: tpch_dump/
  inflating: tpch_dump/@.json        
  inflating: tpch_dump/tpch.json             
（中略）
  inflating: tpch_offload.sql        
  inflating: tpch_queries_mysql.sql  
  inflating: tpch_queries_rapid.sql

MySQL DB へデータロード

MySQL Shell のロードダンプユーティリティを使って、サンプルデータを MySQL DB へロードします。

$ mysqlsh -u 【ユーザー名】 -h 【hostname の URL】 -p
Please provide the password for '【ユーザー名】@【hostname の URL】': 【パスワードを入力】
Save password for '【ユーザー名】@【hostname の URL】'? [Y]es/[N]o/Ne[v]er (default No): N
MySQL Shell 8.0.34

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
（中略）
No default schema selected; type \use <schema> to set one.
JS > util.loadDump("tpch_dump", {dryRun: false, resetProgress:true, ignoreVersion:true})
Loading DDL and Data from 'tpch_dump' using 4 threads.
Opening dump...
NOTE: Dump format has version 1.0.2 and was created by an older version of MySQL Shell. If you experience problems loading it, please recreate the dump using the current version of MySQL Shell and try again.
（中略）
0 warnings were reported during the load.

テーブル確認

完了したら MySQL Shell を終了して、MySQL クライアントで MySQL DB に接続します。

JS > \quit
Bye!
$ mysql -u 【ユーザー名】 -h 【hostname の URL】 -p
Enter password: 【パスワードを入力】
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 81
Server version: 8.0.33-u2-cloud MySQL Enterprise - Cloud
（中略）
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_autopilot    |
| performance_schema |
| sys                |
| tpch               |
+--------------------+
6 rows in set (0.01 sec)

mysql> USE tpch;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_tpch |
+----------------+
| customer       |
| lineitem       |
| nation         |
| orders         |
| part           |
| partsupp       |
| region         |
| supplier       |
+----------------+
8 rows in set (0.01 sec)

テストデータのデータベースtpchと各テーブルがロードされています。

MySQL DB（HeatWave を使わない状態）でサンプルクエリ実行

HeatWave へデータをロードする前に、MySQL DB でサンプルクエリを流してみます。

mysql>  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;
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty     | sum_base_price  | sum_disc_price    | sum_charge          | avg_qty   | avg_price    | avg_disc | count_order |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| A            | F            | 37734107.00 |  56586554400.73 |  53758257134.8700 |  55909065222.827692 | 25.522006 | 38273.129735 | 0.049985 |     1478493 |
| N            | F            |   991417.00 |   1487504710.38 |   1413082168.0541 |   1469649223.194375 | 25.516472 | 38284.467761 | 0.050093 |       38854 |
| N            | O            | 74476040.00 | 111701729697.74 | 106118230307.6056 | 110367043872.497010 | 25.502227 | 38249.117989 | 0.049997 |     2920374 |
| R            | F            | 37719753.00 |  56568041380.90 |  53741292684.6040 |  55889619119.831932 | 25.505794 | 38250.854626 | 0.050009 |     1478870 |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
4 rows in set (12.79 sec)

5 回実行してみたところ多少ばらつきがあったので、中間の 3 回分を平均したところ 13.07 秒 でした。

HeatWave で試す

MySQL DB のテーブルに登録されたデータはそのままでは HeatWave にロードされないため、最初だけセカンダリエンジンの指定とデータロードが必要です。

セカンダリエンジン指定

各テーブルのセカンダリエンジンとして HeatWave を指定します。

mysql>  ALTER TABLE tpch.customer SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE tpch.lineitem SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE tpch.nation SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE tpch.orders SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE tpch.part SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE tpch.partsupp SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE tpch.region SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE tpch.supplier SECONDARY_ENGINE=RAPID;
Query OK, 0 rows affected (0.01 sec)

HeatWave へデータロード

そして HeatWave へデータロードします。

mysql>  ALTER TABLE tpch.customer SECONDARY_LOAD;
Query OK, 0 rows affected (2.32 sec)

mysql>  ALTER TABLE tpch.lineitem SECONDARY_LOAD;
Query OK, 0 rows affected (1 min 13.45 sec)

mysql>  ALTER TABLE tpch.nation SECONDARY_LOAD;
Query OK, 0 rows affected (0.13 sec)

mysql>  ALTER TABLE tpch.orders SECONDARY_LOAD;
Query OK, 0 rows affected (13.76 sec)

mysql>  ALTER TABLE tpch.part SECONDARY_LOAD;
Query OK, 0 rows affected (2.46 sec)

mysql>  ALTER TABLE tpch.partsupp SECONDARY_LOAD;
Query OK, 0 rows affected (10.36 sec)

mysql>  ALTER TABLE tpch.region SECONDARY_LOAD;
Query OK, 0 rows affected (0.15 sec)

mysql>  ALTER TABLE tpch.supplier SECONDARY_LOAD;
Query OK, 0 rows affected (0.35 sec)

チュートリアルにも書かれていますが、MySQL Shell の Auto Parallel Load ユーティリティを使ってロードすることもできます。

 CALL sys.heatwave_load(JSON_ARRAY("tpch"),NULL);

HeatWave でサンプルクエリ実行

先ほどと同じクエリで試してみます。

mysql> 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;
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty     | sum_base_price  | sum_disc_price    | sum_charge          | avg_qty   | avg_price    | avg_disc | count_order |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| A            | F            | 37734107.00 |  56586554400.73 |  53758257134.8700 |  55909065222.827692 | 25.522005 | 38273.129734 | 0.049985 |     1478493 |
| N            | F            |   991417.00 |   1487504710.38 |   1413082168.0541 |   1469649223.194375 | 25.516471 | 38284.467760 | 0.050093 |       38854 |
| N            | O            | 74476040.00 | 111701729697.74 | 106118230307.6056 | 110367043872.497010 | 25.502226 | 38249.117988 | 0.049996 |     2920374 |
| R            | F            | 37719753.00 |  56568041380.90 |  53741292684.6040 |  55889619119.831932 | 25.505793 | 38250.854626 | 0.050009 |     1478870 |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
4 rows in set (0.87 sec)

5 回実行してみて中間の 3 回分を平均したところ 0.75 秒 でした。

チュートリアルに書かれている約 65 倍とまではいきませんでしたが、約 17 倍高速化されました。

チュートリアルでは今回の環境よりも大きなシェイプを選択しており、HeatWave ノードも 2 つ使用しているようです。

その他

EXPLAIN比較

MySQL DB のみ

mysql> EXPLAIN FORMAT=TREE
    ->  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\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: lineitem.L_RETURNFLAG, lineitem.L_LINESTATUS
    -> Table scan on <temporary>
        -> Aggregate using temporary table
            -> Filter: (lineitem.L_SHIPDATE <= <cache>((DATE'1998-12-01' - interval '90' day)))  (cost=611234 rows=1.99e+6)
                -> Table scan on lineitem  (cost=611234 rows=5.97e+6)

1 row in set (0.01 sec)

HeatWave あり

mysql> EXPLAIN FORMAT=TREE
    ->  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\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: lineitem.L_RETURNFLAG, lineitem.L_LINESTATUS  (cost=1.94e+9..1.94e+9 rows=0)
    -> Group aggregate: sum(lineitem.L_QUANTITY), sum(lineitem.L_EXTENDEDPRICE), sum((lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT))), sum(((lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT)) * (1 + lineitem.L_TAX))), avg(lineitem.L_QUANTITY), avg(lineitem.L_EXTENDEDPRICE), avg(lineitem.L_DISCOUNT), count(0)  (cost=1.94e+9..1.94e+9 rows=0)
        -> Filter: (lineitem.L_SHIPDATE <= (DATE'1998-12-01' - interval '90' day))  (cost=1.06e+9..1.06e+9 rows=0)
            -> Table scan on lineitem in secondary engine RAPID  (cost=0..0 rows=0)

1 row in set, 1 warning (0.03 sec)

各コンソール画面

Workspaces

image.png

Performance

image.png

MySQL Configurations (Details)

image.png

2023/7/25 追記：

続きを書きました。

4
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
4