MySQL HeatWave - In-Memory Query Accelerator

 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 withing 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, also, it uses a HeatWave cluster for massively parallel and high-performance analytics workloads. 

MySQL HeatWave architecture

To completely understand how HeatWave works, let’s look to its architecture:

HeatWave plugin

The HeatWave plugin is responsible for cluster management, query scheduling, and returning query results to the MySQL DB System.  

HeatWave Cluster

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 that is achieved when loading data into the HeatWave Cluster.

HeatWave Storage Layer

Data that is 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 Features


In-Memory Hybrid-Columnar Format

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.

Massively Parallel Architecture

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.

Scale-Out Data Management

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.

Native MySQL Integration

HeatWave is designed as a pluggable MySQL storage engine, which enables management of both the MySQL and HeatWave using the same interfaces.


Provisioning MySQL HeatWave

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 setup 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.


Creating a HeatWave Cluster

Now, you can enable the HeatWave cluster and add nodes to the cluster.

From the DB System, click 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.


Managing the HeatWave cluster

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 stop a HeatWave cluster through a stop or restart action, the data loaded in HeatWave cluster memory is lost. When you start or restart a HeatWave cluster, HeatWave automatically reloads the data that was previously loaded using the HeatWave recovery mechanism. Remember, it will use a Cloud Object Storage for this operation (OCI, AWS, Azure).
  • With the Edit HeatWave Cluster panel, you can change the number of nodes in the HeatWave cluster, the shape you selected and the size of your data. 
  • You can resize (increase or decrease) the nodes of a HeatWave cluster in real-time with no downtime or service disruption.
  • You can permanently detele a HeatWave cluster. Deleting the HeatWave cluster has no effect on the DB system to which the HeatWave cluster is attached. However, deleting the DB system deletes the attached 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.


Preparing tables and loading data into a HeatWave Cluster

Before loading data, the tables must meet the following criteria:

  • The tables you intend to load must be InnoDB tables. Convert tables to InnoDB using:

mysql> ALTER TABLE tbl_name ENGINE=InnoDB;

  • The tables you intend to load must be defined with a primary key. Add a primary key using:

mysql> ALTER TABLE tbl_name ADD PRIMARY KEY (column);
  • Identify all the tables that your queries access to ensure that you load all of them into HeatWave.
  • Column width cannot exceed 65532 bytes.
  • The number of columns per table cannot exceed 900.

Follow the steps to manually load data:

1. Defining RAPID as the secondary engine for tables you want to load:

mysql> ALTER TABLE tbl_name SECONDARY_ENGINE = RAPID;

 2. Loading tables:

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));

Summary

When a HeatWave cluster is enabled, queries issued from a MySQL client or application interacts 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.







Comments

Post a Comment