MySQL Replication Lag Spikes to 10s? 3 Key Parallel Replication Optimizations

Confirm What "10 Seconds" Actually Means

Seconds_Behind_Source

is an approximate metric based on event timestamps and cannot serve alone as a data freshness SLA. Network interruptions, I/O thread stops, long transactions, clock skew, replication filters, parallel worker stalls, and manual SQL thread stops can all make it unreliable or NULL. Troubleshooting must simultaneously examine I/O, SQL/applier, GTID sets, relay log, worker states, and error fields.

First verify MySQL version, binary log format, GTID, and parallel replication variables. All queries should run on the target Source or Replica with the role recorded in the ticket; a common human error is running replica tuning SQL on the source or on the wrong replication channel.

# Code 1: Use controlled login-path to connect and record version and role variables
mysql --login-path=<replica_login_path> --batch --raw -e 
"SELECT @@version AS mysql_version,
  @@server_uuid AS server_uuid,
  @@read_only AS read_only,
  @@super_read_only AS super_read_only,
  @@gtid_mode AS gtid_mode,
  @@binlog_format AS binlog_format;"

-- Code 2: View replication channel status on replica
SHOW REPLICA STATUS FOR CHANNEL '<channel_name>'\G

Key fields: Replica_IO_Running, Replica_SQL_Running (or applier status), Seconds_Behind_Source, Last_IO_Error, Last_SQL_Error, Relay_Log_Space, Retrieved_Gtid_Set, Executed_Gtid_Set. Field names vary by MySQL minor version and multi-source scenarios. Any non-empty Last_*_Error must be handled before increasing parallelism.

-- Code 3: Check each applier worker for errors or long-running work
SELECT
  CHANNEL_NAME,
  WORKER_ID,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LEFT(LAST_ERROR_MESSAGE, 200) AS last_error_message,
  APPLYING_TRANSACTION,
  LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
ORDER BY CHANNEL_NAME, WORKER_ID;

This table observes workers, not proof that parallelism is sufficient. A worker long in APPLYING_TRANSACTION may indicate a long transaction, lock wait, slow disk, or dependency; multiple idle workers may mean the source isn't providing enough parallelizable transactions. Ensure performance_schema is enabled and the account has read permission.

-- Code 4: Check coordinator and connection status to separate I/O and SQL apply issues
SELECT * FROM performance_schema.replication_applier_status_by_coordinator;
SELECT * FROM performance_schema.replication_connection_status;

If connection status is abnormal or I/O thread repeatedly reconnects, optimizing SQL applier won't fix network, authentication, TLS, or source binlog retention issues. If coordinator is normal but workers have SQL errors, handle per error number, transaction, and business constraints; never skip transactions just to zero out lag.

First Gate: Exclude Root Causes That Parallel Replication Cannot Fix

Parallel replication handles "can the replica apply multiple independent transactions in parallel." It cannot split a 30-second large DDL, a bulk UPDATE, a long lock-holding transaction, or a single-row hotspot into multiple workers. When SQL thread shows lag, first examine source write characteristics, running transactions, transaction sizes, and replica disk situation.

-- Code 5: On source, view current long transactions and lock wait clues
SELECT
  trx_id,
  trx_started,
  trx_state,
  trx_rows_modified,
  trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;

information_schema.innodb_trx

shows only currently running transactions, not historical bulk commits. If long transactions appear, further identify the initiator from application releases, slow log, audit, and performance schema; do not directly KILL business sessions. Aborting a transaction may cause long rollback that worsens replication lag.

-- Code 6: On source, find statements with highest cumulative latency
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_ROWS_AFFECTED,
  SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

This is a cumulative summary suitable for locating long-term hotspots, not the current incident window. Record sampling time; cross-validate with slow query log, application release batches, binlog event volume, and business write curves. For write hotspots, optimizing indexes, shortening transactions, batch throttling, sharding, or reducing same-row updates are usually more effective than adding workers.

