Overview
MySQL HeatWave is the perfect database service for organizations looking to analyze their data stored in MySQL. It provides a single MySQL database platform for analytic and mixed workloads, accelerates complex and analytic queries on real-time data without the complex and time-consuming ETL process.
A MySQL HeatWave instance is a cluster composed of a MySQL instance and one or more HeatWave nodes. HeatWave is an in-memory hybrid columnar query accelerator. To get query acceleration in HeatWave, data referenced in the query needs to be loaded to the HeatWave cluster. There are 2 ways to load specific schemas, tables or columns to HeatWave:
- MySQL Autopilot – Auto Load & Auto Unload Advisors
- MySQL command – ALTER TABLE <table> secondary_load;
In both cases, users need to know the exact tables or columns to load to HeatWave for their OLAP queries, if users want to minimize the memory usage in HeatWave. Even if users load all tables to HeatWave, as workload changes, tables/schemas are added and the queries can change, it requires users to re-evaluate the queries/workload and re-adjust the tables that are required to load to HeatWave.
We now added a new automated capability (AutoLoad) to MySQL Autopilot – Auto Load & Auto Unload which continuously monitors workload run in MySQL and use machine learning model to predict the queries that can offload to HeatWave for query acceleration. Based on the list of queries, the new AutoLoad dynamically loads or unloads tables to HeatWave cluster. This happens without any user intervention, allowing the amount of data loaded to HeatWave dynamically adapt to the ever-changing workload. Queries now automatically get offloaded to HeatWave for better performance without any manual setup and maintenance. This reduces the work for DBA and provides developers and data analysts using MySQL HeatWave a transparent and complete hands-off experience.
The new capability also complements with existing manual loads (Autopilot – Auto Parallel Load & Auto Unload, MySQL command ALTER TABLE). If users manually load tables to HeatWave cluster, AutoLoad will not automatically unload the tables even if the tables have not been used by any queries run in HeatWave.
Figure 1: (1) The manual approach using the ALTER TABLE MySQL command requires the end users to set the load parameters or properties themselves.; (2) MySQL AutoPilot – Auto Load Advisor: The advisor generates exact commands for load with optimal parameters; (3) MySQL AutoPilot – AutoLoad continually observes the system and automatically loads or unloads tables to HeatWave to provide the optimal query performance.
Use Cases
Below shows examples of how AutoLoad automatically improves application performance:
- Applications with continuously changing workloads
AutoLoad improves query processing performance for the applications where workloads are continuously changing. In such applications, often new set of tables replace the existing tables, and data or query characteristics change over time. AutoLoad detects such changes in the workloads and updates the system by loading new set of tables and unloading tables which are not relevant anymore. Thus, accelerating applications and improving Heatwave cluster utilization even when workloads are changing.
The diagram below depicts a workload that changes over time. For a given month, OLAP queries with joins between Bank_Trasaction table and Click_Data table dominate the workload for most of the month. However, for a few days in the month, the application needs to run an audit. As a result, queries with joins between Bank_Transaction table and Audit_Data table dominate the workload. The change in workload is detected by HeatWave and corresponding tables are loaded and unloaded to maximize query acceleration. While loading and unloading tables, overall state of the system including amount of memory available in the HeatWave cluster and user choices are considered.
Figure 2: Frequency of tables accessed by queries
- Data analysts using data visualization or business intelligent (BI) tools
Data analysts use BI tool such as Oracle Analytics Cloud to run reports or analyze business data for various purposes. Often time, data analysts know about the tables and data but are unaware of the underlying database used. With MySQL HeatWave and AutoLoad , DBA does not need to know in advanced what kind of queries data analyst would run to preload the tables to HeatWave for query acceleration. AutoLoad would automatically learn the characteristics of the workloads and starts loading tables in the background, enabling data analyst to analyze data quickly.
How it works
When a user provisions HeatWave cluster, AutoLoad starts monitoring all the queries which could benefit from HeatWave for query acceleration. It starts identifying target queries and the corresponding referenced tables. AutoLoad then uses advanced machine learning model to predict and to rank the importance of the tables for the workload. Based on the ranking, AutoLoad loads the tables to HeatWave cluster. It intelligently predicts the memory needed in HeatWave, and only load the tables to HeatWave that the current HeatWave cluster size can handle. AutoLoad also loads the tables to HeatWave when the system is under low resource utilization, ensuring application performance is not affected during the load process.
User initiated manual load operations, using ALTER TABLE or using Auto Load Advisor are always prioritized. For example, when a user manually loads a table to HeatWave and there is not enough memory in HeatWave to store this table because AutoLoad had loaded other tables to HeatWave, AutoLoad detects this event and automatically unload the least important table(s) to make room for the manual load table, hence allowing the system to prioritize user decision.
Summary
To get query acceleration with HeatWave, data must be loaded into Heatwave cluster. With the enhanced MySQL Autopilot Auto Load and Auto Unload (AutoLoad), the system continuously monitors the workload in MySQL and automatically load necessary tables to HeatWave to enable query acceleration. This reduces the work for system setup and maintenance, enabling users to have a complete hands-off data load experience.
Reference