So we tried it. The first run failed, and it failed in a way that is easy to miss: the replica took every transaction, reported success, and stored nothing. We tracked down why, fixed it, and the whole test suite passes now. This post is what we tested, how we checked it, the bug we found, and where it stands.

It’s still an experiment, not production software. The code and the test harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.

Why replicate into DuckDB

A DuckDB table on one server is already useful. The analytical queries get fast and the application does not change. But almost nobody runs their reports on the primary – they run them on a replica, so the big scans stay out of the way of the OLTP traffic.

So the shape of it is simple. The primary stays InnoDB and takes the writes. The replica has the same tables, only marked ENGINE=DuckDB. Row-based replication ships the changes across, the replica writes them into the column store, and the reports run there. You get an analytics replica out of the replication you already run.

Row events are engine-agnostic on purpose. The primary logs the row changes, not the SQL, and the replica applies them through the storage-engine API. On paper, then, the replica should not care that one side is InnoDB and the other DuckDB. We wanted to see the paper version hold up on a running server.

The setup

Two containers from the same image, one primary and one replica. It’s all in Docker, so it repeats cleanly.

• Primary: InnoDB, binlog_format=ROW, GTID on.
• Replica: same server, GTID on, tables made with ENGINE=DuckDB.
• Replication uses SOURCE_AUTO_POSITION=1.

One thing you have to get right before any data moves. Create the replica tables as ENGINE=DuckDB yourself. A CREATE TABLE … ENGINE=InnoDB on the primary goes into the binlog with the ENGINE word still in it, and the replica runs it exactly as written, so you would end up with an InnoDB table there, not a DuckDB one. There is no automatic mapping. Pre-create the DuckDB tables on the replica, and let the row changes flow into them.

