体验云上处理速度超快的MySQL HeatWave

甲骨文云技术 2021-02-03

3484

本文由Technology Hub同事Xin Zhang翻译。原文地址为：

https://qiita.com/sugimount/items/9ecf8800cd59ba2a635c

介绍

MySQL数据库服务(MDS)刚刚发布了HeatWave！HeatWave可以让您超级加速MySQL对工作负载的加载，提高大型数据分析和耗时SQL查询的速度。只要在控制台界面中打开MySQL数据库服务的HeatWave功能复选框，就可以使用HeatWave，所以您不需要对现有的应用程序进行任何更改。您不需要对应用端做任何改动，只要启用HeatWave就可以加快对工作负载的加载。

在本文中，我将向您展示如何创建一个新的MySQL数据库服务和HeatWave。

服务限额(Service Limit)

由于服务限额(Service Limit)，HeatWave在默认情况下是不能使用的状态。首先，需要申请放宽服务限额。

从下拉菜单选择MySQL后，以下两个项目与HeatWave有关。服务限制的值为0，所以您可以通过页面顶部请求服务限制(request a service limit)链接请求放宽限额。

MySQL Analytics VM.Standard.E3 Nodes Count : HeatWave Node
MySQL Database for Analytics VM.Standard.E3 Nodes Count : MySQL本身

(译者注：2021年1月下旬，改名为MySQL Database for HeatWave VM.Standard.E3 Nodes Count)

需要申请以下两种类型。

MySQL Analytics VM.Standard.E3 Nodes Count，需要至少2个节点(Node)。
MySQL Database for Analytics VM.Standard.E3 Nodes Count，MySQL本身，需要至少1个。

我们把理由写成这样（日文应该也可以）。（译者注：这里仅是将日文翻译成中文，推荐大家尽量使用英文。）

HeatWave的每个Node可以存储400GB左右的数据，所以根据您要卸载(Offload)到HeatWave的数据量来调整您申请的Node数量。

我想验证一下HeatWave的操作，所以申请放宽限制。

MySQL Analytics VM.Standard.E3 Nodes Count : 3 Node

MySQL Database for Analytics VM.Standard.E3 Nodes Count : 1 Node

审批通过后，服务限额(Service Limit)将这样增加。

创建MySQL Database Service

服务限制被放宽后，我们将创建MySQL数据库服务+HeatWave，从MySQL中选择"数据库系统(DB Systems)"。

点击创建"MySQL数据库系统(Create MySQL DB System)"。

输入"名称(Name)"和"说明(Description)"后，点击 "更改配置(Change Shape)"。

选择可以使用HeatWave的配置(Shape)。目前，E3 Shape中只有MySQL.Analytics.VM.Standard.E3可以启用HeatWave。

输入"数据存储大小(Data Storage Size)"。

输入各个项目值，点击"下一步(Next)"。

点击"创建(Create)"创建MySQL数据库服务。

状态变成"正在创建(Creating)"。在我的环境中，状态变成"活动(Active)"用了不到10分钟。

状态变成"活动(Active)"了。让我们把HeatWave添加到MySQL数据库服务中。从左下角的菜单中选择"HeatWave"。

点击"添加 HeatWave 集群(Add HeatWave Cluster)"。

确保配置(Shape)是MySQL.Analytics.VM.Standard.E3 ，输入所需的"节点计数(Node Count)"，然后点击"添加 HeatWave 集群(Add HeatWave Cluster)"。据悉，每个HeatWave Node可以存储约400GB的数据。

因为HeatWave被设为3个节点，所以可以看到HeatWave Nodes变成了3行"正在创建(Creating)"。

HeatWave集群变成"活动(Active)"用了不到10分钟。

创建1亿行测试数据

现在我们已经将HeatWave功能添加到MySQL数据库服务中。我们将在OCI计算实例上安装MySQL客户端并进行连接，以便创建1亿行测试数据。

激活MySQL安装库。

sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

安装MySQL客户端。

sudo yum install mysql-community-client

依赖关系如下。

========================================================================================================================
 Package                                   Arch              Version                 Repository                    Size
========================================================================================================================
Installing:
 mysql-community-client                    x86_64            8.0.22-1.el7            mysql80-community             48 M
 mysql-community-libs                      x86_64            8.0.22-1.el7            mysql80-community            4.6 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
 mysql-community-libs-compat               x86_64            8.0.22-1.el7            mysql80-community            1.2 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
