Getting Started with MySQL HeatWave on AWS

If you’re developing database applications on AWS and need real-time analytics and extremely fast queries, that’s what MySQL HeatWave on AWS was built to do. Instead of crafting complicated ETL pipelines to move data around to a separate analytics database, HeatWave leverages in-memory acceleration to provide real-time analytics (OLAP) functionality without impacting the performance of transactional workloads (OLTP). MySQL HeatWave is a fully managed service, which allows developers to quickly create and deploy secure cloud native applications using the world’s most popular open source database. MySQL HeatWave is developed and supported by the MySQL development team at Oracle.

One of the best things about MySQL HeatWave is the ability to run analytics directly against your existing transactional data, which means you only need to manage one database instead of two, and can get real-time, secure analytics without the complexity, latency, and cost of ETL duplication.

To get going, let’s Start for free on AWS. A quick sign-up will provide $300 in free credits to see just how awesome MySQL HeatWave on AWS can be.

Provide Country/Territory, first and last name, plus an email address for verification.
Click Verify my email
Upon clicking the link in the email verification message, you will be prompted to provide a strong password, a cloud account name, and a home region. Select US East (Ashburn) as the home region. NOTE: If you do not choose IAD / Ashburn as your home region, you will need to request region limit increase. Once that is increased, you will be able to subscribe to Ashburn region (this is required for HeatWave on AWS).
Complete the registration process by entering an address and billing details.
Accept the agreement and click Start my free trial
Within a few moments your account will be created and you will be able to log in!
On the main dashboard you should see the banner to get started with MySQL HeatWave on AWS. Click Go to service.
On the next page you will have an option to Request Upgrade which will convert your account to a paid account. Don’t worry! You won’t be charged anything… remember you have $300 in free credits to play with.
Note - It will take a few minutes for all of the backend processing to complete before you can complete the upgrade process. If you receive a message indicating the same, feel free to step away for a bit. Give your eyes some rest.
Once you are able to complete the upgrade process, you can move on to the next step.
Since you selected Ashburn as home region, you should be able to skip to step 3, which guides you to request the HeatWave on AWS service limit.
Once you get the service limit, you can enable HeatWave on AWS.
You’re now ready to set up a MySQL HeatWave cluster on AWS!

Note that MySQL HeatWave on AWS is integrated with OCI’s Identity and Access Management system. When you sign up for HeatWave on AWS, you’ll be directed to the OCI login page where you must sign in with an OCI Cloud Account (which you created earlier). To keep things simple, billing is managed and monitored within OCI.

Prerequisites

Your Oracle Cloud Account name, admin username and password
A compatible browser (Chrome 69+, Safari 12.1+, or Firefox 62+ or any browser that is Oracle Jet-approved)

Overview

To start working with MySQL HeatWave on AWS you need neither AWS experience nor an AWS account. However, if you do have those, there’s no limit to what you can do to integrate AWS apps with MySQL HeatWave. So let’s get going!

Log into the MySQL HeatWave on AWS console
Create a MySQL DB System with HeatWave Cluster
Load some sample data
Connect to database and run some queries
Load tables into HeatWave and query some more

Steps

