Dontcheff

HeatWave MySQL DB Systems in OCI

In DBA, MySQL, OCI on June 7, 2021 at 10:44

HeatWave is a distributed, scalable, shared-nothing, inmemory, columnar, query processing engine designed for fast execution of analytic queries. It is enabled when you add a HeatWave cluster to a MySQL DB System.

You can think of HeatWave as an easy way to run high performance analytics against the MySQL database.

When creating a MySQL DB System in Oracle Cloud Infrastructure, the options are now Standalone, High Availability and (the newest) HeatWave:

Here is a how the HeatWave architecture looks like:

A HeatWave cluster supports up to 64 nodes. The number you choose depends on the size of the database and the amount of compression that is achieved when loading the data into the HeatWave cluster. You cannot connect the app/session drectly to the HeatWave cluster.

Starting from scratch, choose a MySQL DB system from the OCI Databases menu:

And then start creating a MySQL DB system with the HeatWave option:

Once ready, adding additional clusters (when needed) is simple:

As of now, the only available shape is VM.Standard.E3, so you might get error messages when trying to add more than 4 clusters:

Here is what I got: “You have reached your Analytics Cluster service limit of 4 in this Availability Domain for MySQL.HeatWave.VM.Standard.E3. Please try launching in a different Availability Domain or Region, or try using a different shape. If you have reached all Service limits, please contact Oracle support to request a limit increase.”

Note that 1 node matches to ½ a TB of memory. The increase is linear.

You can rely also on Oracle to estimate the number of nodes:

Before loading data into the HeatWave cluster, the data/tables have to be prepared. Preparing tables involves modifying table definitions to exclude certain columns, define string column encodings, add data placement keys, and specify HeatWave (RAPID) as the secondary engine for the table – note that InnoDB is the primary engine. Loading a table into a HeatWave cluster requires executing an ALTER TABLE operation with the SECONDARY_LOAD keyword.

If a query accesses a table that is not loaded, the query is not offloaded to the HeatWave cluster for processing. Note that queries that meet certain prerequisites are automatically offloaded from the MySQL DB System to the HeatWave cluster for accelerated processing. Results are returned to the MySQL DB System node and to the MySQL client or application that issued the query.

When a table is loaded, data is sliced horizontally and distributed among HeatWave nodes. After a table is loaded, changes to a table’s data on the MySQL DB System node are automatically propagated to the HeatWave nodes. No user action is required to keep data synchronized.

All the details can be found in the HeatWave documentation (it is short – 68 pages only). This is the best place to understand how to prepare & load the data and how to unload tables. The document is full of examples and lists and string functions and operators. Also, you can find a list of all functions, data types, variables, JOIN types, SQL modes, and other expressions and functionality that are not supported by HeatWave.

Unloading table is pretty straightforward:

mysql> ALTER TABLE sales SECONDARY_UNLOAD;

Here are some additional link if interested in more detail:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: