2.8 Workload Optimization using Advisor

2.8.1 Auto Encoding

2.8.2 Auto Data Placement

2.8.3 Query Insights

2.8.4 Advisor Examples

2.8.5 Advisor Report Table

This section describes the Advisor utility, which provides the following optimization capabilities:

• Auto Encoding

  Recommends string column encodings that minimize the required cluster size and improve query performance. See Section 2.8.1, “Auto Encoding”.

• Auto Data Placement

  Recommends data placement keys for optimizing JOIN and GROUP BY query performance. See Section 2.8.2, “Auto Data Placement”.

• Query Insights

  Provides runtimes for successfully executed queries and runtime estimates for EXPLAIN queries, queries cancelled using Ctrl+C, and queries that fail due to out of memory errors. Runtime data is useful for query optimization, troubleshooting, and estimating the cost of running a particular query or workload. See Section 2.8.3, “Query Insights”.

Advisor is workload-aware and provides recommendations based on machine learning models, data analysis, and HeatWave query history. Advisor analyzes the last 1000 successfully executed HeatWave queries.

Advisor is implemented as a stored procedure named heatwave_advisor, which resides in the MySQL sys schema. Running Advisor involves issuing a CALL statement for the stored procedure with optional arguments.

mysql> CALL sys.heatwave_advisor (options);

Issue the following statement to view Advisor command-line help:

mysql> CALL sys.heatwave_advisor(JSON_OBJECT("output","help"));

Advisor can be run from any MySQL client or connector.

Advisor requirements:

• To run Advisor, the HeatWave Cluster must be active.

• The user must have the following MySQL privileges:

  • The PROCESS privilege.

  • The EXECUTE privilege on the sys schema.

  • The SELECT privilege on the Performance Schema.