5.2 热浪监测_MySQL HeatWave 用户指南

MySQL HeatWave 用户指南 / 第 5 章 HeatWave 性能和监控 / 5.2 热浪监测

5.2 热浪监测

本部分提供可用于监控 HeatWave 的查询。

查询分为以下类别：

HeatWave 节点状态

查看每个 HeatWave 节点的状态：

mysql> SELECT ID, STATUS FROM 
       performance_schema.rpd_nodes;
+----+---------------+
| ID | STATUS        |
+----+---------------+
|  0 | AVAIL_RNSTATE |
|  1 | AVAIL_RNSTATE |
+----+---------------+

有关列说明，请参阅第 5.4.4 节，“rpd_nodes 表”。

HeatWave 内存使用

查看每个 HeatWave 节点的内存使用情况：

mysql> SELECT ID, MEMORY_USAGE, MEMORY_TOTAL, BASEREL_MEMORY_USAGE FROM 
       performance_schema.rpd_nodes;
+----+--------------+--------------+----------------------+
| ID | MEMORY_USAGE | MEMORY_TOTAL | BASEREL_MEMORY_USAGE |
+----+--------------+--------------+----------------------+
|  0 |    115760258 | 515396075520 |            115760152 |
|  1 |    115845086 | 515396075520 |            115844980 |
+----+--------------+--------------+----------------------+

有关列说明，请参阅第 5.4.4 节，“rpd_nodes 表”。

数据加载进度和状态

将表加载到 HeatWave 所需的时间取决于数据大小。您可以通过发出以下查询来监视加载进度，该查询返回一个指示加载进度的百分比值。

mysql> SELECT VARIABLE_VALUE
       FROM performance_schema.global_status
       WHERE VARIABLE_NAME = 'rapid_load_progress';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 100.000000     |
+----------------+

要检查特定模式中表的加载状态：

mysql> USE performance_schema;
mysql> SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id
       WHERE rpd_tables.ID = rpd_table_id.ID AND SCHEMA_NAME LIKE 'tpch';
+------------------------------+---------------------+
| NAME                         | LOAD_STATUS         |
+------------------------------+---------------------+
| tpch.supplier                | AVAIL_RPDGSTABSTATE |
| tpch.partsupp                | AVAIL_RPDGSTABSTATE |
| tpch.orders                  | AVAIL_RPDGSTABSTATE |
| tpch.lineitem                | AVAIL_RPDGSTABSTATE |
| tpch.customer                | AVAIL_RPDGSTABSTATE |
| tpch.nation                  | AVAIL_RPDGSTABSTATE |
| tpch.region                  | AVAIL_RPDGSTABSTATE |
| tpch.part                    | AVAIL_RPDGSTABSTATE |
+------------------------------+---------------------+

有关加载状态的信息，请参阅第 5.4.8 节，“rpd_tables 表”。

要查看 HeatWave 中为表加载的数据量，以字节为单位：

mysql> USE performance_schema;
mysql> SELECT rpd_table_id.TABLE_NAME, rpd_tables.NROWS, rpd_tables.LOAD_STATUS, 
       rpd_tables.SIZE_BYTES FROM rpd_table_id, rpd_tables 
       WHERE rpd_table_id.ID = rpd_tables.ID ORDER BY SIZE_BYTES;
------------+---------+---------------------+------------+
| TABLE_NAME | NROWS   | LOAD_STATUS         | SIZE_BYTES |
+------------+---------+---------------------+------------+
| region     |       5 | AVAIL_RPDGSTABSTATE |    4194304 |
| nation     |      25 | AVAIL_RPDGSTABSTATE |    8388608 |
| part       |  200000 | AVAIL_RPDGSTABSTATE |   33554432 |
| customer   |  150000 | AVAIL_RPDGSTABSTATE |   41943040 |
| orders     | 1500000 | AVAIL_RPDGSTABSTATE |  226492416 |
+------------+---------+---------------------+------------+

要查看表加载操作完成的时间：