# Code 7: Check replica disk, filesystem, and mysqld resource pressure
df -hT <MySQL_data_dir>
df -i <MySQL_data_dir>
iostat -xz 1 10
pidof mysqld
ps -o pid,ppid,stat,etime,%cpu,%mem,cmd -p "$(pidof mysqld)"

iostat

comes from sysstat; if not installed, don't install packages during an incident—use existing host monitoring. High await, near-saturated util, space or inode exhaustion can block relay log, binlog, InnoDB flushing, and replication apply. Disk metrics must be judged with device type, write volume, and baseline; a single high await is not conclusive.

-- Code 8: Verify source meets ROW, GTID, and write-set dependency tracking basics
SELECT
  @@global.binlog_format AS binlog_format,
  @@global.gtid_mode AS gtid_mode,
  @@global.enforce_gtid_consistency AS enforce_gtid_consistency,
  @@global.transaction_write_set_extraction AS write_set_extraction,
  @@global.binlog_transaction_dependency_tracking AS dependency_tracking;

For MySQL 8.0, WRITESET depends on transaction_write_set_extraction, commonly XXHASH64. If source isn't ROW, GTID strategy incomplete, or replication includes older replicas, complete compatibility assessment first—don't just set one variable. WRITESET isn't a magic "more parallelism": real data dependencies, missing primary keys, oversized transactions, or frequent same-hot-row updates still serialize.

Killer Move 1: Make Source Emit Valuable Dependency Information

LOGICAL_CLOCK

parallel replication uses binlog dependency info to decide which transactions can apply in parallel. Source using WRITESET or WRITESET_SESSION exposes more parallelizable space than commit order alone. WRITESET uses transaction write-sets to judge conflicts; WRITESET_SESSION conservatively preserves same-session transaction order. Choice depends on whether the application relies on same-session commit relationships, transaction isolation assumptions, and consistency requirements—not throughput alone.

Back up existing variables and config file first. SET PERSIST writes to MySQL's persisted config and immediately changes the global value, so it's a production write operation; validate in a production-like environment first, then roll out one source at a time with observation.

# Code 9: On Source, back up relevant variables and config file
#!/usr/bin/env bash
set -euo pipefail

login_path="<source_login_path>"
config_file="<config_file>"
backup_dir="<backup_dir>"

mkdir -p "$backup_dir"
mysql --login-path="$login_path" --batch --raw -e 
"SHOW GLOBAL VARIABLES WHERE Variable_name IN
('binlog_format','gtid_mode','enforce_gtid_consistency',
 'transaction_write_set_extraction',
 'binlog_transaction_dependency_tracking');"
> "$backup_dir/source-replication-vars.before.txt"
sudo cp -a "$config_file" "$backup_dir/mysqld.cnf.before"

Backup files must not contain plaintext passwords, but variables, file paths, and replication topology are internal information—restrict access. If <config_file> is an include-directory fragment, back up the actual effective file, not just the main config. Different Ubuntu MySQL installations have different config hierarchies; confirm read order with mysqld --verbose --help or existing ops docs.

# Code 10: Source MySQL 8.0 config snippet example
# Place into existing [mysqld] config level; do not overwrite entire my.cnf.
[mysqld]
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_tracking = WRITESET

The snippet shows target state, not a direct restart switch. binlog_format, GTID, and replication consistency parameters may affect the whole topology and clients; any migration from STATEMENT/MIXED or GTID-off must be a separate project. Only in an existing ROW+GTID environment should you evaluate WRITESET items; don't change five categories of basic replication settings in one lag incident.

-- Code 11: After approval, persist write-set and dependency tracking on Source
SET PERSIST transaction_write_set_extraction = 'XXHASH64';
SET PERSIST binlog_transaction_dependency_tracking = 'WRITESET';

