5.2 HeatWave Monitoring

This section provides queries that you can use to monitor HeatWave.

Queries are organized into the following categories:

HeatWave Node Status

To view the status of each HeatWave node:

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

For column descriptions, see Section 5.4.4, “The rpd_nodes Table”.

HeatWave Memory Usage

To view the memory usage for each HeatWave node:

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 |
+----+--------------+--------------+----------------------+

For column descriptions, see Section 5.4.4, “The rpd_nodes Table”.

Data Load Progress and Status

  • The time required to load a table into HeatWave depends on data size. You can monitor load progress by issuing the following query, which returns a percentage value indicating load progress.

    mysql> SELECT VARIABLE_VALUE
           FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'rapid_load_progress';
    +----------------+
    | VARIABLE_VALUE |
    +----------------+
    | 100.000000     |
    +----------------+
  • To check the load status of tables in a particular schema:

    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 |
    +------------------------------+---------------------+

    For information about load statuses, see Section 5.4.8, “The rpd_tables Table”.

  • To view the amount of data loaded in HeatWave for a table, in bytes:

    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 |
    +------------+---------+---------------------+------------+
  • To view the time that a table load operation completed:

    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 |
    +------------+---------+---------------------+----------------------------+

Change Propagation

  • To determine if change propagation is enabled globally, query the rapid_change_propagation_status variable:

    mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status 
           WHERE VARIABLE_NAME = 'rapid_change_propagation_status';
    +----------------+
    | VARIABLE_VALUE |
    +----------------+
    | ON             |
    +----------------+
  • To determine if change propagation is enabled for a particular table, query the POOL_TYPE data from the HeatWave Performance Schema tables. RAPID_LOAD_POOL_TRANSACTIONAL indicates that change propagation is enabled for the table. RAPID_LOAD_POOL_SNAPSHOT indicates that change propagation is disabled.

    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 |
    +---------------+-------------------------------+

Query Execution

  • To view the number of queries offloaded to the HeatWave Cluster for execution since the last time HeatWave Cluster was started:

    mysql> SELECT VARIABLE_VALUE
           FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'rapid_query_offload_count';
    +----------------+
    | VARIABLE_VALUE |
    +----------------+
    | 62             |
    +----------------+
  • From MySQL 8.0.29, the Performance Schema statement event tables (see Performance Schema Statement Event Tables), and the performance_scema.threads and performance_scema.processlist tables include an EXECUTION_ENGINE column that indicates whether a query was processed on the PRIMARY or SECONDARY engine, where the primary engine is InnoDB and the secondary engine is HeatWave. The sys.processlist and sys.x$processlist views in the MySQL sys Schema also include an execution_engine column.

    This query shows the schema, the first 50 characters of the query, and the execution engine that processed the query:

    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        |
    +----------------+----------------------------------------------------+------------------+
  • From MySQL 8.0.29, the Performance Schema statement summary tables (see Statement Summary Tables) include a COUNT_SECONDARY column that indicates the number of times a query was processed on the SECONDARY engine (HeatWave).

    This query retrieves the total number of secondary engine execution events from the events_statements_summary_by_digest table:

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

    This query counts all engine execution events for a particular schema and shows how many occurred on the primary engine (InnoDB) and how many occurred on the secondary engine (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

Query History and Statistics

  • To view the HeatWave query history including query start time, end time, and wait time in the scheduling queue, as discussed in Auto Scheduling.

    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;

    The query returns the following data:

    • QUERY_ID

      The ID assigned to the query by HeatWave. IDs are assigned in first in first out (FIFO) order.

    • CONNECTION_ID

      The connection ID of the client that issued the query.

    • QUERY_START

      The time the query was issued.

    • QUERY_END

      The time the query finished executing.

    • QUEUE_WAIT

      The amount of time the query waited in the scheduling queue.

    • EXEC_START

      The time that HeatWave started executing the query.

  • To view the number of records in the rpd_query_stats table. The rpd_query_stats table stores query compilation and execution statistics (the query history) for the last 1000 successfully executed queries.

    mysql> SELECT COUNT(*) FROM performance_schema.rpd_query_stats;
    +----------+
    | count(*) |
    +----------+
    |     1000 |
    +----------+
  • To view query IDs for the first and last successfully executed queries:

    mysql> SELECT MIN(QUERY_ID), MAX(QUERY_ID) FROM performance_schema.rpd_query_stats;
    +---------------+---------------+
    | MIN(QUERY_ID) | MAX(QUERY_ID) |
    +---------------+---------------+
    |             2 |          1001 |
    +---------------+---------------+
  • To view the query count for a table and the last time the table was queried:

    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 |
    +------------+---------+-------------+----------------------------+

Scanned Data

HeatWave tracks the amount of data scanned by all queries and by individual queries.

To view a cumulative total of data scanned (in MBs) by all successfully executed HeatWave queries from the time the HeatWave Cluster was last started, query the hw_data_scanned global status variable. For example:

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

The cumulative total does not include data scanned by failed queries, queries that were not offloaded to HeatWave, or EXPLAIN queries.

The hw_data_scanned value is reset to 0 only when the HeatWave Cluster is restarted.

If a subset of HeatWave nodes go offline, HeatWave retains the cumulative total of scanned data as long as the HeatWave Cluster remains in an active state. When the HeatWave Cluster becomes fully operational and starts processing queries again, HeatWave resumes tracking the amount of data scanned, adding to the cumulative total.

To view the amount of data scanned by an individual HeatWave query or to view an estimate for the amount of data that would be scanned by a query run with EXPLAIN, run the query and query the totalBaseDataScanned field in the QKRN_TEXT column of the performance_schema.rpd_query_stats table:

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           | ""            |
+----------+------------+--------------+---------------+

The example above retrieves any error message associated with the query ID. If a query fails or was interrupted, the number of bytes scanned by the failed or interrupted query and the associated error message are returned, as shown in the following examples:

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."                                      |
+----------+------------+--------------+-----------------------------------------------+