MySQL Seconds_Behind_Source is unreliable: measuring real replication lag

You run SHOW REPLICA STATUS and see Seconds_Behind_Source = 0. Both replication threads say Yes. You assume the replica is current. During a source write burst, you fail over and discover hours of missing transactions. This happens because Seconds_Behind_Source is not a real-time lag measurement. It is a timestamp diff between the replica’s clock and the timestamp of the event the SQL thread is currently applying. When the SQL thread reaches the end of the relay log, the metric snaps to zero even if the I/O thread has not fetched newer events. When the SQL thread stops, it returns NULL, which most lag alerts ignore. When the source was idle and then bursts, it jumps to the idle duration even though real lag is minimal. With parallel replication, it oscillates based on worker timing. For failover-critical decisions, you need a different measurement strategy.

What it is and why it matters

Seconds_Behind_Source (or Seconds_Behind_Master in MySQL 5.7) is the default replication lag metric in SHOW REPLICA STATUS. It is the first value most operators check when they suspect a replica is stale. Because it is built in and requires no extra setup, it often becomes the only lag signal in monitoring systems and failover scripts.

This is dangerous. Failover decisions, read-after-write consistency checks, and RPO estimates all depend on knowing exactly how far behind a replica is. If the metric reports 0 when the replica is actually hours behind, or NULL when replication is broken, your automation makes wrong decisions silently. Knowing when this metric is lying is a prerequisite for safe replicated topologies.

How it works

MySQL estimates Seconds_Behind_Source as the difference between the replica’s current time and the timestamp of the event the SQL thread is currently applying. It does not compare the replica against the source’s current time. It does not measure network delay or I/O thread fetch delay directly. It is purely a function of the event timestamp inside the relay log.

Because the calculation is bounded by the relay log, the SQL thread can be fully caught up with every event it has received and still report 0, even when the I/O thread has not pulled new events from the source for hours. The metric tells you whether the SQL thread is busy, not whether the replica is current.

flowchart TD
  S[Source event
timestamp T1] --> IO[Replica I/O thread]
  IO --> RL[Relay log]
  RL --> SQL[Replica SQL thread]
  SQL --> SBS[Seconds_Behind_Source
now - T1]
  IO -->|Stalled| E1[Relay log stale]
  E1 --> F1[SBS = 0
real lag hidden]
  S -->|Idle then burst| E2[Old timestamp]
  E2 --> F2[SBS = 3600
real lag seconds]
  SQL -->|Stopped| F3[SBS = NULL
alert silent]

Where it shows up in production

The metric fails in four specific ways that have each caused production incidents.

False zero when the I/O thread lags

When the SQL thread drains the relay log and goes idle, Seconds_Behind_Source snaps to 0. If the I/O thread is stalled, reconnecting after a network blip, or slower than the source’s write rate, the relay log itself falls behind. The operator sees a flat zero during the exact moment lag is accumulating. This is common during heavy write bursts, cross-region replication, or when the source’s binlog dump thread is overloaded.

NULL instead of infinity when replication breaks

If the SQL thread stops due to a duplicate key error, schema mismatch, or an unsupported statement, Seconds_Behind_Source returns NULL. An alert threshold like Seconds_Behind_Source > 30 never fires, because NULL is not greater than 30. Replication is broken and lag is growing unboundedly, but the metric appears blank rather than alarming. This is one of the most dangerous silent failure modes in MySQL replication monitoring.

Spikes after source idle periods

If the source was idle for an hour and then receives a burst of writes, the next event in the relay log carries a timestamp from an hour ago. Seconds_Behind_Source jumps to 3600 even though the replica applies the burst within seconds of arrival. This produces false-positive lag alerts and pages that do not represent real data staleness. The metric answers “how old is this event?” not “how long did it take to arrive?”.

Inaccuracy with parallel replication

With replica_parallel_workers > 0, the value oscillates as workers apply transactions with different original commit timestamps. It does not aggregate backlog across workers, so it cannot represent true apply lag in multi-threaded configurations. A replica can have a deep apply queue and report a low number because one fast worker happened to commit recently.