SELECT
  @@global.transaction_write_set_extraction AS write_set_extraction,
  @@global.binlog_transaction_dependency_tracking AS dependency_tracking;

Executing this SQL requires system variable admin privileges and should be done in a low-risk window. Changes affect only subsequently generated binlog events; old relay log transactions won't suddenly gain more parallelism, so the validation window must cover new writes. If the business has same-session ordering dependencies, evaluate WRITESET_SESSION 's conservatism—don't ignore semantics just because a benchmark shows higher throughput.

-- Code 12: Confirm new binlog events on Source and replica GTID progress
SHOW MASTER STATUS;
SELECT
  @@global.gtid_executed AS source_executed_gtid,
  @@global.binlog_transaction_dependency_tracking AS dependency_tracking;

SHOW MASTER STATUS

remains a valid command in MySQL 8.0. It records current binlog file and position, but cannot alone decide failover eligibility; GTID sets, all replica states, binlog retention, and business write-freeze strategy must be evaluated together. Collect this info without leaking internal database names and binlog details in the ticket.

Killer Move 2: Let Replica Truly Apply in Parallel

After source produces parallelizable info, replica still needs multiple applier workers and replica_parallel_type = LOGICAL_CLOCK. Worker count isn't "more is better": each worker consumes memory, transaction context, and I/O pressure. Beyond what disk, CPU, or the dependency graph can parallelize, extra workers only add scheduling overhead. Suitable count starts small and increments in the same incident or load-test model while observing.

Before any STOP REPLICA, confirm instance role, replication channel, business read traffic, failover status, and change window. Stopping SQL_THREAD pauses that channel's apply; Seconds_Behind_Source may rise briefly—this isn't failure, but impact and recovery thresholds must be pre-communicated.

-- Code 13: On Replica, record current parallel replication settings
SELECT
  @@global.replica_parallel_type AS parallel_type,
  @@global.replica_parallel_workers AS parallel_workers,
  @@global.replica_preserve_commit_order AS preserve_commit_order,
  @@global.replica_pending_jobs_size_max AS pending_jobs_size_max;

Variable names per MySQL 8.0. Older versions may still use slave_parallel_* compatibility names, but don't mix them in scripts. replica_pending_jobs_size_max is one memory limit for coordinator-cached pending jobs; increasing it must be evaluated with worker count, transaction size, and node memory—not just to eliminate a wait.

# Code 14: On Replica, save status and variables before change
#!/usr/bin/env bash
set -euo pipefail

login_path="<replica_login_path>"
backup_dir="<backup_dir>"

mkdir -p "$backup_dir"
mysql --login-path="$login_path" --batch --raw -e 
"SHOW REPLICA STATUS FOR CHANNEL '<channel_name>';
" > "$backup_dir/replica-status.before.txt"
mysql --login-path="$login_path" --batch --raw -e 
"SHOW GLOBAL VARIABLES WHERE Variable_name IN
('replica_parallel_type','replica_parallel_workers',
 'replica_preserve_commit_order','replica_pending_jobs_size_max');
" > "$backup_dir/replica-parallel-vars.before.txt"

Non-interactive SHOW REPLICA STATUS format is less readable than \G but suits raw evidence saving. Multi-source replication must specify channel in every statement; don't rely on default channel—stopping the wrong one expands impact. If the instance has mixed async and semi-sync topology, also confirm this replica's role in the failover chain.

-- Code 15: On canary Replica, switch to LOGICAL_CLOCK and set initial worker count
STOP REPLICA SQL_THREAD FOR CHANNEL '<channel_name>';

SET PERSIST replica_parallel_type = 'LOGICAL_CLOCK';
SET PERSIST replica_parallel_workers = <worker_count>;
SET PERSIST replica_preserve_commit_order = ON;

START REPLICA SQL_THREAD FOR CHANNEL '<channel_name>';
SHOW REPLICA STATUS FOR CHANNEL '<channel_name>'\G

