MySQL HeatWave 用户指南  / 第 2 章热浪  /  2.8 最佳实践

2.8 最佳实践

HeatWave 最佳实践在本节的以下主题下进行了描述：

准备数据

在准备加载到 HeatWave 中的数据时，建议采用以下做法：

  • 考虑使用 Auto Parallel Load 实用程序，而不是手动准备表格并将其加载到 HeatWave 中。请参阅第 2.2.3 节，“使用自动并行加载加载数据”。

  • 要最大程度地减少数据所需的 HeatWave 节点数，请排除查询未访问的表列。有关排除列的信息，请参阅 排除表列。

  • 要节省内存空间，请将 CHAR、 VARCHAR和 TEXT-type 列长度设置为最长字符串值所需的最小长度。

  • 在适当的情况下，将字典编码应用于 CHAR、 VARCHAR和 TEXT-type 列。字典编码减少了 HeatWave 集群节点上的内存消耗。在为字典编码选择字符串列时使用以下条件：

    1. 该列不用作 JOIN查询中的键。

    2. 您的查询不会对列执行 LIKE、SUBSTR、 CONCAT等操作。变长编码支持字符串函数和运算符和LIKE谓词；字典编码没有。

    3. 该列具有有限数量的不同值。字典编码最适合具有有限数量的不同值的列，例如 “国家”列。

    4. 该列预计在更改传播期间添加的新值很少。避免对具有大量插入和更新的列进行字典编码。将大量新的唯一值添加到字典编码列可能会导致更改传播失败。

    TPC Benchmark™ H (TPC-H) 中的以下列提供了适合和不适合字典编码的字符串列示例：

    • ORDERS.O_ORDERPRIORITY

      此列仅用于范围查询。与列关联的值是有限的。在更新期间，不太可能添加大量新的唯一值。这些特性使该列适合字典编码。

    • LINEITEM.L_COMMENT

      该列不用于连接或其他复杂表达式，但作为注释字段，值应该是唯一的，因此该列不适合字典编码。

    如果对选择编码类型有疑问，请使用可变长度编码，当表加载到 HeatWave 时默认应用该编码，或使用 HeatWave Encoding Advisor 获取编码建议。请参阅 第 2.7.3.1 节，“自动编码”。

  • 当未定义数据放置键时，数据按表的主键分区。如果按主键对数据进行分区不能提供合适的性能，则只考虑定义数据放置键。

    为最耗时的查询保留数据放置键的使用。在这种情况下，定义数据放置键：

    • 最常用的JOIN键。

    • 运行时间最长的查询的键。

    考虑使用自动数据放置来获得数据放置建议。请参阅 第 2.7.3.2 节，“自动数据放置”。

置备

要为工作负载确定合适的 HeatWave 集群大小，您可以估计所需的集群大小。集群大小估计由 HeatWave 自动配置功能生成，该功能使用机器学习模型根据节点形状和数据采样来预测所需节点的数量。说明：

执行簇大小估计：

  • 将 HeatWave 集群添加到数据库系统时，确定要加载的数据所需的节点数。

  • 定期，以确保您的数据有适当数量的 HeatWave 节点。随着时间的推移，数据大小可能会增加或减少，因此通过执行集群大小估算来监控数据大小非常重要。

  • 在运行查询时遇到内存不足错误时。在这种情况下，HeatWave Cluster 可能没有足够的内存容量。

  • 当数据增长率很高时。

  • 当事务率（更新和插入率）很高时。

将数据导入 MySQL 数据库系统

MySQL Shell 是推荐的用于将数据导入 MySQL 数据库系统的实用程序。MySQL Shell 转储和加载实用程序专为与 MySQL 数据库系统一起使用而构建；适用于所有类型的进出口。MySQL Shell 支持从对象存储导出和导入。支持的最低 MySQL 源版本是 5.7.9。

入站复制

笔记

