MySQL Database Service delivers a database with all the essential features to help you rapidly pioneer innovative applications. MySQL Database Service is easy to use, secure, and enterprise-ready. It combines the benefits of a widely adopted open-source database solution with a strong ecosystem, millions of users, and the backing of Oracle.
MySQL HeatWave increases MySQL DB System performance and eliminates the need for a separate database and tools for OLTP, Analytics, and Machine Learning features.
MySQL HeatWave combines transaction processing, real-time analytics, and machine learning within a single MySQL database. It stores data in main memory in a hybrid columnar format. Its processing engine is enhanced with aggressive compression of the in-memory data, which helps to reduce the memory footprint. And it uses a HeatWave cluster for massively parallel and high-performance analytics workloads.
To completely understand how HeatWave works, let's look at its architecture:
The HeatWave plugin is responsible for cluster management, query scheduling, and returning query results to the MySQL DB System.
The HeatWave nodes store data in memory and process analytics and machine learning queries. Each HeatWave node hosts an instance of the HeatWave query processing engine. The number of HeatWave nodes required depends on data size and the amount of compression achieved when loading data into the HeatWave Cluster.
Data loaded into HeatWave is automatically persisted to a Cloud Object Storage (OCI, AWS, Azure), which allows data to be reloaded quickly when the HeatWave Cluster resumes after a pause or when the HeatWave Cluster recovers from a cluster or node failure.
HeatWave stores data in memory in a hybrid columnar format. Hybrid means that it uses all the benefits of columnar query processing combined with in-memory storage to avoid materialization and update costs associated with pure columnar format.
The use of a HeatWave cluster allows massively parallel analytics workloads. A HeatWave Cluster in Oracle Cloud Infrastructure (OCI) or Oracle Database Service for Azure (ODSA) supports up to 64 nodes. On Amazon Web Services (AWS), a HeatWave Cluster supports up to 128 nodes.
The storage layer automatically scales to the size required by your HeatWave cluster and operates independently in the background. HeatWave storage layer uses a Cloud Object Storage for pause and resume of the HeatWave Cluster and for fast recovery in case of a HeatWave node or cluster failure.
HeatWave is designed as a pluggable MySQL storage engine, which enables the management of both MySQL and HeatWave using the same interfaces.
You can try out MySQL HeatWave for 30 days at:
cloud.oracle.com/tryit or oracle.com/cloud/free
It will take you to a screen that will allow you to register for a free trial. The free trial gives you access to many products, including MySQL Database Service and MySQL HeatWave.
Once you've set up your account. You can now log in, and Oracle will take you to the Get Started Screen.
In the following example, I will describe how to deploy and manage HeatWave clusters on Oracle Cloud Infrastructure. I will use the OCI Console to add a HeatWave cluster to the MySQL DB system.
Open the navigation menu and select Databases. Under MySQL, click DB Systems. Click Db System:
It will ask for the db name, username, and password. In the system type options, select HeatWave to add the HeatWave plugin.
It will also ask for the hardware configuration. You can add HeatWave clusters only to shapes that support HeatWave. It is recommended to use MySQL.HeatWave.BM.Standard.E3 or MySQL.HeatWave.BM.Standard shape.
Click Create.
It takes around 10 minutes to create the MySQL DB System and the HeatWave plugin.
Now, you can enable the HeatWave cluster and add nodes to the cluster.
From the DB System, click the "More actions" drop-down menu and select "Add HeatWave cluster."
On the Edit HeatWave Cluster panel, change the number of nodes in the HeatWave cluster.
Optionally you can click Estimate Node Count to estimate the number of nodes required based on the shape you selected and the size of your data.
Click "Add HeatWave Cluster."
On the left menu, select HeatWave, and you will see all the information related to the HeatWave cluster. Node creation will take around 10 minutes.
From the MySQL DB System, you can edit, start, stop, or restart a HeatWave cluster.
Consider the following points when you change the status of the HeatWave cluster:
When you enable a HeatWave Cluster, queries that meet certain prerequisites are offloaded from MySQL DB System to the HeatWave cluster automatically for accelerated processing.
You can also manually load data into the HeatWave cluster by executing load statements.
We will review the basic statements for preparing tables and loading data.
Before loading data, the tables must meet the following criteria:
mysql> ALTER TABLE tbl_name ENGINE=InnoDB; mysql> ALTER TABLE tbl_name ADD PRIMARY KEY (column); Follow the steps to manually load data:
mysql> ALTER TABLE tbl_name SECONDARY_ENGINE = RAPID; mysql> ALTER TABLE tbl_name SECONDARY_LOAD; You can check the progress of loading data to HeatWave using:
mysql> SELECT VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'rapid_load_progress';
You can view runtime data for all queries in the HeatWave cluster history using:
mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", TRUE));
When a HeatWave cluster is enabled, queries issued from a MySQL client or application interact with the HeatWave Cluster by connecting to the MySQL DB System. Results are returned to the MySQL DB System and to the MySQL client or application that issued the query.
A HeatWave Cluster consists of a MySQL DB System and HeatWave nodes. It means that it is a fully managed database service available only through Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Oracle Database Service for Azure (ODSA).
For information about creating and managing HeatWave Clusters on Oracle Cloud Infrastructure (OCI), see the MySQL Database Service documentation.
For information about creating and managing HeatWave Clusters on Amazon Web Services (AWS), see the MySQL HeatWave on AWS Service Guide.
For information about creating and managing HeatWave Clusters on Oracle Database Service for Azure (ODSA), see the Oracle Database Service for Azure documentation.
One of the sayings we have at Viscosity is our customers "have four aces in their pocket." Over the next 3 days, the talented staff at Viscosity, along with our Oracle ACEs, will address more Oracle Database 18c and 19c new features. Continue to join us next year as we continue our Oracle Database 19c hands-on lab workshops.
Happy Holidays!