This is a high-risk replication change. <worker_count> should be a capacity-evaluated small initial value (e.g., based on vCPU, disk, concurrent writes, and load-test results), not simply equal to CPU cores. STOP/START targets only SQL_THREAD; I/O thread may continue receiving relay log. Immediately check Last_SQL_Error, SQL thread state, and lag curve. If current version requires stopping the whole channel or variables don't support SET PERSIST, follow that version's manual and --help / SHOW VARIABLES.

-- Code 16: Check if workers started and have explainable state
SELECT
  CHANNEL_NAME,
  WORKER_ID,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LAST_ERROR_TIMESTAMP,
  APPLYING_TRANSACTION,
  LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker
WHERE CHANNEL_NAME = '<channel_name>'
ORDER BY WORKER_ID;

After increasing worker count, the table should show corresponding workers, but "many workers appear" ≠ throughput gain. Watch whether they handle transactions in the same window, have errors, lag decreases, disk and CPU still have headroom. Persistent single active worker may indicate real dependencies, long transactions, or insufficient source dependency info—not just a missing config.

-- Code 17: From coordinator perspective, check waits and errors
SELECT
  CHANNEL_NAME,
  SERVICE_STATE,
  LAST_ERROR_NUMBER,
  LEFT(LAST_ERROR_MESSAGE, 200) AS last_error_message
FROM performance_schema.replication_applier_status_by_coordinator
WHERE CHANNEL_NAME = '<channel_name>';

The coordinator reads relay log and dispatches transactions. If workers are idle but coordinator state is abnormal, go back to relay log, I/O, disk, and error logs; if coordinator is normal but commits are order-constrained, distinguish whether this is the expected cost of preserve_commit_order or severe business transaction dependencies.

Killer Move 3: Preserve Commit Order and Accept Its Cost

replica_preserve_commit_order=ON

makes parallel workers commit transactions in the source's commit order. This matters for scenarios needing replica commit visibility order consistent with source, and reduces confusion where a later-committed transaction is visible while an earlier one isn't. The cost: workers may wait at commit phase for preceding transactions, so actual parallel gain depends on workload dependencies.

It's not a full replication consistency guarantee, nor a substitute for GTID, binlog, crash recovery, semi-sync, or read-write routing design. Whether ON is needed depends on business read-from-replica semantics; if turning it off only to chase lower Seconds_Behind_Source, first assess whether it breaks read-after-write, transaction ordering, or failover conventions.

-- Code 18: Explicitly verify final effective values of commit order and parallel type
SELECT
  @@global.replica_parallel_type AS parallel_type,
  @@global.replica_parallel_workers AS parallel_workers,
  @@global.replica_preserve_commit_order AS preserve_commit_order;
SHOW REPLICA STATUS FOR CHANNEL '<channel_name>'\G

Validation must span at least one normal business window and compare against pre-change baseline. Beyond Seconds_Behind_Source, observe relay log growth, worker errors, commit waits, disk latency, CPU, business query errors, and data freshness probes. If lag drops briefly but replica I/O saturates or business read latency rises, you've only moved the bottleneck.

-- Code 19: View running transactions and lock waits on Replica
SELECT
  trx_id,
  trx_started,
  trx_state,
  trx_rows_modified,
  trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 20;

Long transactions on replica may come from replication workers, read-only business queries, admin sessions, or reports. Combine with processlist, Performance Schema, and application connection identifiers to judge. Never bulk-kill threads without evidence; killing long reads may hurt business, killing replication workers may cause recovery and re-apply costs.

-- Code 20: Identify InnoDB tables without primary keys as parallel capability review clues
SELECT
  t.table_schema,
  t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.table_constraints AS c
  ON c.table_schema = t.table_schema
  AND c.table_name = t.table_name
  AND c.constraint_type = 'PRIMARY KEY'
