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”.
The following prerequisites apply for offloading queries to HeatWave:
The query must be a
SELECT
statement.INSERT ... SELECT
andCREATE TABLE ... SELECT
statements are supported, but only theSELECT
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. Ifautocommit
is disabled, queries are not offloaded and execution is performed on the MySQL DB System. To check theautocommit
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.
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)
Concurrently issued queries are prioritized for execution. For information about query prioritization, see 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”.
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,
JOIN
s, 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.
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
andoptimizer_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, theExtra
column in theEXPLAIN
output shows the following text: “Using secondary engine RAPID
”; otherwise, that text does not appear. The following query example uses theTIMEDIFF()
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;
NoteIf 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 theRapid_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.
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
queriesRuntime 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;
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.
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 theINSERT ... SELECT
statement is subject to the same HeatWave requirements and limitations as regularSELECT
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 theINSERT
table is the same as theSELECT
table because MySQL Server uses a temporary table in this case, which cannot be offloaded.INSERT INTO
statements are not offloaded. Settingsome_view
SELECTuse_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 theuse_secondary_engine
setting.
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