$ guides / proxysql / proxysql-all-replicas-lagging ▌

Operations Guides

ProxySQL all replicas lagging: reads falling back onto the writer

You configured a read/write split with replicas in the reader hostgroup, each with max_replication_lag set to keep stale reads away from clients. Now all of them are SHUNNED and the reader hostgroup has zero ONLINE backends. Read queries are either failing or falling back onto the writer, which absorbs the full read and write workload.

The cascade is the danger. As each replica lags and shuns, its traffic redirects to the remaining ONLINE replicas, increasing their load. The last standing replica absorbs all read traffic, its lag climbs, and it shuns too. The reader hostgroup goes empty and every read hits the writer.

The root cause is upstream: write volume has outstripped replica replay capacity, a large transaction or DDL is serializing replay across every replica simultaneously, or the network path between primary and replicas is saturated.

What this means

ProxySQL’s monitor module periodically checks Seconds_Behind_Master on each backend where max_replication_lag > 0. When the value exceeds the threshold, the backend is marked SHUNNED. It stays shunned until the next lag check reports a value below the threshold.

The PAGE-worthy condition is “all replicas in the reader hostgroup are SHUNNED and no ONLINE backend remains.” Read queries routed to the reader hostgroup either return an error or fall back onto the writer hostgroup. Errors break clients. Writer spillover overloads the primary with read load it was not sized for, degrading write latency and potentially cascading into full primary overload.

A critical nuance: Seconds_Behind_Master is unreliable during large transaction replay. It can show 0 (because the SQL thread is actively applying events from a recent timestamp) and then jump to a large value once the transaction finishes. Lag can exceed your threshold for a while before ProxySQL detects it, or it can oscillate around the threshold causing repeated shun/unshun cycles.

flowchart TD
    A["High write volume or large DDL on primary"] --> B["All replicas lag simultaneously"]
    B --> C["Replica 1 SHUNNED"]
    B --> D["Replica 2 SHUNNED"]
    C --> E["Reads concentrate on remaining replicas"]
    D --> E
    E --> F["Last replica absorbs all reads"]
    F --> G["Last replica lags from load"]
    G --> H["Reader hostgroup empty"]
    H --> I["Reads fail or fall back to writer"]

Common causes

Cause	What it looks like	First thing to check
Write volume exceeds replay capacity	Lag increases gradually across all replicas during peak write hours and never catches up	Compare write QPS on the primary against replica SQL thread throughput
Large transaction or DDL serializing replay	All replicas lag at the same time, lag spikes to a large value, then slowly drains	Check primary for recent ALTER TABLE, bulk INSERT, or long-running transactions
Network bottleneck primary to replicas	Lag correlates across all replicas, coincides with bandwidth saturation	Check network throughput on the primary replication interface
Seconds_Behind_Master NULL treated as high lag	Replicas shunned immediately after replication stops or reconnects	Check `mysql-monitor_slave_lag_when_null` vs `max_replication_lag`
Monitor credential issues	`MySQL_Monitor_replication_lag_check_ERR` rising, lag readings missing or stale	Verify monitor user has `REPLICATION CLIENT` privilege on each backend

Quick checks

Run these on the ProxySQL admin port (6032) unless otherwise noted.

-- Which backends are SHUNNED and which hostgroups have no ONLINE backends
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree
FROM stats_mysql_connection_pool ORDER BY hostgroup, status;

-- Configured lag thresholds per backend
SELECT hostgroup_id, hostname, port, max_replication_lag
FROM runtime_mysql_servers WHERE max_replication_lag > 0;

-- The monitor's replication lag log for recent readings
SELECT * FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC LIMIT 30;

-- Monitor lag check success and failure counters
SELECT Variable_Name, Variable_Value FROM stats_mysql_global
WHERE Variable_Name LIKE '%replication_lag%';

-- ConnUsed on the writer to detect read spillover
-- Replace <writer_hostgroup> with your actual writer hostgroup ID
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, Queries
FROM stats_mysql_connection_pool WHERE hostgroup = <writer_hostgroup>;

-- Queries that hit lagging backends mid-execution
SELECT Variable_Name, Variable_Value FROM stats_mysql_global
WHERE Variable_Name = 'backend_lagging_during_query';

How to diagnose it

