✦

Technology Geek

18+ YEARS AMONG THE CLOUDS

✦ TRANSMISSION

OCI MySQL HeatWave: Running Analytics Directly on the Transactional Database

The standard approach to analytics on a transactional database is to extract data to a separate system. ETL pipelines move data from MySQL to a data warehouse. Reports run on the warehouse, not the production database. This works but it introduces lag, pipeline complexity, schema drift, and another system to operate. For many workloads, the data in the warehouse is already hours old by the time anyone queries it.

OCI MySQL HeatWave changes the architecture. HeatWave is an in-memory query accelerator that attaches directly to your MySQL Database Service instance. OLTP queries continue running against the MySQL storage engine as normal. Analytics queries are automatically offloaded to the HeatWave cluster, which processes them in parallel across its nodes using columnar in-memory storage. No ETL. No separate warehouse. No data movement. Analytical queries that take minutes on a standard MySQL instance complete in seconds on HeatWave.

This post covers deploying MySQL HeatWave with Terraform, loading tables into the HeatWave cluster, running queries, using HeatWave AutoML, and monitoring cluster performance.

Architecture

Application (OLTP writes and reads)
        |
   MySQL Database Service (InnoDB engine)
        |
   HeatWave Cluster (in-memory columnar store)
   [node-1] [node-2] [node-3]
        |
   Analytics / Reporting / AutoML

The MySQL instance and HeatWave cluster share the same endpoint. Your application connects to MySQL as normal. When a query is better served by HeatWave, the MySQL optimizer routes it to the cluster automatically. You do not change your connection string or query patterns.

Step 1: IAM Policy

resource "oci_identity_policy" "mysql_heatwave_policy" {
  compartment_id = var.compartment_id
  name           = "mysql-heatwave-policy"
  description    = "Permissions for MySQL HeatWave deployment and management"

  statements = [
    "Allow group ${var.dba_group_name} to manage mysql-family in compartment id ${var.compartment_id}",
    "Allow group ${var.dba_group_name} to manage virtual-network-family in compartment id ${var.compartment_id}",
    "Allow group ${var.app_group_name} to use mysql-family in compartment id ${var.compartment_id}",
    "Allow service mysqlcsn to use virtual-network-family in compartment id ${var.compartment_id}"
  ]
}

Step 2: MySQL Database Service with HeatWave

resource "oci_mysql_mysql_db_system" "heatwave_db" {
  compartment_id      = var.compartment_id
  display_name        = "production-heatwave-db"
  description         = "MySQL HeatWave for transactional and analytical workloads"
  availability_domain = data.oci_identity_availability_domains.ads.availability_domains[0].name
  subnet_id           = var.private_subnet_id
  shape_name          = "MySQL.HeatWave.VM.Standard.E3"
  admin_username      = "mysqladmin"
  admin_password      = var.mysql_admin_password

  data_storage_size_in_gb = 512
  is_highly_available     = true
  mysql_version           = "8.0.32"
  port                    = 3306
  port_x                  = 33060

  backup_policy {
    is_enabled        = true
    retention_in_days = 7
    window_start_time = "02:00"

    pitr_policy {
      is_enabled = true
    }
  }

  maintenance {
    window_start_time = "sun 03:00"
  }

  deletion_policy {
    automatic_backup_retention = "RETAIN"
    final_backup               = "REQUIRE_FINAL_BACKUP"
    is_delete_protected        = true
  }

  defined_tags = {
    "Operations.Environment" = "production"
    "Operations.ManagedBy"   = "terraform"
  }
}

# HeatWave cluster attached to the DB system
resource "oci_mysql_heat_wave_cluster" "heatwave_cluster" {
  db_system_id = oci_mysql_mysql_db_system.heatwave_db.id
  cluster_size = 3
  shape_name   = "MySQL.HeatWave.VM.Standard.E3"
}

output "mysql_endpoint" {
  value       = oci_mysql_mysql_db_system.heatwave_db.endpoints[0].hostname
  description = "MySQL connection hostname"
}

The shape_name = "MySQL.HeatWave.VM.Standard.E3" must be specified on both the DB system and the HeatWave cluster and they must match. HeatWave shapes are distinct from standard MySQL shapes and include the memory capacity required for the in-memory columnar store.

The cluster_size = 3 provisions three HeatWave nodes. Each node holds a partition of the data loaded into the cluster. More nodes means more data fits in memory and queries parallelize across more workers. Start with the minimum that fits your dataset and scale up as it grows.

Step 3: Loading Tables into HeatWave

Tables are not automatically loaded into HeatWave. You explicitly load each table using the ALTER TABLE statement with the SECONDARY_ENGINE=RAPID attribute, which designates it for HeatWave storage.

