ProxySQL replica shunned for replication lag: max_replication_lag and stale-read protection

When a MySQL replica falls behind its primary, ProxySQL’s monitor module detects the lag and pulls that replica out of read rotation by marking it SHUNNED. This is deliberate stale-read protection: applications should not read from a replica that has not applied recent writes, because they would see stale results or violate read-after-write consistency.

The mechanism is governed by max_replication_lag, a per-backend column in mysql_servers measured in seconds. When the monitor’s replication lag check finds Seconds_Behind_Master exceeding that threshold, the backend is shunned until lag drops back below it. The tradeoff is direct: shunning protects consistency but reduces read capacity. In a topology with two or three replicas, losing one shifts 33 to 50 percent of read traffic onto the remaining backends, which can push them over the same threshold.

How it works

The replication lag check runs on the monitor module’s own threads, separate from query-processing workers. The cycle operates as follows.

The monitor connects to the backend using the credentials in mysql-monitor_username and mysql-monitor_password (separate from client credentials). The monitor user needs REPLICATION CLIENT privilege to execute SHOW SLAVE STATUS.
The monitor reads Seconds_Behind_Master from the result.
If the value exceeds max_replication_lag, a failure counter increments. The backend is shunned after mysql-monitor_replication_lag_count consecutive exceeded checks (default 1 ). With the default of 1, a single check above threshold triggers shunning.
Once shunned, the backend stops receiving new queries. Existing connections to the shunned backend are terminated . Clients with active queries on that backend receive MySQL error 2006 (“Server has gone away”). max_replication_lag is a hard limit, not a soft hint.
The monitor continues checking the shunned backend at the same interval. When Seconds_Behind_Master drops below the threshold, the backend returns to ONLINE and resumes receiving traffic.

flowchart TD
    A["Monitor: SHOW SLAVE STATUS"] --> B{"max_replication_lag > 0?"}
    B -->|"No, disabled"| C["Skip lag check"]
    B -->|"Yes"| D{"Lag exceeds threshold?"}
    D -->|"No"| E["Backend stays ONLINE"]
    D -->|"Yes"| F["Backend SHUNNED"]
    F --> G["Existing connections terminated"]
    F --> H["Read traffic diverts to other replicas"]
    H --> I{"Next check: lag below threshold?"}
    I -->|"Yes"| E
    I -->|"No"| F

Global variables controlling timing and debounce:

mysql-monitor_replication_lag_interval (default 10000ms): how often the monitor checks lag on each backend.
mysql-monitor_replication_lag_timeout (default 1000ms): how long the monitor waits for a response before timing out.
mysql-monitor_replication_lag_count (default 1): consecutive lag-exceeded checks required before shunning. Increase to debounce transient spikes.
mysql-monitor_slave_lag_when_null (default 60): assumed lag when Seconds_Behind_Master is NULL, which occurs when replication is stopped or a thread is broken. Set this higher than max_replication_lag to shun replicas with broken replication.
mysql-monitor_replication_lag_group_by_host (default false): deduplicates lag checks when the same physical server appears in multiple hostgroups.

To check which backends have lag monitoring enabled:

-- Check configured lag thresholds (admin port 6032)
SELECT hostgroup_id, hostname, port, max_replication_lag
  FROM runtime_mysql_servers
  WHERE max_replication_lag > 0;

To see the monitor’s recent lag readings:

-- Recent replication lag check results
SELECT hostname, port, time_start_us, success_time_us, repl_lag, error
  FROM monitor.mysql_server_replication_lag_log
  ORDER BY time_start_us DESC LIMIT 20;

A non-null error means the check itself failed (timeout, connection refused, privilege error), which is a different problem from a successful check that found high lag.

Where it shows up in production

Read/write split with a lagging replica. A reader hostgroup has two or three replicas, each with max_replication_lag set to 10 or 30 seconds. During a large batch write, one replica falls behind, gets shunned, and its traffic shifts to the others.

All replicas lagging simultaneously. When the primary sustains a high write rate (bulk import, DDL, large transaction), all replicas may exceed the threshold. ProxySQL shuns them one by one until the reader hostgroup has zero ONLINE backends. Read queries then fail or fall back to the writer hostgroup depending on routing rules, potentially overloading the primary.

Seconds_Behind_Master misleading the monitor. Seconds_Behind_Master is unreliable during large transactions. It measures the timestamp difference of the event currently being replayed. During a long-running transaction replay (for example, a large ALTER TABLE), it can show 0 because the SQL thread is actively processing an event, then jump to a large value once the transaction finishes. ProxySQL may keep a replica ONLINE while it is actually far behind, or shun it only after a sudden jump.

