HeatWave – A MySQL cloud feature to speed up your queries

April 13, 2021

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

Some key points of HeatWave:

Single MySQL database for OLTP & analytics applications
All existing applications work without any changes
Extreme performance:

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:

Create a dedicated replication user on the source
Create a dump of the MySQL instance
Load the dump into MySQL Database Service with HeatWave
Create a replication channel on MySQL Database Service with HeatWave
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:

$
mysqlsh root@localhost:3306 --sql

1 2	$ mysqlsh root@localhost:3306 --sql

then create the replication user:

MySQL  localhost:3306 ssl SQL > 
CREATE USER rpl@'10.0.1.%' IDENTIFIED BY 'Rpl1234_' REQUIRE SSL;

1 2	MySQL localhost:3306 ssl SQL > CREATE USER rpl@'10.0.1.%' IDENTIFIED BY 'Rpl1234_' REQUIRE SSL;

with his relevant privileges:

MySQL  localhost:3306 ssl SQL > 
GRANT REPLICATION SLAVE on *.* to rpl@'10.0.1.%';

1 2	MySQL localhost:3306 ssl SQL > GRANT REPLICATION SLAVE on . to rpl@'10.0.1.%';

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 😉

MySQL  localhost:3306 ssl JS > 
util.dumpInstance("/backup/myDump20210316", {ocimds: true, threads: 10, compatibility: ["force_innodb", "strip_definers", "strip_restricted_grants", "strip_tablespaces"]})

1 2	MySQL localhost:3306 ssl JS > util.dumpInstance("/backup/myDump20210316", {ocimds: true, threads: 10, compatibility: ["force_innodb", "strip_definers", "strip_restricted_grants", "strip_tablespaces"]})

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 🙂

mysqlsh root@10.0.1.10:3306

1	mysqlsh root@10.0.1.10:3306

MySQL  10.0.1.10:3306 ssl JS > 
util.loadDump("/backup/myDump20210316", {updateGtidSet: "replace", threads: 10, waitDumpTimeout: 1800})

1 2	MySQL 10.0.1.10:3306 ssl JS > util.loadDump("/backup/myDump20210316", {updateGtidSet: "replace", threads: 10, waitDumpTimeout: 1800})

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

MySQL  10.0.1.10:3306 ssl SQL > 
SELECT 
   SERVICE_STATE, 
   HOST, 
   USER, 
   PORT,
   CHANNEL_NAME
FROM performance_schema.replication_connection_configuration 
   INNER JOIN performance_schema.replication_applier_status 
      USING (CHANNEL_NAME) 
WHERE CHANNEL_NAME = 'replication_channel'\G
*************************** 1. row ***************************
SERVICE_STATE: ON
         HOST: 10.0.0.9
         USER: rpl
         PORT: 3306
 CHANNEL_NAME: replication_channel

MySQL 10.0.1.10:3306 ssl SQL >

SELECT

SERVICE_STATE,

HOST,

USER,

PORT,

CHANNEL_NAME

FROM performance_schema.replication_connection_configuration

INNER JOIN performance_schema.replication_applier_status

USING (CHANNEL_NAME)

WHERE CHANNEL_NAME = 'replication_channel'\G

*************************** 1. row ***************************

SERVICE_STATE: ON

HOST: 10.0.0.9

USER: rpl

PORT: 3306

CHANNEL_NAME: 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.

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!

Olivier DASINI

MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–

Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–

Filed under:
Cloud, MDS, MySQL, Tuto by Olivier DASINI

4 Responses to “HeatWave – A MySQL cloud feature to speed up your queries”

HeatWave – A MySQL cloud feature to speed up your queries | RSS Feeds

April 14th, 2021 at 7:44

[…] Read more […]
Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket | dasini.net - Diary of a MySQL experts

August 24th, 2021 at 8:46

[…] articles on my blog about MySQL Shell dump & load utilities.I would recommend you to read HeatWave – A MySQL cloud feature to speed up your queries and MySQL SHELL – The new […]
Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket | dasini.net - Diary of a MySQL experts

August 24th, 2021 at 11:17

[…] mode. But you will not be able to use some advanced features like MDS High Availability, nor MDS HeatWave)Another alternative is to use create_invisible_pks instead of ignore_missing_pks. Please check the […]
Webinar – Analytique avec MySQL Database Service, HeatWave et Oracle Analytics Cloud | dasini.net - Journal d'experts MySQL

September 20th, 2021 at 10:54

[…] mot clé est MySQL HeatWave, l’accélérateur en mémoire de requêtes qui booste les […]

dasini.net – Diary of a MySQL expert