MySQL HeatWave on AWS – New and upcoming capabilities in March and April 2023

HeatWave Scale Out Data Management

MySQL HeatWave on AWS now provides an optimized storage layer built on S3 to store the HeatWave in-memory hybrid columnar representation of the data. This allows data to be reloaded to each HeatWave node independently and in parallel, significantly improves the service uptime and performance of operations such as error recovery, maintenance, and system restart.

The fast data reload from HeatWave storage layer also enables users to easily and quickly pause and resume a HeatWave cluster to save cost when HeatWave is not needed.

With 4TB TPCH data, it takes 140 minutes to reload LINEITEM table data from MySQL while 3.5 minutes to reload from HeatWave storage layer, a 40 times improvement.

To learn more about HeatWave scale out data management, see HeatWave architecture documentation.

MySQL Autopilot for OLTP: Auto Shape Prediction

Auto shape prediction collects the most recent query execution metrics and uses advanced machine learning models to predict the MySQL database instance shape for optimal transactional processing performance. Auto shape prediction continuously monitors OLTP workload to provide suggestion that adapts to evolving workload patterns, allowing you to maintain the best OLTP price performance over time.

Auto shape prediction eliminates the manual guess work needed from database administrators to figure out the optimal hardware configuration for OLTP workload. Using a MySQL instance shape with inadequate memory or CPU leads to significantly degraded performance due to frequent buffer pool misses (long IO) and/or overutilization of CPU. Over-provisioning MySQL instance shape results in underutilizing server (DRAM, CPU) resources and incurring unnecessary resource cost. Picking the right MySQL instance shape requires analyzing the workload in production as the workload dictates the ideal size (read/write ratio, file size, access patterns). Moreover, workload evolves over time, therefore continuous monitoring and assessment of the workload is needed.

Auto shape prediction continuously collects MySQL statistics such as buffer pool usage, workload activity and access patterns for the current workload. Statistics are collected in varying intervals and predictions are created based on advanced machine learning technique. If there is insufficient activity or buffer pool usage is growing in an interval, auto shape prediction then defers making a prediction for that interval.

MySQL HeatWave on AWS also provides an interactive web console interface for auto shape prediction. This allows you to easily monitor the recommendations from auto shape prediction. It also shows trends and insights on the workload with metrics such as buffer pool hit ratio and buffer pool utilization factor which allows you to understand your workload better.

To learn more, go to auto shape prediction documentation

MySQL Configuration

MySQL configuration is now available on MySQL HeatWave on AWS. You can view MySQL configuration on a DB system and create and apply custom MySQL configuration to a DB system.

MySQL HeatWave on AWS provides a set of pre-defined and optimized MySQL configurations based on MySQL shape and workload. MySQL HeatWave on AWS supports OLTP only workload and OLAP/mixed workloads. For each MySQL shape, there are 2 sets of default configuration:

1. MySQL only configuration optimized for OLTP workload
2. MySQL with HeatWave configuration optimized for OLAP and mixed workloads

Figure 1. View MySQL configuration

You can create custom configuration by setting the desired initialization variables and user variables based on the default configuration (Figure 2 & 3) and apply the custom configuration to a new or existing DB system (Figure 4).

Figure 2. Step 1 of creating a new custom configuration

Figure 3. Step 2 of creating a new custom configuration

Figure 4. Apply custom configuration to DB system

Automatic Backup

MySQL HeatWave on AWS now support automatic backup. Backup is created automatically at a time selected while creating the DB system. The default retention period is 7 days. You can define the retention period between 1 and 35 days. When you delete a DB system, scheduled backups are deleted.

Interactive console for HeatWave AutoML

You can now explore HeatWave AutoML functionalities using the MySQL HeatWave on AWS interactive console. Users can train machine learning models, do predictions, explain model as well as the individual predictions, visualize the models and do what if analysis to determine impact of feature changes on the model outcome. With the interactive console, it makes it even easier for users who do not have data science or database expertise to use the machine learning functionality.

To learn more about HeatWave AutoML console functionalities, see Interactive console for machine learning in MySQL HeatWave

Addition Resources

• HeatWave release notes
• Get US$300 in credit and try MySQL HeatWave for 30 days: Try MySQL HeatWave for Free