ProxySQL backend flapping between ONLINE and SHUNNED: monitor-induced oscillation

A ProxySQL backend alternating between ONLINE and SHUNNED more than 3 times in 10 minutes is a distinct failure pattern from a persistent SHUNNED state. The backend MySQL is healthy when checked directly, but ProxySQL’s monitor module produces false-negative health decisions on a jittery network path or under transient backend pressure. Each oscillation kills active connections, causing ConnERR spikes, latency spikes on surviving backends, and dips in Questions as traffic is disrupted and redistributed.

The root cause is a mismatch between the monitor’s sensitivity and real-world backend responsiveness. The monitor runs periodic health checks on each backend; if consecutive checks fail, it instructs the hostgroup manager to shun the server. When the check interval is too aggressive or the failure threshold too low for the network conditions, transient latency spikes lasting milliseconds can cascade into SHUNNED transitions lasting seconds.

What this means

ProxySQL has two independent paths that can trigger SHUNNED status.

Monitor path: The monitor module runs ping and connect checks against each backend at configurable intervals. After mysql-monitor_ping_max_failures (default 3) consecutive ping failures, the monitor signals the MySQL Hostgroups Manager to shun the backend. With default mysql-monitor_ping_interval of 8000ms and mysql-monitor_ping_max_failures of 3, detection requires 3 consecutive failed checks spread across approximately 24 seconds.

Connection error path: During normal data-plane operation, if ProxySQL encounters more than mysql-shun_on_failures (default 5) connection errors to the same backend, the backend is automatically shunned for mysql-shun_recovery_time_sec (default 10 seconds). The effective shun threshold is min(mysql-shun_on_failures, mysql-connect_retries_on_failure) (default min(5, 10) = 5). Raising mysql-shun_on_failures alone has no effect once it exceeds mysql-connect_retries_on_failure.

Recovery from SHUNNED is not triggered by the monitor alone. A shunned server is brought back ONLINE only after mysql-shun_recovery_time_sec has elapsed and a connection attempt succeeds, which requires active traffic to the hostgroup. In a flapping scenario, traffic is flowing, so recovery typically happens within the recovery window. The cycle repeats when the next transient failure occurs.

flowchart TD
    A["Backend ONLINE"] -->|"monitor ping fails
N times in a row"| B["Backend SHUNNED
connections killed"]
    B -->|"recovery timer expires
+ traffic triggers re-check"| C["Backend re-checked"]
    C -->|"ping succeeds"| A
    C -->|"ping fails again"| B

Common causes

Quick checks

# Credentials on the command line are visible in process list and shell history.
# Use a my.cnf credentials file or MYSQL_PWD in production.

# Check current backend status and connection error counts
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, ConnERR, Latency_us FROM stats_mysql_connection_pool;"

# Check monitor ping log for alternating success/failure pattern
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 50;"

# Check monitor check success/failure counters
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name IN ('MySQL_Monitor_ping_check_OK','MySQL_Monitor_ping_check_ERR','MySQL_Monitor_connect_check_OK','MySQL_Monitor_connect_check_ERR');"

# Check current monitor and shun variable values
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables WHERE variable_name IN ('mysql-monitor_ping_interval','mysql-monitor_ping_max_failures','mysql-monitor_ping_timeout','mysql-shun_on_failures','mysql-shun_recovery_time_sec','mysql-connect_retries_on_failure') ORDER BY variable_name;"

# Verify backend health directly (bypass ProxySQL)
mysql -u <backend_user> -p<backend_password> -h <backend_host> -P <backend_port> \
  -e "SELECT @@hostname, @@read_only; SHOW PROCESSLIST;"

# Check network path between ProxySQL and backend (~100 seconds for 100 cycles)
mtr -n -c 100 <backend_host>

How to diagnose it

1. Confirm the flapping pattern. Query stats_mysql_connection_pool every 30-60 seconds over a 10-minute window and record status changes. More than 3 transitions between ONLINE and SHUNNED in 10 minutes confirms the pattern. A single SHUNNED event that stays is a different problem.

2. Examine the monitor ping log. Look at monitor.mysql_server_ping_log for the affected backend. In a monitor-induced flapping scenario, you will see alternating rows where ping_success_time_us is non-zero (success) and zero (failure), with ping_error containing the error string on failure rows. The error is typically a timeout or connection reset, not an authentication or permission error.

3. Check the monitor connect log. Query monitor.mysql_server_connect_log for the same period. Connect failures alongside ping failures indicate a network path problem. Ping failures alone suggest the backend is reachable but not responding to the ping query in time.

4. Verify the backend is healthy directly. Connect to the backend MySQL bypassing ProxySQL. Run SHOW PROCESSLIST and check for long-running queries, lock waits, or thread pool exhaustion. Check backend CPU, I/O wait, and connection count. If the backend is genuinely healthy, the problem is in the monitor’s sensitivity or the network path.

5. Correlate with timing. Does the flapping happen at regular intervals? If the period roughly matches shun_recovery_time_sec plus the monitor check interval, it is monitor-induced. Does it happen during a specific time window such as a backup, batch job, or replication lag spike? If so, the backend is genuinely too busy to respond to health checks during that window.