只有 OCI 上的 MySQL 数据库服务才支持入站复制。

  • 复制 DDL 操作：在将表加载到 HeatWave 之前，RAPID 必须定义为表的辅助引擎；例如： 

    ALTER TABLE orders SECONDARY_ENGINE = RAPID;

    在 8.0.31 版之前，不允许对使用辅助引擎定义的表执行 DDL 操作。在这些版本中，在将 DDL 操作从本地实例复制到使用辅助引擎定义的数据库系统上的表之前，您必须将 SECONDARY_ENGINE选项设置为 NULL；例如： 

    ALTER TABLE orders SECONDARY_ENGINE = NULL;

    将SECONDARY_ENGINE选项 设置为从表定义NULL中删除该 SECONDARY_ENGINE选项并从 HeatWave 中卸载该表。要在复制 DDL 操作后将表重新加载到 HeatWave 中，请SECONDARY_LOAD 在语句中指定选项ALTER TABLE。 

    ALTER TABLE orders SECONDARY_LOAD;

加载数据中

考虑使用 Auto Parallel Load 实用程序，而不是手动准备表格并将其加载到 HeatWave 中。请参阅 第 2.2.3 节，“使用自动并行加载加载数据”。AWS 上的 MySQL HeatWave 用户还可以选择从 MySQL HeatWave 控制台加载数据。请参阅 MySQL HeatWave on AWS 服务指南中的 管理 HeatWave 数据。

将数据加载到 HeatWave 中可以分为三种类型：Initial Bulk Load、 Incremental Bulk Load和 Change Propagation。

  • 初始批量加载：首次将数据加载到 HeatWave 或重新加载数据时执行。执行初始批量加载的最佳时间是在非高峰时段，因为批量加载操作会影响 MySQL 数据库系统上的 OLTP 性能。

  • 增量批量加载：当有大量数据要加载到已在 HeatWave 中加载的表中时执行。增量批量加载涉及以下步骤：

    1. 执行SECONDARY_UNLOAD 从 HeatWave 卸载表的操作。请参阅 第 2.5 节，“卸载表”。

    2. 将数据导入 MySQL 数据库系统节点上的表。

    3. 执行SECONDARY_LOAD 操作以将表重新加载到 HeatWave 中。请参阅 第 2.2 节，“加载数据”。

    根据数据量，增量批量加载可能是一种比等待更改传播发生更快的加载新数据的方法。它还可以更好地控制何时加载新数据。与初始构建加载一样，执行增量批量加载的最佳时间是在非高峰时段，因为批量加载操作会影响 MySQL 数据库系统上的 OLTP 性能。

  • 更改传播：将表加载到 HeatWave 后，数据更改会自动从InnoDBMySQL 数据库系统上的表传播到 HeatWave 中的对应表。请参阅第 2.2.7 节，“更改传播”。

使用以下策略来提高加载性能：

  • 增加读线程数

    对于大中型表，通过 innodb_parallel_read_threads 在 MySQL 数据库系统上设置变量将读取线程数增加到 32。 

    mysql> SET SESSION innodb_parallel_read_threads = 32;

    如果 MySQL DB System 不忙，您可以将该值增加到 64。

    小费

    Auto Parallel Load 实用程序自动优化每个表的并行读取线程数。请参阅 第 2.2.3 节，“使用自动并行加载加载数据”。对于 AWS 上的 MySQL HeatWave 用户，从 MySQL HeatWave 控制台加载数据时，并行读取线程的数量也得到了优化。请参阅 管理 HeatWave 数据。

  • 同时加载表

    如果你有很多中小型表（大小小于 20GB），从多个会话加载表： 

    Session 1: 
mysql> ALTER TABLE supplier SECONDARY_LOAD;

Session 2: 
mysql> ALTER TABLE parts SECONDARY_LOAD;

Session 3: 
mysql> ALTER TABLE region SECONDARY_LOAD;

Session 4: 
mysql> ALTER TABLE partsupp SECONDARY_LOAD;

  • 避免或减少冲突操作

    数据加载操作与 MySQL 数据库系统上的其他 OLTP DML 和 DDL 操作共享资源。要提高加载性能，请避免或减少冲突的 DDL 和 DML 操作。例如，避免LINEITEM在执行操作时对表运行 DDL 和大型 DMLALTER TABLE LINEITEM SECONDARY_LOAD操作。

自动编码和自动数据放置

Advisor 实用程序分析您的数据和 HeatWave 查询历史以提供字符串列编码和数据放置键建议。当查询发生变化时、数据发生显着变化时以及重新加载修改后的表后，考虑重新运行 Advisor 以获得更新的建议。

