Why Replication Lag Kept Me Up at Night
Six months ago, a production incident hit our team hard. Users were reading stale data — orders showing as pending even after payment confirmed. The root cause? Replication lag on our MySQL read replicas had silently grown to 40+ seconds during a traffic spike. We had no alert, no visibility, and no runbook.
I’ve worked with MySQL, PostgreSQL, and MongoDB across a dozen projects. Each has its strengths. But replication lag doesn’t care which database you pick — it’s the failure mode that shows up when you least expect it, usually during a traffic spike at 11pm. This guide is what I wish I had before that incident.
Step Zero: Check Your Lag Right Now
Before anything else, open a terminal and run one of these. It takes five minutes. Don’t skip it.
MySQL: Check Replica Status
SSH into your replica server and run:
SHOW REPLICA STATUS\G
-- or on older MySQL versions:
SHOW SLAVE STATUS\GThe two numbers that matter most:
- Seconds_Behind_Source (or
Seconds_Behind_Master) — seconds the replica is behind the primary. Anything above 5s in production warrants attention. - Replica_SQL_Running — must be
Yes. If it’sNo, replication has stopped entirely.
PostgreSQL: Check Replication Lag
On the primary server, query the built-in replication view:
SELECT
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
(sent_lsn - replay_lsn) AS replication_lag_bytes,
write_lag,
flush_lag,
replay_lag
FROM pg_stat_replication;
On the replica itself, a simpler check:
SELECT
now() - pg_last_xact_replay_timestamp() AS replication_delay;
A NULL result means the replica hasn’t replayed any transactions yet. On a busy system, that’s worth investigating immediately.
What Actually Causes Replication Lag
Applying the wrong fix wastes hours. After chasing this problem across multiple systems, I’ve seen lag trace back to three distinct places:
1. Write Throughput Spikes
The primary ingests writes faster than the replica can apply them. Bulk imports and batch jobs running during business hours are the usual culprits. By default, the replica’s SQL thread applies transactions one at a time — that serial bottleneck is what kills you under load.
2. Long-Running Transactions on the Replica
MySQL: a long SELECT on the replica acquires a metadata lock that blocks the SQL thread from applying updates. PostgreSQL: hot_standby_feedback = on causes the primary to retain old row versions, while long queries on the standby create their own backlog. Both scenarios look like lag but have different fixes.
3. Network and I/O Bottlenecks
Slow disk I/O on the replica causes write lag. A saturated network link causes the binary log or WAL to back up at the send stage. PostgreSQL makes this easy to pinpoint — the write_lag, flush_lag, and replay_lag columns in pg_stat_replication tell you exactly where the time is being lost.
Pinpointing Which Stage Is Slow (PostgreSQL)
-- Compare individual lag stages
SELECT
application_name,
write_lag, -- time to write WAL to replica OS buffer
flush_lag, -- time to flush WAL to disk on replica
replay_lag -- time to apply changes
FROM pg_stat_replication;
write_lag high? Network problem. flush_lag high? Disk I/O on the replica. replay_lag high? Either CPU pressure or long-running queries blocking WAL apply.
Automated Monitoring: Because 3am Manual Checks Don’t Work
You can’t SSH into replicas every hour. Here’s how to get alerts sent to you instead.
Prometheus + postgres_exporter
Install postgres_exporter on each replica. It exposes pg_replication_lag out of the box.
docker run -d \
--name postgres-exporter \
-e DATA_SOURCE_NAME="postgresql://monitor_user:password@localhost:5432/postgres?sslmode=disable" \
-p 9187:9187 \
quay.io/prometheuscommunity/postgres-exporter
Then add an alert rule in Prometheus:
groups:
- name: postgres_replication
rules:
- alert: PostgresReplicationLagHigh
expr: pg_replication_lag > 30
for: 2m
labels:
severity: warning
annotations:
summary: "Replication lag is {{ $value }}s on {{ $labels.instance }}"
MySQL Replica Lag Monitoring Script
A Python script you can run as a cron job or plug into your existing alerting stack:
import pymysql
import sys
REPLICA_DSN = {
"host": "replica-host",
"user": "monitor",
"password": "secret",
"database": "information_schema",
}
LAG_THRESHOLD_SECONDS = 10
def check_replica_lag():
conn = pymysql.connect(**REPLICA_DSN)
with conn.cursor(pymysql.cursors.DictCursor) as cur:
cur.execute("SHOW REPLICA STATUS")
row = cur.fetchone()
if not row:
print("ERROR: No replica status — is this actually a replica?")
sys.exit(2)
sql_running = row.get("Replica_SQL_Running") or row.get("Slave_SQL_Running")
lag = row.get("Seconds_Behind_Source") or row.get("Seconds_Behind_Master") or 0
if sql_running != "Yes":
print(f"CRITICAL: SQL thread is not running")
sys.exit(2)
if lag > LAG_THRESHOLD_SECONDS:
print(f"WARNING: Replication lag is {lag}s (threshold: {LAG_THRESHOLD_SECONDS}s)")
sys.exit(1)
print(f"OK: Replication lag is {lag}s")
sys.exit(0)
if __name__ == "__main__":
check_replica_lag()
Enable Parallel Replication in MySQL
High write throughput? Switch to multi-threaded replication. This single change cut our lag by 80% during nightly batch imports — it’s the most impactful tuning available on a write-heavy replica:
-- On the replica (requires STOP REPLICA first)
STOP REPLICA;
SET GLOBAL replica_parallel_workers = 8;
SET GLOBAL replica_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL replica_preserve_commit_order = ON;
START REPLICA;
PostgreSQL: Tune max_wal_senders and wal_keep_size
A replica that falls too far behind can lose the WAL segments it needs to catch up — at that point replication breaks, and a full resync is the only way out. Set a buffer to prevent it:
# postgresql.conf on primary
wal_keep_size = 1024 # keep 1GB of WAL, adjust based on your write rate
max_wal_senders = 5
wal_level = replica
Replication slots give even stronger guarantees, but come with a real trade-off: if a replica goes offline for days, the primary holds WAL indefinitely and disk fills up. Monitor retained WAL before deploying slots to production:
-- Create a physical replication slot on primary
SELECT pg_create_physical_replication_slot('replica_slot_1');
-- Monitor slot retained WAL
SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
Practical Tips From 6 Months in Production
1. Never Read Sensitive Data From Replicas Without a Lag Check
Payment status, inventory counts, user permissions — these should come from the primary, or pass a lag gate first:
def get_replication_delay(conn):
with conn.cursor() as cur:
cur.execute("SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))")
return cur.fetchone()[0] or 0
if get_replication_delay(replica_conn) < 5:
# safe to read from replica
result = replica_conn.execute(query)
else:
# fall back to primary
result = primary_conn.execute(query)
2. Schedule Heavy Batch Jobs Off-Peak
Bulk inserts and data migrations should run during low-traffic windows — 2–4am, not 2pm. On MySQL, also split large LOAD DATA INFILE operations into smaller batches with a short SLEEP(0.1) between them. It gives the replica room to breathe rather than falling further and further behind.
3. Watch for Long Queries on the Replica
Analytics queries on PostgreSQL standbys can block WAL replay for minutes at a time. Either route heavy reports to a dedicated replica, or put a hard cap on the analytics role:
ALTER ROLE analytics_user SET statement_timeout = '30s';
4. Build a Runbook, Not Just an Alert
An alert without a runbook is noise at 3am. Whoever picks up that page needs to know which queries to run first and what the escalation path looks like. After our incident, we wrote REPLICATION_LAG_RUNBOOK.md and linked it directly in the PagerDuty alert body. Nobody had to guess anymore.
5. Test Replica Failover Before You Need It
Replication is only useful if you can promote a replica under pressure. Run a failover drill in staging — pg_ctl promote for PostgreSQL, STOP REPLICA; RESET REPLICA ALL; for MySQL. Make sure your entire on-call rotation has done it at least once. The first time shouldn’t be during an outage.
What to Do Before the Next Spike Hits
Replication lag hides until it explodes. It was invisible to us for weeks before that 40-second incident that woke half the team up at midnight. Both PostgreSQL and MySQL surface enough internal metrics to catch it early — but only if someone is actually watching.
Start with the quick-check commands above. Wire up monitoring this week, not next sprint. Write that runbook before you need it. Future you at 3am will be grateful.
