MySQL :: HeatWave User Guide :: 7 Running Queries

Query Offload Prerequisites

The following prerequisites apply for offloading queries:

The query must be a SELECT statement. INSERT ... SELECT and CREATE TABLE ... SELECT statements are supported, but only the SELECT portion of the statement is offloaded to HeatWave. See CREATE TABLE ... SELECT Statements, and INSERT ... SELECT Statements.
All tables accessed by the query must be defined with RAPID as the secondary engine. See Section 3.5, “Defining the Secondary Engine”.
All tables accessed by the query must be loaded in HeatWave. See Chapter 4, Loading Data.
autocommit must be enabled. If autocommit is disabled, queries are not offloaded and execution is performed on the MySQL DB System. To check the autocommit setting:
```
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
```
Queries must only use supported functions and operators. See Section 12.2, “Supported Functions and Operators”.
Queries must avoid known limitations. See Section 12.6, “Limitations”.

If any prerequisite is not satisfied, the query is not offloaded and falls back to the MySQL DB System for execution by default. This behavior is controlled by the use_secondary_engine variable, which is set on ON by default. A use_secondary_engine=OFF setting forces a query to execute on the MySQL DB System. A use_secondary_engine=FORCED setting forces a query to execute on HeatWave or fail if that is not possible.

Running a Query

Before running a query, use EXPLAIN to determine if the query can be offloaded. If so, the Extra column of EXPLAIN output shows: “Using secondary engine RAPID”. If that information does not appear, the query cannot be offloaded.

mysql> EXPLAIN SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM orders 
       WHERE O_ORDERDATE >= DATE '1994-03-01' GROUP BY O_ORDERPRIORITY 
       ORDER BY O_ORDERPRIORITY\G
*************************** 1. 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: 33.33
        Extra: Using where; Using temporary; Using filesort; Using secondary 
        engine RAPID

After using EXPLAIN to verify that the query can be offloaded, run the query and note the execution time.

Note

For information about obtaining HeatWave query runtime estimates before running a query, see Query Runtimes and Estimates.

mysql> SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM orders
       WHERE O_ORDERDATE >= DATE '1994-03-01' GROUP BY O_ORDERPRIORITY 
       ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (0.04 sec)

To compare HeatWave query execution time with MySQL DB System execution time, disable the use_secondary_engine variable and run the query again to see how long it takes to run on the MySQL DB System.

mysql> SET SESSION use_secondary_engine=OFF;

mysql> SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM orders 
       WHERE O_ORDERDATE >= DATE '1994-03-01' GROUP BY O_ORDERPRIORITY 
       ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (8.91 sec)

If a query does not offload and you cannot determine why, refer to Chapter 11, Troubleshooting, or try debugging the query using the procedure described in Debugging Queries.

Note

Concurrently issued queries are prioritized for execution. For information about query prioritization, see Auto Scheduling.

Query Runtimes and Estimates

HeatWave query runtimes and runtime estimates can be viewed using HeatWave Advisor Query Insights feature or by querying the performance_schema.rpd_query_stats table. Runtime data is useful for query optimization, troubleshooting, and estimating the cost of running a particular query or workload.

HeatWave query runtime data includes:

Runtimes for successfully executed queries.
Runtime estimates for EXPLAIN queries.
Runtime estimates for queries cancelled using Ctrl+C.
Runtime estimates for queries that fail due to an out-of-memory error.

Runtime data is available for queries in the HeatWave query history, which is a non-persistent store of information about the last 1000 executed queries.

Using Query Insights

To view runtime data for all queries in the HeatWave history:

mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", TRUE));

To view runtime data for queries executed by the current session only:

mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", TRUE, 
       "query_session_id", JSON_ARRAY(connection_id())));

For additional information about using Query Insights, see Section 9.3, “Query Insights”.

Using the rpd_query_stats Table

To view runtime data for all queries in the HeatWave query history:

mysql> SELECT query_id,
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id, 
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.accumulatedRapidCost'),'$[0]') AS time_in_ns, 
       JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message
       FROM performance_schema.rpd_query_stats;

To view runtime data for a particular HeatWave query, filtered by query ID:

