HeatWave MySQL – new capabilities for OLTP workloads
HeatWave MySQL is a robust and time-tested ACID compliant database which is ideal for transactional processing, mixed workloads, real-time analytics, and machine learning where the data is stored inside MySQL database.
On the cusp of Oracle Cloud World 2024, we’d like to call attention to some recent enhancements that are now available for Heatwave MySQL. These enhancements deliver improvements in availability, disaster recovery, performance, manageability and operability, and developer productivity.
Improve high availability and disaster recovery
Cross region backup for disaster recovery
To enhance data durability and disaster recovery capabilities, HeatWave MySQL now offers cross-region backup, where MySQL backups can be copied from one region to another. When the region, where HeatWave MySQL instances are deployed, becomes unavailable, users can use the cross-region backup to restore the HeatWave instance in another region with minimum data loss. Cross-region backups support OCI Identity policies which allow organization to control data movement between specific regions, ensuring data jurisdiction regulations are complied.
Shape change for MySQL HA with HeatWave cluster
To reduce the service downtime, users can now change the shape of their MySQL HA database system with attached Heatwave clusters, which previously required terminating and recreating the HeatWave cluster.
Performance improvement
Hypergraph-based optimizer for MySQL
We are now introducing a new hypergraph-based optimizer which uses true cost based to guide the join search space to produce optimized join query plan. This improves the performance for complex queries particularly for the ones involving joins. For complex queries in industry’s standard benchmark like TPC-H and TPC-DS, we see significant accelerations in query performance.
Users can choose to enable this new optimizer for their SQL queries at the session level via
If the users desire to go back to using the old optimizer, the hypergraph optimizer can be turned off via
Autopilot Indexing
HeatWave Autopilot Indexing is an ML-based feature designed to help customers to optimize their MySQL database systems for better cost and performance. With Autopilot Indexing, users no longer need to manually identify which secondary indexes are most beneficial for their workload.
Autopilot Indexing automatically generates secondary index recommendations for creating and dropping indexes based on the current workload. The feature considers both the performance of the queries and the cost of maintaining the indexes. It also provides index creation time, performance and storage estimations, as well as explanations for the recommendations it generates.
Bulk Ingest
For customers who want to import a large amount of data upfront into MySQL database, we now offer capability to bulk ingest at the instance creation time. This is done by parallelizing the insertion of the data into the table. Ingestion performance improves for both cases where the source data is sorted or unsorted. Below is an example of improvement which has been observed.
Manageability and operability
OCI Ops insight service integration
The Operations Insight service (Ops Insight) is an extension to OCI Database Management service which caters to fleet monitoring, database performance management of HeatWave MySQL. Ops insight provides ML-based resource usage trends and capacity planning, data explorer based performance analysis, and dashboard capabilities for HeatWave MySQL. This enables DBA to predict consumption for cost control and predict workload patterns for performance and scalability improvement
Ops Insight provides the following capacity planning features for HeatWave MySQL deployed in OCI:
- Identify SQL performance patterns, and trends for the workloads running within HeatWave DB systems
- Unified Capacity Planning user interface to analyze and predict capacity for HeatWave MySQL DB systems and Oracle databases, and analyze insights across the entire database fleet
- Leverage all the benefits of Capacity Planning including browsing Insights and Aggregate tabs at the CPU, Storage, Memory and I/O levels to right-size your database fleets
- Utilize AutoML based forecasting algorithms to forecast future consumption of underlying resources
- Set utilization thresholds alarms to be proactively informed of resource shortfalls for timely corrective actions
Ops Insight service usage incurs an additional cost on top of HeatWave MySQL usage.
Automatic Storage Expansion
This enhancement automatically detects and expands the MySQL storage on the database system when workload demands require additional resources. Once enabled, the service continually monitors free storage space, and automatically allocates more as required, up to a limit specified.
Virtual IP address for inbound replication
Inbound replication channels now originate their connections to sources using virtual IP for the database system, simplifying the management of firewall rules and security lists by maintaining a consistent IP address throughout the channel’s lifetime.
Developer Productivity
Stored Procedures and Functions in JavaScript
Developers can now run JavaScript procedural logic directly within the MySQL database as stored programs. This simplifies the application, the cost of client processing, and reduces the network latency.
Developers can execute SQL queries within their JavaScript code using a SQL API, enabling seamless processing of MySQL table data. This is particularly effective with MySQL’s JSON data type, which integrates naturally with the JavaScript environment. The feature supports the latest ECMAScript 2023 standard and includes just-in-time compilation along with other cutting-edge optimizations.
This feature is available in Heatwave on OCI, AWS and Azure as well as MySQL Enterprise Edition.
Summary
The HeatWave engineering team continues to deliver improvements in availability, performance, operability, and developer productivity for your transaction processing workloads. We invite you to learn more at our sessions at Oracle Cloud World 2024 , and by following the Oracle MySQL blog.
More Information