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


MySQL HeatWave User Guide  /  ...  /  Running Query Insights

2.8.4.1 Running Query Insights

For Query Insights to provide runtime data, a query history must be available. Query Insights provides runtime data for up to 1000 queries, which is the HeatWave query history limit. To view the current HeatWave query history, query the performance_schema.rpd_query_stats table:

mysql> SELECT query_id, LEFT(query_text,160) 
          FROM performance_schema.rpd_query_stats;

The following example shows how to retrieve runtime data for the entire query history using Query Insights. In this example, there are three queries in the query history: a successfully executed query, a query that failed due to an out of memory error, and a query that was cancelled using Ctrl+C. For an explanation of Query Insights data, see Section 2.8.4.2, “Query Insights Data”.

mysql> CALL sys.heatwave_advisor(JSON_OBJECT('query_insights', true));
+-------------------------------+
| INITIALIZING HEATWAVE ADVISOR |
+-------------------------------+
| Version: 1.12                 |
|                               |
| Output Mode: normal           |
| Excluded Queries: 0           |
| Target Schemas: All           |
|                               |
+-------------------------------+
6 rows in set (0.01 sec)

+---------------------------------------------------------+
| ANALYZING LOADED DATA                                   |
+---------------------------------------------------------+
| Total 8 tables loaded in HeatWave for 1 schemas         |
| Tables excluded by user: 0 (within target schemas)      |
|                                                         |
| SCHEMA                            TABLES        COLUMNS |
| NAME                              LOADED         LOADED |
| ------                            ------         ------ |
| `tpch128`                              8             61 |
|                                                         |
+---------------------------------------------------------+
8 rows in set (0.02 sec)

+-------------------------------------------------------------------------------------+
| QUERY INSIGHTS                                                                      |
+-------------------------------------------------------------------------------------+
| Queries executed on Heatwave: 4                                                     |
| Session IDs (as filter): None                                                       |
|                                                                                     |
| QUERY-ID  SESSION-ID  QUERY-STRING             EXEC-RUNTIME  COMMENT                |
| --------  ----------  ------------             ------------  -------                |
|        1          32  SELECT COUNT(*)                                               |
|                       FROM tpch128.LINEITEM    0.628                                |
|        2          32  SELECT COUNT(*)                                               |
|                       FROM tpch128.ORDERS      0.114 (est.)  Explain.               |
|        3          32  SELECT COUNT(*)                                               |
|                       FROM tpch128.ORDERS,                                          |
|                       tpch128.LINEITEM         5.207 (est.)  Out of memory          |
|                                                              error during           |
|                                                              query execution        |
|                                                              in RAPID.              |
|        4          32  SELECT COUNT(*)                                               |
|                       FROM tpch128.SUPPLIER,                                        |
|                       tpch128.LINEITEM         3.478 (est.)  Operation was          |
|                                                              interrupted by         |
|                                                              the user.              |
| TOTAL ESTIMATED:   3   EXEC-RUNTIME:       8.798 sec                                |
| TOTAL EXECUTED:    1   EXEC-RUNTIME:       0.628 sec                                |
|                                                                                     |
|                                                                                     |
| Retrieve detailed query statistics using the query below:                           |
|     SELECT log FROM sys.heatwave_advisor_report WHERE stage = "QUERY_INSIGHTS" AND  |
|     type = "info";                                                                  |
|                                                                                     |
+-------------------------------------------------------------------------------------+

mysql> SELECT log FROM sys.heatwave_advisor_report
          WHERE stage = "QUERY_INSIGHTS"
          AND type = "info";
+--------------------------------------------------------------------------------------+
| log                                                                                  |
+--------------------------------------------------------------------------------------+
| {"comment": "", "query_id": 1, "query_text": "SELECT COUNT(*) FROM tpch128.LINEITEM",|
|  "session_id": 32, "runtime_executed_ms": 627.6099681854248,                         |
|  "runtime_estimated_ms": 454.398817}                                                 |
|                                                                                      |
| {"comment": "Explain.", "query_id": 2, "query_text": "SELECT COUNT(*)                |
|   FROM tpch128.ORDERS", "session_id": 32, "runtime_executed_ms": null,               |
|  "runtime_estimated_ms": 113.592768}                                                 |
|                                                                                      |
| {"comment": "Out of memory error during query execution in RAPID.", "query_id": 3,   |
|  "query_text": "SELECT COUNT(*) FROM tpch128.ORDERS, tpch128.LINEITEM",              |
|  "session_id": 32, "runtime_executed_ms": null, "runtime_estimated_ms": 5206.80822}  |
|                                                                                      |
| {"comment": "Operation was interrupted by the user.", "query_id": 4,                 |
|  "query_text": "SELECT COUNT(*) FROM tpch128.SUPPLIER, tpch128.LINEITEM",            |
|  "session_id": 32, "runtime_executed_ms": null, "runtime_estimated_ms": 3477.720953} |
+--------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)