mysql> USE performance_schema;
mysql> SELECT rpd_table_id.TABLE_NAME, rpd_tables.NROWS, rpd_tables.LOAD_STATUS, 
       rpd_tables.LOAD_END_TIMESTAMP FROM rpd_table_id, rpd_tables 
       WHERE rpd_table_id.ID = rpd_tables.ID;
+------------+---------+---------------------+----------------------------+
| TABLE_NAME | NROWS   | LOAD_STATUS         | LOAD_END_TIMESTAMP         |
+------------+---------+---------------------+----------------------------+
| region     |       5 | AVAIL_RPDGSTABSTATE | 2021-12-06 14:32:15.209825 |
| part       |  200000 | AVAIL_RPDGSTABSTATE | 2021-12-06 14:32:07.594575 |
| customer   |  150000 | AVAIL_RPDGSTABSTATE | 2021-12-06 14:31:57.210649 |
| nation     |      25 | AVAIL_RPDGSTABSTATE | 2021-12-06 14:17:53.472208 |
| orders     | 1500000 | AVAIL_RPDGSTABSTATE | 2021-12-06 14:24:45.809931 |
+------------+---------+---------------------+----------------------------+

变化传播

要确定是否全局启用更改传播，请查询 rapid_change_propagation_status 变量：

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status 
       WHERE VARIABLE_NAME = 'rapid_change_propagation_status';
+----------------+
| VARIABLE_VALUE |
+----------------+
| ON             |
+----------------+

要确定是否为特定表启用了更改传播，请查询POOL_TYPE HeatWave Performance Schema 表中的数据。 RAPID_LOAD_POOL_TRANSACTIONAL表示为表启用了更改传播。 RAPID_LOAD_POOL_SNAPSHOT指示更改传播已禁用。

mysql> USE performance_schema;
mysql> SELECT NAME, POOL_TYPE FROM rpd_tables,rpd_table_id 
       WHERE rpd_tables.ID = rpd_table_id.ID AND SCHEMA_NAME LIKE 'tpch';
+---------------+-------------------------------+
| NAME          | POOL_TYPE                     |
+---------------+-------------------------------+
| tpch.orders   | RAPID_LOAD_POOL_TRANSACTIONAL |
| tpch.region   | RAPID_LOAD_POOL_TRANSACTIONAL |
| tpch.lineitem | RAPID_LOAD_POOL_TRANSACTIONAL |
| tpch.supplier | RAPID_LOAD_POOL_TRANSACTIONAL |
| tpch.partsupp | RAPID_LOAD_POOL_TRANSACTIONAL |
| tpch.part     | RAPID_LOAD_POOL_TRANSACTIONAL |
| tpch.customer | RAPID_LOAD_POOL_TRANSACTIONAL |
+---------------+-------------------------------+

查询执行

要查看自上次启动 HeatWave 集群以来卸载到 HeatWave 集群以执行的查询数量：

mysql> SELECT VARIABLE_VALUE
       FROM performance_schema.global_status
       WHERE VARIABLE_NAME = 'rapid_query_offload_count';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 62             |
+----------------+

从 MySQL 8.0.29 开始，Performance Schema 语句事件表（参见 Performance Schema Statement Event Tables）performance_scema.threads 和 performance_scema.processlist 表包括一个EXECUTION_ENGINE列，指示查询是否在 PRIMARYorSECONDARY 引擎上处理，其中主引擎是 InnoDB 和辅助引擎是热浪。MySQL Schema 中的sys.processlist 和视图也包含一列。 sys.x$processlistsysexecution_engine

此查询显示架构、查询的前 50 个字符以及处理查询的执行引擎：

mysql> SELECT CURRENT_SCHEMA, LEFT(DIGEST_TEXT, 50), EXECUTION_ENGINE 
       FROM performance_schema.events_statements_history 
       WHERE CURRENT_SCHEMA='tpch';