Confirm all reader-hostgroup backends are SHUNNED. Query stats_mysql_connection_pool for the reader hostgroup. If every row shows status = SHUNNED, you have zero read capacity through the proxy.
Read the replication lag log. Query monitor.mysql_server_replication_lag_log for the actual lag values the monitor observed. This distinguishes genuine high lag from NULL or error results that indicate monitor failures.
Check whether Seconds_Behind_Master is NULL on each replica. When replication stops (I/O thread dies, relay log corruption, primary disconnect), Seconds_Behind_Master becomes NULL. ProxySQL substitutes mysql-monitor_slave_lag_when_null (default 60 seconds). If max_replication_lag is set below 60, the replica is shunned immediately. Check directly on each replica:

-- Run directly on each replica, not through ProxySQL
SHOW REPLICA STATUS\G
-- MySQL 8.0.21 and older: SHOW SLAVE STATUS\G

Identify the source of lag on the primary. Check for large transactions, DDL, or sustained high write volume:

-- Long-running transactions on the primary
-- MySQL 8.0: performance_schema.processlist is preferred;
-- INFORMATION_SCHEMA.PROCESSLIST works but is deprecated
SELECT ID, USER, HOST, TIME, STATE, LEFT(INFO, 200) AS query
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE TIME > 30 AND COMMAND != 'Sleep' ORDER BY TIME DESC;

Check monitor check error rates. If MySQL_Monitor_replication_lag_check_ERR is rising, the monitor cannot read lag at all. This typically means the monitor user lacks REPLICATION CLIENT privilege on the backend. The replica appears to have no lag data, so ProxySQL may shun it based on the mysql-monitor_slave_lag_when_null substitution.
Check the writer ConnUsed and Queries counter. If reads are spilling onto the writer, its ConnUsed will be elevated relative to baseline and its Queries counter will be higher than normal. This confirms the fallback is happening and tells you how much extra load the writer is absorbing.
Check backend_lagging_during_query. A rising value means queries reached backends that went stale mid-execution. Clients may have received stale or inconsistent data.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`status` in `stats_mysql_connection_pool`	Direct status of each backend. SHUNNED means removed from rotation.	All reader-hostgroup backends SHUNNED simultaneously
`max_replication_lag` in `runtime_mysql_servers`	The threshold that triggers shunning. If 0, lag checking is disabled.	Threshold set below `mysql-monitor_slave_lag_when_null` default of 60
`monitor.mysql_server_replication_lag_log`	Actual lag readings the monitor collected. Ground truth for why shunning occurred.	Consistently high values across all replicas, or NULL and error results
`MySQL_Monitor_replication_lag_check_ERR`	Failed lag checks. Monitor cannot determine replica health.	Sustained nonzero rate
`backend_lagging_during_query` in `stats_mysql_global`	Queries that hit a backend that became lagging while the query was in flight.	Rising counter
`ConnUsed` on remaining ONLINE replicas	The last standing replica absorbs all redirected read traffic. This is the cascade signal.	ConnUsed climbing sharply on the last ONLINE replica
`ConnUsed` on the writer	If reads fall back to the writer, writer connection usage spikes beyond write-only baseline.	Writer ConnUsed significantly above normal with no write-traffic increase
`Seconds_Behind_Master` on each replica (direct check)	The value the monitor bases shunning decisions on. Unreliable during large transactions.	Value jumping between 0 and large values, or NULL

Fixes

Write volume exceeds replica replay capacity

If lag increases gradually across all replicas during peak write hours and never catches up, the write workload has outgrown the replica replay throughput.

Enable parallel replication. MySQL 5.7+ supports multi-threaded replica replay via slave_parallel_workers and slave_parallel_type = LOGICAL_CLOCK (renamed to replica_parallel_workers and replica_parallel_type in MySQL 8.0.26+). A single SQL thread on each replica is the replay bottleneck for write-bound workloads. This is usually the highest-impact fix.
Upgrade replica storage. Faster storage (NVMe vs SATA SSD) reduces I/O wait during replay, especially for workloads with high write amplification.
Add replicas. Distributes read load and gives more aggregate replay capacity. Does not fix per-replica replay speed.
Temporarily raise max_replication_lag. Accepts more staleness to keep replicas in rotation. This is a stopgap, not a fix:

-- Temporarily raise threshold (admin port 6032)
UPDATE mysql_servers SET max_replication_lag = <new_value>
WHERE hostgroup_id = <reader_hostgroup>;
LOAD MYSQL SERVERS TO RUNTIME;
-- Run SAVE MYSQL SERVERS TO DISK only if you want this to persist across restarts.

Large transaction or DDL serializing replay

