MySQL HeatWave, in depth: offloading analytics from InnoDB to an in-memory columnar cluster, loading, change propagation, diagnosing offload and when it is the wrong tool

Two engines behind one endpoint

A HeatWave deployment has two parts. The DB System is an ordinary MySQL server with InnoDB. It owns the data, runs every write, and runs every query that is not offloaded. The HeatWave cluster is a set of nodes that hold a copy of selected tables in memory, in a hybrid columnar format, partitioned across the nodes so that scans, filters, joins and aggregations run in parallel on all of them. In MySQL terms InnoDB is the primary engine and HeatWave is a secondary engine. Its engine name, which you will see in DDL and in EXPLAIN, is RAPID.

Why is a columnar copy faster for analytics? A query that sums one column of a wide table reads only that column in a column store. Values of one type sit together, compress well and can be processed many at a time with vector instructions. Spreading the table across nodes adds more cores and more memory bandwidth. The background is in columnar databases and OLTP versus OLAP. HeatWave's contribution is to hide that second system behind the MySQL optimizer, so the application does not change.

Loading tables

Nothing is offloaded until a table is loaded. There are two ways to do it. The manual way is three DDL steps per table. First, exclude columns that HeatWave cannot hold or that queries never use, by marking them NOT SECONDARY. Second, declare RAPID as the table's secondary engine. Third, run the load itself.

-- 1. Keep large or unsupported columns out of HeatWave memory.
ALTER TABLE orders MODIFY notes BLOB NOT SECONDARY;

-- 2. Declare the secondary engine (a metadata change; takes a brief exclusive lock).
ALTER TABLE orders SECONDARY_ENGINE = RAPID;

-- 3. Copy the rows from InnoDB into the cluster, read in parallel.
ALTER TABLE orders SECONDARY_LOAD;

-- Later, to free the memory:
ALTER TABLE orders SECONDARY_UNLOAD;

The automatic way is Auto Parallel Load, a stored procedure in the sys schema. It skips tables and columns that cannot be loaded, sets the secondary engine, checks that there is enough memory, chooses the load parallelism and loads the data. Run it in dryrun mode first. That prints the script it would run and loads nothing, which makes it a sizing tool as well as a loader. Newer releases (from MySQL 8.2.0) also have a guided load that folds the first two manual steps into the SECONDARY_LOAD statement.

-- See what would happen for the whole schema, without loading anything.
CALL sys.heatwave_load(JSON_ARRAY("shop"), JSON_OBJECT("mode", "dryrun"));

-- Then load it.
CALL sys.heatwave_load(JSON_ARRAY("shop"), JSON_OBJECT("mode", "normal"));

Loading takes locks and reads every row, so treat it like any other bulk operation: schedule it, and watch replication lag and application latency while it runs. Supported column types and table requirements change between releases, so check the documentation for your version rather than relying on memory, and let the dry run tell you what it will skip.

Change propagation and stale tables

After the first load, the copy has to keep up with InnoDB. DML on the DB System is collected and applied to HeatWave in batch transactions. According to the HeatWave documentation, a batch is sent every 200 milliseconds, or when the propagation buffer reaches 64 MB, or when a HeatWave query reads data that DML has changed. That third trigger is what keeps an offloaded query consistent with the writes before it. Writers never wait: INSERT, UPDATE and DELETE on InnoDB are not delayed by propagation.

Propagation can fail. When it does, the table is marked stale, and queries that touch a stale table are simply not offloaded. They still run and still return correct answers, but on InnoDB, which is slow. Some propagation failures are recovered by an automatic reload when the cluster is idle; for others you must unload and reload the table yourself. Monitor for this, because the only symptom your users see is that analytics got slow.

-- Is change propagation running at all?
SELECT VARIABLE_VALUE FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'rapid_change_propagation_status';

-- Per table: TRANSACTIONAL means changes are propagated (9.2.1 and later naming;
-- earlier releases report RAPID_LOAD_POOL_TRANSACTIONAL).
SELECT NAME, POOL_TYPE
  FROM performance_schema.rpd_tables
  JOIN performance_schema.rpd_table_id USING (ID)
 WHERE SCHEMA_NAME = 'shop';

How the optimizer decides to offload

