2.8 Workload Optimization using Advisor

2.8.1 Advisor Syntax

2.8.2 Auto Encoding

2.8.3 Auto Data Placement

2.8.4 Query Insights

2.8.5 Unload Advisor

2.8.6 Advisor Command-line Help

2.8.7 Advisor Report Table

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

• Advisor Auto Encoding

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

• Advisor Auto Data Placement

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

• Advisor Query Insights

  Provides runtime information for successfully executed queries and runtime estimates for EXPLAIN queries, queries cancelled with 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.4, “Query Insights”.

• Unload Advisor

  Recommends tables to unload, that will reduce HeatWave memory usage. The recommendations are based upon when the tables were last queried.

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.

To run Advisor, the HeatWave Cluster must be active, and 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.