WHERE t.engine = 'InnoDB'
  AND t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('mysql','sys','performance_schema','information_schema')
  AND c.constraint_name IS NULL
ORDER BY t.table_schema, t.table_name;

Missing primary keys don't directly cause replication stalls, but they reduce write-set extraction, row location, and failure recovery predictability. Adding PKs to large tables is a high-risk DDL project involving locks, replication, storage, and business compatibility—cannot be done ad-hoc during a lag incident. First build a table inventory, write patterns, capacity, and online DDL assessment, then plan separately.

Judge Real Speedup with Repeatable Monitoring

Replication optimization requires breaking down "lag": Is I/O receiving timely? Is relay log piling up? Is SQL/applier advancing? Does each worker have errors? Has source write volume increased? Has replica disk become a bottleneck? A single dashboard green light isn't enough; compare before/after over the same business cycle.

# Code 21: Continuously sample replication health for change-window observation
#!/usr/bin/env bash
set -euo pipefail

login_path="<replica_login_path>"
channel="<channel_name>"
interval_seconds=10

while true; do
  date --iso-8601=seconds
  mysql --login-path="$login_path" --batch --raw -e 
  "SHOW REPLICA STATUS FOR CHANNEL '$channel';
  "
  sleep "$interval_seconds"
done

This script outputs continuously, suitable for a controlled terminal or log collection task; don't leave it running unmanaged in a shell. SHOW REPLICA STATUS column widths and fields change by version; production monitoring should use a parsing-stable exporter or explicit SQL views, not manual scanning of long tables.

# Code 22: Replication lag alert expression example; metrics per actual mysqld exporter
max by (instance, channel_name) (
  mysql_slave_status_seconds_behind_master
)

Some exporters still use slave_status metric names even though MySQL commands use replica terminology. Metric names, channel labels, and NULL representation must match the actual exporter. Alerts should combine SQL/IO thread state, duration, and business freshness metrics to avoid noise during brief write spikes.

# Code 23: Replication SQL thread non-running state example; metrics per actual exporter
max by (instance, channel_name) (
  mysql_slave_status_sql_running == 0
)

Thread-state alerts and lag alerts have different severity: SQL thread stop usually demands immediate error and consistency handling, not waiting for a lag threshold. If exporter uses different metrics or exports boolean state as string labels, rewrite per actual format and validate in test environment.

# Code 24: Roll back Replica parallel replication settings from controlled backup
#!/usr/bin/env bash
set -euo pipefail

login_path="<replica_login_path>"
channel="<channel_name>"
old_workers="<original_worker_count>"
old_parallel_type="<original_parallel_type>"
old_commit_order="<original_commit_order>"

mysql --login-path="$login_path" --execute 
"STOP REPLICA SQL_THREAD FOR CHANNEL '$channel';
 SET PERSIST replica_parallel_workers = $old_workers;
 SET PERSIST replica_parallel_type = '$old_parallel_type';
 SET PERSIST replica_preserve_commit_order = $old_commit_order;
 START REPLICA SQL_THREAD FOR CHANNEL '$channel';
 SHOW REPLICA STATUS FOR CHANNEL '$channel';
"

Before rollback, confirm the three original values from Code 14's backup—don't rely on memory. This script also pauses SQL_THREAD, affecting data freshness; if Last_SQL_Error, GTID inconsistency, or relay log anomalies already exist, stop automated rollback and follow replication failure procedures. After rollback, verify worker state, lag, error logs, and business read paths to confirm the lag isn't caused by other resource bottlenecks.

Parallel replication is most effective when the source generates many independent, fine-grained ROW transactions and the replica has available CPU, disk, and memory. It's most powerless against ultra-long transactions, hotspot updates, blocking DDLs, error stops, disk saturation, and network interruptions. First use evidence to separate these two categories, then validate incrementally in the order "WRITESET → LOGICAL_CLOCK + workers → commit order" so that lag optimization yields gains while retaining explainability and rollback capability.