MySQL Autopilot, this is the name of the new component of MySQL HeatWave service – the in-memory query acceleration engine for MySQL Database Service n Oracle Cloud Infrastructure (OCI) – that Oracle announced these days.
MySQL Autopilot: a leap into the future
An innovation that will allow, with advanced automatic learning techniques, to automate and make the use of HeatWave easier and more efficient. Something that, in such an advanced way, is not present in the “database”, without forgetting that the Autopilot functionality will be available at no additional cost to customers who already use MySQL HeatWave.
MySQL Autopilot will then automate many of the most important and challenging aspects of achieving high performance on large-scale queries – including provisioning, data loading, query execution and error handling. Advanced techniques will be used to sample data, collect statistics on data and queries and create machine learning models using Oracle AutoML to model memory use, network load, execution times. These machine learning models are used by MySQL Autopilot to perform its key functionality.
MySQL Autopilot will make the HeatWave Query Optimizer smarter as more queries are run, resulting in continuously improving system performance over time – a capacity not available on Amazon Redshift, Amazon Aurora, Snowflake or other cloud offerings based on MySQL.
MySQL Autopilot: the features offered
- Automatic provisioning predicts the number of HeatWave nodes needed to run a workload through adaptive sampling of data from the tables on which analysis is required. This means that customers no longer need to manually estimate their optimal cluster size. No other database services provide this capability.
- The automatic parallel loading can optimize load time and memory usage, because it predicts the optimal degree of parallelism for each table loaded into HeatWave. No other cloud provider offers this capability.
- Automatic data placement predicts the column on which tables should be partitioned in-memory to help get the best performance for queries. It also predicts the degree of query performance improvement with the new recommended column. This minimizes the movement of data through nodes for suboptimal choices that can be made by operators when manually selecting the column. No other database services provide this capability.
- Automatic coding can determine the optimal representation of the columns that are loaded into HeatWave, taking queries into account. This optimal representation provides the best query performance and minimizes cluster size to minimize costs.
- Automatic improvement of the query plan learns from various query execution statistics to improve your future query execution plan. This increases system performance as more queries are run. No other database services provide this capability.
- Automatic estimation of query time can estimate the execution time of a query before executing it. This provides a prediction of how long a query will take, and allows customers to decide if the query duration is too long and instead run a different query.
- Automatic propagation of changes intelligently determines the optimal time when changes in the MySQL database should be propagated to the data management level with HeatWave Scale-Out. This feature helps ensure that changes are propagated at the right optimal cadence. No other cloud provider offers this capability.
- Automatic scheduling it can determine which queries in the queue are short-term and prioritize them over long-term queries in an intelligent way to reduce the overall wait time. Most other databases use the First In, First Out (FIFO) mechanism for scheduling.
- Automatic error recovery provision new nodes and reload necessary data if one or more HeatWave nodes become unresponsive due to a software or hardware failure.
Oracle’s point of view
“MySQL Database Service with HeatWave is the only MySQL database that efficiently supports both OLTP and OLAP, allowing users to run mixed workloads or real-time analytics on their MySQL database with 10 to 1,000 times better and less performance. half the cost of other analytical or MySQL-based databases, ”he said Edward Screven, Chief Corporate Architect, Oracle. “MySQL HeatWave is one of the fastest growing cloud services on Oracle Cloud and an increasing number of customers are moving their MySQL workloads to HeatWave. The series of innovations announced are the result of Oracle’s years of research and development, and their combination offers huge improvements in automation, performance and cost, further distancing HeatWave from other cloud services. “
The introduction of MySQL Scale-out Data Management
As part of the announcement, Oracle also introduced MySQL Scale-out Data Management, which improves data reload performance in HeatWave up to 100 times. HeatWave now supports a cluster of 64 nodes – up from 24 previously available – and is capable of processing up to 32TB of data – previously 12TB.
Scalability is further improved to nearly 0.9 – and 1.0 is a perfect scaling factor. These new improvements further reinforce HeatWave’s price / performance advantages over its main competitors. HeatWave offers better performance at a lower price for analytical and mixed workloads than all other competitive analytics and database cloud services. In particular, according to the tests performed, HeatWave offers a price / performance ratio:
- 13 times better than Amazon Redshift AQUA – 6.5 times faster, at half the cost (TPC-H 10TB);
- 35 times better than Snowflake – 7 times faster at one fifth the cost (TPC-H10TB);
- 36 times better than Google Big Query – 9 times faster at a quarter the cost (TPC-H 30TB);
- 15 times better than Azure Synapse – 3 times faster, at one fifth the cost (TPC – H 3’TB)
- 42x better than Amazon Aurora for mixed workloads – with 18x lower latency and 110x higher throughput, at 42% cost (CH-benCHmark 100G)
Oracle also makes the benchmark code publicly available for customers to run the benchmarks by visiting this site. Oracle also announced that the TPC-DS industry standard benchmark can now be accelerated using HeatWave.