HeatWave Release Notes
This topic describes how to debug queries that fail to offload to MySQL HeatWave for execution.
Review the requirements.
The examples in this topic use the sample database
airportdb. To learn how to download the sample database, see AirportDB Analytics Quickstart.
By default, SELECT queries are
offloaded to MySQL HeatWave for execution and fall back to the
DB System if that is not possible. To force a query to execute
on MySQL HeatWave or fail if that is not possible, set the
use_secondary_engine variable
to FORCED. In this mode, a
SELECT statement returns an
error if it cannot be offloaded. The
use_secondary_engine variable
can be set as shown:
Using a SET statement before
running queries:
mysql> SET SESSION use_secondary_engine = FORCED;
Using a SET_VAR optimizer
hint when issuing a query:
mysql> SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ ... FROM ...
If a query fails to offload and you cannot identify the
reason, enable tracing and query the
INFORMATION_SCHEMA.OPTIMIZER_TRACE
table to debug the query.
As of MySQL version 9.0.0, the optimizer trace also includes reasons for not offloading a query due to dynamic offload cost calculation.
-
To enable MySQL optimizer trace, set the
optimizer_traceandoptimizer_trace_offsetvariables as shown:mysql> SET SESSION optimizer_trace="enabled=on"; mysql> SET optimizer_trace_offset=-2;NoteIf you encounter the following error:, when executing long queries
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Missing a name for object member." at position 1038459To avoid this error, increase the memory allocated for the optimizer trace, before enabling the trace by executing:
SET optimizer_trace_max_mem_size = 1024000000; -
Issue the problematic query using
EXPLAIN. If the query is supported by MySQL HeatWave, theExtracolumn in theEXPLAINoutput shows the following text:Using secondary engine RAPID; otherwise, that text does not appear.The following query example uses
INTERVAL()function to calculate and display the time interval into which the departure hour falls, based on the specified thresholds (6, 12, 18, 24). by MySQL HeatWave:This query is not offloaded to MySQL HeatWave.
mysql> EXPLAIN SELECT flightno, INTERVAL(HOUR(departure),6,12,18,24) FROM flightschedule\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: flightschedule partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 9828 filtered: 100 Extra: NULL 1 row in set, 1 warning (0.2389 sec)Execute the following
SELECTstatements to detect the error:mysql> SELECT QUERY, TRACE->'$**.Rapid_Offload_Fails' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: EXPLAIN SELECT flightno, INTERVAL(HOUR(departure),6,12,18,24) FROM flightschedule TRACE->'$**.Rapid_Offload_Fails': [{"Reason": "HW_ER_1014: The function, [INTERVAL(HOUR(departure),6,12,18,24)], is not supported."}, {"Reason": "HW_ER_1160: Expr, [INTERVAL(HOUR(departure),6,12,18,24)], is not supported."}] 1 row in set (0.2385 sec)mysql> SELECT QUERY, TRACE->'$**.secondary_engine_not_used' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: EXPLAIN SELECT flightno, INTERVAL(HOUR(departure),6,12,18,24) FROM flightschedule TRACE->'$**.secondary_engine_not_used': NULL 1 row in set (0.2385 sec)In order to offload this query, replace the
INTERVAL()function with multiple IF().mysql> EXPLAIN SELECT flightno, IF(HOUR(departure)<6,0,IF(HOUR(departure)<12,1,IF(HOUR(departure)<18,2,3))) FROM flightschedule\G *************************** 1. row *************************** id: 1 select_type: NONE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Using secondary engine RAPID. Use EXPLAIN FORMAT=TREE to show the plan. -
Query the
INFORMATION_SCHEMA.OPTIMIZER_TRACEtable for a failure reason. There are two trace markers for queries that fail to offload:Rapid_Offload_Failssecondary_engine_not_used
To query for the
Rapid_Offload_Failstrace marker, issue this query:mysql> SELECT QUERY, TRACE->'$**.Rapid_Offload_Fails' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;To query for the
secondary_engine_not_usedtrace marker, issue this query:mysql> 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.
-
If an
INFORMATION_SCHEMA.OPTIMIZER_TRACEquery trace indicates that a subquery is not yet supported, try unnesting the subquery. For example, the following query contains a subquery and is not offloaded as indicated by theEXPLAINoutput, which does not showUsing secondary engine.mysql> EXPLAIN SELECT COUNT(*) FROM orders o WHERE o_totalprice> (SELECT AVG(o_totalprice) FROM orders WHERE o_custkey=o.o_custkey); *************************** 1. row *************************** id: 1 select_type: PRIMARY table: o partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 14862970 filtered: 100.00 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: orders partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 14862970 filtered: 10.00 Extra: Using where 2 rows in set, 2 warnings (0.00 sec)Re-write as follows to unnest the subquery so that it can be offloaded.
mysql> EXPLAIN SELECT COUNT(*) FROM orders o, (SELECT o_custkey, AVG(o_totalprice) a_totalprice FROM orders GROUP BY o_custkey)a WHERE o.o_custkey=a.o_custkey AND o.o_totalprice>a.a_totalprice;
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:
mysql> [{"Reason": "Currently unsupported RAPID query compilation scenario"}]For a query that does not meet the query cost threshold for MySQL HeatWave, the following reason is reported, and the query is not offloaded:
mysql> [{"Reason": "The estimated query cost does not exceed secondary_engine_cost_threshold."}]If query does not offload due to Dynamic Offload (newer alternative to cost threshold), the following message is displayed.
mysql> [{message : "HeatWave dynamic threshold rejects query"}]The query cost threshold prevents queries of little cost from being offloaded to MySQL HeatWave. For information about the query cost threshold, see Section 11.1.1, “MySQL HeatWave Issues”.
To execute such queries using the MySQL HeatWave secondary engine,
set the use_secondary_engine system
variable to FORCED state using
the following syntax options. By default, this variable is set
to ON.
mysql> SET SESSION use_secondary_engine = 'FORCED';
Query OK, 0 rows affected (0.3451 sec)
For a query that attempts to access a column defined as
NOT SECONDARY, the following reason is
reported:
mysql> [{"Reason": "Column risk_assessment is marked as NOT SECONDARY."}]
Columns defined as NOT SECONDARY are
excluded when a table is loaded into MySQL HeatWave. See
Section 4.2.6.1, “Exclude Table Columns”.
If you encounter out-of-memory errors when running queries:
-
Avoid or rewrite queries that produce a Cartesian product. In the following query, a
JOINpredicated is not defined between thesupplierandnationtables, which causes the query to select all rows from both tables:mysql> SELECT s_nationkey, s_suppkey, l_comment FROM lineitem, supplier, nation WHERE s_suppkey = l_suppkey LIMIT 10; ERROR 3015 (HY000): Out of memory in storage engine 'Failure detected in RAPID; query execution cannot proceed'.To avoid the Cartesian product, add a relevant predicate between the
supplierandnationtables to filter out rows:mysql> SELECT s_nationkey, s_suppkey, l_comment FROM lineitem, supplier, nation WHERE s_nationkey = n_nationkey AND s_suppkey = l_suppkey LIMIT 10; -
Avoid or rewrite queries that produce a Cartesian product introduced by the MySQL optimizer. Due to lack of quality statistics or non-optimal cost decisions, MySQL optimizer may introduce one or more Cartesian products in a query even if a query has predicates defined among all participating tables. For example:
mysql> SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment FROM lineitem, supplier, nation, customer, orders WHERE c_custkey = o_custkey AND o_orderkey = l_orderkey AND c_nationkey = s_nationkey AND c_nationkey = n_nationkey AND c_custkey < 3000000 LIMIT 10; ERROR 3015 (HY000): Out of memory in storage engine 'Failure detected in RAPID; query execution cannot proceed'.The
EXPLAINplan output shows that there is no common predicate between the first two table entries (NATIONandSUPPLIER).mysql> EXPLAIN SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment FROM lineitem, supplier, nation, customer, orders WHERE c_custkey = o_custkey AND o_orderkey = l_orderkey AND c_nationkey = s_nationkey AND c_nationkey = n_nationkey AND c_custkey < 3000000 LIMIT 10; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: supplier partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 99626 filtered: 100.00 Extra: Using secondary engine RAPID *************************** 2. row *************************** id: 1 select_type: SIMPLE table: nation partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 25 filtered: 10.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID *************************** 3. row *************************** id: 1 select_type: SIMPLE table: customer partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1382274 filtered: 5.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID *************************** 4. 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: 10.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID *************************** 5. row *************************** id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 56834662 filtered: 10.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPIDTo force a join order so that there are predicates associated with each pair of tables, add a
STRAIGHT_JOINhint. For example:mysql> EXPLAIN SELECT o_orderkey, c_custkey, l_shipdate, s_nationkey, s_suppkey, l_comment FROM SUPPLIER STRAIGHT_JOIN CUSTOMER STRAIGHT_JOIN NATION STRAIGHT_JOIN ORDERS STRAIGHT_JOIN LINEITEM WHERE c_custkey = o_custkey and o_orderkey = l_orderkey AND c_nationkey = s_nationkey AND c_nationkey = n_nationkey AND c_custkey < 3000000 LIMIT 10; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: supplier partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 99626 filtered: 100.00 Extra: Using secondary engine RAPID *************************** 2. row *************************** id: 1 select_type: SIMPLE table: customer partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1382274 filtered: 5.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID *************************** 3. row *************************** id: 1 select_type: SIMPLE table: nation partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 25 filtered: 10.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID *************************** 4. 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: 10.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID *************************** 5. row *************************** id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 56834662 filtered: 10.00 Extra: Using where; Using join buffer (hash join); Using secondary engine RAPID Avoid or rewrite queries that produce a very large result set. This is a common cause of out of memory errors during query processing. Use aggregation functions, a
GROUP BYclause, or aLIMITclause to reduce the result set size.Avoid or rewrite queries that produce a very large intermediate result set. In certain cases, large result sets can be avoided by adding a
STRAIGHT_JOINhint, which enforces a join order in a decreasing of selectiveness.-
Check the size of your data by performing a cluster size estimate. If your data has grown substantially, the MySQL HeatWave Cluster may require additional nodes.
For MySQL HeatWave on OCI, see Generating a Node Count Estimate in the MySQL HeatWave on OCI Service Guide.
For MySQL HeatWave on AWS, see Estimating Cluster Size with MySQL HeatWave Autopilot in the MySQL HeatWave on AWS Service Guide.
For MySQL HeatWave for Azure, see Provisioning MySQL HeatWave Nodes in the MySQL HeatWave for Azure Service Guide.
-
MySQL HeatWave optimizes for network usage by default. Try running the query with the
MIN_MEM_CONSUMPTIONstrategy by setting by settingrapid_execution_strategytoMIN_MEM_CONSUMPTION. Therapid_execution_strategyvariable can be set as shown:-
Using a
SETstatement before running queries:mysql> SET SESSION rapid_execution_strategy = MIN_MEM_CONSUMPTION; -
Using a
SET_VARoptimizer hint when issuing a query:mysql> SELECT /*+ SET_VAR(rapid_execution_strategy = MIN_MEM_CONSUMPTION) */ ... FROM ... Unloading tables that are not used. These tables consume memory on MySQL HeatWave nodes unnecessarily. See Section 4.4.2, “Unload Data Manually”.
Excluding table columns that are not accessed by your queries. These columns consume memory on MySQL HeatWave nodes unnecessarily. This strategy requires reloading data. See Section 4.2.6.1, “Exclude Table Columns”.
-
After running queries, consider using MySQL HeatWave Autopilot Advisor for encoding and data placement recommendations. See Section 5.8, “Optimize Workloads for OLAP”.
Learn about Auto Scheduling.