-- primary (InnoDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=InnoDB;

-- replica (same columns, DuckDB)
CREATE TABLE t1 (id BIGINT PRIMARY KEY, region INT, amount DECIMAL(12,2)) ENGINE=DuckDB;

The other rule is a primary key on the replica table. UPDATE and DELETE row events find the row by its old image, and the engine needs the key for that. INSERT works without one, but put a key on it anyway.

One script drives all of this: bench/tb/07-replication-spike.sh. It starts both containers, wires up replication, runs every scenario below, and prints PASS or FAIL for each.

What we tested, and how

The part that matters is the checking. Row counts are not enough – the replica can hold the right number of rows and still have the wrong data in them. So after each step the script dumps the whole table on both sides, ordered by primary key, and compares an md5 of the two dumps. One byte off is a FAIL. And rather than sleep between steps, it waits on WAIT_FOR_EXECUTED_GTID_SET(), so the checks do not race the replica.

Here is what went through it.

Basic DML. Insert, update a row, delete a row, compared after each one.

All the column types, in a single wide table: signed and unsigned integers, DECIMAL, DOUBLE, DATE, DATETIME, TIMESTAMP, CHAR, VARCHAR, TEXT, BLOB, a few NULLs, and a unicode string. Insert it, update it, compare byte for byte. Blobs get their own note below.

DDL. ALTER TABLE ADD COLUMN, ALTER TABLE ADD INDEX, and DROP TABLE against a DuckDB replica table. These arrive as statements. We check that the column shows up, the index shows up, the old rows survive, and the drop removes the table.

Transactions. A transaction with two inserts and an update has to land on the replica as one unit. A transaction the primary rolls back has to leave nothing behind. We also open a transaction straight on the replica and both roll it back and commit it, to check the engine’s own commit and rollback.

Bulk load. 5000 rows through LOAD DATA on the primary, has to arrive and match.

Durability. Two cases, and the second is the hard one.

• Clean restart. Stop the replica properly, write on the primary while it is down, start it again, and see it pick up from its GTID position.

• Crash. Apply some rows, then SIGKILL the replica. No clean shutdown, no checkpoint. Bring it back, write more on the primary, and check one exact thing: every row present once. Nothing lost – DuckDB has to replay its write-ahead log when it opens the file – and nothing applied twice, which means the saved position has to line up with the data that actually reached disk.

The bug: multi-engine transactions lost data

The first full run fell down on the wide-table test. Zero rows on the replica, and then everything after it failed too. The applier had stopped with HA_ERR_KEY_NOT_FOUND. It went to UPDATE a row that was not there, because the INSERT before it had returned success and written nothing.

When a scenario fails, the harness saves the applier error, both server logs, and both schemas. The replica log had the line that mattered:

[Warning] Combining the storage engines InnoDB and DuckDB is deprecated, but the
statement or transaction updates both the InnoDB table mysql.slave_worker_info and the
DuckDB table rpl.wide.

That line is the whole thing. A replica does not only write your data. In the same transaction it also writes its own position into InnoDB system tables – mysql.slave_worker_info, the relay-log info, gtid_executed. So every applied transaction touches two engines at once: InnoDB for the position, DuckDB for the data. Two engines means MySQL runs a real two-phase commit: prepare, then commit.

Our prepare was wrong. It took the open DuckDB transaction, moved it into a registry meant for external XA COMMIT, and cleared the per-connection state. Then commit looked at that state, found it empty, and committed nothing. The position went into InnoDB, the GTID advanced, the binlog moved on, and the DuckDB rows were thrown away. No error anywhere. The replica looked healthy while it dropped every write.

We cut it down to the smallest case, with no replication at all. One server, one transaction into a DuckDB table and an InnoDB table:

BEGIN;
INSERT INTO duck VALUES (1,10),(2,20),(3,30);   -- DuckDB
INSERT INTO inno VALUES (1,10),(2,20),(3,30);   -- InnoDB
COMMIT;
-- duck: 0 rows   inno: 3 rows

InnoDB kept its three rows, DuckDB kept none, and COMMIT said it was fine. A DuckDB-only transaction was fine as well, because with one engine MySQL skips the prepare step. It only broke with a second engine in the transaction. And on a replica, that is every transaction.

The fix

Small change, in the engine’s transaction code. prepare now remembers which prepared transaction belongs to the connection, and commit finishes that one instead of an empty state. External XA is untouched. It went out as v0.2.3.

With that in place the reproducer keeps three rows in both tables, and the full run comes back clean, crash test included:

[8]  data integrity: all column types, NULL / unicode / negatives ....... PASS
[9]  DDL replication (ALTER ADD COLUMN / ADD INDEX / DROP) .............. PASS
[10] transactions (atomic commit, rollback, engine commit/rollback) ..... PASS
[11] bulk LOAD DATA on master -> replica ................................ PASS
[12] durability: graceful restart, then SIGKILL crash recovery .......... PASS

VERDICT: PASS=24  FAIL=0

The crash case is the important one. After a SIGKILL in the middle of applying, the replica came back with every committed row exactly once, matching the primary. Committed transactions survive the kill, and the position stays in step with them.

We left two tests behind so this cannot slip back in quietly: an MTR test, txn_mixed_engine, that runs a mixed DuckDB+InnoDB transaction on every build, and scripts/repro-2pc-dataloss.sh, which you can point at any published image to check it.

What works, and what doesn’t yet

Where it stands on v0.2.3, for an InnoDB primary feeding a DuckDB replica:

The things to keep in mind:

• Create the replica tables as ENGINE=DuckDB yourself. A replicated CREATE TABLE keeps the primary’s engine, so it will not turn into DuckDB on its own.

• Replica tables need a primary key for UPDATE and DELETE.

• The applier goes row by row. That is fine for a normal OLTP change stream. It is not fine for keeping up with a primary that bulk-loads at full speed – the replica will fall behind.

• Committed transactions are crash-safe, with one small gap. The engine holds a prepared-but-not-committed transaction in memory only, so a crash in the short window between prepare and commit can lose that single transaction. The applier commits right away, so the window is small, but it is not zero.

• Blobs behave differently over replication than through a direct statement. A plain UPDATE of a BLOB or TEXT column has a known limit in the engine and does not apply. Over replication it does apply, because the row event carries a full before-and-after image instead of the shared buffer the direct path uses.

And the obvious one. This is an experiment. It is a functional result from a test harness on small data, not an HA or failover benchmark. We did not test multi-source replication, filters, or a real write rate.

Where it stands

An InnoDB primary feeding a DuckDB replica works on v0.2.3. Inserts, updates, deletes, every common type, schema changes, transactions, bulk load – they all replicate and match, and it comes back clean from both a graceful restart and a hard kill. The one real bug, silent data loss on every replicated transaction, is found, understood, fixed, and covered by tests.

It is not production-ready, and we do not treat it as such. But the idea holds up. Point normal MySQL replication at a DuckDB replica, and you get an analytics copy that keeps itself in sync.

The post Replicating from InnoDB into a DuckDB storage engine appeared first on Percona.

Here is what came out. InnoDB finished 18 of the 22 queries and spent more than 28 hours of query time on them. Four never finished. Our engine ran all 22 in about three minutes. It loaded the data 25 times faster than InnoDB, and it used 5 times less disk. On the queries it stays close to plain DuckDB, and on a few it is ahead.

It’s still an experiment, not production software. Code and the benchmark harness are on GitHub under GPLv2: https://github.com/Percona-Lab/ducksdb-mysql-engine.

The machine, and how we ran it

• One server, 80 cores, 187.5 GB RAM.
• SF500: about 500 GB of raw CSV, 3,000,028,242 lineitem rows.
• Three engines, one at a time: InnoDB, our engine, native DuckDB.
• All of it through the harness in the repo (bench/tb), in Docker.

Two details about how we ran it change how the numbers read.

The load streams. We generate a chunk of CSV, load it, delete it, then generate the next one. So the disk never holds more than one 20 GB chunk, which is the only reason 500 GB fits on the box at all.

And “native DuckDB” is not a second copy of the data. It opens the engine’s own DuckDB file read-only and queries that. Same bytes on both sides. That keeps the comparison honest, and it means there is no separate native load time to report.

Loading the data

InnoDB took 25.5 times longer. The engine hands LOAD DATA straight to a DuckDB COPY instead of going row by row through the handler, so the three billion lineitem rows go in in about nineteen minutes, and the whole set in thirty-six. InnoDB inserts row by row and builds the primary key as it goes. That is where the rest of the fifteen hours goes.

Storage on disk

DuckDB stores columns and compresses them, so 500 GB of CSV comes down to 132 GB. InnoDB stores rows and carries the index with them, and it ends up bigger than the CSV it came from: 673 GB, five times the DuckDB file. The InnoDB lineitem.ibd on its own is 446 GB. That is more than three times our entire database.

Storage, lower is better. The DuckDB engine holds all of SF500 in 132 GB.

Query time

All 22 queries. Warm runs, minimum of a few, in seconds. InnoDB had a two-hour cap per query; the ones that hit it are marked DNF.

SF500, all 22 queries, log scale, lower is better. Hatched InnoDB bars did not finish inside the cap.

Two things to take from this.

InnoDB is far behind, which is no surprise. Scanning three billion rows for a wide GROUP BY or a six-way join is the wrong job for a row store. Four queries (Q9, Q13, Q17, Q20) did not finish at all, and the eighteen that did add up to more than 28 hours. This is the exact problem the engine is for. It is not a mark against InnoDB, which is doing the transactional job it was built for.

The comparison worth reading is our engine against plain DuckDB, since both are the same DuckDB reading the same file. Over all 22 they are close: 186 seconds for ours, 153 for native. Query by query it goes both ways. On the selective ones ours is often faster — Q6 (1.3 vs 4.1), Q19 (2.4 vs 8.6), Q17, Q20. On the biggest joins native wins — Q18 (27 vs 12), Q21, Q1. That gap comes from settings, not data: the memory limit, the thread count, and running inside mysqld versus a bare CLI. Either way, both are around a thousand times faster than the row store.

Correctness

We checked the answers, not only the clock. For every query we compared our engine’s output to native DuckDB’s, numbers rounded to four decimals and the order ignored. 21 of 22 matched exactly. None mismatched. One was skipped because a result file came back empty on one side. So the engine gives the same answers as plain DuckDB.

What this means, and where it stops

At 500 GB the small-scale picture holds and gets sharper. Analytical queries that took hours on InnoDB, or never finished, come back in seconds on the DuckDB engine. The load is far quicker, and the footprint is far smaller. All of it inside one MySQL server, with the tables queried the normal way.

The limits are the same as before:

• It is for analytics, not OLTP. Point lookups and single-row work stay on the row path, where an index seek is the right tool.
• DuckDB runs inside mysqld, so a heavy query under a tight memory limit can go over budget. DUCKSDB_MEMORY_LIMIT and DUCKSDB_TEMP_DIR let it spill to disk instead of failing. We set a limit here so the big CTEs spill rather than get OOM-killed.
• Some queries still fall back to normal MySQL and run on the row path.
• It is one workload on one machine. The result is strong, but the engine is still an experiment, not something for production traffic.

Try it

Pull the image and run your own queries:

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=secret \
  perconalab/ducksdb-mysql-engine:latest

The engine, the patches, and the harness that produced these numbers are on GitHub: https://github.com/Percona-Lab/ducksdb-mysql-engine. The per-query numbers and the method are in the repo. If it breaks, or your hardware gives different numbers, open an issue.

The post The DuckDB MySQL engine at 500 GB appeared first on Percona.

This article is also available in Chinese: 中文版. Browse all English articles.

Have you ever run into a mysqld process that has been starting for a long time and still won’t come up? When that happens, you can use perf top to check what the MySQL process is mainly doing. If what you see looks like the figure below — the MySQL main thread (the one starting from mysqld_main) spending the vast majority of its time rolling back transactions — then you are very likely hitting a large-transaction rollback.

The most common way to get here is a large transaction that fills up the disk while writing its binlog, crashing the instance. The largest binlog file I have run into was over 114GB. Since the Binlog Cache’s temporary file is only cleaned up after the binlog is written, that transaction occupied 228GB in total. The MySQL parameter binlog_error_action controls the behavior when writing to the binlog file fails. The default is ABORT_SERVER, which shuts the process down. You can also set it to IGNORE_ERROR, which closes the binlog file on a write failure so that later transactions produce no binlog at all. That obviously leaves the primary and the replica inconsistent, so don’t use it unless you have no other choice.

Root Cause

Why does the main thread have to roll transactions back when the MySQL process starts? It comes from the binlog crash-safe mechanism; here is only a brief overview. DML in a transaction produces binlog events, and when the transaction commits, those events are written to the binlog file and persisted. To keep the data and the binlog consistent after a crash and restart, MySQL designed a crash-safe mechanism that applies two-phase commit (2PC) to ordinary transactions, also known as internal XA.

As the figure shows, under internal XA a transaction commits in three steps:

1. The storage engine prepares the transaction. The transaction state changes from ACTIVE to PREPARED, and both the state and the XID are persisted to the redo log.
2. The transaction produces an Xid_event, which is written to the binlog file together with the DML binlog events and persisted.
3. The transaction commits.

When the server goes down unexpectedly, a transaction may be in one of the following states:

• Active: under two-phase commit, this kind of transaction was never written to the binlog.
• Prepared but not written to the binlog (or only partially written): the transaction is already in the Prepared state, but its XID does not appear in the binlog file.
• Prepared and written to the binlog: the transaction is already in the Prepared state, and its XID appears in the binlog file.
• Committed: the transaction has been written to the binlog and committed.

For a Committed transaction, the design already guarantees that its binlog events made it into the binlog file, so the binlog and the data are consistent and nothing needs to be done at startup. For an Active transaction, the binlog events certainly never reached the binlog file, and InnoDB has a background rollback thread that rolls it back automatically. A Prepared transaction has to be handled according to the XID information in the last binlog file: if its XID appears in the binlog file, the transaction must be committed to keep the binlog and the data consistent; otherwise it must be rolled back.

Handling Prepared transactions is called Binlog Recovery, and it must be completed before MySQL starts serving users. Committing a transaction is usually fast, but rolling one back generally takes about as long as executing it did. If a transaction took an hour to execute, the rollback will very likely take another hour, and MySQL is unavailable throughout.

Why must all these transactions be resolved before the server starts serving? It has to do with how the XID is implemented. An XID is made up of the MySQL prefix plus a query_id, and query_id is a global counter that starts over from 1 after a restart. If the earlier Prepared transactions are neither committed nor rolled back after startup, two Prepared transactions may end up with the same XID, and recovery has no way to tell which one to commit and which one to roll back.

Rolling Back Prepared Transactions Asynchronously

In AliSQL, we designed an asynchronous rollback mechanism to solve this problem.

As the figure shows, this design splits the rollback of a Prepared transaction into two parts:

1. The main thread sets the transaction state to Active and persists that state.
2. InnoDB’s background rollback thread asynchronously rolls back all of the transaction’s changes.

Binlog Recovery can start serving traffic as soon as the first part is done. Since that step executes very quickly, Binlog Recovery finishes in a very short time.

After a crash and restart, Active transactions are rolled back directly by InnoDB’s background thread, without needing the XID to drive the decision. So during recovery, simply changing the state of the transactions to be rolled back from Prepared to Active avoids the problem of two Prepared transactions sharing an XID. The key here is to persist the Active state, so that the transaction is still Active after a crash and restart and InnoDB will roll it back automatically.

Community InnoDB already rolls a Prepared transaction back by first setting it to Active and then undoing it from the undo records. The Active state is written to the redo log; it is simply not persisted at that moment. However, InnoDB persists the redo log once per second by default, so the state gets persisted very soon after the change. This means that when a large-transaction rollback keeps an instance from starting, even on community MySQL, we only need to force a restart of the mysqld process and the large transaction turns into a background rollback that no longer blocks startup.

The source code for this feature was contributed to MariaDB and has been merged into MariaDB 11.7; see MDEV-33853 for details.

Conclusion

With the asynchronous rollback design, the Binlog Recovery phase only has to set Prepared transactions to Active, while the genuinely time-consuming rollback is carried out asynchronously by InnoDB’s background rollback thread. This optimization shortens a startup that used to take tens of minutes, or even hours, to one that completes in seconds.

This investigation began as a performance comparison for different memory allocators. However, during benchmarking, I discovered unexpected effects deserving a more detailed explanation. I hope you find these findings both interesting and useful.

Imagine you need to set up a MySQL database server. Every detail is planned: the operating system, the CPU architecture, the number of cores, the amount of RAM, the storage capacity and speed. On paper the hardware looks like it can handle the workload. But in reality, things rarely go exactly as planned. So, conducting a thorough stress test is the next thing to do.

2. Realities of stress testing

You configure your MySQL server setting the innodb_buffer_pool_size to 70-80% of your available RAM. This creates a large fast buffer for your data and indexes, reducing the need for slower disk input/output.

After a warmup period and a few hours of testing, everything looks great. The server is working at a steady pace, performance is stable. You tick the box – the server has passed the basic stress test. Thinking everything is fine, you consider leaving the test running over the weekend, expecting only minor fluctuations in performance.

However, when you check the status the next morning, you find that the CPU is idle and the mysqld process has vanished. Did it crash? You check the server error logs, but there is no record of a crash or a shutdown—not even a core dump. Then, you look at the system logs and find something unexpected:

journalctl -k -g mysqld

Jun 09 07:29:14 beast-node7.tp.int.percona.com kernel: Out of memory:
Killed process 3936620 (mysqld) total-vm:194627592kB, anon-rss:183047480kB, file-rss:640kB, shmem-rss:0kB,
UID:955676158 pgtables:355860kB oom_score_adj:0

It appears that mysqld ran out of memory and was terminated by the OOM (Out of Memory) killer after running for about 16 hours.

We will focus on Resident Set Size (RSS), which is the subset of Virtual Memory Size (VSZ). RSS is the most significant part of VSZ and other parts like swap (only 8Gb) do not make notable contributions.

The RSS reached 183GiB, significantly higher than the initial 145GiB (with the innodb_buffer_pool_size set to 135G). The mysqld process had grabbed nearly 40GiB of extra memory, which at first looked like a memory leak. I ran my stress tests on different versions of MySQL and Percona Server and found a recurring pattern: memory usage climbed steadily until the system killed the process.

I won’t dive into the leak diagnosis here, but the result was clear: mysqld wasn’t leaking memory in the traditional sense. However, we still had to explain that 40GiB growth.

3. Configuration and methodology

The configuration was as follows:

The testing was done as follows:

What we wanted to achieve:

• Create conditions when memory allocations and deallocations inside the database server are frequent.
• Utilize as much of physical memory as possible (at least 80%) by giving it to InnoDB Buffer Pool.
• Use all available CPU resources in the most efficient way to prevent threads contesting for execution time (the number of connections should match the number of logical CPU cores).
• Eliminate any layers that add overhead and get in the way of direct measuring of allocators frequency and efficiency. The connections will be established using a socket file.

Servers configuration file:

# Make sure data dir is on NVMe
datadir=/nvme/data

# Thread Pool is enabled only for Percona Server
plugin-load-add=thread_pool.so
thread_pool_size=16
thread_pool_max_threads=5000
thread_pool_stall_limit=500

# Disable binary logging
skip-log-bin

# Connection settings
max_connections = 200

# Logging
log-error = /home/bogdan.degtyariov/servers/data/mysql-error.log
pid-file = /home/bogdan.degtyariov/servers/data/mysql.pid

# Socket
socket = /tmp/mysql-alloc-test.sock

# Disable SSL requirement
require_secure_transport = OFF

# Other settings
sql_mode = ""
wait_timeout = 288000        # 80 hours
interactive_timeout = 288000 # 80 hours

# Table settings
default-storage-engine = InnoDB

# InnoDB redo log configuration
innodb_redo_log_capacity = 32G

# Minimize flush overhead (not crash-safe, but optimal for testing)
innodb_flush_log_at_trx_commit = 0

# Memory configuration
innodb_buffer_pool_size = 150G # Configurable down to 80G
innodb_buffer_pool_instances = 16
innodb_io_capacity = 20000

# Performance optimizations
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 256M
innodb_doublewrite = OFF

# Transparent Huge Pages can be turned ON or OFF for the testing
large-pages = ON

4. Where did the memory go?

Memory management is complex, so let’s simplify. Applications rarely talk directly to the Linux kernel because the kernel typically works in 4KB pages, which is inefficient for developers. Instead, applications use allocators like glibc malloc, jemalloc, or tcmalloc. These tools handle memory operations by minimizing overhead, managing bookkeeping, and preventing fragmentation. Most importantly, they use caching.

When a program frees memory, the allocator rarely returns it to the OS immediately. Instead, it moves that memory into an internal “free-list” cache. Reusing memory from this cache is much faster than requesting new memory from the kernel.

Also, the Percona Server for MySQL and upstream MySQL Server use their own implementation of the memory arena allocator called MEM_ROOT. Historically MEM_ROT was architected decades ago when the standard Linux implementation of glibc memory allocator was slow and prone to lock contention in multithreaded programs.

Enabling memory profiling revealed that MEM_ROOT allocations for cursor metadata in stored routines was responsible for most of the additional memory acquired by the server process:

sp_head::execute_procedure           (TPC-C stored procedure)
   └─ sp_instr_copen::execute        (OPEN <cursor> statement)
       └─ sp_cursor::open
           └─ mysql_open_cursor
               ├─ Materialized_cursor::send_result_set_metadata  87831 MB (94.3%)
               └─ Query_result_materialize::start_execution      5311 MB  (5.7%)
                   └─ MEM_ROOT::Alloc / AllocBlock / ForceNewBlock

NOTE: 87G is a significant growth of memory allocation considering that in that run the server initially allocated ~85G with Innodb_buffer_pool_size=80G.

The problem happens regardless of the data size because the actual issue is in stored routines cursor metadata. When the stored procedure is called the memory allocated for cursor metadata is not freed. Over the course of many repeated calls to the same stored procedure the cumulative amount of memory for the cursor can reach any value.

The following graph demonstrates the memory growth in Percona Server 8.4.8-8 from ~80G to over ~180G in RSS and over 200G VSZ over the period of 24 hours.

Thus, a bug was reported for Percona Server: https://perconadev.atlassian.net/browse/PS-11472

With Percona Server for MySQL 9.7.0-1 the RSS/VSZ growth was at a slower rate, but still noticeable and it was not flattening towards a stable horizontal line (the server was configured with a small amount of memory for innodb_buffer_pool_size=4G and run for 5 hours instead of 20).

Memory profiling showed the new allocations in version 9.7.0-1 were in the same place where cursor metadata is handled:

sp_head::execute_procedure           (TPC-C stored procedure)
   └─ sp_instr_copen::execute        (OPEN <cursor> statement)
       └─ sp_cursor::open
           └─ mysql_open_cursor
               ├─ Materialized_cursor::send_result_set_metadata
               └─ Query_result_materialize::start_execution      
                   └─ MEM_ROOT::Alloc / AllocBlock / ForceNewBlock 4,025.8 MB (99.6%)

5. Possible workarounds

My tests showed that OOM crashes happened consistently under two specific conditions:,

1. Connections are never closed and stay open permanently
2. Connections ran queries at maximum speed without any pauses

Also, when the connection lifetime was limited and users were made to close connection and reconnect after 1M transactions, the memory exhaustion stopped, and memory was freed correctly – all with only a minor impact on performance. To minimize the delays associated with creating a new connection thread on the server I used the connection pool functionality in HammerDB. When the connection lifetime is ended, the actual connection is not closed, but “reset” and reused. This frees the context accumulated during the connection activity and stimulates returning memory to the OS. This connection pool mechanism is more efficient than the open/close cycle for maintaining the connection lifetime. 

I had two runs with reconnecting users: with and without connection pool. The graph demonstrates that using the pool improves the performance in this test.

Also, during another experiment with unlimited connection lifetime, adding a 0.5ms pause after a few transactions prevented the crashes, though performance dropped slightly more.

The memory graphs have consistent periodic oscillations that never reach into the dangerous zone.

6. Summary

To sum it up: the observed MySQL’s memory bloating is caused by a problem in the server cursor implementation not freeing metadata memory. 

Under heavy, constant load, that memory accumulates to the amount which eventually causes an OOM crash. Capping how long connections stay active or adding a short pause between transactions, gives the server time to clean itself up. Normally the client side processing adds such pauses without need to do it on purpose.

Finally, it is important to remember that the best benchmark results do not always guarantee the best real-life performance.

The post Stored Procedures memory consumption in Percona Server for MySQL appeared first on Percona.

Over my career as a database reliability engineer, I've come to a conclusion which I don't see repeated often:

All practical implementations have to balance the opposing concerns of correctness vs. performance & availability (this is kind of similar to CAP theorem , but not exactly the same).   Perfect correctness with no data loss across geographic distances would result in a database which is too slow or too costly to be useful for most applications. And these constraints cannot be overcome because it's the physical bounds of reality which imposes these limits.

Why geographic distances? I'll explain this step-by-step below.

Step 1: A Single Isolated Database Instance

You start with installing a copy of PostgreSQL or MySQL on a single instance, or using a managed database product from a cloud provider such as AWS's RDS.  At this scale the database performs everything you need.  Full ACID compliance, fast reads and writes, transactions all work beautifully.  We're done, right?

A happy little database on its own.  You'll never see one this happy again.

Well only if you ignore that hardware and VM instances are not flawless.  Despite the database software being more than adequate, sometimes that underlying hardware or VM infrastructure will fail.  The naive response is to just wait until the original instance can be restored and you continue on your merry way.  However this could take minutes, or hours, or days.  If you have customers paying to use your service, they're not going to be so patient.  So now you need High Availability! 

Step 2: High Availability

High Availability means that you want your system to recover to a usable state as quickly as possible.  How quick? It depends on the specific implementation.  For me, <10 seconds is common.  <1 second is the goal.  For systems like RDS their default gives you ~2 minutes average recovery time, with options that will get that down to ~30 seconds.  Not really HA-enough for my taste, but for most people it's a vast improvement over hours or days of outage!

BUT THERE IS A TRADEOFF -- A COST!

Do you see it yet? It's that improving reliability means having another copy of your database ready to take over when the primary fails. Maintaining that copy takes time. Time is the tradeoff.

Every time you write data to your primary/live instance, that data must be recorded somewhere else where it will be available when another database instance takes over to maintain availability. Well, you could certainly build a system where this isn't true, but imagine your customer's surprise when you flip from one database instance to another and data they thought they had persisted suddenly disappears.  Even worse is the problems you would invite when you flip back to the original copy of the database and that data suddenly appears again.  This is our "correctness" problem.

 The correctness problem is this: when we have to maintain multiple copies of our "source of truth" data, how can we make sure they all stay in-sync?

I have some good news and bad news on that front: the good news is that we absolutely can keep all our copies perfectly in-sync and ensure perfect correctness.  The bad news is our database system will now be so slow it's probably unusable on a practical level.

 It works like this:

 When a request comes to our primary/active database to update/insert/delete data, we can pause the transaction at the time of commit and go transfer that transaction data to our other copies.  Only once we have confirmed the data has been durably persisted to our other copies do we finish the commit and return a success to the original transaction's client.  This adds time to the client's request.  They have to wait for data to be transferred over the network between our databases.  For two database instances in the same datacenter, this could be microseconds -- not terrible, maybe not even noticeable.

 However, that's not the end of the problems we've added.  Now what happens if our backup database fails and can no longer accept updates, even when it's not being actively used? To maintain correctness we would have to stop accepting writes to the primary database as well!  It's the only way to ensure they always remain perfectly in sync is to treat a failure of one node as a whole-system failure.

 Wait, we were supposed to be increasing availability.  Did we just actually decrease it instead? Also when the primary database fails and we flip to the secondary we now no longer have a backup copy and we lose HA properties until the other instance is restored.

 We could just run more backup copies, but now we have more data transfers to keep everything in-sync.  We could just say we only need 2 out of N nodes to be in-sync at all times and mark the others as unusable temporarily until they can re-sync.  Or is that 3 out of N, so that we have a backup-for-the backup.  And our cost to serve a single copy of our data set has gone up to what? 3x? 5x?  It's starting to get more expensive now too.

Now I hope you're starting to see the complexity of the problem here.  We could go into permutations of redundant architectures until the cows come home, but I'll spare you.  Suffice it to say that every single architecture we could examine or invent is going to run into the same fundamental limit -- it takes time to keep copies of our data up-to-date perfectly. And the only way to mitigate the time constraint is to relax the correctness constraint. There is no way around this.

 And we're not even done yet, because our highly available system still only operates in a single physical datacenter. A single disaster which takes out the whole datacenter still means we're hosed.  Many companies just call this good enough and accept the risk (after all us-east-1 never goes down, right?)  But for others, their customers won't be happy with an extended outage even if you can claim it's not your fault.  For true fault-tolerance you need yet another copy of your data in some other physical location, usually far enough so that the same hurricane, or earthquake or power grid outage doesn't affect both locations.  This is how we arrive at geographic distribution.

Step 3: Disaster Recovery & Geographic Databases

 The astute will note that this is just an extension of the same problem we have with transit times in our HA setup, now with larger distances involved.  This should be easy! How much more time could we possibly have to manage?

Let's take New York to Los Angeles as an example.  If you were able to send data at the speed of light, it would take 16 milliseconds! And that's a one-way trip.  To let our primary database receive a confirmation that the data was received we need a minimum of 32 ms.  And keep in mind this is the theoretical maximum the laws of physics allow for a straight-line path.  In practice even if our network was fiber from end-to-end, we still have stops at various routers along the way for processing.  A real network request therefore takes more like 66ms per trip, and a 130ms round-trip time.

I have yet to experience any commercial enterprise willing to accept database write latency of 130ms.  Using cloud services you might end up paying thousands, or even tens of thousands of dollars per year for a system that can process <100 write transactions per second.

Sad databases, so far apart.

 So what do you do?  How do you surmount the laws of physics? You don't. You MUST compromise something.

And that's the entire point of this article -- you cannot escape the fundamental laws of physics.  You only choose what properties are desirable and know that you will be giving up other things.  If you absolutely cannot tolerate data loss, then your system will be incredibly slow (or costly).  If you want great performance and efficiency, there will be ways you can lose data.

 I talk about the laws of physics because someone might see the 130ms round-trip-time and think that we just need to do some fancy computing to optimize that.  Or change how we build networks. But even if we did that we'd get at most ~4x improvement. There is not even a single order of magnitude left between our current performance and the maximum allowed by the laws of physics.  We cannot optimize time much more than we already have!  No matter how advanced technology of the future becomes, this same problem will still exist until the end of the universe. Even at the speed of light, it takes a significant amount of time to move data.

 Most companies use a strategy of creating an HA cluster with strong consistency guarantees within a single datacenter only, and then using an "eventual consistency" approach to shipping data to another geographic location. If the need arises to run their application from a different geographic location, they call it "disaster recovery" and let clients know that recovery could take hours and some data loss is expected. This applies equally if you're using async replication to a warm-standby database or if you're taking backups or snapshots and filling in the gaps between full backups with transaction logs.  If your primary datacenter fails while processing user requests, there will always be some window of time where data written to your database at that location won't make it to your backup. It could be seconds, or minutes. No matter what you cannot guarantee consistency with an async update model.

 This is why databases are hard -- there's never a perfect solution which will work all the time for all use-cases.  And this also ignores the entire other class of database problems which relate to availability which is what happens when someone writes a bad query that DOSes your database.  Just scale up your database, or just partition it, right? But as we see in this article, scaling a database is hard because everything takes time.  Partitioning is hard because you create new consistency problems.  Availability is hard because keeping things in-sync is hard.

A Postscript 

Communicating this is honestly the most challenging part of my job.  Initial development of most software projects starts with the single isolated database either from a cloud provider or just on someone's laptop.  They develop against a non-distributed database system and a tiny database size and everything works! It's blazing fast, it's perfectly consistent. No durability issues, etc.  When engineers take this to production they are soon frustrated by the production database which seems slower and less reliable.  They underestimate that the production database has so much more demands and constraints against it.

Yet I will talk with an engineering team one week that stresses how important consistency guarantees are for them.  Sure, I can do that.  Then the next week I'll talk to another team that demands the fastest performance possible.  Now we have a challenge.  Then after an incident I'll be yelled at by a manager who says we need to make availability our highest priority because our largest customer is threatening to churn.  Then as we approach the end of our fiscal year I'll have other people breathing down my neck saying we need to cut costs. Then this whole cycle repeats.

 You can try to fix this by running multiple database systems.  The slow-yet-scalable system; the ultra-fast-but-lossy system; the perfectly-consistent-but-tiny metadata store.  This is why so many companies run several different types of databases.  Redis for ephemeral data, MySQL/PostgreSQL for transactional guarantees, key-value stores for easy scaling of simple data.  But now the job is to make sure engineers are choosing the right location for their data.  Inevitably not all data will find the right home on the first try and migrating data from one system to another is always a big task which isn't fun.  It all feels a bit like Sisyphus some days, but at least it's job security!

“Through transparent roadmaps, community-driven collaboration, contributor programs, and the MySQL Governance model, we aim to create an environment where innovation can accelerate while preserving the reliability, compatibility, security, and operational excellence that organizations around the world depend on.”

Q1. Oracle has announced a “new era” of MySQL community engagement at MySQL’s 30th anniversary. Can you walk us through what specifically prompted this strategic shift, and what concrete changes can the community expect to see in how Oracle approaches MySQL development and governance?

HVC: Throughout 2025 we celebrated 30 years of MySQL and reflected on the past and present, but more importantly, the future. The MySQL Community team sought feedback from around the globe on how to lead the next generation of MySQL innovation and open source collaboration. We came to Jason in November and shared that feedback and proposed a plan to rebuild community trust. By December we agreed on a plan, calling it a new era of Community Engagement.

We have entered a deeper collaboration with the MySQL Community, focused on faster innovation, greater transparency, deeper community collaboration, and expanding the ecosystem. Starting with the April 2026 release, we’re delivering more features directly into the MySQL Community Edition core while preserving the stability customers rely on.

As part of this effort, we introduced the MySQL Governance model, which provides clear pathways for participation, community leadership, and long-term collaboration with the broader MySQL ecosystem. Together, these initiatives are designed to build deeper trust, accelerate innovation, and grow the MySQL ecosystem.

Q2. One of the most significant announcements is moving previously commercial-only features into the MySQL Community Edition. What drove this decision, and what other enterprise features are you planning to bring to the community edition in the coming months?

JW: Both our Community and our Customers are asking for stability and faster innovation. At the same time, they want more visibility into our roadmap and a stronger voice in shaping it. Driving some previously Enterprise-only features into the Community Edition addresses both needs, while we not only deliver those features, but we also build, prioritize, and deliver new features and innovations into MySQL.

With the GA of MySQL 9.7.0 LTS, MySQL moves from the 9.x innovation series to a new Long-Term Support release line. This begins the 9.7.x LTS series, giving users a stable branch to standardize on while continuing to build on the innovation delivered through the 9.x cycle.

This release matters not only because it establishes the next LTS baseline, but because it reflects a broader direction for MySQL. Over the last several releases, we have talked about giving users earlier visibility into what is coming, broadening access to important capabilities, and working more openly with the MySQL community. With MySQL 9.7.0 LTS, that direction is reflected in the product itself.

Several capabilities previously limited to MySQL Enterprise Edition are now available in MySQL Community Edition, while Dynamic Data Masking is now available in MySQL Enterprise Edition. Together, these changes make MySQL 9.7.0 LTS a meaningful release for DBAs, developers, and operators across both editions.

More capability in MySQL Community Edition

One of the biggest themes in MySQL 9.7.0 LTS is the continued expansion of MySQL Community Edition. Across 4 major technical areas, this release delivers 8 notable new Community Edition capabilities — a substantial broadening of what DBAs and developers can do with Community Edition.

The 4 major areas

• Replication observability and HA behavior
  • Flow-control monitoring
  • Multi-threaded applier extended statistics
  • Automatic Eviction & Rejoin
  • Up-to-date Aware Primary Election
• Telemetry and observability integration
  • Telemetry / OpenTelemetry support
• Modern application development
  • MySQL JSON Duality Views
• Query optimization and performance
  • Hypergraph Optimizer
  • Profile-Guided Optimization (PGO)

Q3. Some community members have expressed concerns about MySQL’s development velocity and commit rates. Jason, as SVP of Data Services, what specific steps are you taking to address these concerns, and how do you plan to balance cloud service development with core MySQL innovation?

JW: Oracle has invested heavily in MySQL since 2010, and we hear feedback from the community. People want to see that investment show up in a more visible way, especially through faster delivery in the open. We’re working on that in a few concrete ways: getting more features into MySQL Community Edition, sharing more of the roadmap and worklogs, using Early Access releases to get feedback earlier, and creating more public forums where contributors can talk directly with the MySQL engineering team.

We’re also putting more structure around how people can participate, through the MySQL Governance model, contributor summits, design discussions, and clearer contribution paths. The goal is straightforward: be more open about where MySQL is going and give the community more practical ways to influence priorities, test features earlier, report issues, and contribute improvements. Cloud and core MySQL are not separate priorities for us — the core database is the foundation for Community, Enterprise, and HeatWave, so continued innovation in MySQL itself remains central to everything we’re doing.

In addition to accelerating innovation, we are creating more opportunities for community participation through public roadmaps, Early Access releases, public discussions, contributor summits, and the MySQL Governance model. Together, these initiatives provide greater transparency into our priorities while creating structured mechanisms for contributors to participate, provide feedback, and help influence the future direction of MySQL.

Q4. Oracle has published the MySQL Community roadmap and promised to facilitate community contributions through worklogs and bug reports. How will this differ from past practices, and what mechanisms are you putting in place to ensure transparent, bidirectional communication between Oracle’s engineering team and external contributors?

HV: With our Community Engagement Plans, MySQL customers and users get the best of both worlds: enterprise-grade stability and faster access to innovation. They’ll also have greater visibility into what’s coming and more opportunities to provide input, which helps them align MySQL with their own technology roadmaps. In addition to publishing select worklogs and CVE information, we have continued Labs for new features and early access releases leading up to the 9.7 launch, which will continue in future releases, with our next Early Access planned for early July. These provide valuable insight and transparency to community members and invaluable feedback to the engineering team. 

We have organized a series of public discussions (four so far), with a fifth planned for July, as well as established a quarterly Contributor Summit and regular design meetings under the MySQL Governance model. The first Contributor Summit took place in May 2026, with a design meeting held the week prior. The next Contributor Summit is scheduled for August 2026 in Broomfield, Colorado.

The governance model provides structured pathways for participation through code contributions, testing, documentation, reviews, technical discussions, and community leadership. It introduces clearly defined roles—including Contributors, Committers, Project Leads, Core Project Leads, a Steering Committee, and a Vulnerability Group—to help ensure transparent collaboration while maintaining MySQL’s standards for quality, stability, compatibility, and security.

In the last quarter, we also published the MySQL Developer Guide, which describes how to effectively contribute and participate in the evolution of MySQL.

To catch up on previous discussions, see highlights from earlier sessions:

• Edition #4 highlights (contributions and feature requests)
• Edition #3 highlights (bugs and contributions)
• Edition #2 highlights (ecosystem and metrics)
• Edition #1 highlights (community roadmap)

Q5. How do you see the MySQL governance structure evolving to give the community a stronger voice while maintaining Oracle’s stewardship?

HV: The MySQL Governance model is a key part of how we are evolving community participation while maintaining Oracle’s long-term stewardship of the project. The model is built on principles of transparent processes, merit-based participation, shared stewardship, and a commitment to quality, stability, compatibility, and security.

Oracle remains the primary steward of MySQL while creating clearer pathways for the community to participate in shaping the project’s future. Community members can contribute through code, testing, documentation, bug reports, design discussions, and reviews. As contributors gain experience and demonstrate sustained engagement, they can take on greater responsibilities through defined governance roles.

The model also introduces a Steering Committee that brings together perspectives from Oracle, users, customers, hyperscalers, and the broader open source ecosystem to help guide long-term priorities, governance evolution, ecosystem growth, and community engagement.

Together with public roadmaps, Early Access releases, GitHub collaboration, contributor summits, and design meetings, the governance model creates a structured framework for community participation while preserving the engineering excellence and operational stability that organizations around the world depend on.

Q6. PostgreSQL has been gaining ground with features like pgvector for AI workloads, while MySQL faced criticism for lack of similar capabilities. How does Oracle plan to ensure MySQL remains competitive not just with PostgreSQL, but also with cloud-native databases and newer entrants in the database market?

JW: MySQL offers a uniquely predictable and stable operational model at global scale, combined with strong performance and ease of use. Backed by Oracle, it delivers enterprise-grade reliability while maintaining the flexibility and innovation of open source. We will continue to collaborate with the community to deliver innovations based on our published roadmap into MySQL Community Edition. 

Q7. The move of MySQL into Oracle’s cloud organization raised concerns about resource allocation. Can you address these concerns and explain how Oracle is ensuring MySQL has the engineering resources it needs to execute on this new community-focused vision?

JW: MySQL’s success has always come from the combination of strong stewardship and a vibrant community. Oracle continues to invest deeply in both. What’s new is increased transparency, stronger engagement with the community, and more structured ways for contributors, partners, customers, and ecosystem participants to help shape MySQL’s future through the MySQL Governance model and related community programs.

These investments complement our continued engineering investment in MySQL Community Edition, MySQL Enterprise Edition, and MySQL HeatWave.

Q8. You’ve mentioned expanding collaboration with Linux distributions, particularly Canonical and Ubuntu, as well as supporting major open source projects like WordPress and Drupal. What does this ecosystem support look like in practice, and how will Oracle work with companies that some might consider competitors in the MySQL space?

HV: We have built relationships and communication between the MySQL Community Team and open source maintainers to ensure the pathways are smooth for projects to build their projects and platforms using MySQL.  We continue to strengthen communications and remove barriers to collaboration. 

That spirit of collaboration is reflected in the MySQL Governance model and community engagement efforts. The recent Contributor Summit brought together Oracle engineers and contributors from organizations including Amazon, Google, Percona, ProxySQL, Readyset, VillageSQL, and participants from across the broader MySQL ecosystem, including MariaDB, to share ideas and help shape the future of MySQL.

We continue to focus on growing and expanding the MySQL ecosystem, referencing the analogy of a rising tide lifting all boats. Growing the community and bringing more collaboration and alignment makes us all stronger together and creates opportunities throughout the ecosystem.

Q9. For organizations currently running MySQL in production, what’s your message about long-term support and the roadmap? With MySQL 8.0 approaching end of life and MySQL 9.7 LTS published in April 2026, how should enterprises plan their migration strategies and what assurances can you provide about stability and backward compatibility?

JW: MySQL offers a uniquely predictable and stable operational model at global scale, combined with strong performance and ease of use. Backed by Oracle, it delivers enterprise-grade reliability while maintaining the flexibility and innovation of open source. 

The release introduces a new long-term support version of MySQL Community Edition, and MySQL Enterprise Edition, along with expanded feature delivery into the core, early access capabilities, and the first phase of our enhanced transparency and community engagement model.

Q10. Looking beyond the immediate announcements, what is Oracle’s five-year vision for MySQL? How do you see MySQL evolving to meet the demands of AI workloads, cloud-native architectures, and modern developer expectations while preserving the simplicity and reliability that made it the world’s most popular open source database?

JW: MySQL powers everything from startups to hyperscale platforms. It’s used by companies like Uber and Booking, and underpins major platforms like WordPress and Ubuntu. That breadth of adoption is a strong validation of its reliability and scalability.

 The vision is simple: build MySQL in the open with the community, accelerate innovation without sacrificing quality or stability, and continue to scale and grow the ecosystem around the world’s most widely used open source database platform.

A key part of that vision is establishing a sustainable governance framework that enables broader participation, develops future community leaders, and creates stronger connections between Oracle, contributors, customers, partners, hyperscalers, and the broader open source ecosystem.

Through transparent roadmaps, community-driven collaboration, contributor programs, and the MySQL Governance model, we aim to create an environment where innovation can accelerate while preserving the reliability, compatibility, security, and operational excellence that organizations around the world depend on.

Jason Wilcox
Senior Vice President, Data and AI Platform, Oracle Cloud Infrastructure (OCI)
Jason Wilcox leads the Data and AI Platform organization at Oracle Cloud Infrastructure (OCI), overseeing the design and development of OCI’s data platforms, AI infrastructure and platform services, and open source technologies. His portfolio spans cloud-scale data services, data processing and integration platforms, operational services for AI workloads, and widely adopted open source technologies that developers and enterprises rely on to build modern applications. These services help customers manage and use data, run AI workloads, and operate secure, reliable, and scalable systems on OCI.

Heather Vancura
Vice President, External Standards & Community Engagement, Oracle Cloud Infrastructure (OCI) Heather VanCura is Vice President of External Standards & Community Engagement at Oracle, where she leads Java Community programs and the MySQL Community Outreach team. With over 20 years of experience at Oracle and Sun Microsystems, she is a central figure in the global ecosystem, focusing on community growth, engagement, and standardization efforts.

………………….

Follow us on X

Follow us on LinkedIn

This article is also available in Chinese: 中文版. Browse all English articles.

Since MySQL 5.6, the MySQL replication team has been working to reduce replication lag. The first step was schema-level parallel application of the binlog, but schema-level parallelism only helps when writes are spread across many databases; in the common case, where most write traffic hits a single database, it provides almost no parallelism. MySQL 5.7 then introduced the Commit-Order parallel-replay strategy, which depends on how many transactions run concurrently on the primary: the replica can replay quickly only when the primary is highly concurrent. When concurrency on the primary is low, the replica still replays slowly and lag builds up. To fix that, MySQL 5.7 also introduced the Writeset (row-level) strategy, which lets the replica replay in parallel quickly no matter how concurrent the primary is.

We rolled out the writeset-based strategy across our fleet long ago, and it eliminated roughly 60% of our replication-lag problems. Another more than 30% comes from large transactions and DDL — the hardest replication-lag problem to solve in MySQL. Last year we built a mechanism in AliSQL called Binlog Realtime Replication (BRR) that solves it completely.

How Binlog Realtime Replication Works

The figure above shows why large transactions and DDL cause replication lag. Binlog replication works at the granularity of a transaction: a transaction’s events are written to the binlog file only after it commits, then shipped to the replica and executed there (a DDL can be treated as a single transaction). The change becomes visible to applications only once the replica finishes executing. If a transaction takes a long time on the primary, it takes just as long on the replica, and the lag equals the replica’s execution time. In practice the lag is often worse. First, a large transaction produces very large binlog events, which adds transmission delay. Second, while a large transaction — especially a DDL — is running, it can block the replay of other transactions, so relay log piles up; once the large transaction or DDL finishes replaying, that backlog also needs time to drain before the replica catches up.

The idea behind the optimization is simple: have the replica start executing the large transaction or DDL at the same time as the primary, and once the primary commits, tell the replica to commit too. With this mechanism, replication lag for large transactions and DDL stays under one second. The chart below compares the lag from a large transaction before and after the optimization: with realtime replication, large transactions no longer cause lag, and neither do DDLs.

The feature has been enabled by default in our RDS service since 2025. To date more than 3,000 instances have used it, running realtime replication about 300,000 times for large transactions and about 60,000 times for DDL.

Implementing Realtime Replication

The core idea of realtime replication fits in one sentence: as soon as the primary starts executing, it ships the binlog events (or DDL) to the replica, which executes them in lockstep; when the primary finally commits or rolls back, the replica does the same.

Realtime replication has two parts: realtime transmission and realtime application. Realtime transmission streams the binlog events a large transaction produces on the primary to the replica as they are generated; that part is covered in Binlog Transmission Optimization for Large MySQL Transactions. Realtime application replays those events on the replica as they arrive, using a dedicated group of replay threads, as shown below:

While a transaction runs on the primary, the binlog events it produces are first buffered in the Binlog Cache. If the transaction is large (the Binlog Cache exceeds a threshold), the primary’s Dump thread reads the Binlog Cache temporary file and sends the events straight to the replica. The replica writes them into a dedicated Brr Cache (not the relay log file), where a new group of Brr Worker threads applies them in real time.

For DDL, binlog events are produced later than for a large transaction — a DDL writes its Query_log_event into the Binlog Cache only during the commit phase. BRR therefore handles DDL specially: once the primary starts executing the DDL, it builds the Query_log_event directly and puts it in an in-memory buffer, ddl_query_buffer; the Dump thread reads events from this buffer and sends them to the replica, where a Brr Worker again executes the DDL in real time.

As a result, replica execution of DDL and large transactions shifts from run only after the primary finishes to run on the primary and replica in parallel, leaving only network transmission and commit as the residual lag — typically on the order of tens of milliseconds.

Below we look at how BRR is implemented, from both the primary and the replica side.

Overall BRR Architecture

Primary Side

When a large transaction or DDL needs realtime replication, a Brr_trx is created and registered with the Brr_trx_manager.

Brr_binlog_sender is an extension of the Dump thread; it reads events from a Brr_trx and pushes them to the replica. Originally the Dump thread did just one thing: read events from the binlog file and send them to the replica. BRR gives it one more job — poll each active Brr_trx, read binlog events from its Binlog Cache temporary file or from ddl_query_buffer, and send them to the replica.

Realtime transmission reuses the existing Dump channel. To tell BRR traffic apart from ordinary traffic, BRR borrows an idea from Semisync and attaches an extra BRR Header to each event; the header identifies whether an event is BRR or ordinary replication traffic. And to keep BRR events from choking the ordinary binlog-event channel, BRR applies flow control.

Replica Side

Using the BRR Header, the replica’s IO thread splits events into two kinds: BRR events go into the Brr_cache, while normal events take the original path into the relay log.

Brr_cache is the replica-side storage for a BRR transaction; each BRR transaction has one Brr_cache. When the IO thread receives a BRR event, it uses the brr_index in the header to locate the matching Brr_cache (if it’s the first event, it creates a new Brr_cache and wakes a Brr Worker), writes the event into the Brr_cache temporary file, and updates the readable position.

Brr_rpl_info manages these BRR transactions.

The BRR Worker threads are dedicated to applying BRR transactions. When idle, a Brr Worker picks a Brr_cache that hasn’t started being applied and makes itself its owner. From then on it is bound to that Brr_cache, looping to read and replay binlog events until it sees a Gtid_log_event (the primary has committed) or receives a BRR_ROLLBACK_EVENT (the primary rolled back).

The gtid_executed Snapshot

The uncommitted BRR transactions from the primary run in parallel on the replica alongside already-committed transactions. If a BRR transaction depends on an already-committed one, its binlog events must not start until that dependency has finished replaying on the replica; Otherwise you get escalating failures: a deadlock, then a broken replication channel, and in the worst case data inconsistency between primary and replica. Take this example:

1
2
INSERT INTO t1(pk, c2) VALUES(pk1, 1);
UPDATE t1 SET c2 = 2;  -- large transaction

The UPDATE is the large transaction, and it must not begin until the INSERT has finished replaying. If the UPDATE runs first, it fails when updating the pk1 row because that row doesn’t exist yet.

BRR uses a gtid_executed snapshot to enforce these ordering dependencies. When a DDL or large transaction starts on the primary, the primary’s current gtid_executed captures every preceding transaction it saw. Once the replica’s gtid_executed has caught up to that value (that is, is a superset of it), all the transactions this one depends on have been replayed on the replica, and it is safe to start applying it.

To do this, BRR adds a new event type, Brr_gtid_executed_log_event, whose body holds a gtid_executed set. At specific moments the primary takes a gtid_executed snapshot and writes it to the BRR channel; when a replica Brr Worker reads the snapshot, it waits for all the GTIDs in it to finish before continuing.

Realtime Replication of Large Transactions

Creating and Updating a Brr_trx

When a transaction runs on the primary, its binlog events go first into the Binlog Cache (an in-memory buffer backed by a temporary file). In MySQL, once the Binlog Cache fills its in-memory buffer, it spills to the temporary file.

BRR hooks in here: after each batch of events is written to the Binlog Cache, it checks the temporary file’s size. Once the file exceeds a certain size, BRR creates a Brr_trx, records the temporary file name and the current readable position, and registers it with Brr_trx_manager. From then on, every append to the Binlog Cache updates the Brr_trx’s end_position and wakes the Dump thread to send those events to the replica.

Transmitting Binlog Events

Before sending each batch of binlog events, the Dump thread emits a Brr_gtid_executed_log_event as that batch’s dependency snapshot, then sends the batch itself.

Committing the Transaction

For a large transaction, the binlog events sit in the Brr_cache temporary file — not yet relay log — until the Brr Worker reads the Gtid_log_event. When the primary finally commits, it sends the Gtid_log_event over the BRR channel, and the IO thread does two things:

1. Renames the Brr_cache temporary file into a relay log file. Based on the GTID, the primary’s Dump thread then skips sending this transaction, so its events aren’t shipped again as an ordinary transaction.
2. Notifies the Brr Worker to read the Gtid_log_event and Xid_log_event and complete the commit.

Rolling Back the Transaction

The rollback path is straightforward: when the primary rolls back, it sends a BRR_ROLLBACK_EVENT over the BRR channel; on receiving it, the replica’s IO thread sends a KILL_QUERY signal to the corresponding Brr Worker. The Brr Worker detects KILL_QUERY, rolls back the current transaction, cleans up, and moves on to the next Brr_cache.

Note that after being killed, a Brr Worker neither exits nor propagates the error to the SQL thread — unlike an ordinary Worker, which must halt all replication on an error. The reason: for an ordinary Worker the transaction has already committed on the primary, so if the replica gives up, the two diverge. A Brr Worker’s transaction, by contrast, runs concurrently with the primary, so a primary rollback is the normal path and the replica must roll back as well.

Realtime Application of DDL

Creating a Brr_trx

For large transactions, we decide whether a transaction is “large” by the total size of its binlog events in the Binlog Cache. DDL is trickier: some DDLs only touch metadata and finish almost instantly, while for DDLs that touch data the run time depends on how much data is involved and is hard to estimate accurately. So instead of predicting a DDL’s run time up front, we decide whether to realtime-replicate it by whether its execution exceeds a timeout.

Every DDL creates a Brr_trx, but that Brr_trx isn’t sent to the replica right away. A DDL’s Brr_trx has a threshold — 1000 ms by default — and only when the DDL’s run time exceeds it does the Dump thread start sending the Brr_trx. If a DDL finishes quickly, within one second, its Brr_trx is silently discarded and the DDL ships to the replica over the ordinary binlog channel, exactly as if BRR were off.

A DDL’s Brr_trx is created during the DDL’s Prepare phase — that is, after the DDL has acquired the MDL X lock — because only with the X lock does the DDL have permission to operate on the table. Any conflicting operations have either already committed or must wait until the DDL releases the X lock or finishes.

Two gtid_executed Snapshots

An Online DDL runs in three phases: Prepare, Execute, and Commit. After Prepare, the MDL X lock is downgraded to an S lock, so during Execut, DML and DDL can run in parallel. During Commit, the S lock is upgraded back to an X lock; regaining the X lock means all those parallel DMLs have already committed. The replica must honor the same rule: those committed DMLs have to finish replaying before the replica can enter the Commit phase.

So realtime replication of an Online DDL has two points on the replica that must be synchronized: one before entering Prepare, and one before entering Commit. Correspondingly, the primary takes two gtid_executed snapshots — one after the DDL enters Prepare, and one after it enters Commit.

Shipping Binlog Events Twice

In the large-transaction section we saw that a large transaction is transmitted to the replica via BRR, and the copy in the binlog file is not shipped again. DDL is different: it ships twice — once over BRR, and again as the binlog events in the binlog file.

A DDL’s Query_log_event is tiny, so shipping it twice costs almost nothing. Shipping it only once would force us into the large-transaction rename logic (renaming the Brr_cache temporary file into relay log), with all its edge cases. For DDL, simply shipping it twice and discarding the Brr_cache afterward is the simplest approach.

As for ordering, the Dump thread guarantees BRR events ship before ordinary events. That way the Brr Worker is sure to get the DDL first and start executing it; by the time the ordinary events reach the relay log, the Brr Worker is already applying the DDL.

When an ordinary Worker reads the DDL from the relay log, it checks whether the GTID is in owned_gtids. If it is (a Brr Worker is executing it), the ordinary Worker waits; once the Brr Worker commits, the GTID is added into gtid_executed. The ordinary Worker wakes and finds the GTID already in gtid_executed, so it skips the whole DDL.

If the Brr Worker rolled the DDL back, the GTID is removed from owned_gtids and never added to gtid_executed. The ordinary Worker then wakes and sees the transaction wasn’t executed. It runs the DDL normally — the fallback path, equivalent to running with BRR off.

Conclusion

AliSQL’s Binlog Realtime Replication tackles the thorniest lag in MySQL binlog replication — lag from large transactions and DDL — by executing on the primary and replica in parallel. On top of that, we’ve made optimizations for the writeset mechanism, for massively concurrent workloads, and for the medium-sized transactions that batch jobs produce. Together, these have eliminated 95% of the replication lag in our production environment.

This article is also available in Chinese: 中文版. Browse all English articles.

Large transactions are a notorious problem in MySQL: they cause not only replication lag but also stability problems. A previous article, MySQL Large Transaction Commit Optimization, covered the problems a large transaction causes at commit time and the optimizations we made in AliSQL. This article looks at the problems a large transaction causes during semi-synchronous replication, and how AliSQL solves them.

In MySQL Large Transaction Commit Optimization we noted that writing the binlog when a large transaction commits can produce strange slow queries like these:

• An INSERT that normally runs in an instant took 1.3s, yet the slow-query log shows no long lock wait.
• Every statement in a multi-statement transaction had already finished, yet the COMMIT alone took 1.3s.

Besides writing the binlog at commit, transmitting a large transaction’s binlog during semi-synchronous replication produces the same symptom. Below is a simulated test: we used sysbench oltp_write_only to simulate a normal write workload, then in the background, a transaction that generated 2 GB of binlog events (with the large-transaction commit optimization already applied). When the large transaction commits, writes drop to zero and don’t recover until semisync times out.

Root Cause

The figure above shows the commit flow of a transaction under semi-synchronous replication:

• On commit, the transaction runs two-phase commit, starting with Prepare.
• It then writes its binlog events to the binlog file.
• After writing the binlog, it waits for its binlog events to be sent to the replica (after_sync mode).
• The binlog Dump thread then sends the transaction’s binlog events to the replica.
• The replica’s IO thread receives these events and writes them into the relay log file.
• Once it has the complete transaction, the IO thread sends the primary an acknowledgment saying it has all of the transaction’s binlog events. The ack is expressed as a binlog file name and offset. In the figure, Trx_n’s binlog end offset is 530, so the replica’s IO thread sends master-bin.000001:530 to the primary, meaning every transaction before master-bin.000001:530 has been received.
• On the primary, the Semisync Ack Receiver thread receives the ack and, based on the offset, wakes the corresponding transaction.
• Once woken, the transaction finishes committing and returns OK to the user.

There is only one Dump thread between the primary and the replica, and it transmits binlog events in the order they were written to the binlog. The replica’s IO thread likewise writes received events into the relay log in that same order before acknowledging the primary. So a later transaction can’t be sent until the earlier one has finished. If the current transaction has a huge number of binlog events, sending them takes a very long time, and a later transaction — however small — has to wait. That wait includes not just its own transmission time but the large transaction’s ahead of it. Hence the slow-log symptom: a small transaction suddenly becomes very slow.

To cope with this, MySQL provides the rpl_semi_sync_master_timeout parameter, which sets how long a transaction waits for an ack; once the wait exceeds rpl_semi_sync_master_timeout, replication automatically falls back to asynchronous. We can set this to a small value to avoid the severe case where a large transaction makes the whole instance unwritable.

An RPO = 0 Design Based on Semi-Synchronous Replication

Because a transaction under semi-synchronous replication can’t commit until its binlog has been replicated to a replica, it’s natural to think of using semisync to build an RPO = 0 (zero data loss) consistency solution.

This architecture needs two replicas, and semisync guarantees that a transaction commits only after it receives an ack from at least one of them.

• If the primary crashes, the data has been replicated to at least one replica.
• If one replica becomes unavailable, cluster availability is unaffected.

To guarantee RPO = 0, semisync must never fall back to async. MySQL semisync has two points where it can degrade to async:

• After a crash and restart, transactions already written to the binlog are committed automatically, even though they may not yet have been replicated to a replica.
• Once the wait reaches rpl_semi_sync_master_timeout, it degrades to async.

The former can’t be controlled from outside — it requires changing MySQL’s code. The latter requires setting rpl_semi_sync_master_timeout to a very large value so semisync never degrades. Large transactions are clearly the thorniest issue in an RPO = 0 design: the moment one appears, it makes the whole cluster unwritable, so the design must take countermeasures. A DBA with strong influence over the application can arrange for it to avoid large transactions; but at a large company, with sprawling and complex applications, eliminating them entirely is hard, and an RDS provider has no control over its users at all. In practice, availability usually matters far more than consistency, so many designs adopt a temporary-degradation strategy, falling back to async whenever a large transaction appears.

Realtime Transmission of Large Transactions

In AliSQL we designed a realtime-transmission mechanism to solve the problems large-transaction transmission causes; with it, there is no need to degrade semisync to async.

The realtime large-transaction transmission mechanism reads a transaction’s binlog events out of the Binlog Cache temporary file and sends them to the replica while the transaction is still doing DML. The key steps:

• During DML execution, once the binlog events of a transaction has produced exceed a certain amount, the transaction is registered in the large-transaction list and handled as a large transaction.
• Based on that list, the binlog Dump thread reads the large transaction’s binlog temporary file and sends its contents to the replica. The large transaction’s binlog events and the events from the binlog file are sent interleaved, with flow control on the large transaction: events from the binlog file take priority, so the transaction currently committing is unaffected.
• The large transaction’s binlog events carry a special marker and extra information. When the replica’s IO thread receives them, it stores them in a temporary file called the Relay Log Cache.
• At commit, once the Dump thread has sent all the binlog events, it sends a Gtid_event to the replica.
• On receiving the Gtid_event, the replica knows it has all of the transaction’s binlog events, and it turns the Relay Log Cache into a Relay Log file.
• When several large transactions run at once, the mechanism can transmit them all in real time simultaneously.

From these steps we can see: a large transaction’s binlog events are sent to the replica bit by bit as they are produced, so at commit only the Gtid_event needs to be sent. The amount of data sent at commit is therefore tiny, and it no longer blocks other transactions’ binlog-event transmission. It also removes the sudden burst of network traffic, reducing congestion.

Relay Log Cache

The realtime-transmission mechanism follows directly from the large-transaction commit optimization and reuses parts of its implementation. A transaction’s binlog events are produced and accumulate during DML execution; once they exceed binlog_cache_size, they are written to a temporary file, and at commit they are written to the binlog file all at once. In MySQL Large Transaction Commit Optimization, a large transaction’s temporary file is automatically turned into a new binlog file, which eliminates the problems that large-transaction commit causes.

Realtime large-transaction transmission reuses this logic, reserving some space at the head of the Relay Log Cache. When the Relay Log Cache is turned into a Relay Log file, that head space is filled with the special binlog events a relay log needs, such as the Format_description_event.

Handling Failures

A large transaction runs for a long time, so any failure along the way has to be handled.

• If the large transaction rolls back on the primary, the binlog Dump thread sends a rollback to the replica; on receiving it, the IO thread destroys the corresponding Relay Log Cache.
• If the IO thread’s connection to the primary drops, or a STOP SLAVE is issued, the IO thread destroys all Relay Log Caches. After reconnecting, it restarts realtime replication of the large transaction.

Results

We used sysbench oltp_write_only to simulate a normal write workload, then committed, in the background, a transaction that generated 2 GB of binlog events. The results:

With realtime replication, the application’s writes run smoothly, with no more drops to zero.

Conclusion

In MySQL’s semi-synchronous replication architecture, large transactions are a classic problem. To keep them from destabilizing the instance, people have had to work hard to eliminate large transactions from their applications, or simply let replication degrade to async. Realtime large-transaction transmission moves the transmission of a large transaction’s binlog events from the commit phase up to the execution phase, sending each event to the replica as soon as it is produced. This avoids blocking other transactions’ binlog-event transmission for a long time at commit, and avoids network congestion. When a large transaction comes along, semisync no longer needs to degrade to async — clearing away a thorny obstacle on the path to a semisync-based RPO = 0 design.