HeatWave

Oracle MySQL Autopilot: machine-learning–based automation

MySQL Autopilot automates many of the most important and often challenging aspects of achieving high query performance at scale—including provisioning, data loading, query execution, 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. These machine-learning models are then 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 over time—a capability not available on Amazon Aurora, Amazon Redshift, Snowflake, or other MySQL-based database services. MySQL Autopilot is available at no additional charge for MySQL HeatWave customers.

MySQL Autopilot provides the following capabilities:

• Auto provisioning predicts the number of HeatWave nodes required for running a workload by adaptive sampling of table data on which analytics is required. This means that customers no longer need to manually estimate the optimal size of their cluster. No other database service provides this capability.
• Auto parallel load optimizes the load time and memory usage by predicting the optimal degree of parallelism for each table being loaded into HeatWave. No other cloud vendor offers this capability.
• Auto data placement predicts the column on which tables should be partitioned in-memory to achieve the best performance for queries. It also predicts the expected gain in query performance with the new column recommendation. This minimizes data movement across nodes due to suboptimal choices that can be made by operators when manually selecting the column. No other database service provides this rich capability.
• Auto query plan improvement learns various statistics from the execution of queries and improves the execution plan of future queries. This improves the performance of the system as more queries are run. No other database service provides this capability.
• Auto encoding determines the optimal representation of columns being loaded into HeatWave taking the queries into consideration. This optimal representation provides the best query performance and minimizes the size of the cluster to minimize the cost.
• Auto query time estimation estimates the execution time of a query prior to executing the query. For customers, this provides a prediction of how long a query will take—enabling them to decide if the duration of the query is too long and to instead run a different query.
• Auto change propagation intelligently determines the optimal time when changes in MySQL Database should be propagated to the HeatWave scale-out data management layer. This ensures that changes are propagated at the right optimal cadence. No other cloud vendor offers this capability.
• Auto scheduling determines which queries in the queue are short running and prioritizes them over long running queries in an intelligent way to reduce overall wait time. Most other databases use the First In, First Out (FIFO) mechanism for scheduling.
• Auto error recovery provisions new nodes and reloads necessary data if one or more HeatWave nodes is unresponsive due to software or hardware failure.

Scale-out data management

100X faster recovery

When data is loaded from MySQL into HeatWave, a copy of the in-memory representation is made to the scale-out data management layer built on the OCI object store. Any changes made to data in MySQL are transparently propagated to this data layer. When an operation requires reloading of data to HeatWave such as during error recovery, data can be accessed from the HeatWave data layer, in parallel, by multiple HeatWave nodes. This results in significant improvement in performance. For example, for a 10 TB HeatWave cluster, the time it takes to recover and reload data reduces from 7.5 hours to 4 minutes—over a 100X improvement.

Faster than Amazon and Snowflake at a fraction of the cost

MySQL HeatWave is faster than all the competing database services— and at a fraction of the cost. This has been demonstrated by multiple standard industry benchmarks like TPCH, TPCDS, CH-benCHmark—and on real-world customer workloads. See the 30 TB TPCH benchmark results shown above. Results from other benchmarks are here:

Faster analytics than Snowflake, Amazon Redshift, Amazon Aurora, Amazon RDS, Google BigQuery, and Azure Synapse at a fraction of the cost

• 6.8X faster than Snowflake on AWS at one-fifth the cost, resulting in 35X better price performance (10 TB TPCH)
• 6.8X faster than Amazon Redshift with AQUA at half the cost, resulting in 13X better price performance (10 TB TPCH)
• 9X faster than Google BigQuery at one-fourth the cost, resulting in 36 times better price/performance (30 TB TPCH)
• 3 times faster than Azure Synapse at one-fifth the cost, resulting in 15X better price performance (30 TB TPCH)
• 1400X faster than Amazon Aurora at half the cost, resulting in 2800X better price performance (4 TB TPCH)
• 5400X faster than Amazon RDS at two-thirds the cost, resulting in 8100X better price performance

Faster mixed workload than Amazon Aurora at a fraction of the cost

Most real world applications have a mix of OLTP and complex OLAP queries. For such workloads too, MySQL HeatWave is much faster and costs a fraction of Amazon Aurora. Using industry standard CH-benCHmark on a 100 GB dataset for OLAP queries, HeatWave is 18X faster, provides 110X better throughput, and 2.4X less cost than Amazon Aurora, resulting in 42X better price performance. For OLTP queries, HeatWave has the same performance as Amazon Aurora and is 2.4X less cost, resulting in 2.4X better price performance.

• See the benchmark details