mysql> SELECT query_id,
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id, 
       JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.accumulatedRapidCost'),'$[0]') AS time_in_ns, 
       JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message
       FROM performance_schema.rpd_query_stats WHERE query_id = 1;

EXPLAIN output includes the query ID. You can also query the performance_schema.rpd_query_stats table for query IDs:

mysql> SELECT query_id, LEFT(query_text,160) FROM performance_schema.rpd_query_stats;

Tracking Scanned Data

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

Data Scanned By All 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.

Data Scanned By Individual Queries

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

CREATE TABLE ... SELECT Statements

The SELECT query of a CREATE TABLE ... SELECT statement is offloaded to HeatWave for execution, and the table is created on the MySQL DB System. Offloading the SELECT query to HeatWave reduces CREATE TABLE ... SELECT execution time in cases where the SELECT query is long running and complex. SELECT queries that produce large result sets do not benefit from this feature due to the large number of DML operations performed on the MySQL DB system instance.

The SELECT table must be loaded in HeatWave. For example, the following statement selects data from the orders table on HeatWave and inserts the result set into the orders2 table created on the MySQL DB System:

mysql> CREATE TABLE orders2 SELECT * FROM orders;

The SELECT portion of the CREATE TABLE ... SELECT statement is subject to the same HeatWave requirements and limitations as regular SELECT queries.

INSERT ... SELECT Statements

The SELECT query of an INSERT ... SELECT statement is offloaded to HeatWave for execution, and the result set is inserted into the specified table on the MySQL DB System. Offloading the SELECT query to HeatWave reduces INSERT ... SELECT execution time in cases where the SELECT query is long running and complex. SELECT queries that produce large result sets do not benefit from this feature due to the large number of DML operations performed on the MySQL DB system instance.

The SELECT table must be loaded in HeatWave, and the INSERT table must be present on the MySQL DB System. For example, the following statement selects data from the orders table on tHeatWave and inserts the result set into the orders2 table on the MySQL DB System:

mysql> INSERT INTO orders2 SELECT * FROM orders;

Usage notes:

The SELECT portion of the INSERT ... SELECT statement is subject to the same HeatWave requirements and limitations as regular SELECT queries.
Functions, operators, and attributes deprecated by MySQL Server are not supported in the SELECT query.
The ON DUPLICATE KEY UPDATE clause is not supported.
SELECT .. UNION ALL queries are not offloaded if the INSERT table is the same as the SELECT table because MySQL Server uses a temporary table in this case, which cannot be offloaded.
INSERT INTO some_view SELECT statements are not offloaded. Setting use_secondary_engine=FORCED does not cause the statement to fail with an error in this case. The statement is executed on the MySQL DB System regardless of the use_secondary_engine setting.

Using Views

HeatWave supports querying views. The table or tables upon which a view is created must be loaded in HeatWave. Queries executed on views are subject to the same offload prerequisites and limitations as queries executed on tables.

In the following example, a view is created on the orders table, described in Chapter 8, Table Load and Query Example. The example assumes the orders table is loaded in HeatWave.

mysql> CREATE VIEW v1 AS SELECT O_ORDERPRIORITY, O_ORDERDATE FROM orders;

To determine if a query executed on a view can be offloaded to HeatWave for execution, use EXPLAIN. If offload is supported, the Extra column of EXPLAIN output shows “Using secondary engine RAPID”, as in the following example:

mysql> EXPLAIN SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT 
       FROM v1 WHERE O_ORDERDATE >= DATE '1994-03-01' GROUP BY O_ORDERPRIORITY 
       ORDER BY O_ORDERPRIORITY\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ORDERS
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1488248
     filtered: 33.32999801635742
        Extra: Using where; Using temporary; Using filesort; Using secondary engine RAPID

Auto Scheduling

HeatWave uses a workload-aware, priority-based, automated scheduling mechanism to schedule concurrently issued queries for execution. The scheduling mechanism prioritizes short-running queries but considers wait time in the queue so that costlier queries are eventually scheduled for execution. This scheduling approach reduces query execution wait times overall.

When HeatWave is idle, an arriving query is scheduled immediately for execution. It is not queued. A query is queued only if a preceding query is running on HeatWave.

A light-weight cost estimate is performed for each query at query compilation time.

