The ugliest replica-lag incident I ever owned was not caused by slow hardware. The replica had the same 48 cores and faster NVMe than the primary, and it still fell four hours behind every night during batch processing, then crept back to zero by noon. The reason is structural: your primary executes transactions from dozens of connections concurrently, and a MariaDB replica applies the binary log through one SQL thread. One thread, no matter how fast the disk, cannot keep up with thirty-two writers. MariaDB's answer is a parallel applier with more modes than MySQL offers — none, minimal, conservative, optimistic, aggressive — and the one worth understanding deeply is optimistic mode, because it works on a bet: run transactions in parallel before proving they do not conflict, and pay for wrong guesses with rollbacks.
This post is how that bet works, when it pays, and how to measure lag honestly once the applier is parallel — because Seconds_Behind_Master stops telling the truth the moment you turn this on.
Why a replica falls behind in the first place
MariaDB replication has two moving parts on the replica: the IO thread copies events from the primary's binlog into the local relay log, and the SQL thread executes them in order. The IO thread is almost never the bottleneck; copying bytes is cheap. The SQL thread is where physics bites. A primary committing two thousand transactions per second across forty connections is doing forty things at once; the classic single-threaded applier does one thing at once, and if the average transaction takes even a few milliseconds to apply — a few row writes, an fsync at the end — the math collapses immediately.
Parallel replication changes the shape: slave_parallel_threads creates a pool of applier workers, and a driver thread reads the relay log and hands events out to them. The question the five modes answer is the interesting one: which transactions are allowed to run at the same time?
The five modes of slave_parallel_mode
none is the historical behavior — one SQL thread, strictly serial. minimal keeps serial execution but parallelizes the commit step, which matters when the replica is fsync-bound: the row work happens one transaction at a time, but the durability flushes group together. It is a narrow fix for a narrow problem, and I have rarely seen it be the whole answer.
conservative — the default until MariaDB 10.5.0, before optimistic took over as the default in 10.5.1 — discovers parallelism from the primary's group commit. Transactions that committed in the same group on the primary already proved, under the primary's lock manager, that they did not touch the same rows — so the replica can safely apply that whole group at once. Safe, deterministic, no rollbacks ever. Its ceiling is the primary's group-commit density: if your primary commits transactions one at a time — a handful of connections running autocommit micro-transactions, no wait tuning — then groups stay thin and conservative mode degenerates toward serial apply with extra bookkeeping. One myth to kill here: sync_binlog=1 does not force one-member groups. Group commit still batches whatever transactions finish concurrently into a single fsync — sync_binlog=1 syncs once per group, not once per transaction — which is exactly what binlog_commit_wait_count and binlog_commit_wait_usec exploit. Measure the real density with Binlog_commits versus Binlog_group_commits before you judge the ceiling.
optimistic stops asking the primary for proof. It assumes conflicts are rare and lets workers run transactions in parallel regardless of what group commit looked like; when two transactions do collide on a row, the later one is rolled back and retried. Optimistic keeps a short memory of transactions that recently conflicted and serializes repeat offenders, which is how it avoids rolling back the same hot transaction forever.
aggressive is optimistic with the memory removed: every transaction is eligible for parallel execution every time, even ones that conflicted before. It squeezes the last bit of parallelism out of workloads that are almost always disjoint, at the price of more retries when they are not.
-- on the replica: enable the parallel applier
STOP SLAVE;
SET GLOBAL slave_parallel_threads = 8;
SET GLOBAL slave_parallel_mode = 'optimistic';
START SLAVE;
-- on the primary: denser commit groups feed conservative mode
SET GLOBAL binlog_commit_wait_count = 20;
SET GLOBAL binlog_commit_wait_usec = 10000;Two operational notes. First, SET GLOBAL does not survive a restart — put slave_parallel_threads and slave_parallel_mode in the config file or your tuning evaporates at the next reboot, which is always during an incident. Second, slave_parallel_max_queued caps how many bytes of events each worker may queue (the default is 128 KB per thread); when the replica falls behind, queued relay-log events become a memory consumer, and when the queue fills, the driver thread stops reading and you have invented a new kind of backpressure.
What optimistic mode does on a conflict
The conflict detector is InnoDB's ordinary row locking, which is elegant: no separate machinery. Workers execute ahead of each other, and when a transaction that is later in binlog order needs a row lock held by an earlier transaction that has not committed yet, the later one becomes the victim — it rolls back, waits for the earlier transaction to commit, and re-executes. A rollback costs roughly what the original statement cost, so a conflicted big UPDATE burns its work twice. That is the entire trade: optimistic mode converts the risk of waiting into the risk of wasted work.
The counter that tells you how the bet is going is Slave_retried_transactions. Occasional increments are the price of admission; a counter climbing in step with your traffic means the workload has genuine hot rows and speculation is a net loss.
One guarantee survives all of this: commit order. Transactions may execute out of order, but within a replication domain, commits are released strictly in primary binlog order, so a client reading the replica never sees transaction 42's effects without transaction 41's. If you deliberately spread traffic across GTID domains for out-of-order parallel apply, you opted out of that guarantee between domains — different domains execute and commit independently. The corollary people forget is that one slow early transaction gates the visible progress of everything queued behind it in its domain — parallel apply raises throughput, it does not relax ordering. If a specific session must never be parallelized, the skip_parallel_replication session variable marks its transactions to run alone.
When optimistic beats conservative — and when it loses
Optimistic earns its keep when the primary's commit groups are thin: durability settings that force one fsync per transaction, batch windows where each worker commits one large transaction, low primary concurrency exactly when you need the replica to sprint — like draining a four-hour backlog after downtime. It also shines on workloads that are naturally disjoint: per-tenant schemas, append-mostly event tables, anything where two random transactions almost never touch the same row.
Conservative wins when the primary already group-commits densely — a busy OLTP primary with binlog_commit_wait_count and binlog_commit_wait_usec tuned gives conservative mode plenty of proven-safe parallelism with zero rollback risk. It also wins on hot-row workloads: queue tables, status columns everyone updates, counters. I have watched optimistic mode on a queue-heavy workload roll back so often it was slower than plain serial apply. If you cannot measure, default to conservative; if you can measure, capture an hour of production relay log during your lag window and replay it under each mode on a scratch replica. The answer is workload-dependent, and I have seen both directions win.
Measuring lag when Seconds_Behind_Master is lying
Under parallel apply, Seconds_Behind_Master is only updated when transactions commit — it is not a live view of the work still uncommitted in worker queues. A replica can report a comfortably small number, or sit frozen on a stale one, while workers hold minutes of queued events behind one slow early transaction. The honest lag signal in MariaDB is GTID positions, and SHOW SLAVE STATUS exposes the pieces: Using_Gtid, Gtid_IO_Pos (what the IO thread has fetched), and Gtid_Slave_Pos (what the applier has committed). Slave_SQL_Running_State is also worth reading; states like waiting for room in the worker queue or waiting for a prior transaction to commit tell you which backpressure you are hitting.
-- on the primary: the newest GTID written to the binlog
SELECT @@global.gtid_binlog_pos;
-- on the replica: the newest GTID the applier has committed
SELECT @@global.gtid_slave_pos;
SELECT * FROM mysql.gtid_slave_pos;
SHOW SLAVE STATUS;
SHOW GLOBAL STATUS LIKE 'Slave_retried_transactions';Compare the sequence number in gtid_binlog_pos on the primary with gtid_slave_pos on the replica, domain for domain — both variables can hold GTID sets spanning several domains if anyone ever used gtid_domain_id, so compare per domain rather than string-matching the whole value. When every domain matches, lag is zero no matter what the seconds counter claims. Trend both once a minute and alert on the sequence gap, not on Seconds_Behind_Master — bearing in mind that a sequence gap counts transactions, not elapsed time: five hundred transactions behind can be five seconds of catch-up or five minutes of it. The mysql.gtid_slave_pos table is where the position is persisted across restarts, and it is also the fastest sanity check that GTIDs are advancing at all.
Where MonPG fits
Compare GTID positions by hand for a week and you will start wishing something trended them for you — that is the shape of the tooling gap here. The checks themselves are cheap: per-domain sequence-gap trends per replica, retry-rate growth on Slave_retried_transactions, and mode and thread-count captured as config so a reboot cannot silently revert you to serial apply. On the tooling side I will be plain: MonPG monitors PostgreSQL today, not MariaDB. MariaDB monitoring is coming soon and in active development, and replication is on the shortlist of signals it is being built around — GTID-position lag instead of the seconds counter, optimistic-mode retry trends, and applier configuration drift flagged before it becomes a 3am surprise. And if PostgreSQL is also in your fleet, that workflow exists now: see the PostgreSQL overview and how the engines compare, or browse the rest of the series on the blog.