Replication Lag on Dedicated Hardware: Reading Disk p99 Instead of Average

Replication lag is usually diagnosed against the wrong metric. Monitoring shows average disk IOPS fine, average write latency at 0.8ms, and a replica sitting 40 seconds behind the primary. Nothing looks broken. The reason is that replication is a latency-sensitive consumer: the SQL thread waits on relay log fsync and apply, and it only needs one badly-timed stall per commit batch to fall behind by seconds.

On a dedicated box with a single tenant, average IOPS is dominated by the bulk of well-behaved small writes. The slow tail — queue depth spikes, storage write cache flushes, controller garbage collection — is what actually delays the relay log apply. p99 is the metric that correlates, and p99.9 correlates better still.

Measure it precisely: fio --rw=write --ioengine=libaio --bs=16k --iodepth=64 --lat_percentiles=1 --output-format=json, or read node_disk_io_time_seconds_total and the per-device iodepth from the kernel. Watch SHOW REPLICA STATUS fields Seconds_Behind_Source and Relay_Log_Space on the dedicated database servers while you push a write-load test at the primary.

Why p99 beats average here

Averaging hides the exact event that breaks replication. If 99% of writes complete in 0.5ms and 1% take 40ms, the average is 0.9ms — but that 40ms tail occurs 10 times per 1,000 writes, and the apply thread processes writes serially. Two slow tail events inside one commit group delay the whole group, and the backlog compounds because the primary never slows down.

Queue depth is the leading indicator. When aqu-sz exceeds 4–8 on a single NVMe device, the device is queuing, and any write that arrives behind the queue waits for everything ahead of it. Lag shows up 10–30 seconds later, after the backlog has formed.

Relay log fsync settings

MySQL 8 writes the relay log to disk with its own durability settings. With sync_relay_log=1 and relay_log_info_repository=TABLE, every batch fsyncs the relay log and updates the table — two durable operations per transaction group, both hitting the same device. Setting sync_relay_log=10000 or 0 (rely on OS flush) removes that cost but means a crash can replay relay log from an earlier point; because the replica can re-fetch from the primary via GTID, that is usually safe on a dedicated replica.

• sync_binlog=1 on primary — required for durability, costs one fsync per commit
• sync_relay_log=0 on replica — biggest single lag reduction, safe with GTID
• binlog_group_commit_sync_delay=0 default; raise to 100–500µs only if commit rate is extreme
• innodb_flush_log_at_trx_commit=1 on replica — do not lower it to chase lag

Group commit interaction

On the primary, group commit batches concurrent transactions into one fsync, so throughput scales without extra IOPS. On the replica, the parallel appliers (replica_parallel_workers=8, with replica_preserve_commit_order=1) replay a batch but must respect commit ordering, which means a single slow worker stalls the group. If one schema has a hot table and the others are cold, parallel workers buy little until you split the hot table or use WRITESET dependency tracking.

A useful test: set replica_parallel_workers=16 and watch whether lag falls. If it does not, your workload has a single serial dependency and the disk tail is the true limit.

One more interaction worth knowing: binlog_transaction_dependency_tracking=WRITESET lets the replica derive dependencies per row rather than per commit, which increases how many workers can apply safely at once. On a box with 8–16 cores, this is often worth 30–50% more apply throughput without touching a single disk setting.

Storage write cache policy

RAID controllers with a battery-backed cache absorb bursts, but the policy matters. Write-back with a healthy BBU is fast and safe; write-through is safe and slow; write-back with a failed or missing BBU is a data-loss risk that vendors sometimes ship by default. Check storcli /c0 /v0 show or megacli -LDInfo -Lall -aAll and confirm the cache state, then confirm the policy is write-back only when protected.

On NVMe with no RAID, the device’s volatile write cache is the equivalent. Disabling it (hdparm -W0) roughly doubles write latency but removes the flush cliff. On a replica, where re-fetching is cheap, keeping the cache enabled is a reasonable trade you should make explicitly.

Watch for the flush cliff in monitoring rather than guessing at it: if p99 write latency is flat until a certain queue depth and then jumps tenfold, you are hitting the device’s internal cache flush or garbage collection window. Reducing sustained writes by 20% is often enough to move the whole workload below that knee.

p99 thresholds and expected lag

These are observed on single-tenant NVMe replicas applying a mixed OLTP stream at 2,000–5,000 writes/s.

Disk write p99	Queue depth	Expected lag	Action
< 1ms	< 2	0–1s	Healthy
1–3ms	2–4	1–5s	Watch
3–8ms	4–8	5–20s	Check relay fsync, parallel workers
8–20ms	8–16	20–90s	Saturation — investigate device
> 20ms	> 16	Minutes	Replace disk or reduce write rate

The order of fixes

First, cut the write rate at the source — batch updates, drop redundant indexes, move audit logging off the hot path. Second, set sync_relay_log=0 and verify parallel workers actually apply in parallel. Third, check write cache policy is what you think it is. Only then consider faster disks; most lag cases resolve at step one or two.

Plot p99 write latency and lag on the same graph. When they diverge — lag rises while p99 stays flat — the problem is inside MySQL: a serial dependency, a metadata lock, or a worker applying a single hot table. When they move together, it is the disk, and no amount of worker tuning will fix it. A dedicated box with a known, measured p99 and no noisy neighbours is what keeps both lines flat, and picking dedicated hardware on measured percentiles is the difference between guessing and knowing.