Log into MySQL HeatWave on AWS
If you’ve just completed the sign-up process and logged in, you may be a step ahead. Else you will want to navigate to the MySQL HeatWave on AWS landing page. Here you will enter your cloud account name (may already be cached by the browser) and click Continue.
On the next page you are prompted for the user ID and password you created during the sign-up process. Once everything is entered correctly you will find yourself on the MySQL HeatWave dashboard.
Create a MySQL DB System with HeatWave Cluster
Now the fun begins! Let’s get to provisioning.
Click Create MySQL DB System on the dashboard to bring up the Create MySQL DB System and HeatWave Cluster dialog. This first portion is focused on configuring the underlying MySQL instance and HeatWave cluster itself.
Fill out the required details:
1. Display name
2. Description
3. Administrator credentials (don’t lose these)
4. Select the Hardware configuration. Note the shape determines the number of vCPU cores and amount of RAM. (i.e. MySQL.4.32GB is 4 vCPU and 32GB of RAM)
5. Select the starting Data storage size in GiB…this can be increased later if/when necessary.
6. Select the latest DB version (Currently only supports the latest MySQL version 8.0.31)
7. Choose a maintenance window (Automatic or Manual)
8. Choose an availability zone (AZ). Note: If you choose manual, you will be prompted to select the physical AWS availability zone. If you have existing applications deployed on AWS, you will need to reconcile the logical AZ displayed in the AWS console with the physical AZ selected here.
9. Provide networking details. The MySQL instance will become accessible via public endpoint. By default, no ingress connectivity is allowed. For the Allowed client addresses you must enter a specific, public IP address followed by /32. i.e. if your client computer’s public IP is 1.2.3.4, enter 1.2.3.4/32. You can enter multiple values separated by a semicolon. For this example, we will also use the default ports 3306 and 33060.
10. Click Next
11. Provide a Display Name and Description for your HeatWave cluster.
12. Select a shape - this will determine how much memory is available in each cluster node.
13. Select a cluster size, from 1–128. For this exercise, it is recommended you select the HeatWave.16GB shape and a cluster size of 1.
14. Click Create, then sit back and relax for a few minutes while the magic happens.
Load sample data
Now that everything is up and running, let’s load some data and put this thing to the test.
1. Open a new browser tab and log into the OCI console to access Cloud Shell. If prompted for credentials, you will use the same cloud account, username, and password as entered when logging into HeatWave.
2. Open the OCI Cloud Shell
3. Retrieve the sample database: airport-db
```
wget https://downloads.mysql.com/docs/airport-db.tar.gz

tar xvzf airport-db.tar.gz
```
4. Locate the public IP address associated with your Cloud Shell environment. This will be added to the MySQL Allowed client addresses.
```
dig +short myip.opendns.com @resolver1.opendns.com
```
5. Return to the MySQL dashboard and edit the MySQL DB instance. Add the public IP address with /32 to the Allowed client addresses and click Save.
6. Return to the browser tab with Cloud Shell and start a MySQL Shell session. You will need the admin username and password supplied when creating the DB System. You will also need the Host Name copied earlier.
```
mysqlsh username@hostname
```
  After pressing enter, you will be prompted for the password.
7. Use loadDump to import the database that was unzipped previously.
```
util.loadDump("airport-db", {threads: 16, deferTableIndexes: "all", ignoreVersion: true})
```
  This step might take a few minutes. Now is a great opportunity to grab a quick snack or check the weather forecast.
8. When the import is complete you are ready to move on. Nice work!
Run a few simple queries… just for good measure
We will compare performance against HeatWave a bit later. The MySQL shell is a pretty powerful tool but the interface isn’t for everyone. Before we move on, let’s run a few more queries just for fun; a great chance to flex those command line muscles.
1. Why don’t we try picking up a few more rows in our query:
```
select * from airplane limit 10000;
```
2. Looking good? Just one more…for fun!
```
mysql SELECT booking.price, count(*) FROM booking WHERE booking.price > 500 GROUP BY booking.price ORDER BY booking.price LIMIT 10;
```
  Make note of execution time.
3. Exit the MySQL Shell by typing ‘\d’ and pressing ENTER.
4. Load tables into HeatWave and query some more You’re doing great…and now it’s time to turn up the heat! In this final section you will get to unleash the awesome power of MySQL HeatWave.
  1. Return to the browser tab with the MySQL HeatWave dashboard. Navigate to the workspaces pane.
  2. Select your DB system, then enter the admin username and password you created earlier (you saved those, right?). Click Connect and give it a few seconds to open the door.
  3. Now that you are connected, it’s time to run the same query as before. This will still run against InnoDB, but that’s intentional.
```
mysql SELECT booking.price, count(*) FROM booking WHERE booking.price > 500 GROUP BY booking.price ORDER BY booking.price LIMIT 10;
```
    Note the time it takes to complete…and get ready!
5. Go to Manage Data in HeatWave tab, select airportdb, this will select all tables in the DB. Click Load into HeatWave button.
  This will bring up a window that outlines the task and provides an estimated time to completion. After giving it a quick once-over, click Load Tables.
  Once you load a new table into HeatWave, it will automatically track and load all data going forward. Or, at least until you UNLOAD that table.
  SIDE NOTE: If you don’t need the power of HeatWave for a while you can always stop it to help keep costs low. While it is stopped, the system will continue to track data DIFFS for all loaded tables. When HeatWave gets fired back up, it will automatically update the loaded tables.
Run the previous query again and note the performance difference.
Feel free to experiment with larger data sets and/or more complex queries. Any time you want to test performance benchmarks between InnoDB and HeatWave, you need only unload, then re-load the tables.
That brings us to the end. You can either terminate, stop, or leave running the resources you created…and don’t forget to pat yourself on the back. Great job!

Summary

Thus far we’ve created a MySQL database instance and HeatWave cluster on AWS, loaded some data, and poked around the system a bit. You saw the dramatic performance improvement gained by running queries in HeatWave…all without the need to ETL any data between systems. With all this power at your fingertips, we highly encourage you continue to explore the performance and capabilities. We’ve only just scratched the surface!

Want to know more? Join the discussion in our public Slack channel!