MySQL HeatWave on OCI

# Create a MySQL DB System
oci mysql db-system create \
  --compartment-id <compartment-ocid> \
  --display-name "analytics-db" \
  --description "MySQL HeatWave for analytics workloads" \
  --shape-name "MySQL.HeatWave.VM.Standard" \
  --availability-domain "AD-1" \
  --subnet-id <private-subnet-ocid> \
  --admin-username "admin" \
  --admin-password "YourStr0ng_P@ssword!" \
  --data-storage-size-in-gbs 1024 \
  --mysql-version "9.0.0" \
  --backup-policy '{
    "isEnabled": true,
    "retentionInDays": 7,
    "windowStartTime": "02:00"
  }' \
  --maintenance '{
    "windowStartTime": "sun 03:00"
  }' \
  --is-highly-available true \
  --configuration-id <configuration-ocid>

# Wait for the DB System to become active
oci mysql db-system get \
  --db-system-id <db-system-ocid> \
  --query 'data.{"Name": "display-name", "State": "lifecycle-state", "Shape": "shape-name", "Endpoint": endpoints[0]."ip-address", "Port": endpoints[0].port}' \
  --output table

# List available MySQL shapes
oci mysql shape list \
  --compartment-id <compartment-ocid> \
  --query 'data[?contains(name, `HeatWave`)].{"Shape": name, "CPUs": "cpu-core-count", "Memory (GB)": "memory-size-in-gbs"}' \
  --output table

# Connect to the MySQL instance
mysql -h <endpoint-ip> -u admin -p -P 3306

# Add a HeatWave cluster to an existing DB System
oci mysql heat-wave-cluster add \
  --db-system-id <db-system-ocid> \
  --shape-name "MySQL.HeatWave.VM.Standard" \
  --cluster-size 3 \
  --is-lakehouse-enabled true

# Check HeatWave cluster status
oci mysql heat-wave-cluster get \
  --db-system-id <db-system-ocid> \
  --query 'data.{"State": "lifecycle-state", "Nodes": "cluster-size", "Shape": "shape-name", "Lakehouse": "is-lakehouse-enabled"}' \
  --output table

# Use HeatWave Auto Shape Estimation to determine optimal cluster size
# Run from MySQL shell:
# CALL sys.heatwave_advisor(JSON_OBJECT("target_schema", JSON_ARRAY("analytics_db")));

# Scale the HeatWave cluster
oci mysql heat-wave-cluster update \
  --db-system-id <db-system-ocid> \
  --cluster-size 5

# Stop the HeatWave cluster to save costs (data is reloaded on restart)
oci mysql heat-wave-cluster stop \
  --db-system-id <db-system-ocid>

# Restart the HeatWave cluster
oci mysql heat-wave-cluster start \
  --db-system-id <db-system-ocid>

-- Enable HeatWave for specific tables
ALTER TABLE orders SECONDARY_ENGINE = RAPID;
ALTER TABLE order_items SECONDARY_ENGINE = RAPID;
ALTER TABLE products SECONDARY_ENGINE = RAPID;
ALTER TABLE customers SECONDARY_ENGINE = RAPID;

-- Load the tables into HeatWave memory
ALTER TABLE orders SECONDARY_LOAD;
ALTER TABLE order_items SECONDARY_LOAD;
ALTER TABLE products SECONDARY_LOAD;
ALTER TABLE customers SECONDARY_LOAD;

-- Check which tables are loaded into HeatWave
SELECT NAME, LOAD_STATUS, LOAD_PROGRESS,
       POOL_TYPE, DATA_MEMORY_BYTES / 1024 / 1024 AS data_mb
FROM performance_schema.rpd_tables
ORDER BY data_mb DESC;

-- Check HeatWave node status
SELECT * FROM performance_schema.rpd_nodes;

-- Use HeatWave Auto Parallel Load for large tables
CALL sys.heatwave_load(JSON_ARRAY("analytics_db"), NULL);

-- Verify a query is using HeatWave (check EXPLAIN)
EXPLAIN SELECT
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(oi.quantity * oi.unit_price) AS total_spend
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.customer_name
ORDER BY total_spend DESC
LIMIT 20;

-- The EXPLAIN output should show "Using secondary engine RAPID"
-- If it shows standard MySQL execution, the optimizer determined
-- HeatWave would not be faster for this specific query

-- Create an external table pointing to Object Storage
CREATE TABLE sales_history_ext ENGINE = LAKEHOUSE
SECONDARY_ENGINE = RAPID
AS SELECT * FROM (
    SELECT
        CAST(col1 AS DATE) AS sale_date,
        CAST(col2 AS VARCHAR(50)) AS product_id,
        CAST(col3 AS VARCHAR(100)) AS product_name,
        CAST(col4 AS DECIMAL(10,2)) AS sale_amount,
        CAST(col5 AS INT) AS quantity,
        CAST(col6 AS VARCHAR(50)) AS region
    FROM TABLE(
        OCI_LAKEHOUSE_READER(
            'oci://data-lake@namespace/sales/',
            JSON_OBJECT(
                'format', 'parquet',
                'schema', JSON_ARRAY(
                    JSON_OBJECT('name', 'sale_date', 'type', 'DATE'),
                    JSON_OBJECT('name', 'product_id', 'type', 'VARCHAR(50)'),
                    JSON_OBJECT('name', 'product_name', 'type', 'VARCHAR(100)'),
                    JSON_OBJECT('name', 'sale_amount', 'type', 'DECIMAL(10,2)'),
                    JSON_OBJECT('name', 'quantity', 'type', 'INT'),
                    JSON_OBJECT('name', 'region', 'type', 'VARCHAR(50)')
                )
            )
        )
    )
);

