HeatWave

HeatWave is a distributed, scalable, shared-nothing, in-memory, hybrid columnar, query processing engine designed for extreme performance. Enable HeatWave by adding a HeatWave cluster to a MySQL DB system.

HeatWave Overview

HeatWave provisions HeatWave clusters for accelerated processing of analytic queries.

A HeatWave cluster consists of a MySQL DB system node and two or more HeatWave nodes. The MySQL DB system node includes a HeatWave plugin that is responsible for cluster management, query scheduling, and returning query results to the MySQL DB system. HeatWave nodes store data in memory and process queries.

When you enable a HeatWave cluster, queries that meet certain prerequisites are automatically offloaded from the MySQL DB system to the HeatWave cluster for accelerated execution. The queries that you issue from a MySQL Client or application interacts with the HeatWave cluster by connecting to the MySQL DB system node. The HeatWave cluster returns the results to the MySQL DB system node and to the MySQL Client or application that issued the query.

This guide describes how to deploy and manage HeatWave clusters using the Console. After deploying a HeatWave cluster, refer to the following:

  • HeatWave User Guide: Describes how to load data and run queries.
  • HeatWave tpch Quickstart: Describes how to add a HeatWave cluster to a MySQL DB system, import the tpch sample database into the DB system using the MySQL Shell Parallel Table Import utility, manually load data into HeatWave, and run queries.
  • HeatWave airportdb Quickstart: Describes how to add a HeatWave cluster to a MySQL DB system, import the airportdb sample database into the DB system using the MySQL Shell Dump Load utility, load data into HeatWave using Auto Parallel Load, and run queries.

Related Topics

Adding a HeatWave Cluster

Use the Console to add a HeatWave Cluster to your MySQL DB system.

This task requires the following:
  • A DB system with a MySQL.HeatWave.VM.Standard.E3 or MySQL.HeatWave.BM.Standard.E3 shape. For data sets of 10TB or more, it is recommended to use the MySQL.HeatWave.BM.Standard.E3 shape.
Do the following to add a HeatWave cluster to an existing MySQL DB system:
  1. Open the navigation menu and select Databases. Under MySQL, click DB Systems.
  2. Do one of the following:
    • Choose Add HeatWave Cluster from the Actions menu on the same line as your DB system.
    • Click the name of the DB system to open the MySQL DB System Details page. Select HeatWave from the Resources list. On the HeatWave Cluster Information frame, select Add HeatWave Cluster.
    • Click the name of the DB system to open the MySQL DB System Details page. Click More Actions, and select Add HeatWave Cluster.
  3. On the Add HeatWave Cluster panel, do the following:
    • Select a Shape: Select the shape for the HeatWave nodes. Click Change Shape to select a shape for your HeatWave nodes.
      Note

      Currently, the MySQL.HeatWave.VM.Standard.E3 and MySQL.HeatWave.BM.Standard.E3 shapes are supported for HeatWave nodes.
    • Node Count: Specify the number of HeatWave nodes to create. A minimum of 2 and a maximum of 64 nodes is supported.
    • Estimate Node Count: (Optional) Click it to estimate the number of nodes required based on the shape you selected and the size of your data. See Generating a Node Count Estimate.
  4. Click Add HeatWave Cluster .

Managing HeatWave Clusters

Editing a HeatWave Cluster

Use the Console to change the number of nodes of the HeatWave Cluster.

  1. Open the navigation menu and select Databases. Under MySQL, click DB Systems.
  2. In the HeatWave filter, select Attached to filter the DB systems that have a HeatWave cluster attached.
  3. Do one of the following:
    • Select Edit HeatWave Cluster from the Actions menu on the same line as your DB system.
    • Click the name of the DB system to open the DB System Details page. Select HeatWave from the Resources list. On the HeatWave Cluster Information frame, click Edit.
    • Click the name of the DB system to open the DB System Details page. Click More Actions, and select Edit HeatWave Cluster.
  4. On the Edit HeatWave Cluster panel, change the number of nodes in the HeatWave cluster.
  5. (Optional) Click Estimate Node Count to estimate the number of nodes required based on the shape you selected and the size of your data. See Generating a Node Count Estimate.
  6. Click Save Changes.
  7. Reload data to HeatWave after completing your changes.

HeatWave Cluster Information

This section describes the HeatWave Cluster Information and HeatWave Node sections of the DB System Details page, where you can view, manage, and edit your HeatWave cluster .

Table 15-1 HeatWave Cluster Information

Field Description
Actions The following actions are possible:
Cluster Size The number of nodes in the cluster.
Shape The shape used for HeatWave cluster nodes.
State The state of the HeatWave cluster:
  • CREATING: Yellow icon. Resources are being reserved for the HeatWave cluster, and the cluster is being created. Provisioning can take several minutes. The cluster is not ready to use yet.

  • ACTIVE: Green icon. The HeatWave cluster was successfully created.

  • UPDATING: Yellow icon. The HeatWave cluster is in the process of starting, stopping, restarting, or updating after an edit.

  • INACTIVE: Grey icon. The HeatWave cluster is powered off by the stop action in the console or API.

  • DELETING: Orange icon. The HeatWave cluster is being deleted by the terminate action in the console or API.

  • DELETED: Grey icon. The HeatWave cluster has been deleted and is no longer available.

  • FAILED: Red icon. An error condition prevented the creation or continued operation of the HeatWave cluster.

