HeatWave Release Notes
This topic describes how you can view MySQL HeatWave query runtimes and
runtime estimates using the
MySQL HeatWave Autopilot
Advisor Auto Query Time Estimation feature, or by
querying the
performance_schema.rpd_query_stats table.
Runtime data is useful for query optimization, troubleshooting, and estimating the cost of running a particular query or workload.
MySQL HeatWave query runtime data includes:
Runtimes for successfully executed queries
Runtime estimates for
EXPLAINqueriesRuntime estimates for queries cancelled using
Ctrl+CRuntime estimates for queries that fail due to an out-of-memory error
Runtime data is available for queries in the MySQL HeatWave query history, which is a non-persistent store of information about the last 1000 executed queries.
Review the requirements.
Verify if the query is offloaded to MySQL HeatWave for processing.
The examples in this topic use the sample database
airportdb. To learn how to download the sample database, see AirportDB Analytics Quickstart.
Perform the following steps to view runtime data using Autopilot Advisor Auto Query Time Estimation:
-
To view runtime data for all queries in the MySQL HeatWave history:
mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", true)); +-------------------------------+ | INITIALIZING HEATWAVE ADVISOR | +-------------------------------+ | Version: 4.15 | | | | Output Mode: normal | | Excluded Queries: 0 | | Target Schemas: All | | | +-------------------------------+ 6 rows in set (0.2571 sec) +---------------------------------------------------------+ | ANALYZING LOADED DATA | +---------------------------------------------------------+ | Total 14 tables loaded in HeatWave for 1 schemas | | Tables excluded by user: 0 (within target schemas) | | | | SCHEMA TABLES COLUMNS | | NAME LOADED LOADED | | ------ ------ ------ | | `airportdb` 14 106 | | | +---------------------------------------------------------+ 8 rows in set (0.2571 sec) +--------------------------------------------------------------------------------------------------------------------+ | QUERY INSIGHTS | +--------------------------------------------------------------------------------------------------------------------+ | Queries executed on Heatwave: 8 | | Session IDs (as filter): None | | | | QUERY-ID SESSION-ID QUERY-STRING EXEC-RUNTIME (s) COMMENT | | -------- ---------- ------------ ---------------- ------- | | 11 20083 select count(*) from flight 0.010 | | 12 20083 select count(*) from flight 0.007 | | 13 20083 select count(*) from flight 0.005 | | 14 53204 SELECT airline_id, COUNT(*) AS flight_id FROM flig... 0.000 (est.) Explain. | | 15 53204 SELECT airline_id, COUNT(*) AS flight_id FROM flig... 0.022 | | 16 53204 SELECT airline_id, COUNT(*) AS flight_id FROM flig... 0.000 (est.) Explain. | | 17 53204 SELECT airline_id, COUNT(*) AS flight_id FROM flig... 0.007 | | 18 53204 SELECT airline_id, COUNT(*) AS flight_id FROM flig... 0.006 | | | | TOTAL ESTIMATED: 2 EXEC-RUNTIME: 0.000 sec | | TOTAL EXECUTED: 6 EXEC-RUNTIME: 0.057 sec | | | | | | Retrieve detailed query statistics using the query below: | | SELECT log FROM sys.heatwave_autopilot_report WHERE stage = "QUERY_INSIGHTS" AND type = "info"; | | | +--------------------------------------------------------------------------------------------------------------------+ 21 rows in set (0.2571 sec) Query OK, 0 rows affected (0.2571 sec) -
To view runtime data for queries executed by the current session only:
mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", true, "query_session_id", JSON_ARRAY(connection_id()))); +-------------------------------+ | INITIALIZING HEATWAVE ADVISOR | +-------------------------------+ | Version: 4.15 | | | | Output Mode: normal | | Excluded Queries: 0 | | Target Schemas: All | | | +-------------------------------+ 6 rows in set (0.2464 sec) +---------------------------------------------------------+ | ANALYZING LOADED DATA | +---------------------------------------------------------+ | Total 14 tables loaded in HeatWave for 1 schemas | | Tables excluded by user: 0 (within target schemas) | | | | SCHEMA TABLES COLUMNS | | NAME LOADED LOADED | | ------ ------ ------ | | `airportdb` 14 106 | | | +---------------------------------------------------------+ 8 rows in set (0.2464 sec) +--------------------------------------------------------------------------------------------------------------------+ | QUERY INSIGHTS | +--------------------------------------------------------------------------------------------------------------------+ | Queries executed on Heatwave: 0 | | Session IDs (as filter): [56742] | | | | QUERY-ID SESSION-ID QUERY-STRING EXEC-RUNTIME (s) COMMENT | | -------- ---------- ------------ ---------------- ------- | | | | TOTAL ESTIMATED: 0 EXEC-RUNTIME: 0.000 sec | | TOTAL EXECUTED: 0 EXEC-RUNTIME: 0.000 sec | | | | | | Retrieve detailed query statistics using the query below: | | SELECT log FROM sys.heatwave_autopilot_report WHERE stage = "QUERY_INSIGHTS" AND type = "info"; | | | +--------------------------------------------------------------------------------------------------------------------+ 13 rows in set (0.2464 sec)
Perform the following steps to view runtime data using the
performance_schema.rpd_query_stats
table:
-
To view runtime data for all queries in the MySQL HeatWave query history:
mysql> SELECT query_id, JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id, JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.accumulatedRapidCost'),'$[0]') AS time_in_ns, JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message FROM performance_schema.rpd_query_stats; +----------+------------+------------+---------------+ | query_id | session_id | time_in_ns | error_message | +----------+------------+------------+---------------+ | 11 | 20083 | NULL | "" | | 12 | 20083 | NULL | "" | | 13 | 20083 | NULL | "" | | 14 | 53204 | NULL | NULL | | 15 | 53204 | NULL | "" | | 16 | 53204 | NULL | NULL | | 17 | 53204 | NULL | "" | | 18 | 53204 | NULL | "" | +----------+------------+------------+---------------+ 8 rows in set (0.2328 sec) -
To view runtime data for a particular MySQL HeatWave query, filtered by query ID:
mysql> SELECT query_id, JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id, JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.accumulatedRapidCost'),'$[0]') AS time_in_ns, JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message FROM performance_schema.rpd_query_stats WHERE query_id = 1; -
To view the query ID by querying
performance_schema.rpd_query_statstable:mysql> SELECT query_id, LEFT(query_text,160) FROM performance_schema.rpd_query_stats; +----------+--------------------------------------------------+ | query_id | LEFT(query_text,160) | +----------+--------------------------------------------------+ | 11 | select count(*) from flight | | 12 | select count(*) from flight | | 13 | select count(*) from flight | | 14 | EXPLAIN SELECT airline_id, COUNT(*) AS flight_id | | FROM flight | | WHERE departure <= DATE '2015-06-09' | | GROUP BY airline_id | | ORDER BY airline_id | | 15 | SELECT airline_id, COUNT(*) AS flight_id | | FROM flight | | WHERE departure <= DATE '2015-06-09' | | GROUP BY airline_id | | ORDER BY airline_id | | 16 | EXPLAIN SELECT airline_id, COUNT(*) AS flight_id | | FROM flight | | WHERE flight_id <= 66810 | | GROUP BY airline_id | | ORDER BY airline_id | | 17 | SELECT airline_id, COUNT(*) AS flight_id | | FROM flight | | WHERE flight_id <= 66810 | | GROUP BY airline_id | | ORDER BY airline_id | | 18 | SELECT airline_id, COUNT(*) AS flight_id | | FROM flight | | WHERE flight_id <= 66810 | | GROUP BY airline_id | | ORDER BY airline_id | +----------+--------------------------------------------------+
Learn how to diagnose query offload errors.