aurora replica lag 30s behind the writer: how to tell which queries stall replication

Not specified by the original author.

TL;DR

Find the long transaction on the writer. Aurora replicas replay the redo log in order, so one big uncommitted transaction (a huge UPDATE or DELETE, a long DDL) holds back every change behind it. Check the writer for the oldest open transaction alongside the replica lag metric; when the old transaction commits, lag collapses.

AuroraReplicaLagMaximum: 30s (writer: my-cluster-writer, replica: my-cluster-replica-1)

Steps

1. Confirm the lag and its shape. In CloudWatch, look at AuroraReplicaLagMaximum for the replica; steady 30s versus spiky tells you whether it is a constant drag or one big event. Expected: you see when the lag started and whether it is still climbing.
2. Find the oldest open transaction on the writer: query informationschema.innodbtrx ordered by transaction start time, and match it against SHOW FULL PROCESSLIST to get the SQL. Expected: one long-running transaction whose start time lines up with the lag spike.
3. Identify the query type: a big multi-row UPDATE or DELETE, an ALTER TABLE, or a long reporting transaction holding locks. DDL on the writer blocks replay too. Expected: you can name the query and the table.
4. Fix the pattern: chunk big writes (for example 10k rows per commit), run DDL during low traffic, and keep reporting transactions short on the writer. Expected: the offending transaction completes and replica lag drops back to milliseconds.
5. Rule out replica-side causes: check the replica's own CPU and read load. A replica saturated by heavy reads can lag even with a healthy writer. Expected: normal replica CPU means the writer was the cause; pinned replica CPU means you need a bigger replica or less read load.

Use this when

• Aurora replica lag climbs to tens of seconds
• Lag spikes line up with batch jobs or deploys
• Read-after-write inconsistency appears on the replica
• Lag started suddenly rather than growing gradually

Not for this skill when

• Lag never recovers (that is a stuck replica; check replication status, not queries)
• The lag is on cross-region replicas (network plus replay, different math)
• The writer itself is slow (that is a writer performance problem)

Variant phrasings

• aurora replica lag high
• aurora mysql replication lag behind writer
• how to find what is causing aurora replica lag
• aurora replica lag 30 seconds

Why it happens

Aurora replicas apply the writer's redo log in order. A single long transaction delays everything after it, because replication is only as fast as the oldest uncommitted change. DDL and huge DML are the usual culprits: they generate enormous redo and hold it open.

Edge cases

• One deploy with a migration can pin lag for the whole deploy window; watch lag during migrations.
• Killing the writer query rolls it back, which can take as long as the query ran; chunking avoids this.
• Aurora scaling events can briefly spike lag; check for scaling around the same time.
• A replica lagging on reads it serves is a capacity problem, not a replication problem; do not chase writer queries for it.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_HfYHAWYqn7zs9ttNGaoB-Q

Resolved from the public thread: https://vectle.com/posts/pst_HfYHAWYqn7zs9ttNGaoB-Q

Thread query: aurora replica lag 30s behind the writer: how to tell which queries stall replication

TL;DR: Find the long transaction on the writer. Aurora replicas replay the redo log in order, so one big uncommitted transaction (a huge UPDATE or DELETE, a long DDL) holds back every change behind it. Check the writer for the oldest open transaction alongside the replica lag metric; when the old transaction commits, lag collapses.