2.7.9 Autopilot Report Table

MySQL 8.0.32 deprecates the heatwave_advisor_report and heatwave_load_report tables, and replaces them with the heatwave_autopilot_report table in the sys schema. A future release will remove them.

When Advisor or Auto Parallel Load run, they send detailed output to the heatwave_autopilot_report table in the sys schema. This includes execution logs and generated load scripts.

The heatwave_autopilot_report table is a temporary table. It contains data from the last execution of Advisor or Auto Parallel Load. Data is only available for the current session and is lost when the session terminates or when the server shuts down.

Autopilot Report Table Query Examples

Query the heatwave_autopilot_report table after calls to Advisor or Auto Parallel Load, as in the following examples:

• View warning information:

  mysql> SELECT log FROM sys.heatwave_autopilot_report WHERE type="warn";

• View error information if Advisor or Auto Parallel Load stop unexpectedly:

  mysql> SELECT log FROM sys.heatwave_autopilot_report WHERE type="error";

• View the generated DDL statements for Advisor recommendations, or to see the commands that would be executed by Auto Parallel Load in normal mode:

  mysql> SELECT log->>"$.sql" AS "SQL Script" 
            FROM sys.heatwave_autopilot_report 
            WHERE type = "sql" 
            ORDER BY id;

• Concatenate Advisor or Auto Parallel Load generated DDL statements into a single string to copy and paste for execution. The group_concat_max_len variable sets the result length in bytes for the GROUP_CONCAT() function to accommodate a potentially long string. The default group_concat_max_len setting is 1024 bytes.

  mysql> SET SESSION group_concat_max_len = 1000000;
  mysql> SELECT GROUP_CONCAT(log->>"$.sql" SEPARATOR ' ') 
            FROM sys.heatwave_autopilot_report 
            WHERE type = "sql" 
            ORDER BY id;

• For Advisor, retrieve Auto Query Time Estimation data in JSON format:

  mysql> SELECT log 
            FROM sys.heatwave_autopilot_report 
            WHERE stage = "QUERY_INSIGHTS" AND type = "info";

• For Advisor, retrieve Auto Query Time Estimation data in SQL table format:

  mysql> SELECT log->>"$.query_id" AS query_id,
            log->>"$.session_id" AS session_id,
            log->>"$.query_text" AS query_text,
            log->>"$.runtime_estimated_ms" AS runtime_estimated_ms,
            log->>"$.runtime_executed_ms" AS runtime_executed_ms,
            log->>"$.comment" AS comment
            FROM sys.heatwave_autopilot_report 
            WHERE stage = "QUERY_INSIGHTS" AND type = "info"
            ORDER BY id;

• For Auto Parallel Load, view the number of load commands generated:

  mysql> SELECT Count(*) AS "Total Load Commands Generated"
            FROM sys.heatwave_autopilot_report 
            WHERE type = "sql" ORDER BY id;

• For Auto Parallel Load, view load script data for a particular table:

  mysql> SELECT log->>"$.sql"
            FROM sys.heatwave_autopilot_report 
            WHERE type="sql" AND log->>"$.schema_name" = "db0" AND log->>"$.table_name" = "tbl" 
            ORDER BY id;