-- Connect to MySQL and run these statements

-- Mark tables for HeatWave loading
ALTER TABLE orders.order_header SECONDARY_ENGINE=RAPID;
ALTER TABLE orders.order_lines SECONDARY_ENGINE=RAPID;
ALTER TABLE products.catalog SECONDARY_ENGINE=RAPID;
ALTER TABLE customers.profiles SECONDARY_ENGINE=RAPID;

-- Load the tables into the HeatWave cluster
ALTER TABLE orders.order_header SECONDARY_LOAD;
ALTER TABLE orders.order_lines SECONDARY_LOAD;
ALTER TABLE products.catalog SECONDARY_LOAD;
ALTER TABLE customers.profiles SECONDARY_LOAD;

-- Verify load status
SELECT
  NAME,
  LOAD_STATUS
FROM
  performance_schema.rpd_tables
JOIN
  performance_schema.rpd_table_id
  USING (ID)
WHERE
  LOAD_STATUS = 'AVAIL_RPDGSTABSTATE';

Tables with LOAD_STATUS = 'AVAIL_RPDGSTABSTATE' are fully loaded and available for HeatWave query processing. The load time depends on table size. A 100GB table typically loads in a few minutes across a three-node cluster.

For tables that receive frequent writes, HeatWave maintains propagation automatically. Changes committed to InnoDB are propagated to the HeatWave cluster within seconds, keeping analytical queries consistent with the transactional data.

Step 4: Query Routing and Validation

The MySQL optimizer decides which engine to use for each query. You can verify a query is routed to HeatWave using EXPLAIN:

-- Check if a query uses HeatWave
EXPLAIN SELECT
  c.region,
  COUNT(DISTINCT o.customer_id)     AS unique_customers,
  SUM(ol.quantity * ol.unit_price)  AS total_revenue,
  AVG(ol.quantity * ol.unit_price)  AS avg_order_value
FROM
  orders.order_header  o
  JOIN orders.order_lines    ol ON o.order_id    = ol.order_id
  JOIN customers.profiles    c  ON o.customer_id = c.customer_id
WHERE
  o.order_date BETWEEN '2026-01-01' AND '2026-06-30'
  AND o.status = 'COMPLETED'
GROUP BY
  c.region
ORDER BY
  total_revenue DESC;

-- Look for "Use secondary engine RAPID" in the Extra column
-- This confirms the query is offloaded to HeatWave

-- Force a query to HeatWave explicitly
SET SESSION use_secondary_engine = FORCED;

-- Disable HeatWave for a specific query (for comparison)
SET SESSION use_secondary_engine = OFF;
SELECT ...;
SET SESSION use_secondary_engine = ON;

Run the same analytical query with HeatWave on and off and compare execution times. For aggregation queries across large tables with joins, HeatWave typically delivers 10x to 100x improvement over MySQL running on InnoDB alone.

Step 5: HeatWave AutoML

HeatWave AutoML trains machine learning models directly inside the database using data already loaded into the cluster. No data export, no separate ML platform, no feature engineering pipeline. You call stored procedures that train, evaluate, and deploy models against your MySQL tables.

-- Train a churn prediction model on customer data
CALL sys.ML_TRAIN(
  'analytics.customer_features',          -- training table
  'churned',                               -- target column
  JSON_OBJECT(
    'task',              'classification',
    'optimization_metric', 'accuracy',
    'exclude_column_list', JSON_ARRAY('customer_id', 'signup_date')
  ),
  @churn_model
);

-- Check training status
SELECT @churn_model;

-- Load the trained model for prediction
CALL sys.ML_MODEL_LOAD(@churn_model, NULL);

-- Run predictions on new customers
CALL sys.ML_PREDICT_TABLE(
  'analytics.new_customers',               -- input table
  @churn_model,                            -- trained model
  'analytics.churn_predictions',           -- output table
  JSON_OBJECT('task', 'classification')
);

-- Review predictions
SELECT
  customer_id,
  churned_prediction,
  churned_probability
FROM
  analytics.churn_predictions
ORDER BY
  churned_probability DESC
LIMIT 100;

-- Get model explanation - feature importance
CALL sys.ML_EXPLAIN_TABLE(
  'analytics.new_customers',
  @churn_model,
  'analytics.churn_explanations',
  JSON_OBJECT('prediction_explainer', 'permutation_importance')
);

AutoML supports classification, regression, forecasting, anomaly detection, and recommendation tasks. The model is stored inside the MySQL instance. Predictions are generated by calling sys.ML_PREDICT_ROW for single-row inference or sys.ML_PREDICT_TABLE for batch inference, both of which run inside the database without any external service call.