在所有情况下，请在运行 Advisor 之前重新运行您的查询。请参阅 第 2.7.3 节，“使用 Advisor 优化工作负载”。

运行查询

运行查询时建议采用以下做法：

  • 如果查询卸载失败，无法确定原因，请启用跟踪并查询 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表以调试查询。请参阅 调试查询。

    如果优化器跟踪没有返回所有跟踪信息，请增加优化器跟踪缓冲区大小。表格的 MISSING_BYTES_BEYOND_MAX_MEM_SIZE 列 INFORMATION_SCHEMA.OPTIMIZER_TRACE 显示跟踪中丢失了多少字节。如果该列显示非零值，请相应地增加 optimizer_trace_max_mem_size 设置。例如： 

    SET optimizer_trace_max_mem_size=1000000;

  • 如果 INFORMATION_SCHEMA.OPTIMIZER_TRACE 查询跟踪表明尚不支持子查询，请尝试取消嵌套子查询。例如，以下查询包含一个子查询，并且未如EXPLAIN 输出所示卸载，不显示“使用辅助引擎”。 

    mysql> EXPLAIN SELECT COUNT(*) FROM orders o WHERE o_totalprice> (SELECT AVG(o_totalprice) 
       FROM orders WHERE o_custkey=o.o_custkey)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: o
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 10.00
        Extra: Using where
2 rows in set, 2 warnings (0.00 sec)

    可以按如下方式重写此查询以取消嵌套子查询，以便可以卸载它。 

    mysql> EXPLAIN SELECT COUNT(*) FROM orders o, 
       (SELECT o_custkey, AVG(o_totalprice) a_totalprice 
       FROM orders GROUP BY o_custkey)a 
       WHERE o.o_custkey=a.o_custkey AND o.o_totalprice>a.a_totalprice;

  • 默认情况下，SELECT查询被卸载到 HeatWave 执行，如果不可能则回退到 MySQL 数据库系统。要强制查询在 HeatWave 上执行或在不可能时失败， use_secondary_engine variable请将FORCED. 在此模式下，如果SELECT语句无法卸载，则会返回错误。use_secondary_engine 变量可以设置如下所示 ：

    • SET在运行查询之前 使用语句：

      mysql> SET SESSION use_secondary_engine = FORCED

    • SET_VAR 发出查询时 使用优化器提示：

      mysql> SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ ... FROM ...

  • 如果在运行查询时遇到内存不足错误：

    1. 避免或重写产生笛卡尔积的查询。在以下查询中， 未在和 表JOIN之间定义谓词，这导致查询从两个表中选择所有行： suppliernation

      mysql> SELECT s_nationkey, s_suppkey, l_comment FROM lineitem, supplier, nation 
       WHERE s_suppkey = l_suppkey LIMIT 10;
ERROR 3015 (HY000): Out of memory in storage engine 'Failure detected in RAPID; query 
execution cannot proceed'.

      为避免笛卡尔积，在表supplier和 nation表之间添加相关谓词以过滤掉行： 

      mysql> SELECT s_nationkey, s_suppkey, l_comment FROM lineitem, supplier, nation 
       WHERE s_nationkey = n_nationkey and s_suppkey = l_suppkey LIMIT 10;

    2. 避免或重写产生由 MySQL 优化器引入的笛卡尔积的查询。由于缺乏质量统计或非最佳成本决策，MySQL 优化器可能会在查询中引入一个或多个笛卡尔积，即使查询在所有参与表中定义了谓词。例如： 

      mysql> SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment 
       FROM lineitem, supplier, nation, customer, orders 
       WHERE c_custkey = o_custkey AND o_orderkey = l_orderkey
       AND c_nationkey = s_nationkey AND c_nationkey = n_nationkey AND c_custkey < 3000000
       LIMIT 10;
