An analytical replica for MySQL, on Oracle’s HeatWave benchmark

We ran the benchmark Oracle uses to sell MySQL HeatWave on DBTrail, next to a Percona Server taking 500 transactions per second (TPS). With the analytic sessions pointed at DBTrail’s port instead of MySQL, they:

• Finished 919 queries in 30 minutes, against 290 in 2 hours on MySQL alone
• 12 times more a minute, and none killed.
• And MySQL got faster: the transactions’ 99th percentile went from 42 ms to 31 ms, because the heavy reports were no longer running on it.

DBTrail is an open source (Apache 2.0) analytical replica for MySQL, Percona Server, MariaDB and Amazon RDS. It reads your binlog the way a replica does, keeps a columnar copy of your tables in Parquet files, and answers the heavy queries on that copy with DuckDB, while your MySQL keeps the transactions.

The best-known version of that idea is MySQL HeatWave. Oracle keeps InnoDB for the transactions and a columnar copy of the same tables in memory next to it, and a router looks at each query and sends the heavy ones to the copy without the application changing a line.

But HeatWave lives only inside Oracle’s own MySQL service. If your MySQL is Percona Server on your own machines, MariaDB, or Amazon RDS, the way in is to replicate into a paid HeatWave DB system and run your reports there. HeatWave’s engine is not open source; the MySQL under it is.

The replica does the same job next to the MySQL you already have. Two parts of it matter here:

• Engine Routing: the replica gives you a MySQL port. For every statement it decides who answers. Cheap lookups and anything inside a transaction go to your MySQL. Heavy reads go to the copy. (More on how it decides.)
• Fresh mode: before the copy answers, it adds the changes captured since its last refresh. The answer is seconds old, not minutes.

HeatWave is the yardstick everyone knows, so we measured the replica with the benchmark Oracle uses to sell it. We did not run HeatWave itself: every HeatWave figure in this post is one Oracle publishes.

The test: Oracle’s own HeatWave test program

Oracle publishes its HeatWave numbers for a mixed workload derived from CH-benCHmark: transactions derived from TPC-C writing, and 22 analytic queries reading the same tables at the same time. The test program is OLTPBench, patched by Oracle and published under Apache 2.0 (oracle/heatwave, commit 8aa9f662). We used that program and those queries.

The load, as Oracle runs it:

• TPC-C-derived transactions at 500 TPS, from 128 sessions.
• 4 sessions running the analytic queries back to back.

What we ran it on, all on AWS:

• Source: Percona Server 8.4.11 on an r7i.2xlarge (8 vCPU, 64 GB).
• DBTrail, a build of what shipped as 0.122.0, on an r7i.4xlarge (16 vCPU, 128 GB), in fresh mode, with Engine Routing.
• 100 warehouses (about 10 GB). Oracle used 1,000.

Every query ran with a 5-minute limit, on both sides.

Two runs:

1. MySQL alone. The analytic sessions connect to MySQL directly. 2 hours.
2. Through DBTrail. The analytic sessions connect to DBTrail’s port instead. Nothing else changes: same queries, same MySQL, same transaction load. 30 minutes.

The runs are not the same length. The MySQL-alone run is the long one on purpose: a query that takes minutes needs hours to give a median. We say below what that costs the comparison.

One change to Oracle’s schema. Its tables declare foreign keys with ON DELETE CASCADE. Before MySQL 9.6, and on MariaDB, a cascade is applied inside InnoDB and writes no row event for the rows it changes, so nothing reading the binlog sees them. DBTrail notices those tables and stops applying recent changes to them (it answers from the last full read instead, which is correct but older). For this test we declared the same foreign keys as NO ACTION. The benchmark never deletes a parent row, so no query sees a difference.

Results: 12 times more analytic queries a minute

12 times more queries a minute, none killed. Through DBTrail, the 4 sessions finished 919 analytic queries in 30 minutes: 30.6 a minute. On MySQL alone they finished 290 in 2 hours: 2.4 a minute. On MySQL, 64 of those 290 hit the 5-minute limit and returned nothing. Through DBTrail, none did. The slowest one took 47 seconds.

Median time per query, by query:

Query	MySQL alone	Through DBTrail	Speedup
Q1	50.9 s	3.9 s	13×
Q2	41.6 s	4.4 s	10×
Q3	3.8 s	3.7 s	1×
Q4	29.2 s	4.6 s	6×
Q5	killed after 5 min (13 of 13)	6.9 s	more than 43×
Q6	22.3 s	0.5 s	43×
Q7	29.6 s	5.1 s	6×
Q8	14.8 s	3.8 s	4×
Q9	15.6 s	11.9 s	1×
Q10	killed after 5 min (14 of 14)	29.8 s	more than 10×
Q11	44.0 s	1.3 s	34×
Q12	20.1 s	0.24 s	85×
Q13	17.1 s	0.37 s	47×
Q14	24.0 s	0.23 s	105×
Q15	killed after 5 min (11 of 14)	4.4 s	more than 68×
Q17	158.3 s	6.8 s	23×
Q18	killed after 5 min (13 of 13)	37.2 s	more than 8×
Q19	8.8 s	7.2 s	1×
Q20	130.8 s	3.1 s	43×
Q21	killed after 5 min (13 of 13)	5.9 s	more than 51×
Q22	3.3 s	3.0 s	1×