Created The date and time the HeatWave cluster was created.
Last Updated The date and time the HeatWave cluster was last updated.

Table 15-2 HeatWave Nodes

Column Description
Node Id The name of the HeatWave node.
State Current state of the HeatWave node:
  • CREATING: Yellow icon. Resources are being reserved for the HeatWave node, and the node is being created. Provisioning can take several minutes. The node is not ready to use yet.

  • ACTIVE: Green icon. The HeatWave node was successfully created.

  • UPDATING: Yellow icon. The HeatWave node is in the process of starting, stopping, restarting, or updating.

  • INACTIVE: Grey icon. The HeatWave node is powered off by the stop or restart action in the Console or API.

  • DELETING: Orange icon. The HeatWave node is being deleted by the terminate action in the Console or API.

  • DELETED: Grey icon. The HeatWave node has been deleted and is no longer available.

  • FAILED: Red icon. An error condition prevented the creation or continued operation of the HeatWave node.

Created The date and time the HeatWave node was created.

Checking the HeatWave State

Use the Console to check the HeatWave state.

  1. Open the navigation menu and select Databases. Under MySQL, click DB Systems.
  2. Choose your compartment from the List Scope.
  3. In the HeatWave filter, select Attached to filter the DB systems that have a HeatWave cluster attached.
  4. In the list of DB systems, find your DB system, and check the icon in the HeatWave State column. The color of the icon and the associated text indicate the status of the HeatWave cluster. See HeatWave States.
  5. To check the status of HeatWave nodes, click the name of the DB system to open the DB System Details page. Select HeatWave from the Resources list, and check the State column in the HeatWave Nodes frame.
HeatWave States

Check the icon in the HeatWave State column to view the state of your HeatWave cluster.

Table 15-3 HeatWave States

Icon HeatWave State Description
Grey Inactive The HeatWave cluster is powered off by the stop or reboot action in the Console or API.
Red Deleted The HeatWave cluster is deleted and is no longer available.
Failed An error condition prevented the creation or continued operation of the HeatWave cluster.
Yellow Creating HeatWave is reserving resources, booting, and creating the HeatWave cluster. Provisioning can take several minutes. You cannot use the cluster yet.
Updating The HeatWave cluster is starting, stopping, restarting, or updating.
Deleting The HeatWave cluster is being deleted by the terminate action in the Console or API.
Green Active The HeatWave cluster is successfully created.

HeatWave Cluster Failure and Recovery Overview

HeatWave monitors the node status regularly and if there is no response from a node after 60 seconds, it considers it a node failure. A node failure triggers the recovery process and HeatWave automatically attempts to bring the node online and reform the cluster.

After reforming the HeatWave cluster, HeatWave attempts to reload data that was previously loaded. By default, HeatWave reloads the data from an Object Storage persistence layer, which is created when you enable the HeatWave cluster for the first time. To facilitate recovery, data is persisted to Object Storage when data is loaded into HeatWave and when data changes is propagated from the DB system to HeatWave. If reloading data from Object Storage fails for any reason, HeatWave reloads the data from the DB system. Loading data from Object Storage is faster because the data does not need to be converted to the HeatWave storage format, as is required when loading data from the DB system.When you unload a table, the data is removed from HeatWave, and in a background operation, it is removed from Object Storage too.

Starting, Stopping, or Restarting a HeatWave Cluster

Use the Console to start, stop, or restart a HeatWave cluster.

  1. Open the navigation menu, and select Databases. Under MySQL, click DB Systems.
  2. In the HeatWave filter, select Attached to filter the DB systems that have a HeatWave cluster attached.
  3. Click the name of your DB system to open the DB System Details page.
  4. In the Resources list, click HeatWave.
  5. Select one of the following actions:
    • Start: Start a stopped HeatWave cluster. After the HeatWave cluster is started, the Stop action is enabled and the Start option is disabled.
    • Stop: Stop a running HeatWave cluster. After the HeatWave cluster is stopped, the Start action is enabled.
      Note

      Stopping the HeatWave cluster stops billing for the cluster. Billing resumes if you restart the HeatWave cluster.
    • Restart: Shut down a HeatWave cluster, and restart it.
      Note

      When you stop a HeatWave cluster through a stop or restart action, the data loaded in HeatWave cluster memory is lost. When you start or restart a HeatWave cluster, HeatWave automatically reloads the data that was previously loaded using the HeatWave recovery mechanism as described in HeatWave Cluster Failure and Recovery Overview. Data changes that occur on the DB system while the HeatWave cluster is offline are included in the reloaded data.

Deleting a HeatWave Cluster

