Optimize OLTP and OLAP Workloads with MySQL HeatWave Autopilot

As businesses grow, their databases must support both fast transactions and timely analytics. OLTP workloads handle everyday operations such as orders and payments, while OLAP workloads analyze large datasets for reporting and planning. Without appropriate optimization, growing data volumes and competing resource demands can slow transactions and delay insights.

MySQL HeatWave supports online transaction processing (OLTP) through MySQL InnoDB and accelerates eligible online analytical processing (OLAP) queries through the HeatWave cluster. Autopilot helps with workload-based index recommendations for OLTP and loading, encoding, data placement, and execution improvements for OLAP.

How MySQL HeatWave serves both workloads

Applications connect to the MySQL HeatWave database System and submit SQL transactions. MySQL InnoDB engine handles transactional storage and updates, while eligible analytical queries can be offloaded to the HeatWave cluster. Loaded data is distributed across HeatWave nodes, and subsequent DML changes are propagated automatically.

Start by identifying which queries serve transactions and which perform analytics. Then check their execution paths and measure each workload against its own service target.

Improve OLTP with Autopilot Indexing

OLTP workloads include customer lookups, order creation, inventory updates, and payment status changes, etc. A useful tuning goal is predictable response time at the application’s expected concurrency. Measure transaction latency, transactions per second, and write latency before making changes.

Available from MySQL 9.0.0, Autopilot Indexing analyzes statement digest history from Performance Schema and recommends adding or dropping secondary indexes. Its output includes estimated workload benefit, storage impact, and DDL for the proposed changes.

Review the advisor’s recommendations against the whole workload: faster reads must be weighed against index maintenance costs on writes. To view the available indexing advisor options on a supported deployment:

CALL sys.autopilot_index_advisor(
  JSON_OBJECT(
    'target_schema', JSON_ARRAY('<your_schema>'),
    'output', 'normal'
  )
);

For scheduled/scripted collection, run the query below. Recommendations are saved to autopilot_index_advisor_report.

CALL sys.autopilot_index_advisor(
  JSON_OBJECT(
    'target_schema', JSON_ARRAY('<your_schema>'),
    'output', 'silent'
  )
);

The Autopilot Indexing documentation explains the recommendations and required privileges. It treats projected gains as estimates and validate changes with measured results. It also checks whether long transactions, repeated queries, or contention on frequently updated rows explain slow requests. An index recommendation addresses only part of the transaction path.

Key prerequisites and guardrails:

• It needs a representative workload with at least five statements in Performance Schema digest history.
• Re-run when workload patterns materially change.
• Test proposed new indexes for read improvement versus write/storage cost.
• For proposed index removals, make the index invisible first, monitor, then drop only after validation. Keep in mind – index hints can break if an index is removed.
• It does not recommend primary-key, foreign-key, functional, partitioned-table, or LIKE-optimizing indexes.

Optimize OLAP with workload history

MySQL HeatWave Autopilot Advisor uses machine learning, data analysis, and query history to generate recommendations for analytical workloads. Advisor analyzes the last 1000 successfully executed MySQL HeatWave queries. Its capabilities address different sources of performance and memory pressure.

Advisor is workload-aware and provides recommendations based on machine learning models, data analysis, and MySQL HeatWave query history. Advisor analyzes the last 1000 successfully executed MySQL HeatWave queries.

Example 1, recommend string column encodings:

CALL sys.heatwave_advisor(
  JSON_OBJECT(
    'target_schema', JSON_ARRAY('<your_schema>'),
    'auto_enc', JSON_OBJECT('mode', 'recommend')
  )
);

This recommends encodings that may reduce memory usage and improve analytical query performance.

Example 2, recommend data placement keys:

CALL sys.heatwave_advisor(
  JSON_OBJECT(
    'target_schema', JSON_ARRAY('sales')
  )
);

With these options, the advisor runs Auto Data Placement. It analyzes query history and recommends keys that can reduce data movement for JOIN and GROUP BY operations. It requires at least five qualifying queries in HeatWave query history.

Example 3, recommend rarely used tables to be unloaded from HeatWave cluster:

CALL sys.HEATWAVE_ADVISOR(
  JSON_OBJECT(
    'target_schema', JSON_ARRAY('<your_schema>'),
    'auto_unload', JSON_OBJECT(
      'mode', 'recommend',
      'last_queried_hours', 168
    )
  )
);

The results are written into the heatwave_autopilot_report table. Treat recommendations as candidates: apply and measure in staging before production, particularly data-placement changes and unloads.

Optimize mixed OLTP and OLAP workloads

When a client application submits a query, the MySQL Query Optimizer evaluates several criteria to determine the execution path.

As of MySQL 9.0.0, MySQL HeatWave uses Dynamic Query Offload to automatically select between InnoDB and HeatWave Rapid engine for eligible queries, by considering query characteristics, available InnoDB indexes, the HeatWave query queue, and change propagation status when choosing the execution engine.

For mixed workloads, this allows an indexed customer lookup to stay on InnoDB while a large analytic aggregation can run on HeatWave. This choice adapts to current conditions, helping support responsive transactions and efficient analytics without application-managed query routing.

Dynamic query offload works automatically in MySQL HeatWave 9.0.0 and later; it requires no special query syntax. Keep use_secondary_engine set to ON to allow engine selection.

Here is an example assuming sales.orders is already loaded into an active HeatWave cluster:

-- Allow automatic engine selection
SET SESSION use_secondary_engine = ON;

-- OLTP example: an indexed lookup may stay on InnoDB
EXPLAIN FORMAT=TRADITIONAL
SELECT id, customer_id, total
FROM sales.orders
WHERE id = 1001;

-- OLAP example: a large aggregation may use HeatWave
EXPLAIN FORMAT=TRADITIONAL
SELECT customer_id, SUM(total) AS revenue
FROM sales.orders
GROUP BY customer_id;

For the analytical query, look for Using secondary engine RAPID in the Extra column. Its presence confirms HeatWave offload; routing depends on the query and current system conditions.

-- Require HeatWave for this query
SET SESSION use_secondary_engine = FORCED;

SELECT customer_id, SUM(total) AS revenue
FROM sales.orders
GROUP BY customer_id;

-- Restore automatic selection
SET SESSION use_secondary_engine = ON;

Summary

Optimizing OLTP and OLAP workloads helps businesses maintain fast transactions while delivering timely analytics. MySQL HeatWave with Autopilot supports both through workload-aware index recommendations, automated data loading, and analytical query optimization. Dynamic Query Offload selects between InnoDB and HeatWave for eligible queries based on query characteristics and current system conditions. Together, these capabilities reduce manual tuning and help teams manage performance as data volumes and workload demands grow.