A single large transaction (bulk INSERT, data load) or DDL (ALTER TABLE) on the primary serializes replay on every replica at once. All replicas lag simultaneously, ProxySQL shuns all of them, and there is no non-lagging replica to absorb redirected traffic.

Identify the transaction. Check INFORMATION_SCHEMA.PROCESSLIST on the primary for long-running statements. Check binary logs for recent large events.
Batch the work. Break large inserts into smaller chunks. Replicas can apply events incrementally rather than blocking on one massive transaction.
Use online DDL. MySQL native online DDL, or tools like gh-ost or pt-online-schema-change, avoid single-transaction serialization across replicas.
Schedule during low-traffic windows. If the operation is unavoidable, run it when read traffic is low enough that writer fallback is tolerable.

Seconds_Behind_Master NULL causing premature shunning

When replication stops, Seconds_Behind_Master becomes NULL. ProxySQL substitutes mysql-monitor_slave_lag_when_null (default 60 seconds). If max_replication_lag is set below 60, the replica is shunned on any replication interruption, even momentary reconnects.

-- Check current value (admin port 6032)
SELECT variable_name, variable_value FROM global_variables
WHERE variable_name = 'mysql-monitor_slave_lag_when_null';

Options:

Set mysql-monitor_slave_lag_when_null higher than max_replication_lag. The replica stays in rotation during brief replication hiccups.
Set mysql-monitor_slave_lag_when_null to 0. Treats NULL as zero lag. The replica stays in rotation even when replication is broken. Use only if you can tolerate stale reads from a replica with stopped replication.

SET mysql-monitor_slave_lag_when_null = <value>;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Monitor credential issues causing false shunning

If the monitor user lacks REPLICATION CLIENT privilege on a backend, lag checks fail. The monitor reports an error, and ProxySQL may shun the backend based on the mysql-monitor_slave_lag_when_null substitution for the missing reading.

-- Check if lag checks are failing
SELECT Variable_Name, Variable_Value FROM stats_mysql_global
WHERE Variable_Name LIKE '%replication_lag_check%';

If MySQL_Monitor_replication_lag_check_ERR is rising, grant the monitor user the required privilege on each backend:

-- Run on each backend MySQL, not on ProxySQL
GRANT REPLICATION CLIENT ON *.* TO '<monitor_user>'@'<proxysql_host>';
-- FLUSH PRIVILEGES is not needed after GRANT in standard MySQL

Prevention

Alert on lag trends, not just thresholds. A replica sitting at 80% of max_replication_lag for hours is more actionable than one that just got shunned. Alert on gradual increases before they hit the threshold.
Watch ConnUsed on the last ONLINE replica. When N-1 replicas are shunned, the last one absorbs all read traffic. ConnUsed climbing sharply is the leading indicator that it is about to lag and shun too.
Size max_replication_lag realistically. If your replicas normally lag 3 to 10 seconds during peak, a threshold of 5 will produce frequent shunning. Set it to the maximum staleness your application can tolerate.
Make the mysql-monitor_slave_lag_when_null relationship explicit. The default of 60 seconds interacts dangerously with low max_replication_lag values.
Use parallel replication. Multi-threaded replay is the strongest defense against write-volume-induced lag. Without it, a single SQL thread on each replica is the replay bottleneck.
Alert on the cascade, not individual replica lag. The PAGE-worthy condition is “all reader-hostgroup backends SHUNNED.” Individual replica lag is normal and transient. The cascade where the reader hostgroup goes empty is the emergency.
Verify monitor credentials during provisioning. The monitor user needs REPLICATION CLIENT on every backend. A missing privilege produces silent lag check failures and false shunning.

How Netdata helps

Per-second backend status tracking. Netdata collects stats_mysql_connection_pool at high frequency, catching the transition from “one replica SHUNNED” to “all replicas SHUNNED” before the writer gets overloaded.
Replication lag check correlation. Netdata surfaces MySQL_Monitor_replication_lag_check_OK and MySQL_Monitor_replication_lag_check_ERR alongside backend status changes, so you can distinguish genuine lag from monitor failures.
ConnUsed trend per backend. The cascade signature is ConnUsed spiking on the last standing replica. Per-second granularity makes this visible before that replica shuns.
Writer load anomaly detection. Anomaly detection on writer hostgroup metrics (ConnUsed, Queries, latency) flags the read-spillover pattern even without an explicit alert rule.
backend_lagging_during_query tracking. Correlating this counter with lag trends gives early warning of stale reads delivered to clients.