MySQL :: MySQL HeatWave User Guide :: 2.3 Running Queries

MySQL HeatWave User Guide / HeatWave / Running Queries

2.3 Running Queries

When HeatWave is enabled and the data you want to query is loaded, queries that qualify are automatically offloaded from the MySQL DB System to HeatWave for accelerated processing. No special action is required. Simply run the query from a client, application, or interface that is connected to the DB System associated with the HeatWave Cluster. After HeatWave processes a query, results are sent back to the MySQL DB System and to the client, application, or interface that issued the query.

For information about connecting to a MySQL DB System, see Connecting to a DB System, in the MySQL Database Service Guide.

Running queries and other query-related topics are described in the following sections:

For related best practices, see Section 2.8, “Best Practices”.

Query Prerequisites

The following prerequisites apply for offloading queries to HeatWave:

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 Defining the Secondary Engine.
All tables accessed by the query must be loaded in HeatWave. See Section 2.2, “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 2.11, “Supported Functions and Operators”.
Queries must avoid known limitations. See Section 2.16, “Limitations”.

If any prerequisite is not satisfied, the query is not offloaded and falls back to the MySQL DB System for processing by default.

Running Queries

Before running a query, you can use EXPLAIN to determine if the query will be offloaded to HeatWave for processing. If so, the Extra column of EXPLAIN output shows: “Using secondary engine RAPID”.

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

If Using secondary engine RAPID does not appear in the Extra column, the query will not be offloaded to HeatWave. To determine why a query will not offload, refer to Section 2.14, “Troubleshooting”, or try debugging the query using the procedure described in Debugging Queries.

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

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)

Note

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

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 2.13, “Monitoring”.

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 MySQL-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:

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

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

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

Note

If the optimizer trace does not return all of the trace information, increase the optimizer trace buffer size. For more information, see Running Queries.

For the TIMEDIFF() query example used above, querying 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 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 queries of little cost from being offloaded to HeatWave. For information about the query cost threshold, see Section 2.14, “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 Excluding Table Columns.

Query Runtimes and Estimates

You can view HeatWave query runtimes and runtime estimates 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:
```
CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", TRUE));
```

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

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 2.7.3.3, “Query Insights”.

Using the rpd_query_stats Table

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

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:

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:

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

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 Section 2.6, “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

PREV HOME UP NEXT