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:
Oracle Unleashes HeatWave on MySQL, Thumps Amazon Redshift & Aurora by Bob EvansCreating a MySQL DB System – Oracle documentation HeatWave Benchmarks by OracleGuide to HeatWave: Real-time Analytics for MySQL Database Service – mysql.comMySQL HeatWave: 1100x Faster than Aurora, 400x than RDS, 18x than Redshift at 1/3 the cost by Oracle
Read MoreJulian Dontcheff’s Database Blog
