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


HeatWave User Guide  /  ...  /  Debugging Queries

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:

    Press CTRL+C to copy
    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:

    Press CTRL+C to copy
    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:

    Press CTRL+C to copy
    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:

    Press CTRL+C to copy
    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:

    Press CTRL+C to copy
    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:

Press CTRL+C to copy
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:

Press CTRL+C to copy
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:

Press CTRL+C to copy
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”.