MySQL HeatWave Consulting and Managed Services: Engineering Real-Time Analytics at Scale
Oracle MySQL HeatWave collapses the traditional boundary between OLTP and OLAP workloads into a single, in-database engine — eliminating the ETL pipelines, data warehouse copies, and operational overhead that organizations routinely accept as unavoidable costs. At MinervaDB, we have deployed HeatWave across production environments handling hundreds of terabytes of operational and analytical data, and the architecture decisions made at provisioning time determine whether clusters deliver sub-second query responses or stall under concurrent load. Our MySQL HeatWave consulting and managed services practice brings that production experience directly to your environment, from initial sizing through ongoing query optimization and cluster governance.
Performance Engineering for HeatWave Clusters
HeatWave stores data in a columnar, in-memory format distributed across HeatWave nodes. The MySQL optimizer offloads qualifying queries automatically using cost-based decision making, but query offload rates above 95% require deliberate schema and session configuration — not passive reliance on defaults.
HeatWave node sizing: Determine memory requirements before provisioning. HeatWave stores each loaded table column in compressed columnar format; the rule of thumb is 1–2 bytes per row per column, but string columns with high cardinality deviate significantly. Use the HeatWave Autopilot estimator before committing to a node shape:
-- Estimate HeatWave memory requirement for a table
CALL sys.heatwave_advisor(JSON_OBJECT('target_schema', 'sales', 'target_table', 'orders'));
Autopilot returns a JSON response including estimated_memory_footprint_mb, auto_encoding_suggestion, and column-level recommendations. Treat these as baseline inputs, not final answers — we validate against actual query plans.
When a query does not offload, the optimizer returns its reasoning in the secondary_engine_load_status table. Common blockers: unsupported data types (BLOB, TEXT columns without explicit RAPID encoding), subqueries that reference non-loaded tables, or tables below the HeatWave offload threshold row count.
HeatWave ML for in-database machine learning: HeatWave ML trains and runs inference entirely inside the database engine — no data movement to an external ML platform. Training a classification model:
CALL sys.ML_TRAIN(
'sales.churn_training',
'churned',
JSON_OBJECT(
'task', 'classification',
'exclude_column_list', JSON_ARRAY('customer_id')
),
@model_handle
);
-- Score new data
CALL sys.ML_PREDICT_TABLE(
'sales.customers_current',
@model_handle,
'sales.churn_predictions'
);
At MinervaDB, we instrument ML model refresh cycles within MySQL Event Scheduler so that scoring tables stay current without external orchestration.
Scalability: Converged OLTP+OLAP Architecture Patterns
The HeatWave architecture separates compute planes: MySQL InnoDB handles OLTP writes and point lookups; HeatWave nodes handle analytical scan workloads. This separation means you scale each plane independently.
Read replica topology for mixed workloads: For high-write OLTP environments, we configure HeatWave on a read replica cluster. OLTP applications target the primary InnoDB instance; analytical dashboards target the replica endpoint with HeatWave enabled. Replication lag is the critical metric to monitor — analytical results are only as fresh as the replica’s binlog position.
-- Monitor replica lag SHOW REPLICA STATUS\G -- Key field: Seconds_Behind_Source -- On OCI, check replication health via CLI oci mysql db-system list-heat-wave-clusters --db-system-id <ocid>
HeatWave Lakehouse for external data: HeatWave Lakehouse extends the engine to query Parquet, CSV, and ORC files stored in OCI Object Storage directly — without loading data into MySQL tables. This is the correct pattern for historical data tiering: keep 90-day operational data in InnoDB+HeatWave, archive older data to Object Storage, and query both in a single SQL statement.
-- Create an external table mapped to OCI Object Storage
CREATE TABLE orders_archive (
order_id BIGINT,
region VARCHAR(64),
revenue DECIMAL(14,2),
order_date DATE
) ENGINE=lakehouse
SECONDARY_ENGINE=RAPID
SECONDARY_ENGINE_ATTRIBUTE='{"file_block":{"is_autodetect":true,"dialect":{"format":"parquet"}},"file":{"par":"https://objectstorage.us-ashburn-1.oraclecloud.com/p/<PAR>/n/<ns>/b/<bucket>/o/orders_archive/"}}';
Autopilot auto-provisioning: HeatWave Autopilot can recommend cluster shape adjustments when node memory utilization trends above 80% for extended periods. We embed Autopilot recommendation checks into weekly operational reviews, treating the JSON output as an input to our capacity planning process.
High Availability Configuration
HeatWave clusters on OCI run within MySQL Database Service, which provides automated backups, point-in-time recovery, and fault domain placement. HA configuration requires explicit topology decisions.
Standalone vs. HA MySQL DB System:
# Provision an HA MySQL DB System via OCI CLI oci mysql db-system create \ --compartment-id <compartment_ocid> \ --shape-name MySQL.4 \ --subnet-id <subnet_ocid> \ --admin-username dbadmin \ --admin-password '<StrongPassword>' \ --is-highly-available true \ --availability-domain <AD1> \ --data-storage-size-in-gbs 512 \ --display-name prod-heatwave-ha
HA mode provisions three MySQL instances across different fault domains within the same region — one primary and two secondaries using MySQL Group Replication. Failover is automatic and typically completes within 30 seconds.
Backup strategy: Point-in-time recovery (PITR) is enabled by default on OCI MySQL DB System. We configure automated backups with a 35-day retention window and test restoration quarterly:
# Trigger manual backup before schema changes oci mysql backup create \ --db-system-id <db_system_ocid> \ --backup-type FULL \ --display-name pre-migration-backup # List available restore points oci mysql backup list --compartment-id <compartment_ocid> --db-system-id <db_system_ocid>
OCI networking and private endpoints: HeatWave clusters must reside in private subnets. External access routes through OCI Bastion Service or a VPN, never through public IP assignment. We configure Security Lists to restrict port 3306 to known application subnet CIDRs only.
Data Reliability Engineering
Reliability for HeatWave deployments centers on three concerns: HeatWave node warm-up time, data consistency between InnoDB and the in-memory RAPID store, and ETL/ELT pipeline reliability when feeding the cluster.
HeatWave load and reload management: When a cluster restarts, HeatWave nodes must reload table data from InnoDB into memory. For large datasets, this warm-up period can span 5–30 minutes. We configure Autopilot-managed data placement so that high-priority tables load first:
-- Set table load priority
ALTER TABLE orders SECONDARY_ENGINE_ATTRIBUTE='{"load_priority":1}';
-- Manually trigger reload after a maintenance window
ALTER TABLE orders SECONDARY_LOAD;
Change Data Capture (CDC) patterns: When HeatWave feeds downstream consumers, we instrument binlog-based CDC using MySQL’s native binary logging rather than application-level change tracking. This ensures the CDC stream reflects committed transactions, not application assumptions.
Point-in-time recovery validation: PITR is only as reliable as the last validated restore test. At MinervaDB, we script quarterly restore-to-test-environment exercises and validate row counts and application connectivity before signing off.
Data Security
OCI IAM and MySQL user separation: Database user accounts in HeatWave environments follow principle of least privilege. Application service accounts receive SELECT and DML permissions on specific schemas only. DDL permissions are restricted to a separate deployment service account used only during schema migrations.
-- Create restricted application user CREATE USER 'app_read'@'10.0.1.%' IDENTIFIED BY '<password>'; GRANT SELECT ON sales.* TO 'app_read'@'10.0.1.%'; FLUSH PRIVILEGES;
Encryption at rest and in transit: OCI MySQL DB System encrypts data at rest using OCI Vault-managed keys by default. We configure customer-managed encryption keys (CMEK) for environments with regulatory requirements:
oci mysql db-system create \ --customer-managed-encryption-key-id <key_ocid> \ ...
TLS 1.2+ is enforced for all client connections. We validate this by checking ssl_cipher in active session metadata and rejecting any application connection that downgrades to unencrypted transport.
Audit logging: MySQL Enterprise Audit (available on OCI MySQL DB System) logs all connection attempts and DDL operations to a structured JSON log. We ship audit logs to OCI Logging service and configure alerts for privilege escalation patterns.
VPC/OCI network isolation: No HeatWave instance in any production environment we manage accepts inbound traffic from 0.0.0.0/0. All access is through OCI Bastion sessions or site-to-site VPN with certificate-based authentication.
Key Takeaways
- HeatWave offload rates above 95% require deliberate schema preparation and session configuration, not passive defaults.
- HeatWave Lakehouse enables hybrid queries across InnoDB operational data and OCI Object Storage archives in a single SQL statement.
- HA MySQL DB System uses MySQL Group Replication across three fault domains; failover completes within ~30 seconds.
- HeatWave ML trains classification and regression models entirely inside the database — no external ML infrastructure required.
- Node warm-up after cluster restart must be planned for; table load priority configuration minimizes recovery time for critical tables.
- All production HeatWave clusters should reside in private subnets with access gated through OCI Bastion or VPN.
- PITR and backup restore procedures must be tested quarterly — documented backup windows are meaningless without validated recoveries.
How MinervaDB Can Help
At MinervaDB, our engineering team has operated HeatWave clusters across financial services, retail, and SaaS production environments. We handle HeatWave cluster sizing, Autopilot tuning, HeatWave ML model lifecycle management, OCI networking hardening, and migration from on-premises MySQL deployments. Engagements range from a focused architecture review to fully managed ongoing operations where we own cluster health, backup validation, and performance SLAs.
If you are evaluating HeatWave for a new workload or troubleshooting an underperforming deployment, our team is ready to engage. Contact MinervaDB to start the conversation.
Frequently Asked Questions
What is the difference between HeatWave and a separate data warehouse like Snowflake?
HeatWave processes analytical queries inside the MySQL engine — no data movement, no ETL pipeline, no separate cluster to manage. The tradeoff is that HeatWave is optimized for datasets up to hundreds of terabytes within OCI, while dedicated warehouses offer broader ecosystem integrations. At MinervaDB, we help organizations determine which architecture fits the actual query patterns and data volumes at hand.
How long does HeatWave node warm-up take after a restart?
Warm-up duration depends on the total data volume loaded into HeatWave and the node shape’s memory bandwidth. Typical ranges are 5–30 minutes for datasets from 100 GB to several terabytes. We configure table load priority so that business-critical tables recover first, and we schedule maintenance windows to minimize user-facing impact.
Can HeatWave ML replace a dedicated ML platform like SageMaker?
For supervised classification and regression tasks on structured tabular data already resident in MySQL, HeatWave ML is a strong replacement — it eliminates data export, training infrastructure, and deployment pipelines. For unstructured data, computer vision, or NLP tasks, a dedicated ML platform remains appropriate. We assess each use case individually before recommending one approach.
How is HeatWave billed, and what controls costs?
HeatWave nodes are billed per node-hour. The primary cost levers are node count, node shape (memory), and hours the cluster runs. We implement HeatWave cluster start/stop automation on development environments to eliminate idle node charges, and we use Autopilot recommendations to right-size production clusters based on actual memory utilization.
Is migration from on-premises MySQL to HeatWave disruptive?
With proper planning, migration is non-disruptive. We use MySQL Shell’s dump/load utilities for initial data transfer, configure replication from the on-premises primary to the OCI MySQL DB System for ongoing sync, and execute a low-downtime cutover during a maintenance window. Schema compatibility assessment is the first step — we identify any data types or features that require remediation before migration begins.