Use the Console to permanently delete a HeatWave cluster.

  1. Open the navigation menu and select Databases. Under MySQL, click DB Systems.
  2. In the HeatWave filter, select Attached to filter the DB systems that have a HeatWave cluster attached.
  3. Click the name of your DB system to open the DB System Details page.
  4. In the Resources list, click HeatWave.
  5. In the HeatWave Cluster Information frame, click Delete.
  6. On the Delete HeatWave Cluster dialog box, click Delete HeatWave Cluster.
    Note

    Deleting the HeatWave cluster has no affect on the DB system to which the HeatWave cluster is attached. However, deleting the DB system deletes the attached HeatWave cluster.

HeatWave Node Count Estimate Overview

Use the Console to estimate the number of HeatWave nodes required to run a workload.

When you start the service, database tables, on which HeatWave queries are run, have to be loaded to the HeatWave cluster memory. The number of HeatWave nodes required to run a workload depends on the size of the tables and columns to be loaded, and the compression achieved in memory for this data. Under-provisioning the HeatWave cluster results in a data load or query execution failure due to space limitations. Over-provisioning the HeatWave cluster results in additional costs for unneeded resources. Based on the database tables you intend to load to memory, machine learning intelligently estimates the number of HeatWave nodes you require.

Generating a Node Count Estimate

Generate a node count estimate while adding a HeatWave cluster to a DB system, or any time later to adjust the number of nodes as your data increases or decreases in size.

This task requires the following:
  • The data you intend to load into the HeatWave cluster present in the DB system.
  • Optionally, log into your DB System and run ANALYZE TABLE on tables you intend to load into the HeatWave cluster. Estimates should generally be valid without running ANALYZE TABLE, but running ANALYZE TABLE ensures that estimates are as accurate as possible.
Do the following to generate a node count estimate:
  1. Open the navigation menu, and select Databases. Under MySQL, click DB Systems.
  2. In the HeatWave filter, select Attached to filter the DB systems that have a HeatWave cluster attached.
  3. Click the name of your DB system to open the DB System Details page.
  4. In the Resources list, click HeatWave.
  5. In the HeatWave Cluster Information frame, click Add HeatWave Cluster or Edit.
  6. On the Add HeatWave Cluster or Edit dialog box, click Estimate Node Count.
  7. On the Estimate Node Count panel, click Generate Estimate. If you recently generated a node count estimate, the previous estimate details are displayed. Click Regenerate Estimate to create a new estimate.
    The operation may take several minutes depending on the size and properties of your data. When the operation completes, you get a response that contains the following details:
    • Name: Specify the name of the schema.
    • Memory Size Estimate: Specify the estimated amount of memory required for the schema.
    • Information: Specify the number of tables in the schema and the number of tables with errors.
  8. Select the schemas that you want to include in the node count estimate.
    The estimate details in Summary are adjusted automatically after modifying the schema selection.
  9. (Optional) Expand the schema rows to view information about individual tables. Deselect tables that you do not want to include in the estimate.
    Note

    The Information column reports errors if there are problems with a table. For example, an error is reported for tables with unsupported column data types, tables without a primary key, or tables with too many columns. Tables with errors are not included in the node count estimate. You can regenerate the node count estimate after resolving the errors. See Node Count Estimate Table Errors.
  10. Review the estimate details in the Summary, which provides the following information:
    • Shape: Specify the selected HeatWave node shape.
    • CPU Core Count: Specify the CPU core count of the selected HeatWave node shape.
    • Memory Size: Specify the memory size of the selected HeatWave node shape.
    • Node Count: Specify the estimated number of HeatWave nodes required based on the data size and the selected HeatWave node shape.
    • Total Memory Required: Specify the estimated amount of memory required for the HeatWave cluster based on the data size.
    • Total Memory Size: Specify the total HeatWave cluster memory size, which is the memory size of the selected HeatWave node shape multiplied by the Node Count.
    Note

    The load command that appears below the Summary is generated based on the schemas and tables selected for the node count estimate. You can use the command after the HeatWave cluster is provisioned to load the selected schemas and tables. You can run the command from any MySQL client that is connected to the DB system.
  11. Click Apply Node Count Estimate.

Node Count Estimate Table Errors

While estimating node count, you may encounter table errors if certain conditions are not met.

Table 15-4 Node Count Estimate Table Errors

Table Error Description
TOO MANY COLUMNS TO LOAD The table has too many columns. The column limit is 470.
ALL COLUMNS MARKED AS NOT SECONDARY There are no columns to load. All table columns are defined as NOT SECONDARY.
CONTAINS VARLEN COLUMN WITH >8000 BYTES A VARLEN column exceeds the 8000 byte limit. See VARLEN Encoding.
ESTIMATION COULD NOT BE CALCULATED The estimate could not be calculated. For example, a table estimate may not be available if statistics for VARLEN columns are unavailable.
UNABLE TO LOAD TABLE WITHOUT PRIMARY KEY A table must be defined with a primary key before it can be loaded into HeatWave.