MySQL is a popular and powerful open source relational database management system. The inclusion of HeatWave in MySQL has enhanced its capabilities multifold. Read on to know more.
Oracle MySQL HeatWave (formerly called MySQL database service analytics) is an in-memory query accelerator developed at Oracle Labs, and provisions HeatWave clusters for accelerated processing of analytic queries. It is a very powerful service hosted on the cloud, eliminating the need for a separate analytics database and ETL processes.
Traditional analytics solutions: The challenges
Generally, customers have online transaction processing (OLTP) applications, which are connected to an OLTP database like MySQL. If users have to execute a query, they need to move their data to an online analytical processing (OLAP) database. This is done through extraction, transfer and load (ETL), and typically requires a database administrator’s (DBA) involvement. A DBA assists in migrating data from OLTP to OLAP, performs the query execution and sends the results back to OLTP. However, there are a few challenges existing solutions face:
- Running analytic queries against an OLTP database is always slow.
- The ETL processes with separate databases for OLTP and OLAP are lengthy.
- The data that is being processed is not up-to-date.
- The solutions are complex and expensive, as you need two databases and have to pay for two different services along with an experienced DBA.
Oracle MySQL HeatWave
MySQL HeatWave has emerged as a single solution to all the problems mentioned above. Both OLTP and OLAP are integrated adequately, and a built-in real-time query accelerator has been specifically designed for this service. This accelerator is available only in Oracle cloud infrastructure (OCI) and optimised only for it. It always ensures that real-time data is being processed whenever you run the queries, and provides an overall better price to performance ratio. The query processing time and the performance is 5,400 times faster than that of its competition.
Figure 1 shows how HeatWave is integrated into InnoDB. As can be seen, MySQL has InnoDB and HeatWave built in. HeatWave has an Autopilot advisor that helps get recommendations, which makes queries run faster. Let us now move on to a more detailed architecture for better clarity.
As can be seen in Figure 2, the OLAP queries get optimised by the optimiser present in the MySQL database service itself. When the HeatWave cluster is enabled, queries that meet certain prerequisites are automatically offloaded from the MySQL DB system to the HeatWave cluster for accelerated execution without any user intervention. The HeatWave node gives recommendations and the query estimation time, and then executes these within a very short time compared to a regular OLAP processor. It then sends the results back to the MySQL database service.
How is MySQL Autopilot making a difference?
MySQL Labs has been working on Autopilot for at least a decade to enhance its machine learning (ML) capabilities. This optimiser has been increasingly made intelligent with the integration of ML algorithms leading to an excellent performance. It gives smart recommendations based on the data, and only uses 0.1 per cent of the data sample to come up with a query estimation. The query performance at scale is also high, improving the usability of the system. The system is used effectively, especially with respect to data placement or managing change propagation.
Oracle MySQL Autopilot: How it works
MySQL Autopilot helps in four major domains.
System setup: This is essentially the first step. You often need assistance to figure out how many nodes are needed to query across a certain amount of data. That is when auto provisioning comes into the picture. You have to load your data, and the auto provisioning advisor will tell how many nodes you require based on the number of tables and primary keys.
Data load: The auto data load advisor consists of auto parallel loading, auto data placement, and auto encoding. Auto parallel loading is designed intelligently to tell you the number of threads you require to load a certain piece of data in parallel. Auto data placement predicts the column on which the table should be partitioned in the memory in order to achieve the best performance for queries. It also predicts the expected gain in query performance with a new column recommendation.
Query execution: This consists of auto scheduling, auto change propagation, auto query time estimation, and auto query plan improvement. Here again the query plan advisor simply executes a query and improves the execution plan for future queries. It works in an efficient way to optimise query plans via the machine learning algorithms. This allows real-time optimisation of queries because the query optimiser learns and adapts during the database usage time. Auto query time estimation is based on an analytical model of Autopilot’s query processing algorithms. Using a data driven approach improves performance over time as more queries are run. In auto scheduling, short queries are prioritised over long running queries. The system reduces the wait time for shorter queries without changing total execution time. Auto change propagation intelligently determines the optimal time for changes in the MySQL database to be propagated.
Failure handling: This consists of auto error recovery. Whenever there is a HeatWave error, the recovery is automatically taken care of as it is being continuously monitored by the Autopilot advisor. A system failure is handled by restarting the failed node and the HeatWave error is fixed by restarting the failed HeatWave process.
| MySQL Enterprise Edition: A fully managed MySQL database service |
| MySQL Enterprise Edition comes with a whole lot of functionalities such as backup, replication, monitoring and many other tools associated with it. Any on-premise customer using this version of MySQL does not have to worry about the latest updates for their databases as all new updates to the Enterprise Edition are added automatically. This not only saves time but also lets customers focus more on their applications than on maintaining the database and its version.
|
To wrap things up, it is essential to note that MySQL Autopilot supports machine learning based automation. It provides functionalities such as data loading, query execution and failure handling while further improving the performance of HeatWave itself. It is faster and more economical than its competitors. However, remember to make a clear assessment of the requirements before choosing a service as the system’s productivity will rely heavily upon that.