Installing for dependencies:
 mysql-community-client-plugins            x86_64            8.0.22-1.el7            mysql80-community            235 k
 mysql-community-common                    x86_64            8.0.22-1.el7            mysql80-community            616 k


Transaction Summary
========================================================================================================================

检查MySQL数据库服务的"端点(Endpoints)"。

从MySQL客户端连接，用户名和密码是您创建MySQL数据库服务时指定的。

mysql --host heatwave.mysqlsubnet01.vcn.oraclevcn.com -u admin -p

连接成功。

[opc@mysql-client01 ~]$ mysql --host heatwave.mysqlsubnet01.vcn.oraclevcn.com -u admin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 259
Server version: 8.0.22-u4-cloud MySQL Enterprise - Cloud


Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>

检查数据库列表。

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

mysql>

用一个适当的名字创建一个数据库。

CREATE DATABASE sugi01;

检查。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sugi01             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


mysql>

切换到您创建的sugi01数据库。

mysql> USE sugi01;
Database changed
mysql>

没有任何表(Table)。

mysql> SHOW TABLES;
Empty set (0.00 sec)


mysql>

需要sample表，创建sample表并创建10行数据。

CREATE TABLE sample(
id INT(11) NOT NULL AUTO_INCREMENT,
value INT(5) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);


