Documentation Home
MySQL HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.6Mb
PDF (A4) - 1.6Mb


MySQL HeatWave User Guide  /  ...  /  Debugging Queries

2.3.5 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.14, “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”.