HeatWave User Guide  /  Overview  /  HeatWave Autopilot

1.6 HeatWave Autopilot

HeatWave Autopilot automates many of the most important and often challenging aspects of achieving exceptional query performance at scale, including cluster provisioning, loading data, query processing, and failure handling. It uses advanced techniques to sample data, collect statistics on data and queries, and build machine learning models to model memory usage, network load, and execution time. The machine learning models are used by HeatWave Autopilot to execute its core capabilities. HeatWave Autopilot makes the HeatWave query optimizer increasingly intelligent as more queries are executed, resulting in continually improving system performance.

System Setup

  • Auto Provisioning

    Estimates the number of HeatWave nodes required by sampling the data, which means that manual cluster size estimations are not necessary.

  • Auto Shape Prediction

    For HeatWave on AWS, the Auto Shape Prediction feature in HeatWave Autopilot uses MySQL statistics for the workload to assess the suitability of the current shape. Auto Shape Prediction provides prompts to upsize the shape and improve system performance, or to downsize the shape if the system is under-utilized. See: Autopilot Shape Advisor in the HeatWave on AWS Service Guide.

Data Load

  • Auto Parallel Load

    Optimizes load time and memory usage by predicting the optimal degree of parallelism for each table loaded into HeatWave. See: Section 2.2.3, “Loading Data Using Auto Parallel Load”.

  • Auto Schema Inference

    Lakehouse Auto Parallel Load extends Auto Parallel Load with Auto Schema Inference that can analyze the data, infer the external table structure, and create the database and all tables. It can also use header information from the external files to define the column names. See: Section 5.2.4.1, “Lakehouse Auto Parallel Load Schema Inference”.

  • Autopilot Indexing

    Autopilot Indexing can make secondary index suggestions to improve workload performance. See: Section 2.8.1, “Autopilot Indexing”

  • Auto Encoding

    Determines the optimal encoding for string column data, which minimizes the required cluster size and improves query performance. See: Section 2.7.4, “Auto Encoding”.

  • Auto Data Placement

    Recommends how tables should be partitioned in memory to achieve the best query performance, and estimates the expected performance improvement. See: Section 2.7.5, “Auto Data Placement”.

  • Auto Data Compression

    HeatWave and HeatWave Lakehouse can compress data stored in memory using different compression algorithms. To minimize memory usage while providing the best query performance, auto compression dynamically determines the compression algorithm to use for each column based on its data characteristics. Auto compression employs an adaptive sampling technique during the data loading process, and automatically selects the optimal compression algorithm without user intervention. Algorithm selection is based on the compression ratio and the compression and decompression rates, which balance the memory needed to store the data in HeatWave with query execution time. See: Section 2.2.6, “Data Compression”.

  • Unload Advisor

    Unload Advisor can recommend tables to unload to reduce HeatWave memory usage. See: Section 2.7.7, “Unload Advisor”.

  • Auto Unload

    Auto Unload can automate the process of unloading data from HeatWave. See: Section 2.5.3, “Unloading Data Using Auto Unload”.

Query Execution

  • Auto Query Plan Improvement

    Collects previously executed queries and uses them to improve future query execution plans. See: Section 2.3.4, “Auto Query Plan Improvement”.

  • Adaptive Query Execution

    Adaptive query optimization automatically improves query performance and memory consumption, and mitigates skew-related performance issues as well as out of memory errors. It uses various statistics to adjust data structures and system resources after query execution has started. It independently optimizes query execution for each HeatWave node based on actual data distribution at runtime. This helps improve the performance of ad hoc queries by up to 25%. The HeatWave optimizer generates a physical query plan based on statistics collected by Autopilot. During query execution, each HeatWave node executes the same query plan. With adaptive query execution, each individual HeatWave node adjusts the local query plan based on statistics such as cardinality and distinct value counts of intermediate relations collected locally in real-time. This allows each HeatWave node to tailor the data structures that it needs, resulting in better query execution time, lower memory usage, and improved data skew-related performance.

  • Auto Query Time Estimation

    Estimates query execution time to determine how a query might perform without having to run the query. See: Section 2.7.6, “Auto Query Time Estimation”.

  • Auto Change Propagation

    Auto Change Propagation intelligently determines the optimal time when changes to data on the MySQL DB System should be propagated to the HeatWave Storage Layer. Only available for HeatWave on OCI.

  • Auto Scheduling

    Prioritizes queries in an intelligent way to reduce overall query execution wait times. See: Section 2.3.3, “Auto Scheduling”.

  • Auto Thread Pooling

    Provides sustained throughput during high transaction concurrency. Where multiple clients are running queries concurrently, Auto Thread Pooling applies workload-aware admission control to eliminate resource contention caused by too many waiting transactions. Auto Thread Pooling automatically manages the settings for the thread pool control variables thread_pool_size, thread_pool_max_transactions_limit, and thread_pool_query_threads_per_group. For details of how the thread pool works, see Thread Pool Operation.

Failure Handling

  • Auto Error Recovery

    For HeatWave on OCI, Auto Error Recovery recovers a failed node or provisions a new one and reloads data from the HeatWave storage layer when a HeatWave node becomes unresponsive due to a software or hardware failure. See: HeatWave Cluster Failure and Recovery in the HeatWave on OCI Service Guide.

    For HeatWave on AWS, Auto Error Recovery recovers a failed node and reloads data from the MySQL DB System when a HeatWave node becomes unresponsive due to a software failure.