INSERT INTO sample(value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

创建一个account表来存储1亿行的测试数据。

CREATE TABLE account(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
  subid INT(11) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

在创建的account表中创建1亿行数据。

使用包含10行数据的sample表，进行交叉连接(Cross Join)，并将测试数据插入到account表。

在我的环境中，花了大约10分钟17秒。

INSERT INTO account(name, subid)
SELECT
CONCAT('NAME' , @rownum := @rownum + 1),@rownum
FROM
sample AS s1,
sample AS s2,
sample AS s3,
sample AS s4,
sample AS s5,
sample AS s6,
sample AS s7,
sample AS s8,
  (SELECT @rownum := 0) AS v;

数据存储正确。

mysql> SELECT * FROM account LIMIT 10;
+----+--------+-------+---------------------+---------------------+
| id | name   | subid | created_at          | updated_at          |
+----+--------+-------+---------------------+---------------------+
|  1 | NAME1  |     1 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  2 | NAME2  |     2 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  3 | NAME3  |     3 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  4 | NAME4  |     4 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  5 | NAME5  |     5 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  6 | NAME6  |     6 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  7 | NAME7  |     7 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  8 | NAME8  |     8 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
|  9 | NAME9  |     9 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
| 10 | NAME10 |    10 | 2020-12-26 01:53:45 | 2020-12-26 01:53:45 |
+----+--------+-------+---------------------+---------------------+
10 rows in set (0.00 sec)


mysql>

1亿行测试数据存储正确。

mysql> SELECT COUNT(id) FROM account;
+-----------+
| COUNT(id) |
+-----------+
| 100000000 |
+-----------+
1 row in set (0.01 sec)


mysql>

我们执行一个查询，会引起全表扫描，由于是全表扫描1亿行数据，所以会花费不少时间。（这里先不透露准确的秒数）。

mysql> SELECT * FROM account WHERE subid BETWEEN 100 and 110;
+-----+---------+-------+---------------------+---------------------+
| id  | name    | subid | created_at          | updated_at          |
+-----+---------+-------+---------------------+---------------------+
| 100 | NAME100 |   100 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 101 | NAME101 |   101 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 102 | NAME102 |   102 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 103 | NAME103 |   103 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 104 | NAME104 |   104 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 105 | NAME105 |   105 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 106 | NAME106 |   106 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 107 | NAME107 |   107 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 108 | NAME108 |   108 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 109 | NAME109 |   109 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 110 | NAME110 |   110 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
+-----+---------+-------+---------------------+---------------------+
11 rows in set (XX.XX sec)


mysql>

我们来看看此时的执行计划。类型(type)为ALL，也就是全表扫描。

mysql> EXPLAIN SELECT * FROM account WHERE subid BETWEEN 100 and 110;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | account | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97926422 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql>

删除表。（此时不执行，最后如果需要删除时执行。）

DROP TABLE account;

HeatWave 同步进度率显示准备

我们要将1亿行数据同步到HeatWave中，要自动获取同步进度率，需要创建一个简单的Bash脚本。

如果用SQL执行，可以看到与HeatWave的数据同步进度。

SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'rapid_load_progress';

下面是一个运行的例子。现在，什么都没有同步，所以是0%。当同步完成后，该显示将为100%。

mysql> SELECT VARIABLE_VALUE
    -> FROM performance_schema.global_status
    -> WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 0.000000       |
+----------------+
1 row in set (0.00 sec)


mysql>

您也可以使用SQL来生成已经同步的表的列表。

SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;

下面是一个执行的例子。由于现在没有任何同步，所以显示为空(Empty)。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
Empty set (0.00 sec)


mysql>

一旦退出了MySQL客户端，我们将创建一个脚本，定期运行输出与HeatWave的数据同步进度。

mkdir ~/heatwavetest

创建一个简单的Bash脚本，在这个脚本中，您为您的环境指定mysql命令连接和密码。

cat <<'EOF' > ~/heatwavetest/GetHeatWaveProgress.sh
#!/bin/bash
while true; do
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~";
  date;
  mysql --host heatwave.mysqlsubnet01.vcn.oraclevcn.com -u admin -p"your password" --execute="SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';";
echo "          ";
  sleep 10s;
done
EOF

授予执行权限。

chmod +x ~/heatwavetest/GetHeatWaveProgress.sh

运行测试，验证progress.txt显示进度率是否有问题。

~/heatwavetest/GetHeatWaveProgress.sh >> ~/heatwavetest/progress.txt

我们会在后台运行它。

nohup ~/heatwavetest/GetHeatWaveProgress.sh >> ~/heatwavetest/progress.txt &

检查。

[opc@mysql-client01 ~]$ ps aux | grep heat
opc      12272  0.0  0.0 113256  1308 pts/0    S    12:21   0:00 bin/bash home/opc/heatwavetest/GetHeatWaveProgress.sh
opc      12277  0.0  0.0 112780   684 pts/0    S+   12:22   0:00 grep --color=auto heat
[opc@mysql-client01 ~]$

HeatWave 同步开始

在开始同步到HeatWave之前，请检查包含1亿行数据的account表的使用容量。

容量检查SQL。

SELECT
    table_name, engine, table_rows AS tbl_rows,
    avg_row_length AS rlen,  
floor((data_length+index_length)/1024/1024) AS ALL_MB,  #总容量
floor((data_length)/1024/1024) AS DATA_MB,  #数据容量
floor((index_length)/1024/1024) AS Index_MB   #索引容量
FROM
    information_schema.tables  
WHERE
    table_schema=database()  
ORDER BY
    (data_length+index_length) DESC;

执行示例。

我们使用的是4893MB。我们要将一张5GB左右的表同步到HeatWave。

+------------+--------+----------+------+--------+---------+----------+
| TABLE_NAME | ENGINE | tbl_rows | rlen | ALL_MB | DATA_MB | Index_MB |
+------------+--------+----------+------+--------+---------+----------+
| account    | InnoDB | 99785933 |   51 |   4893 |    4893 |        0 |
| sample     | InnoDB |       10 | 1638 |      0 |       0 |        0 |
+------------+--------+----------+------+--------+---------+----------+
2 rows in set (0.00 sec)

SECONDARY_ENGINE里，定义了使用HeatWave(RAPID) 。我猜想，大概是为了将来出了RAPID以外的SECONDARY_ENGINE时，能够切换使用的引擎。在这个时候，同步还没有开始。

ALTER TABLE account SECONDARY_ENGINE = RAPID;

下面的命令将实际开始同步。(在同步完成之前，您不会收到提示)

ALTER TABLE account SECONDARY_LOAD;

执行示例。1亿行数据，约5GB的数据，同步耗时约1分38秒。

mysql> ALTER TABLE account SECONDARY_LOAD;
Query OK, 0 rows affected (1 min 38.30 sec)


mysql>

这是检查数据同步进度的命令，约为62%。

mysql> SELECT VARIABLE_VALUE
    -> FROM performance_schema.global_status
    -> WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 62.118067      |
+----------------+
1 row in set (0.00 sec)


mysql>

您可以看到每个表的数据同步状态。同步中时，那就是"LOADING_RPDGSTABSTATE"。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
+----------------+-----------------------+
| NAME           | LOAD_STATUS           |
+----------------+-----------------------+
| sugi01.account | LOADING_RPDGSTABSTATE |
+----------------+-----------------------+
1 row in set (0.00 sec)


mysql>

同步完成后，进度百分比为100%。

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 100.000000     |
+----------------+
1 row in set (0.00 sec)


mysql>

以表为单位，状态为"AVAIL_RPDGSTABSTATE"。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
+----------------+---------------------+
| NAME           | LOAD_STATUS         |
+----------------+---------------------+
| sugi01.account | AVAIL_RPDGSTABSTATE |
+----------------+---------------------+
1 row in set (0.00 sec)


mysql>

HeatWave 执行测试

现在同步已经完成，我们可以测试HeatWave。首先，我们先来看看EXPLAIN中的执行计划。我们使用的是全表扫描SQL查询。

EXPLAIN SELECT * FROM account WHERE subid BETWEEN 100 and 110;

这里举个例子：类型是ALL，所以发生了全表扫描，但在Extra部分，可以看到显示Using secondary engine RAPID ，表示将卸载(Offload)到HeatWave。此外，您还可以通过Hint子句控制是否进行卸载(Offload)。

mysql> EXPLAIN SELECT * FROM account WHERE subid BETWEEN 100 and 110;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                     |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
|  1 | SIMPLE      | account | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97926422 |    11.11 | Using where; Using secondary engine RAPID |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)


mysql>

现在，让我们来看看它在对一张有1亿行数据的表进行全表扫描时的表现。

结果在0.02秒内返回，虽然没有透露禁用HeatWave时的秒数，但速度非常快。

在我的环境中，它的放大倍数变成了1500倍左右。请您也在您的环境中试试。

mysql> SELECT * FROM account WHERE subid BETWEEN 100 and 110;
+-----+---------+-------+---------------------+---------------------+
| id  | name    | subid | created_at          | updated_at          |
+-----+---------+-------+---------------------+---------------------+
| 109 | NAME109 |   109 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 110 | NAME110 |   110 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 104 | NAME104 |   104 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 100 | NAME100 |   100 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 101 | NAME101 |   101 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 102 | NAME102 |   102 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 103 | NAME103 |   103 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 105 | NAME105 |   105 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 106 | NAME106 |   106 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 107 | NAME107 |   107 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 108 | NAME108 |   108 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
+-----+---------+-------+---------------------+---------------------+
11 rows in set (0.02 sec)


mysql>

附录1：带提示(Hint)的卸载控制(Offload Controll)

您可以在每个SQL查询的基础上控制卸载(Offload)到HeatWave，如下文所述，https://dev.mysql.com/doc/heatwave/en/heatwave-best-practices.html。

提示(Hint)的规范如下。

ON：使用HeatWave，因故障等原因不能使用HeatWave时，由MDS自行处理（与未指定提示时的操作相同）。
OFF：不使用HeatWave。
FORCED : 强制使用HeatWave，如果HeatWave因故障等原因无法使用，SQL查询会出现错误。

SELECT *+ SET_VAR(use_secondary_engine = ON) */ * FROM account WHERE subid BETWEEN 100 and 110;
SELECT *+ SET_VAR(use_secondary_engine = OFF) */ * FROM account WHERE subid BETWEEN 100 and 110;
SELECT *+ SET_VAR(use_secondary_engine = FORCED) */ * FROM account WHERE subid BETWEEN 100 and 110;

ON : 使用HeatWave，但如果不能使用，则由MDS自己处理（与未指定提示时的操作相同）。

EXPLAIN SELECT *+ SET_VAR(use_secondary_engine = ON) */ * FROM account WHERE subid BETWEEN 100 and 110;

执行计划。

mysql> EXPLAIN SELECT *+ SET_VAR(use_secondary_engine = ON) */ * FROM account WHERE subid BETWEEN 100 and 110;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                     |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
|  1 | SIMPLE      | account | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97926422 |    11.11 | Using where; Using secondary engine RAPID |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)


