1.5 MySQL Autopilot

MySQL 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 MySQL Autopilot to execute its core capabilities. MySQL 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. For HeatWave on OCI, see HeatWave Cluster Size Estimates. For MySQL HeatWave on AWS, see Estimating Cluster Size with MySQL Autopilot. For HeatWave in Oracle Database Service for Azure (ODSA), see the Provisioning HeatWave Nodes.

• Auto Shape Prediction

  For MySQL HeatWave on AWS, the Auto Shape Prediction feature in MySQL 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.

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 Encoding

  Determines the optimal encoding for string column data, which minimizes the required cluster size and improves query performance. See Section 2.8.2, “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.8.3, “Auto Data Placement”.

Query Execution

• Auto Query Plan Improvement

  Uses statistics from previously executed queries to improve future query execution plans. See Section 2.3.4, “Auto Query Plan Improvement”.

• Auto Query Time Estimation

  Estimates query execution time, allowing you to determine how a query might perform without having to run the query. Runtime estimates are provided by the Advisor Query Insights feature. See Section 2.8.4, “Query Insights”.

• Auto Change Propagation

  For HeatWave on OCI, 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.

• 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

  Queues incoming transactions to give 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

  On Oracle Cloud Infrastructure (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.

  For MySQL 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.