Tradeoffs and when to use it

Seconds_Behind_Source is not worthless. On lightly loaded, single-threaded topologies where the source is continuously active and both replication threads are confirmed healthy, it offers a rough trend of apply performance. It is useful for answering “is the SQL thread keeping up with the relay log?”.

It is dangerous for automated failover, read-after-write routing, SLA enforcement, and any scenario where the I/O thread could lag independently of the SQL thread. If your orchestrator uses SBS as the sole lag input, it will miss I/O stalls, treat broken replication as healthy, and panic over idle-source artifacts.

How to measure real replication lag

For production decisions, use one of the following methods.

Heartbeat tables with pt-heartbeat

A heartbeat table on the source is updated with the current timestamp every second. The replica reads this row and computes the wall-clock delta. This measures end-to-end latency, including network delay, I/O thread fetch time, and SQL thread apply time.

Percona Toolkit’s pt-heartbeat is the standard implementation. It creates a single-row table, updates it on the source, and provides a --monitor mode on the replica that reports real lag in seconds. Many failover tools prefer heartbeat lag over Seconds_Behind_Source because it does not suffer from the false zero and idle-spike problems.

Run the updater on the source and the monitor on the replica. The output is a simple integer: seconds of lag. Feed this into your orchestrator if you run automated failover.

GTID set comparison

If GTID replication is enabled, compare the source’s executed set against the replica’s.

On the source:

SELECT @@global.gtid_executed;

On the replica:

SELECT @@global.gtid_executed;

Then compare:

SELECT GTID_SUBTRACT('source_gtid_set', 'replica_gtid_set');

A non-empty result shows the exact transactions the replica has not yet executed. This gives precise transaction-level lag. It is the safest way to determine whether a replica is caught up enough to fail over to, because it tells you exactly which transactions are missing.

The tradeoff is that it returns a transaction gap, not a time duration. It answers “are we missing data?” perfectly, but it does not answer “how many seconds behind are we?” without correlating transaction volume with time. For RPO decisions, however, the transaction gap is usually what you actually care about.

Performance Schema worker timestamps

For multi-threaded replication diagnostics, MySQL 8.0 exposes per-worker apply timestamps in performance_schema.replication_applier_status_by_worker. Comparing LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP against the current time gives the exact apply lag for each worker individually.

This is useful for identifying which specific worker is slow, but it covers only the apply side. It does not measure I/O thread fetch delay.

Signals to watch in production

How Netdata helps

• Netdata collects Seconds_Behind_Source alongside Replica_IO_Running and Replica_SQL_Running, enabling composite alerts that require healthy thread states before trusting lag values. This prevents the silent failure mode where a NULL from a stopped SQL thread is missed.
• Netdata charts relay log growth and source write rate on the same dashboard as lag metrics, letting you distinguish an I/O thread stall (relay log flat, SBS zero) from genuine apply catch-up.
• For operators running pt-heartbeat, Netdata can chart custom SQL metrics, letting you plot heartbeat lag directly against SBS to visualize the divergence during idle-source bursts.
• Netdata alerts when either thread leaves the Yes state, independent of lag calculations.

Related guides

• How MySQL actually works in production: a mental model for operators
• MySQL Aborted_connects and Aborted_clients climbing: diagnosis
• MySQL adaptive hash index latch contention: high CPU, low throughput
• MySQL InnoDB buffer pool hit ratio collapse: the cliff edge
• MySQL slow after restart: buffer pool warm-up and the cold cache
• MySQL innodb_buffer_pool_size tuning: 60-80% of RAM and when that breaks
• MySQL Innodb_buffer_pool_wait_free > 0: buffer pool memory pressure
• MySQL InnoDB checkpoint age: the redo log capacity signal nobody watches
• MySQL connection exhaustion: detection, diagnosis, and prevention
• MySQL innodb_deadlock_detect=OFF: when deadlock detection becomes the bottleneck
• MySQL ERROR 1213: Deadlock found when trying to get lock; try restarting transaction
• MySQL FLUSH TABLES WITH READ LOCK stall: backups that freeze the server