dasini.net – Diary of a MySQL expert

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:
  • 400x faster than MySQL, scales to thousands of cores
  • 1100x faster than Amazon Aurora
  • 2.7x faster than Amazon Redshift

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

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:

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

with his relevant privileges:

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"]})

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 

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

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

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

• MySQL Database Service
  • Getting Started with MySQL Database Service
  • MySQL Database Service – Inbound Replication
  • Migrate from on premise MySQL to MySQL Database Service
  • Using MySQL Workbench with MySQL Database Service
  • Options to Run MySQL Server in OCI (and use MySQL Analytics Service)
• HeatWave
  • HeatWave Quickstart
  • HeatWave User Guide
• Performance comparison of HeatWave with MySQL Database, Amazon Redshift, and Amazon Aurora
• MySQL Database Service—New HeatWave Innovations
• MySQL Benchmarks
• MySQL Shell
  • Instance Dump Utility, Schema Dump Utility, and Table Dump Utility
  • Dump Loading Utility
• Oracle Cloud Infrastructure (OCI)
  • Oracle Cloud Infrastructure Free Tier
  • Overview of Object Storage
• MySQL Replication

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

Thanks for using MySQL!

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