-- Load the external table data into HeatWave
ALTER TABLE sales_history_ext SECONDARY_LOAD;

-- Query across MySQL tables and Lakehouse data
SELECT
    p.category,
    sh.region,
    DATE_FORMAT(sh.sale_date, '%Y-%m') AS month,
    COUNT(*) AS transactions,
    SUM(sh.sale_amount) AS revenue,
    AVG(sh.sale_amount) AS avg_order_value
FROM sales_history_ext sh
JOIN products p ON sh.product_id = p.product_id
WHERE sh.sale_date >= '2025-01-01'
GROUP BY p.category, sh.region, month
ORDER BY revenue DESC
LIMIT 50;

-- Auto-generate Lakehouse table definition from file metadata
CALL sys.heatwave_load(
    JSON_ARRAY("analytics_db"),
    JSON_OBJECT(
        "mode", "dryrun",
        "output", JSON_ARRAY("lakehouse_auto_schema")
    )
);

-- Train a classification model to predict customer churn
CALL sys.ML_TRAIN(
    'analytics_db.customer_features',  -- Training data table
    'churned',                          -- Target column
    JSON_OBJECT(
        'task', 'classification',
        'model_handle', 'churn_predictor_v1'
    )
);

-- Check model training status
CALL sys.ML_MODEL_STATUS('churn_predictor_v1');

-- View model metadata and performance metrics
CALL sys.ML_EXPLAIN(
    'analytics_db.customer_features',
    'churned',
    JSON_OBJECT('model_handle', 'churn_predictor_v1')
);

-- Make predictions on new data
CALL sys.ML_PREDICT_TABLE(
    'analytics_db.new_customers',       -- Input table
    'churn_predictor_v1',                -- Model handle
    'analytics_db.churn_predictions'     -- Output table
);

-- View predictions
SELECT
    customer_id,
    customer_name,
    ml_results->>'$.prediction' AS predicted_churn,
    ml_results->>'$.probability' AS churn_probability
FROM analytics_db.churn_predictions
WHERE ml_results->>'$.prediction' = '1'
ORDER BY ml_results->>'$.probability' DESC
LIMIT 20;

-- Train a regression model for revenue forecasting
CALL sys.ML_TRAIN(
    'analytics_db.monthly_revenue',
    'revenue',
    JSON_OBJECT(
        'task', 'regression',
        'model_handle', 'revenue_forecast_v1'
    )
);

-- Train a time series forecasting model
CALL sys.ML_TRAIN(
    'analytics_db.daily_sales',
    'sales_amount',
    JSON_OBJECT(
        'task', 'forecasting',
        'model_handle', 'sales_forecast',
        'datetime_index', 'sale_date',
        'forecast_horizon', 30
    )
);

-- Train an anomaly detection model
CALL sys.ML_TRAIN(
    'analytics_db.transaction_features',
    NULL,  -- No target for unsupervised learning
    JSON_OBJECT(
        'task', 'anomaly_detection',
        'model_handle', 'fraud_detector_v1'
    )
);

# Enable high availability on an existing DB System
oci mysql db-system update \
  --db-system-id <db-system-ocid> \
  --is-highly-available true

# Create a read replica for read scaling
oci mysql replica create \
  --db-system-id <db-system-ocid> \
  --display-name "analytics-read-replica" \
  --description "Read replica for reporting queries"

# List read replicas
oci mysql replica list \
  --compartment-id <compartment-ocid> \
  --db-system-id <db-system-ocid> \
  --query 'data.items[].{"Name": "display-name", "State": "lifecycle-state", "IP": "ip-address"}' \
  --output table

# Create a manual backup
oci mysql backup create \
  --db-system-id <db-system-ocid> \
  --display-name "pre-migration-backup" \
  --backup-type FULL \
  --retention-in-days 30

# List backups
oci mysql backup list \
  --compartment-id <compartment-ocid> \
  --db-system-id <db-system-ocid> \
  --query 'data.items[].{"Name": "display-name", "Type": "backup-type", "State": "lifecycle-state", "Size (GB)": "data-storage-size-in-gbs", "Created": "time-created"}' \
  --output table

# Restore from a backup (creates a new DB System)
oci mysql db-system create \
  --compartment-id <compartment-ocid> \
  --display-name "analytics-db-restored" \
  --shape-name "MySQL.HeatWave.VM.Standard" \
  --subnet-id <subnet-ocid> \
  --source '{
    "sourceType": "BACKUP",
    "backupId": "<backup-ocid>"
  }'

-- Check current HeatWave performance
SELECT
    COUNT_STAR AS total_queries,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_returned,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE QUERY_SAMPLE_TEXT LIKE '%RAPID%'
ORDER BY COUNT_STAR DESC
LIMIT 10;

-- Run the HeatWave Advisor for optimization recommendations
CALL sys.heatwave_advisor(
    JSON_OBJECT(
        "target_schema", JSON_ARRAY("analytics_db"),
        "output", JSON_ARRAY(
            "recommendations",
            "table_analysis",
            "column_analysis"
        )
    )
);

-- Check HeatWave memory utilization
SELECT * FROM sys.heatwave_cluster_status;

-- Monitor change propagation lag
SELECT
    TABLE_NAME,
    LOAD_STATUS,
    ROWS_LOADED,
    ROWS_MODIFIED_SINCE_LOAD
FROM performance_schema.rpd_tables
WHERE ROWS_MODIFIED_SINCE_LOAD > 0
ORDER BY ROWS_MODIFIED_SINCE_LOAD DESC;