+----------------+----------------------------------------------------+------------------+
| CURRENT_SCHEMA | LEFT(DIGEST_TEXT, 50)                              | EXECUTION_ENGINE |
+----------------+----------------------------------------------------+------------------+
| tpch           | SELECT COUNT(*) FROM tpch.LINEITEM                 | SECONDARY        |
+----------------+----------------------------------------------------+------------------+

从 MySQL 8.0.29 开始，Performance Schema 语句汇总表（请参阅语句汇总表）包括一个COUNT_SECONDARY列，该列指示查询在 SECONDARY引擎 (HeatWave) 上处理的次数。

此查询从表中检索辅助引擎执行事件的总数 events_statements_summary_by_digest ：

mysql> SELECT SUM(COUNT_SECONDARY) 
       FROM performance_schema.events_statements_summary_by_digest;
+----------------------+
| SUM(COUNT_SECONDARY) |
+----------------------+
|                   25 |
+----------------------+

此查询计算特定模式的所有引擎执行事件，并显示主引擎 (InnoDB) 上发生了多少事件，辅助引擎 (HeatWave) 上发生了多少事件：

mysql> SELECT SUM(COUNT_STAR) AS TOTAL_EXECUTIONS, SUM(COUNT_STAR) - SUM(COUNT_SECONDARY) 
       AS PRIMARY_ENGINE, SUM(COUNT_SECONDARY) AS SECONDARY_ENGINE 
       FROM performance_schema.events_statements_summary_by_digest 
       WHERE SCHEMA_NAME LIKE 'tpch'\G
*************************** 1. row ***************************
TOTAL_EXECUTIONS: 25
  PRIMARY_ENGINE: 5
SECONDARY_ENGINE: 20

查询历史和统计

查看 HeatWave 查询历史记录，包括查询开始时间、结束时间和调度队列中的等待时间，如自动调度中所述。

SELECT QUERY_ID,
    CONNECTION_ID,
    QUERY_START,
    QUERY_END,
    QUEUE_WAIT,
    SUBTIME(
        SUBTIME(QUERY_END, SEC_TO_TIME(RPD_EXEC / 1000)),
        SEC_TO_TIME(GET_RESULT / 1000)
    ) AS EXEC_START
FROM (
        SELECT QUERY_ID,
            STR_TO_DATE(
                JSON_UNQUOTE(
                    JSON_EXTRACT(QEXEC_TEXT->>"$**.queryStartTime", '$[0]')
                ),
                '%Y-%m-%d %H:%i:%s.%f'
            ) AS QUERY_START,
            JSON_EXTRACT(QEXEC_TEXT->>"$**.timeBetweenMakePushedJoinAndRpdExec", '$[0]') 
            AS QUEUE_WAIT,
            STR_TO_DATE(
                JSON_UNQUOTE(
                    JSON_EXTRACT(QEXEC_TEXT->>"$**.queryEndTime", '$[0]')
                ),
                '%Y-%m-%d %H:%i:%s.%f'
            ) AS QUERY_END,
            JSON_EXTRACT(QEXEC_TEXT->>"$**.rpdExec.msec", '$[0]') AS RPD_EXEC,
            JSON_EXTRACT(QEXEC_TEXT->>"$**.getResults.msec", '$[0]') AS GET_RESULT,
            JSON_EXTRACT(QEXEC_TEXT->>"$**.thread", '$[0]') AS CONNECTION_ID
        FROM performance_schema.rpd_query_stats
    ) tmp;

查询返回以下数据：

QUERY_ID

HeatWave 分配给查询的 ID。ID 以先进先出 (FIFO) 的顺序分配。
CONNECTION_ID

发出查询的客户端的连接 ID。
QUERY_START

发出查询的时间。
QUERY_END

查询完成执行的时间。
QUEUE_WAIT

查询在调度队列中等待的时间量。
EXEC_START

HeatWave 开始执行查询的时间。