Step 6: Monitoring HeatWave Performance

resource "oci_monitoring_alarm" "heatwave_node_failure" {
  compartment_id        = var.compartment_id
  display_name          = "heatwave-node-not-active"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_mysql_database"
  query                 = "HeatWaveNodesActive[5m]{dbSystemId = '${oci_mysql_mysql_db_system.heatwave_db.id}'}.min()  400"
  severity              = "WARNING"
  pending_duration      = "PT5M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "MySQL active connections exceeding 400. Review connection pooling configuration."
}

Query HeatWave performance from inside MySQL using the performance schema:

-- Active HeatWave queries
SELECT
  PROCESSLIST_ID,
  PROCESSLIST_USER,
  PROCESSLIST_TIME,
  PROCESSLIST_INFO
FROM
  performance_schema.threads
WHERE
  PROCESSLIST_STATE LIKE '%rapid%'
  AND PROCESSLIST_INFO IS NOT NULL;

-- HeatWave memory usage per table
SELECT
  t.NAME                                    AS table_name,
  ROUND(SUM(c.COLUMN_MEMORY) / 1024 / 1024, 2) AS memory_mb
FROM
  performance_schema.rpd_tables t
  JOIN performance_schema.rpd_columns c USING (ID)
GROUP BY
  t.NAME
ORDER BY
  memory_mb DESC;

-- Query offload statistics
SELECT
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM
  performance_schema.global_status
WHERE
  VARIABLE_NAME IN (
    'rapid_query_offload_count',
    'rapid_query_offload_fail_count',
    'rapid_query_offload_warn_count'
  );

HeatWave Lakehouse: Querying Object Storage Directly

HeatWave Lakehouse extends the cluster to query data directly from OCI Object Storage without loading it into MySQL first. Parquet, CSV, and Avro files in Object Storage become queryable using standard SQL through an external table definition.

-- Create an external table pointing to Parquet files in Object Storage
CREATE TABLE analytics.historical_orders (
  order_id      VARCHAR(50),
  customer_id   VARCHAR(50),
  order_date    DATE,
  total_amount  DECIMAL(12,2),
  region        VARCHAR(50),
  status        VARCHAR(20)
)
ENGINE=lakehouse
SECONDARY_ENGINE=RAPID
COMMENT='{"file":[{"par":"https://objectstorage.me-jeddah-1.oraclecloud.com/p/TOKEN/n/NAMESPACE/b/analytics-data/o/historical_orders/"}],"dialect":{"format":"parquet"}}';

-- Load the external data into HeatWave
ALTER TABLE analytics.historical_orders SECONDARY_LOAD;

-- Now join historical Object Storage data with live transactional data
SELECT
  h.region,
  h.order_date,
  COUNT(*)                               AS historical_orders,
  SUM(h.total_amount)                    AS historical_revenue,
  COUNT(DISTINCT o.order_id)             AS current_orders
FROM
  analytics.historical_orders h
  LEFT JOIN orders.order_header o
    ON h.region = (
      SELECT region FROM customers.profiles WHERE customer_id = o.customer_id
    )
    AND YEAR(o.order_date) = YEAR(h.order_date)
GROUP BY
  h.region, h.order_date
ORDER BY
  h.order_date DESC, historical_revenue DESC;

This query joins years of historical order data stored as Parquet in Object Storage with live transactional data from the MySQL InnoDB tables, all processed inside HeatWave without any external query engine, ETL pipeline, or data warehouse.

When HeatWave Is the Right Choice

HeatWave solves the problem of needing analytics on fresh data without the operational overhead of a separate analytics platform. It is the right choice when your analytical workload runs on MySQL data, you need results on data that is minutes old rather than hours old, and you want to avoid maintaining an ETL pipeline and a separate data warehouse.

It is not the right choice for multi-source analytics that pull from many different databases, for very large datasets that exceed the in-memory capacity of the cluster, or for organizations that already have a mature data warehouse with established data governance around it.

The operational simplicity is the main argument. One database, one endpoint, one backup policy, one set of IAM policies. OLTP and analytics on the same data, in the same system, with no synchronization lag between them.

Regards,
Osama

#OCI #OracleCloud #MySQL #HeatWave #Terraform #CloudNative #InfrastructureAsCode #IaC #OracleCloudInfrastructure #Analytics #DatabaseEngineering #DevOps #PlatformEngineering #CloudArchitecture #TechBlog #CloudDatabase #OLAP #Oracle #HeatWaveLakehouse #AutoML

Leave a comment

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