Why MySQL 5.7 Seconds_Behind_Master Is Unreliable and How pt‑heartbeat Provides Accurate Replication Lag Measurement

1 Background

MySQL 5.7 reached end‑of‑life in October 2023, yet many production systems still rely on it. A recent incident showed severe master‑slave replication lag after a large transaction, causing data inconsistency between read‑only replicas and the primary.

The business team needs the exact lag value to assess impact and optimize the system.

2 Is Seconds_Behind_Master Reliable?

Seconds_Behind_Master is the field returned by SHOW SLAVE STATUS that indicates how many seconds a replica is behind the primary.

In theory, a value of 0 means the replica is fully synchronized, higher values indicate delay.

In practice, the metric often diverges from real delay: it may show 0 while data is out‑of‑sync or produce spikes unrelated to replication performance.

The root causes stem from its calculation method and MySQL 5.7’s replication architecture.

Root Cause 1: Calculation Method Limitation

The metric is computed as:

longlong slave_seconds_behind_master(Master_info* mi) {
  longlong t0 = mi->clock_diff_with_master;
  longlong t1 = mi->rli->last_master_timestamp;
  longlong t2 = mi->get_master_log_pos() ? time(NULL) : 0;
  return (t2 > t1) ? (t2 - t1 - t0) : 0;
}

Key variables:

t0 – clock difference correction.

t1 – timestamp of the last event written to the binary log on the primary.

t2 – current time on the replica (POSIX time(NULL) ).

The calculation assumes t1 reflects the execution time of the event, but it actually records when the event was written to the binlog, which can be delayed by sync_binlog settings (e.g., sync_binlog=0 ).

Root Cause 2: Single‑Threaded SQL Thread Masking Delay

MySQL 5.7 applies events with a single SQL thread. The timestamp is updated only after a large transaction finishes, so Seconds_Behind_Master stays unchanged during execution and jumps only after completion, hiding real‑time lag.

void Relay_log_info::set_master_log_pos(ulonglong pos) {
  // simplified
  if (pos)
    last_master_timestamp = log_pos_to_timestamp(pos);
}

Root Cause 3: Parallel Replication Misreporting

Even with slave_parallel_workers enabled, the metric still uses the last applied event’s timestamp, ignoring other worker threads. If one thread lags while others are up‑to‑date, the metric may still show 0.

if (mi->rli->slave_parallel_workers > 0 && mi->rli->last_master_timestamp)
  return time(NULL) - mi->rli->last_master_timestamp;

Root Cause 4: Ignoring I/O and Network Delays

The metric does not account for delays in the I/O thread that fetches events from the primary or writes them to the relay log. Network issues can cause the I/O thread to fall behind while the SQL thread appears current, resulting in a false zero value.

3 Solution: pt‑heartbeat

How to Obtain Precise Lag Values

pt‑heartbeat (Percona Toolkit) injects a heartbeat record on the primary and compares timestamps on the replica, delivering accurate lag measurements.

Steps:

On the primary – create a heartbeat table and update it every second:

-- Create table heartbeat.heartbeat with columns ts and server_id
-- Update one row every second (--interval=1)
pt-heartbeat --user=root --password=xxx --create-table --update --interval=1 -D heartbeat

On the replica – monitor the lag:

pt-heartbeat --user=root --password=xxx --monitor -D heartbeat

How to Verify Accuracy

The Perl source of pt‑heartbeat shows the heartbeat injection and lag calculation logic, which uses SELECT NOW(6) on both primary and replica, achieving microsecond precision.

sub update_heartbeat {
  my ($dbh) = @_;
  my $ts = $dbh->selectrow_array('SELECT NOW(6)');
  my $server_id = $dbh->selectrow_array('SELECT @@server_id');
  $dbh->do("INSERT INTO heartbeat.heartbeat (id, ts, server_id) VALUES (1, ?, ?) ON DUPLICATE KEY UPDATE ts = ?, server_id = ?",
           undef, $ts, $server_id, $ts, $server_id);
}

sub check_heartbeat {
  my ($dbh) = @_;
  my $row = $dbh->selectrow_hashref('SELECT ts FROM heartbeat.heartbeat WHERE id = 1');
  my $master_ts = $row->{ts};
  my $slave_ts  = $dbh->selectrow_array('SELECT NOW(6)');
  my $lag = time_diff($slave_ts, $master_ts);
  return sprintf('%.2f', $lag);
}

Advantages:

Real‑time : updates every second, reflecting true replication delay.

Independence : does not rely on MySQL’s internal timestamps.

Limitation : requires synchronized clocks; otherwise use the --skew option.

4 Conclusion and Recommendations

In MySQL 5.7, Seconds_Behind_Master cannot meet precise lag requirements due to flawed timestamp usage, lack of real‑time updates, and omission of parallel and I/O delays. pt‑heartbeat offers a reliable alternative by measuring actual time differences via a heartbeat mechanism.

Recommendations

Deploy pt‑heartbeat in production and set an appropriate --interval (e.g., 0.5 s) to balance accuracy and load.

Ensure master‑slave clocks are synchronized (e.g., via NTP) to guarantee correct lag values.

Define alert thresholds based on business needs and optimize large‑transaction handling.

References

[1] MySQL EOL notice: https://www.mysql.com/support/eol-notice.html

[2] SHOW SLAVE STATUS documentation: https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html

[3] pt‑heartbeat documentation: https://docs.percona.com/percona-toolkit/pt-heartbeat.html