mysql>

OFF : 即使与HeatWave同步，也不会卸载(Offload)。

EXPLAIN SELECT *+ SET_VAR(use_secondary_engine = OFF) */ * FROM account WHERE subid BETWEEN 100 and 110;

执行计划。

mysql> EXPLAIN SELECT *+ SET_VAR(use_secondary_engine = OFF) */ * FROM account WHERE subid BETWEEN 100 and 110;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | account | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97926422 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql>

FORCED : 强制使用HeatWave。

EXPLAIN SELECT *+ SET_VAR(use_secondary_engine = FORCED) */ * FROM account WHERE subid BETWEEN 100 and 110;

执行计划。

mysql> EXPLAIN SELECT *+ SET_VAR(use_secondary_engine = FORCED) */ * FROM account WHERE subid BETWEEN 100 and 110;
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra                                     |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
|  1 | SIMPLE      | account | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97926422 |    11.11 | Using where; Using secondary engine RAPID |
+----+-------------+---------+------------+------+---------------+------+---------+------+----------+----------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)


mysql>

当HeatWave被禁用时，它会立即返回一个错误信息。

mysql> SELECT *+ SET_VAR(use_secondary_engine = FORCED) */ * FROM account WHERE subid BETWEEN 100 and 110;
ERROR 3889 (HY000): Secondary engine operation failed. use_secondary_engine is FORCED but query could not be executed in secondary engine.
mysql>