Offload is automatic but conditional. The optimizer first estimates the cost of the query. Cheap queries, such as primary-key lookups and small range scans, stay on InnoDB, because below the secondary_engine_cost_threshold the round trip is not worth it. For the rest, every table the query touches must be loaded and not stale, every column it references must be loaded, and every function and construct it uses must be supported. If all of that holds, the plan is compiled for HeatWave.

The session variable use_secondary_engine controls what happens next. OFF never offloads. ON, the default, offloads when possible and silently falls back to InnoDB when not. FORCED offloads or returns an error. The silent fallback is convenient in production and dangerous everywhere else, because a query that stops qualifying still works, only much more slowly. The error from FORCED is what you want in tests.

-- Is this query offloaded? Look for the Extra column.
EXPLAIN SELECT DATE(created_at) d, SUM(total) FROM orders GROUP BY d;
-- ... Extra: Using secondary engine RAPID

-- Make a report query fail loudly instead of falling back.
SELECT /*+ SET_VAR(use_secondary_engine = FORCED) */ DATE(created_at) d, SUM(total)
  FROM orders GROUP BY d;

Worked example: a report that stopped offloading

A shop loads orders and order_items. A daily revenue report runs on HeatWave. Its EXPLAIN shows Using secondary engine RAPID. Then a developer adds a filter on orders.notes, a BLOB column that was marked NOT SECONDARY at load time. The report still returns the right numbers, but it now runs on InnoDB, scans every row of both tables, and competes with checkout traffic.

Diagnosis takes three steps. First, EXPLAIN no longer shows the RAPID line. Second, enable the optimizer trace and read the offload markers:

SET SESSION optimizer_trace = "enabled=on";
SET optimizer_trace_offset = -2;

SELECT ... ;   -- the report

SELECT QUERY, TRACE->'$**.Rapid_Offload_Fails'
  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
-- "Column notes is marked as NOT SECONDARY."

SELECT QUERY, TRACE->'$**.secondary_engine_not_used'
  FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Third, fix it. Either move the filter onto a loaded column, for example a short VARCHAR flag derived from the notes, or reload the table with the column included if it is supported and worth the memory. Then add the query to a test that runs it with use_secondary_engine = FORCED, so the next change that breaks offload fails in CI rather than in the database. The same trace also reports other reasons: an unsupported function (HW_ER_1014), a cost below the threshold, or HeatWave's dynamic threshold rejecting the query.

Operating it

• Size memory with the dry run. Auto Parallel Load estimates memory before loading. Leave headroom for growth and for query working memory, since a query that runs out of memory on HeatWave falls back or fails. The rapid_execution_strategy = MIN_MEM_CONSUMPTION setting trades speed for memory on large queries.
• Load only what analytics reads. Every loaded column costs cluster memory. Wide text and JSON blobs that reports never touch should be NOT SECONDARY.
• Watch offload rate, not only latency. Track the fraction of heavy queries that run on RAPID. A drop in that rate is the early warning for stale tables, schema changes and new unsupported syntax.
• Alert on stale tables and propagation status. Poll rpd_tables and rapid_change_propagation_status from your monitoring. The approach in OCI monitoring works for this, and so does any agent that can run SQL.
• Plan for reloads. A stale or unloaded table does not offload until it is loaded again. Know how your deployment reloads after maintenance and how long a full load takes for your data, and do not schedule critical reports straight after it.
• Keep writes on InnoDB in mind. HeatWave accelerates reads only. A write-heavy workload still needs a DB System sized for it, and very high DML rates increase propagation work on the cluster.

Trade-offs against the alternatives

HeatWave is strongest when the analytical questions are about the same data the application writes, and when one team owns both. It is weakest when analytics needs data from many systems, when history is far larger than the memory you want to pay for, or when you want to stay portable across clouds and engines. Check which clouds and regions currently offer it; Oracle's own is described in the OCI overview.

What to do next

1. Pick the five slowest analytical queries on your MySQL database and write down their current run times.
2. Run sys.heatwave_load in dryrun mode on their schema and read what it would skip and how much memory it needs.
3. Mark unused wide columns NOT SECONDARY, load the tables, and confirm Using secondary engine RAPID in EXPLAIN for each query.
4. For any query that does not offload, read Rapid_Offload_Fails in the optimizer trace and fix the cause.
5. Add those queries to a test suite that runs with use_secondary_engine = FORCED.
6. Add alerts for stale tables, propagation status and offload rate, and record how long a full reload takes.