查看 rpd_query_stats表中的记录数。该 rpd_query_stats表存储最近 1000 个成功执行的查询的查询编译和执行统计信息（查询历史记录）。
```
mysql> SELECT COUNT(*) FROM performance_schema.rpd_query_stats;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
```

要查看第一个和最后一个成功执行的查询的查询 ID：

mysql> SELECT MIN(QUERY_ID), MAX(QUERY_ID) FROM performance_schema.rpd_query_stats;
+---------------+---------------+
| MIN(QUERY_ID) | MAX(QUERY_ID) |
+---------------+---------------+
|             2 |          1001 |
+---------------+---------------+

要查看表的查询计数和上次查询表的时间：

mysql> USE performance_schema;
mysql> SELECT rpd_table_id.TABLE_NAME, rpd_tables.NROWS, rpd_tables.QUERY_COUNT, 
       rpd_tables.LAST_QUERIED FROM rpd_table_id, rpd_tables 
       WHERE rpd_table_id.ID = rpd_tables.ID;
+------------+---------+-------------+----------------------------+
| TABLE_NAME | NROWS   | QUERY_COUNT | LAST_QUERIED               |
+------------+---------+-------------+----------------------------+
| orders     | 1500000 |           1 | 2021-12-06 14:32:59.868141 |
+------------+---------+-------------+----------------------------+

扫描数据

HeatWave 跟踪所有查询和单个查询扫描的数据量。

要查看自 HeatWave 集群上次启动以来所有成功执行的 HeatWave 查询扫描的累计数据总量（以 MB 为单位），请查询 hw_data_scanned全局状态变量。例如：

mysql> SHOW GLOBAL STATUS LIKE 'hw_data_scanned';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| hw_data_scanned | 66    |
+-----------------+-------+

累计总数不包括失败查询扫描的数据、未卸载到 HeatWave 的查询或 EXPLAIN查询。

hw_data_scanned仅当 HeatWave Cluster 重新启动时，该值才重置为 0。

如果 HeatWave 节点的子集脱机，只要 HeatWave 集群保持活动状态，HeatWave 就会保留扫描数据的累积总数。当 HeatWave 集群完全运行并再次开始处理查询时，HeatWave 恢复跟踪扫描的数据量，添加到累计总数中。

要查看单个 HeatWave 查询扫描的数据量或查看使用运行的查询将扫描的数据量的估计值，请EXPLAIN运行查询并查询表列中的 totalBaseDataScanned字段： QKRN_TEXTperformance_schema.rpd_query_stats

mysql> SELECT query_id, 
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id,
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.totalBaseDataScanned'), '$[0]') AS data_scanned, 
       JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message 
       FROM performance_schema.rpd_query_stats;
+----------+------------+--------------+---------------+
| query_id | session_id | data_scanned | error_message |
+----------+------------+--------------+---------------+
|        1 | 8          | 66           | ""            |
+----------+------------+--------------+---------------+

上面的示例检索与查询 ID 关联的任何错误消息。如果查询失败或被中断，则返回失败或中断查询扫描的字节数以及相关的错误消息，如以下示例所示：

mysql> SELECT query_id, 
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id,
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.totalBaseDataScanned'), '$[0]') AS data_scanned, 
       JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message 
       FROM performance_schema.rpd_query_stats;
+----------+------------+--------------+------------------------------------------+
| query_id | session_id | data_scanned | error_message                            |
+----------+------------+--------------+------------------------------------------+
|        1 | 8          | 461          | "Operation was interrupted by the user." |
+----------+------------+--------------+------------------------------------------+

mysql> SELECT query_id, 
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id,
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.totalBaseDataScanned'), '$[0]') AS data_scanned, 
       JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message 
       FROM performance_schema.rpd_query_stats;
+----------+------------+--------------+-----------------------------------------------+
| query_id | session_id | data_scanned | error_message                                 |
+----------+------------+--------------+-----------------------------------------------+
|        1 | 8          | 987          | "Out of memory error during query execution in|
|          |            |              |  RAPID."                                      |
+----------+------------+--------------+-----------------------------------------------+