附录2：HeatWave 解除链接

同步状态。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
+----------------+---------------------+
| NAME           | LOAD_STATUS         |
+----------------+---------------------+
| sugi01.account | AVAIL_RPDGSTABSTATE |
+----------------+---------------------+
1 row in set (0.00 sec)


mysql>

HeatWave可以在Alter Table中解除链接。

ALTER TABLE account SECONDARY_UNLOAD;

结果为EMPTY。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
Empty set (0.00 sec)


mysql>

附录3：HeatWave 停止行为

可通过OCI控制台停止。

停止(Stop)。

所有的节点都变成"更新(Updating)"状态。

变成"更新(Updating)"状态时，数据同步率变为0%。

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 0.000000       |
+----------------+
1 row in set (0.00 sec)


mysql>

一段时间后，HeatWave的状态变为"不活动(Inactive)"。

附录4：HeatWave 启动行为

当从停止状态启动时，经过一定时间后就会变成"活动(Active)"状态。

但是，即使表变成"活动(Active)"状态，也不会自动同步。

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 0.000000       |
+----------------+
1 row in set (0.00 sec)


mysql>

您需要手动执行启动同步命令。

ALTER TABLE account SECONDARY_LOAD;

附录5：HeatWave 重启行为

重启(Restart)。

变成"更新(Updating)"状态时，数据同步率变为0%。

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 0.000000       |
+----------------+
1 row in set (0.00 sec)


mysql>

与启动时一样，即使变成"活动(Active)"状态，表也不会自动同步。

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 0.000000       |
+----------------+
1 row in set (0.00 sec)


mysql>

您需要手动执行启动同步命令。

ALTER TABLE account SECONDARY_LOAD;

附录6：检查实时同步性

当前结果，与HeatWave同步。

mysql> SELECT * FROM account WHERE subid BETWEEN 100 and 110;
+-----+---------+-------+---------------------+---------------------+
| id  | name    | subid | created_at          | updated_at          |
+-----+---------+-------+---------------------+---------------------+
| 100 | NAME100 |   100 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 101 | NAME101 |   101 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 102 | NAME102 |   102 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 103 | NAME103 |   103 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 105 | NAME105 |   105 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 106 | NAME106 |   106 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 107 | NAME107 |   107 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 108 | NAME108 |   108 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 104 | NAME104 |   104 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 109 | NAME109 |   109 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 110 | NAME110 |   110 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
+-----+---------+-------+---------------------+---------------------+
11 rows in set (0.03 sec)


mysql>

更新(UPDATE)。

UPDATE account SET name="NAME110_Updated" WHERE id = 110;

立即被更新。