6. Check both shun-related variables. Query mysql-shun_on_failures and mysql-connect_retries_on_failure from global_variables. If connect_retries_on_failure is lower than shun_on_failures, the effective shun threshold is connect_retries_on_failure, not shun_on_failures. Raising only shun_on_failures will not help.

7. Check network quality. Run mtr between the ProxySQL host and the backend host. Even small amounts of packet loss (0.1%) on a cloud network can cause intermittent ping timeouts at aggressive intervals. Check ProxySQL host network interface error counters with ip -s link or ethtool -S.

Metrics and signals to monitor

Fixes

Raise monitor ping interval

Increasing mysql-monitor_ping_interval reduces how frequently the monitor probes each backend, giving transient issues more time to resolve before the next check. The default is 8000ms. On jittery cloud networks, 10000 to 15000ms is often more appropriate.

-- Connect to admin interface (port 6032) first
SET mysql-monitor_ping_interval = 12000;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Tradeoff: Slower detection of genuinely failed backends. With mysql-monitor_ping_max_failures at 3 and interval at 12000ms, detection requires 3 failed checks spread over roughly 36 seconds instead of 24 seconds. In environments with redundant backends, this is usually an acceptable tradeoff.

Raise shun_on_failures and connect_retries_on_failure

To raise the effective shun threshold, you must raise both variables. The effective threshold is min(mysql-shun_on_failures, mysql-connect_retries_on_failure).

SET mysql-shun_on_failures = 10;
SET mysql-connect_retries_on_failure = 20;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Tradeoff: Slower shun response means the proxy sends more queries to a genuinely failing backend before removing it. In environments with redundant backends and fast failover, this favors stability over speed of removal.

Increase shun recovery time

If the flapping is causing rapid oscillation, increasing mysql-shun_recovery_time_sec gives the backend a longer cooldown period before re-checking.

SET mysql-shun_recovery_time_sec = 30;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Tradeoff: Longer time before a recovered backend rejoins the pool. Since recovery requires active traffic to trigger the re-check, the practical impact depends on your traffic pattern.

Address network jitter

If mtr shows packet loss or high latency variance on the path between ProxySQL and the backend:

• Check for network ACL or security group rate limiting.
• Verify MTU settings are consistent across the path.
• Consider whether the backend is in a different availability zone or region with inherently higher latency variance.
• On cloud platforms, check for noisy-neighbor effects on the network fabric.

Address backend resource pressure

If the flapping correlates with backup windows, large queries, or DDL operations:

• Schedule health-critical workloads outside backup windows.
• Check if the backend MySQL thread pool is exhausted during the flap window. The monitor’s ping query competes for a backend thread.
• Verify the backend’s max_connections can accommodate both application traffic and the monitor’s probes during peak load.

Prevention

• Baseline your network. Run mtr between ProxySQL and each backend during normal operation. Know your baseline packet loss and latency variance before you need to diagnose flapping.
• Set monitor intervals appropriate for your network. The default 8000ms is aggressive for cross-AZ or cross-region deployments. Profile how long your backends take to respond to health checks under load.
• Track MySQL_Monitor_ping_check_ERR rate as a leading indicator. Rising error rate precedes SHUNNED transitions.
• Verify config layer consistency after changes. Confirm variables are loaded to RUNTIME and saved to DISK. A restart with stale DISK config silently reverts your tuning.
• Alert on status transitions, not individual events. Alert on more than 3 ONLINE to SHUNNED transitions per backend in 10 minutes. Individual SHUNNED events cause alert fatigue; SHUNNED is ProxySQL’s protection mechanism and is often transient and self-correcting.
• Test tuning under realistic load. Simulate backend pressure (large queries, connection spikes) before deploying to production and verify monitor thresholds behave as expected.

How Netdata helps

• Per-second backend status. Netdata collects stats_mysql_connection_pool at high frequency, catching ONLINE to SHUNNED transitions that last only seconds.
• Monitor error rate correlation. Netdata surfaces MySQL_Monitor_ping_check_OK and MySQL_Monitor_ping_check_ERR alongside backend status, so you can see the check failures that precede each SHUNNED transition without manually correlating across tables.
• ConnERR rate tracking. Per-backend ConnERR deltas show the connection error spikes that accompany shun events, distinguishing monitor-induced flapping from genuine backend failures.
• Questions rate dips. Per-second Questions collection reveals the periodic throughput dips that are the user-visible symptom of flapping.
• Latency divergence detection. Per-backend Latency_us collected over time shows whether one backend’s ping latency is diverging from peers before it gets shunned.
• ML anomaly detection. Anomaly detection can flag the irregular pattern of monitor check failures that precede flapping, even when no static threshold has been crossed.

Related guides

• ProxySQL backend SHUNNED: why a healthy backend gets pulled out of rotation
• How ProxySQL actually works in production: a mental model for operators
• ProxySQL monitoring checklist: the signals every production proxy needs
• ProxySQL monitoring maturity model: from survival to expert