(Q16 is not in Oracle’s set.) Where MySQL hit the limit, the real time is longer than 5 minutes, so the speedup is a floor. Q5 alone, run on MySQL with no limit and no load, took more than an hour.

The rows at 1× are not a failure. Q3, Q9, Q19 and Q22 are cheap enough that Engine Routing sent them to MySQL, which is the point: of the 919 queries, the copy answered 767 and MySQL the rest. HeatWave makes the same choice with its own cost threshold.

Transactions: still 500 TPS, and faster

The transactions held 500 TPS in both runs. Their 99th percentile went from 42.0 ms with MySQL running the analytics to 30.8 ms with DBTrail running them. The analytics were not taking InnoDB’s CPU and buffer pool any more.

Freshness: how old each answer was

Each answer from the copy carries how far behind the source it was. Over the run, the median was 9 seconds, 90% of answers were under 49 seconds behind, and the worst was 289 seconds.

What Oracle publishes for HeatWave

We never ran HeatWave. Everything in this section is what Oracle itself publishes, quoted from its MySQL HeatWave Performance Benchmarks page. We did not measure, test or verify any of it.

For the same mixed workload at 1,000 warehouses (100 GB), Oracle’s MySQL HeatWave Performance Benchmarks page reports, for HeatWave:

• 2 HeatWave.512GB nodes plus a MySQL.32 shape.
• 30,000 transactions per minute (TPM), the same 500 TPS, with 0.02 s latency.
• 6.6 OLAP transactions a minute (each one an analytic query), with 35 s average latency.
• US$16,427 a year, compute only.

Two things to know before setting that next to ours:

• It is not the same size, so we do not claim to be faster than HeatWave. Oracle ran 10 times our data on far larger machines.
• Oracle forced every query to HeatWave with a hint (set_var(use_secondary_engine=forced)). That measures the columnar engine, not the automatic routing. Our run used DBTrail’s routing as it ships: no hints, and nothing in the queries changed.

The page gives no number for freshness: how old the data was when HeatWave answered. We publish ours above, measured on every answer.

Limits

• The source got a bigger neighbor. DBTrail ran on twice the source’s cores. A plain MySQL replica of the same size would also take the load off the source, but it still runs these queries at row-store speed: Q5 alone, with no load at all, took more than an hour on MySQL.
• The two runs differ in length, as said above. The MySQL-alone run had more time for its slow queries to finish, which favors MySQL.
• Foreign keys with cascades get older answers, as explained above.
• Analytics only. HeatWave also does machine learning, vector search and a lakehouse over object storage. DBTrail does none of that. For vectors on MySQL there is MyVector, a plugin for MySQL 8.0 and 8.4.

One endpoint for the application

HeatWave’s application connects to one MySQL and never knows which engine answered. DBTrail’s port behaves the same way for reads: a dashboard or BI tool on a private network can point at it directly.

For an application that also writes, we put ProxySQL in front of both. Writes, SELECT ... FOR UPDATE and everything inside a transaction go to MySQL. Other reads go to DBTrail’s port, which then decides between the copy and MySQL as above. We tested each case: writes and locking reads reached MySQL, a heavy read inside a transaction reached MySQL, and the same heavy read outside one was answered by the copy.

Sessions that open a transaction by turning autocommit off, instead of with BEGIN, need DBTrail 0.124.0 or later.

What it costs

The analytical replica is free and open source (Apache 2.0, on GitHub). It runs next to the MySQL you have, on a machine you choose, and you keep Percona Server, MariaDB or RDS. It does need its own machine: for a load like this one, as big as the source or bigger. We are not claiming that hardware is cheaper than HeatWave’s. The saving is the license, and not having to move your database to get the feature.

Try it

• Install DBTrail and add your MySQL as a source.
• Run MySQL and DuckDB on the same connection explains Engine Routing: what goes to the copy and what stays on MySQL.
• Vadim Tkachenko measured DBTrail on reports over a TPC-C load and on the 22 TPC-H queries, on the Percona blog: DBTrail: analytical reports on your MySQL data without running them on MySQL.
• The test program is Oracle’s, linked above. The schema change is the one described in “The test”.

Questions

Is MySQL HeatWave open source? No. HeatWave runs only inside Oracle’s MySQL service. DBTrail is an open source (Apache 2.0) analytical replica that runs next to the MySQL you already have.

Does the application need changes? No. Reports point at DBTrail’s port instead of MySQL. Applications that also write can put ProxySQL in front of both, as above.

How fresh is the data? In this run, a median of 9 seconds behind the source, with fresh mode on. Fresh mode is off by default.

What does it not do? Machine learning, vector search and a lakehouse. DBTrail does analytics only.

---

The workload is derived from TPC-C and CH-benCHmark, as in Oracle’s test program. It does not follow the TPC specifications, and the results are not comparable to published TPC results.

MySQL and HeatWave are trademarks of Oracle and/or its affiliates. DBTrail is not affiliated with or endorsed by Oracle. We have never run, tested or signed up for HeatWave, and have not accepted its terms; all information about HeatWave in this post comes from what Oracle itself has published.