2.7 Workload Optimization for OLAP

2.7.1 Encoding String Columns

2.7.2 Defining Data Placement Keys

2.7.3 HeatWave Autopilot Advisor Syntax

2.7.4 Auto Encoding

2.7.5 Auto Data Placement

2.7.6 Auto Query Time Estimation

2.7.7 Unload Advisor

2.7.8 Advisor Command-line Help

2.7.9 Autopilot Report Table

2.7.10 Advisor Report Table

Workload optimization for online analytical processing, OLAP, includes using dictionary encoding for certain string columns and defining data placement keys to optimize for JOIN and GROUP BY query performance. Apply these optimizations manually, or use HeatWave Autopilot Advisor, which includes the following:

• Auto Encoding recommends string column encodings that minimize the required cluster size and improve query performance.

• Auto Data Placement recommends data placement keys that optimize JOIN and GROUP BY query performance.

• Auto Query Time Estimation 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.

• 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.

To run Advisor for OLAP workloads, 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.