Queries cancelled via Ctrl-C are removed from the scheduling queue.

For a query that you can run to view the HeatWave query history including query start time, end time, and wait time in the scheduling queue, see Section 12.5, “Metadata Queries”.

Auto Query Plan Improvement

The Auto Query Plan Improvement feature collects and stores query plan statistics in a statistics cache when a query is executed in HeatWave. When a new query shares query execution plan nodes with previously executed queries, the statistics collected from previously executed queries are used instead of estimated statistics, which improves query execution plans, cost estimations, execution times, and memory efficiency.

Each entry in the cache corresponds to a query execution plan node. A query execution plan may have nodes for table scans, JOINs, GROUP BY operations, and so on.

The statics cache is an LRU structure. When cache capacity is reached, the least recently used entries are evicted from the cache as new entries are added. The number of entries permitted in the statistics cache is 65536, which is enough to store statistics for 4000 to 5000 unique queries of medium complexity. The maximum number of statistics cache entries is defined by the OCI-managed rapid_stats_cache_max_entries setting.

Debugging Queries

This section describes how to debug queries that fail to offload to HeatWave for execution. Query debugging is performed by enabling MySQL optimizer trace and querying the INFORMATION_SCHEMA.OPTIMIZER_TRACE table for the failure reason.

To enable MySQL optimizer trace, set the optimizer_trace and optimizer_trace_offset variables as shown:
```
mysql> SET SESSION optimizer_trace="enabled=on";
mysql> SET optimizer_trace_offset=-2;
```

Issue the problematic query using EXPLAIN. If the query is supported by HeatWave, the Extra column in the EXPLAIN output shows the following text: “Using secondary engine RAPID”; otherwise, that text does not appear. The following query example uses the TIMEDIFF() function, which is currently not supported by HeatWave:

mysql> EXPLAIN SELECT TIMEDIFF(O_ORDERDATE,'2000:01:01 00:00:00.000001') FROM orders\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ORDERS
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1488248
     filtered: 100
        Extra: NULL
1 row in set, 1 warning (0.0011 sec)

Query the INFORMATION_SCHEMA.OPTIMIZER_TRACE table for a failure reason. There are two trace markers for queries that fail to offload:

Rapid_Offload_Fails
secondary_engine_not_used

To query for the Rapid_Offload_Fails trace marker, issue this query:

mysql> SELECT QUERY, TRACE->'$**.Rapid_Offload_Fails' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

To query for the secondary_engine_not_used trace marker, issue this query:

mysql> SELECT QUERY, TRACE->'$**.secondary_engine_not_used' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

For the TIMEDIFF() query example used above, querying for the Rapid_Offload_Fails marker returns the reason for the failure:

mysql> SELECT QUERY, TRACE->'$**.Rapid_Offload_Fails' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+---------------------------------------------------+--------------------------------------+
| QUERY                                             | TRACE->'$**.Rapid_Offload_Fails'     |
+---------------------------------------------------+--------------------------------------+
| EXPLAIN SELECT                                    |[{"Reason": "Function timediff is not |
| TIMEDIFF(O_ORDERDATE,'2000:01:01 00:00:00.000001')|             yet supported"}]         |
| FROM ORDERS                                       |                                      |
+---------------------------------------------------+--------------------------------------+

The reason reported for a query offload failure depends on the issue or limitation encountered. For the most common issues, such as unsupported clauses or functions, a specific reason is reported. For undefined issues or unsupported query transformations performed by the optimizer, the following generic reason is reported:

[{"Reason": "Currently unsupported RAPID query compilation scenario"}]

For a query that does not meet the query cost threshold for HeatWave, the following reason is reported:

[{"Reason": "The estimated query cost does not exceed secondary_engine_cost_threshold."}]

The query cost threshold prevents small queries of little cost from being offloaded to HeatWave. For information about the query cost threshold, see Chapter 11, Troubleshooting.

For a query that attempts to access a column defined as NOT SECONDARY, the following reason is reported:

[{"Reason": "Column risk_assessment is marked as NOT SECONDARY."}]

Columns defined as NOT SECONDARY are excluded when a table is loaded into HeatWave. See Section 3.2, “Excluding Table Columns”.

Chapter 7 Running Queries