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;

ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: "Missing a name for object member." at position 1038459

SET optimizer_trace_max_mem_size = 1024000000;

Issue the problematic query using EXPLAIN. If the query is supported by MySQL 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 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 SELECT statements 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_TRACE table for a failure reason. There are two trace markers for queries that fail to offload:

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;

If an INFORMATION_SCHEMA.OPTIMIZER_TRACE query 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 the EXPLAIN output, which does not show Using 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;

Avoid or rewrite queries that produce a Cartesian product. In the following query, a JOIN predicated is not defined between the supplier and nation tables, 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 supplier and nation tables 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 EXPLAIN plan output shows that there is no common predicate between the first two table entries (NATION and SUPPLIER).

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 RAPID

To force a join order so that there are predicates associated with each pair of tables, add a STRAIGHT_JOIN hint. 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 BY clause, or a LIMIT clause 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_JOIN hint, 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.

MySQL HeatWave optimizes for network usage by default. Try running the query with the MIN_MEM_CONSUMPTION strategy by setting by setting rapid_execution_strategy to MIN_MEM_CONSUMPTION. The rapid_execution_strategy variable can be set as shown:

After running queries, consider using MySQL HeatWave Autopilot Advisor for encoding and data placement recommendations. See Section 5.8, “Optimize Workloads for OLAP”.