mysql> SELECT * FROM account WHERE subid BETWEEN 100 and 110;
+-----+-----------------+-------+---------------------+---------------------+
| id  | name            | subid | created_at          | updated_at          |
+-----+-----------------+-------+---------------------+---------------------+
| 109 | NAME109         |   109 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 110 | NAME110_Updated |   110 | 2020-12-26 12:04:38 | 2020-12-26 12:04:38 |
| 104 | NAME104         |   104 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 100 | NAME100         |   100 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 101 | NAME101         |   101 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 102 | NAME102         |   102 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 103 | NAME103         |   103 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 105 | NAME105         |   105 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 106 | NAME106         |   106 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 107 | NAME107         |   107 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
| 108 | NAME108         |   108 | 2020-12-26 10:45:13 | 2020-12-26 10:45:13 |
+-----+-----------------+-------+---------------------+---------------------+
11 rows in set (0.05 sec)


mysql>

附录7：启用HeatWave后，插入1亿行数据

test01.account 表的同步。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
+----------------+---------------------+
| NAME           | LOAD_STATUS         |
+----------------+---------------------+
| sugi01.account | AVAIL_RPDGSTABSTATE |
| test01.account | AVAIL_RPDGSTABSTATE |
+----------------+---------------------+
2 rows in set (0.00 sec)


mysql>

插入1亿行数据：10分46秒(与HeatWave不同步时的时间相差不大)。

mysql> INSERT INTO account(name, subid)
    -> SELECT
    ->   CONCAT('NAME' , @rownum := @rownum + 1),@rownum
    -> FROM
    ->   sample AS s1,
    ->   sample AS s2,
    ->   sample AS s3,
    ->   sample AS s4,
    ->   sample AS s5,
    ->   sample AS s6,
    ->   sample AS s7,
    ->   sample AS s8,
    ->   (SELECT @rownum := 0) AS v;
Query OK, 100000000 rows affected, 2 warnings (10 min 46.39 sec)
Records: 100000000  Duplicates: 0  Warnings: 2


mysql>

附录8：添加同步表时的行为

只有一张表是同步的。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
+----------------+-----------------------+
| NAME           | LOAD_STATUS           |
+----------------+-----------------------+
| sugi01.account | AVAIL_RPDGSTABSTATE   |
+----------------+-----------------------+
1 rows in set (0.00 sec)



mysql>

同步率100%。

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 100.000000     |
+----------------+
1 row in set (0.00 sec)




mysql>

test01.account 表开始同步。

ALTER TABLE account SECONDARY_LOAD;

同步率达到50%。

因为第一个表已经同步，第二个表正在同步中。

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 51.827686      |
+----------------+
1 row in set (0.00 sec)




mysql>

状态。

mysql> SELECT NAME, LOAD_STATUS FROM performance_schema.rpd_tables, performance_schema.rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
+----------------+-----------------------+
| NAME           | LOAD_STATUS           |
+----------------+-----------------------+
| sugi01.account | AVAIL_RPDGSTABSTATE   |
| test01.account | LOADING_RPDGSTABSTATE |
+----------------+-----------------------+
2 rows in set (0.00 sec)




mysql>

在同步第二张表的同时，查询第一张表的返回速度很快(卸载(Offload)到HeatWave了)。

mysql> SELECT * FROM account WHERE subid BETWEEN 100 and 110;
+-----+---------+-------+---------------------+---------------------+
| id  | name    | subid | created_at          | updated_at          |
+-----+---------+-------+---------------------+---------------------+
| 104 | NAME104 |   104 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 109 | NAME109 |   109 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 110 | NAME110 |   110 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 100 | NAME100 |   100 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 101 | NAME101 |   101 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 102 | NAME102 |   102 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 103 | NAME103 |   103 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 105 | NAME105 |   105 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 106 | NAME106 |   106 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 107 | NAME107 |   107 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
| 108 | NAME108 |   108 | 2020-12-26 01:59:10 | 2020-12-26 01:59:10 |
+-----+---------+-------+---------------------+---------------------+
11 rows in set (0.03 sec)




mysql>

参考网址

Performance

https://www.oracle.com/jp/mysql/heatwave/performance/

GitHub

https://github.com/oracle/heatwave-tpch

Document

https://docs.oracle.com/en-us/iaas/mysql-database/doc/heatwave1.html

HeatWave User Guide

https://dev.mysql.com/doc/heatwave/en/

使用SQL创建大量的测试数据

https://qiita.com/cobot00/items/8d59e0734314a88d74c7

本文封面图来自https://blogs.oracle.com/mysql/mysql-best-of-2020

编辑：萧宇

mysql

文章转载自甲骨文云技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

体验云上处理速度超快的MySQL HeatWave

评论