HeatWave – A MySQL cloud feature to speed up your queries

April 13, 2021
Tags: , , ,
Cloud from above by Olivier DASINI

If you have (too) long running select queries it is probably because of lack of relevant indexes, problematic schema that lead to poor queries or inadequate hardware.

That said, sometime even if you doing it right, the query execution time could be too long regarding of what the application or your users expect. It is often true for reporting, real time analytics or BI queries.

At Oracle we have developed HeatWave, that allow you to easily run high performance analytics against your MySQL database.

To be more precise, HeatWave is a massively-scalable integrated analytics engine for MySQL Database Service (MDS), the MySQL DBaaS in Oracle Cloud Infrastructure (OCI).

MySQL HeatWave Architecture

Some key points of HeatWave:

If your application already use a MySQL database that is not MySQL Database Service, you can still use HeatWave, thanks to the Inbound Replication feature.

This is basically what you architecture will look like

Inbound Replication with MySQL Database Service

Your “problematic” select queries will be running on MDS (with HeatWave plugin enabled).

Create this architecture is pretty simple, this is the topic of this article…

Assumptions
You have an application and a MySQL instance somewhere (on-premise / public | private cloud / on the moon, …), called 10.0.1.9 in this article.
You’ve already created your HeatWave instance, called MDSHW (10.0.1.10) in this article.


Not yet created? Don’t worry it is quite easy : MySQL Database Service with a HeatWave cluster

Last but not least, you want to dramatically improve the response time of your select queries.


You can take advantage of the power of HeatWave, by setup a replication channel between your MySQL instance and a MySQL Database Service with a HeatWave cluster.

For simplicity, in this blog post the MySQL instance is on OCI. However this architecture is also relevant with the DB out of OCI.
In that case you will need a VPN (e.g. Using OpenVPN with MySQL Database Service).

Also for simplicity, I’m using MySQL 8.0. However, the following architecture is also relevant with MySQL 5.7.
To understand how to setup a replication channel from MySQL 5.7 to MySQL Database Service, you can read : Replicate from MySQL 5.7 to MySQL Database Service.

What is the plan?

Assuming we already have the application and a MySQL 8.0 database running, we will:

  1. Create a dedicated replication user on the source
  2. Create a dump of the MySQL instance
  3. Load the dump into MySQL Database Service with HeatWave
  4. Create a replication channel on MySQL Database Service with HeatWave
  5. Enable a HeatWave cluster

Create a dedicated replication user on the source

Connect to the MySQL source instance (10.0.1.9), using MySQL Shell:

then create the replication user:

with his relevant privileges:

Simple!

Create a dump of the MySQL instance

If you think mysqldump (or mysqlpump) when you heard “dump” then it is time to upgrade your knowledge!!! 🙂

When using MySQL 8.0 or even 5.7, forget these 2 tools and please welcome MySQL Shell utilities and especially its load / dump tools – you’ll thank me later 😉

Please note ocimds & compatibility options.
These are very important in order to be able to load your data into MDS.

Details are available in this great Anastasia‘s article (MySQL SHELL – The new era) and obviously in the documentation.

In this scenario the dump is stored locally. But MySQL Shell also allow you to store your dump into an OCI Object Storage Bucket.
This could be a better alternative if your data set is large.

Super simple!

Load the dump into MySQL Database Service with HeatWave

Now it is the time to load the dump into MySQL Database Service with HeatWave (10.0.1.10).

MySQL Shell loadDump utility is even more amazing than you think!
It allows you to start loading in parallel the dump even if it is not completed yet 🙂

Please note the updateGtidSet option.
Documentation is available here.

Very simple!

Create a replication channel on MySQL Database Service with HeatWave

This step is even easier than the other 🙂 because most of the work will be done using the OCI console.
I already described it in this article and you’ll find all the details in the documentation.

Below the main stages.

The feature we are looking for is named Channel.
You can find it by clicking on “MySQL” then “Channel” and finally push the “Create Channel” button

Create Inbound MySQL Replication in MySQL Database Service in OCI
Create a replication channel on MySQL Database Service

Then fill the form:

Create in Compartment: Choose the right Compartment

Source Connection – Configure Connection to the MySQL Source
Hostname: 10.0.0.9

Username: rpl
(from the replication user created on the source)

Password: Rpl1234_
(from the replication user created on the source)

SSL Mode: – set when create the replication user –
Required (REQUIRED)
Establish an encrypted connection.

Target – Configure the DB System target
Select a DB System: MDSHW

Finally, push the Create Channel button… et voilà!

Wait for the Channel icon become green (ACTIVE)…

You can confirm the creation of the replication channel with the command SHOW REPLICA STATUS\G or run the following query using the replication channel name (default: replication_channel) :

Enable a HeatWave cluster

The last stage covered in this article is how to enable the HeatWave cluster.

You must choose the number of node (2 minimum), it depends of the size of the data you want to put in HeatWave.
The “Estimate Node Count” feature will help you.

Enable a HeatWave cluster

Then click Add HeatWave Cluster button.

What next?

There are some data preparation and finally load the data into the HeatWave cluster.

But that would be for another article 🙂

Obviously all these information are available in the HeatWave User Guide.

Péroraison

We continuously try to push the limits to improve MySQL, adding new features (check The complete list of new features in MySQL 8.0) to allow you to reach your business objectives.

Obviously, this is also true for MySQL Database Service.
HeatWave, the query accelerator is therefore a great example.

And you know what?
There are plenty of good things to come 🙂

Stay tuned!

Oh, one last thing, you can try MySQL Database Service, HeatWave and other Oracle Cloud Infrastructure services for free.
Click here.

References

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.



Thanks for using MySQL!

One Response to “HeatWave – A MySQL cloud feature to speed up your queries”

  1. […] Read more […]

Leave a Reply