2.3.6 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.

1. 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;

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;
   *************************** 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)

3. 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;

   Note

   If the optimizer trace does not return all of the trace information, increase the optimizer trace buffer size. For more information, see Section 2.9.7, “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:

mysql> [{"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:

mysql> [{"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.15, “Troubleshooting”.

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 HeatWave. See Section 2.2.2.1, “Excluding Table Columns”.