Multi-source replicas. ProxySQL’s lag monitor executes SHOW SLAVE STATUS without a FOR CHANNEL clause. On multi-source replicas, MySQL returns one row per channel and ProxySQL reads only the first. If the first channel has low lag but another is severely delayed, ProxySQL will not detect the problem.

Tradeoffs and gotchas

max_replication_lag=0 should disable checking, but verify. Setting max_replication_lag=0 means the monitor should not check lag for that backend. A reported bug in ProxySQL 2.4.4 caused the comparison current_replication_lag >= 0 && current_replication_lag > max_replication_lag to evaluate true for any non-negative lag when the threshold is 0, shunning replicas despite the “disabled” setting. If you rely on max_replication_lag=0, verify by querying monitor.mysql_server_replication_lag_log for that backend.

Check timeout can cause premature re-enabling. If a shunned replica’s lag check times out, the monitor reports lag as -2 seconds, which is below the threshold. This re-enables the server while it is still lagging. The backend appears healthy in ProxySQL’s status table but serves stale data. The only visible symptom is stale reads reaching applications.

Connection termination is by design. When ProxySQL shuns a replica for lag, existing connections are terminated and clients with active queries receive error 2006. If you have latency-insensitive queries (analytics, reporting, batch reads) that should tolerate stale data, route them to a separate hostgroup without max_replication_lag configured.

pt-heartbeat for accuracy. Setting mysql-monitor_replication_lag_use_percona_heartbeat to a table name (for example, percona.heartbeat) switches lag measurement from SHOW SLAVE STATUS to pt-heartbeat timestamps. This is more accurate during large transactions because it measures actual data replication delay rather than relying on SQL thread position tracking. If your workload involves large transactions that make Seconds_Behind_Master unreliable, use pt-heartbeat.

SHUNNED vs SHUNNED_REPLICATION_LAG. For asynchronous replication, the status is SHUNNED. SHUNNED_REPLICATION_LAG is specific to Group Replication’s two-stage shunning: the server is first flagged SHUNNED with connections preserved briefly, then if lag persists beyond max_transactions_behind_count * 2, it becomes SHUNNED_REPLICATION_LAG with connections terminated. For standard async replication, there is no two-stage process.

Signals to watch

Signal	Why it matters	Warning sign
`status` in `stats_mysql_connection_pool`	Shows ONLINE, SHUNNED, OFFLINE_SOFT, or OFFLINE_HARD per backend	Backend transitioning to SHUNNED, especially multiple in the same hostgroup
`MySQL_Monitor_replication_lag_check_ERR`	Cumulative count of failed lag checks (check could not run)	Rising rate means monitor cannot reach backend or lacks privileges
`MySQL_Monitor_replication_lag_check_OK`	Cumulative count of successful lag checks	Drop to zero means monitor stopped checking
`repl_lag` in `monitor.mysql_server_replication_lag_log`	Measured lag per check with timestamps	Sustained values above threshold, or NULL indicating broken replication
`backend_lagging_during_query` in `stats_mysql_global`	Counts queries that hit a backend after it was detected as lagging but before the shun took effect	Non-zero values mean queries may have returned stale data during the detection window
`ConnUsed` on remaining ONLINE replicas	Connection pool usage per backend	Spike on other backends immediately after one is shunned
`Latency_us` per backend	Monitor-measured ping latency	Divergence between backends in same hostgroup suggests degradation

-- Monitor check counters
SELECT Variable_Name, Variable_Value
  FROM stats_mysql_global
  WHERE Variable_Name IN ('MySQL_Monitor_replication_lag_check_OK',
                          'MySQL_Monitor_replication_lag_check_ERR');

-- Current backend status and pool state
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, Latency_us
  FROM stats_mysql_connection_pool
  ORDER BY hostgroup, srv_host;

How Netdata helps

Per-second backend status. Netdata collects stats_mysql_connection_pool per backend at high frequency, so you see the exact moment a replica transitions to SHUNNED and when it recovers. This catches shun-and-recover cycles that happen faster than typical polling intervals.
Correlating shun events with load. When a replica is shunned, surviving backends absorb its traffic. Per-backend ConnUsed, Queries, and Latency_us charts show whether they are handling the redirected load or approaching saturation.
Monitor check failure rates. Netdata tracks MySQL_Monitor_replication_lag_check_OK and MySQL_Monitor_replication_lag_check_ERR as rate metrics. A rising error rate means the monitor cannot reach or query the backend, which requires different remediation than actual high lag.
Anomaly detection on lag patterns. ML-based anomaly detection flags unusual patterns in replication lag that static thresholds miss, such as gradual drift or oscillation preceding a full shun event.