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.

1. 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.

2. The monitor reads Seconds_Behind_Master from the result.

3. 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, already present before 2.4.0). With the default of 1, a single check above threshold triggers shunning.

4. Once shunned, the backend stops receiving new queries. Existing pooled connections are no longer handed new queries; ProxySQL destroys a used connection when the session discovers the lag-shunned status. An affected in-flight query can fail and, if retries are enabled and safe, be retried on another backend. max_replication_lag is a hard limit, not a soft hint.

5. 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. Current 3.x source still issues SHOW SLAVE STATUS/SHOW REPLICA STATUS without FOR CHANNEL.

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 bug in ProxySQL 2.4.4 caused lagging replicas to be shunned even when the threshold was 0. It was fixed in 2.4.6 (#4077), and current code requires max_replication_lag > 0 before shunning. 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. Current source intentionally re-enables on a -2 timeout result, so keep the check timeout below the operational lag threshold. The backend appears healthy in ProxySQL’s status table but serves stale data. The only visible symptom is stale reads reaching applications.

Used-connection teardown is by design. ProxySQL does not broadcast an instant kill to every socket when lag shunning occurs. A session using a connection to that backend discovers the status, closes that connection, and may retry only when retries are enabled and the query is safely reusable. For latency-insensitive stale reads (analytics, reporting, batch work), route them to a separate hostgroup without max_replication_lag.

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. Asynchronous replication lag sets SHUNNED_REPLICATION_LAG directly. Group Replication additionally has a two-stage flow: an ONLINE server first becomes SHUNNED; if transactions-behind reaches twice monitor_groupreplication_max_transactions_behind_count, it becomes SHUNNED_REPLICATION_LAG.

Signals to watch

-- 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.

Related guides

• ProxySQL backend SHUNNED: why a healthy backend gets pulled out of rotation
• ProxySQL backend flapping between ONLINE and SHUNNED: monitor-induced oscillation
• ProxySQL OFFLINE_SOFT vs OFFLINE_HARD vs SHUNNED: what each backend status means
• ProxySQL backend connection pool exhausted: queries queuing for a free connection
• ProxySQL ConnERR climbing: backend connection errors and how to localise them
• ProxySQL connection storm after restart: an empty pool meeting a mass reconnect