ERROR 3015 (HY000): Out of memory in storage engine 'Failure detected in RAPID; 
query execution cannot proceed'.

      计划输出显示前两个表条目（和 ） EXPLAIN之间没有公共谓词。NATIONSUPPLIER

      mysql> EXPLAIN SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment 
       FROM lineitem, supplier, nation, customer, orders 
       WHERE c_custkey = o_custkey AND o_orderkey = l_orderkey AND c_nationkey = s_nationkey 
       AND c_nationkey = n_nationkey AND c_custkey < 3000000 LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: supplier
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99626
     filtered: 100.00
        Extra: Using secondary engine RAPID
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: nation
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1382274
     filtered: 5.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 56834662
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID

      To force a join order so that there are predicates associated with each pair of tables, add a STRAIGHT_JOIN hint. For example: 

      mysql> EXPLAIN SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment 
       FROM SUPPLIER STRAIGHT_JOIN CUSTOMER STRAIGHT_JOIN NATION STRAIGHT_JOIN ORDERS 
       STRAIGHT_JOIN LINEITEM WHERE c_custkey = o_custkey and o_orderkey = l_orderkey 
       AND c_nationkey = s_nationkey AND c_nationkey = n_nationkey AND c_custkey < 3000000
       LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: supplier
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 99626
     filtered: 100.00
        Extra: Using secondary engine RAPID
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1382274
     filtered: 5.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: nation
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 56834662
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID

    3. Avoid or rewrite queries that produce a very large result set. This is a common cause of out of memory errors during query processing. Use aggregation functions, a GROUP BY clause, or a LIMIT clause to reduce the result set size.

    4. Avoid or rewrite queries that produce a very large intermediate result set. In certain cases, large result sets can be avoided by adding a STRAIGHT_JOIN hint, which enforces a join order in a decreasing of selectiveness.

    5. Check the size of your data by performing a cluster size estimate. If your data has grown substantially, the HeatWave Cluster may require additional nodes.

    6. HeatWave optimizes for network usage by default. Try running the query with the MIN_MEM_CONSUMPTION strategy by setting by setting rapid_execution_strategy to MIN_MEM_CONSUMPTION. The rapid_execution_strategy variable can be set as shown:

      • Using a SET statement before running queries: 

        mysql> SET SESSION rapid_execution_strategy = MIN_MEM_CONSUMPTION;

      • Using a SET_VAR optimizer hint when issuing a query: 

        mysql> SELECT /*+ SET_VAR(rapid_execution_strategy = MIN_MEM_CONSUMPTION) */ ... FROM ...

      • Unloading tables that are not used. These tables consume memory on HeatWave nodes unnecessarily. See Section 2.5, “Unloading Tables”.

      • Excluding table columns that are not accessed by your queries. These columns consume memory on HeatWave nodes unnecessarily. This strategy requires reloading data. See Excluding Table Columns.

    7. After running queries, consider using HeatWave Advisor for encoding and data placement recommendations. See Section 2.7.3, “Workload Optimization using Advisor”.

Monitoring

The following monitoring practices are recommended:

  • For HeatWave on OCI, you can monitor operating system memory usage by setting an alarm to notify you when memory usage on HeatWave nodes remains above 450GB for an extended period of time. If memory usage exceeds this threshold, either reduce the size of your data or add nodes to the HeatWave Cluster. For information about using metrics, alarms, and notifications, refer to MySQL Database Service Metrics, in the MySQL Database Service User Guide.

  • For MySQL HeatWave on AWS, you can monitor memory usage on the Performance tab of the MySQL HeatWave Console. See Performance Monitoring.

  • For HeatWave in Oracle Database Service for Azure (ODSA), you can access Microsoft Azure's Application Insights by selecting Metrics on the details page for the HeatWave Cluster. See the Oracle Database Service for Azure documentation.

  • Monitor change propagation status. If change propagation is interrupted and tables are not automatically reloaded for some reason, table data becomes stale. Queries that access tables with stale data are not offloaded to HeatWave for processing. For instructions, see Section 2.2.7, “Change Propagation”.

Reloading Data

Reloading data is recommended in the following cases:

  • After resizing the cluster by adding or removing nodes. Reloading data distributes the data among all nodes of the resized cluster.

  • After a maintenance window. Maintenance involves a DB System restart, which requires that you reload data into HeatWave. On OCI, consider setting up a MySQL Database Service event notification or Service Connector Hub notification to let you know when an update has occurred.

  • 当 HeatWave 集群由于数据库系统重启而重启时。在这种情况下，HeatWave 集群中的数据会丢失，需要重新加载。