ProxySQL backend_lagging_during_query and backend_offline_during_query: in-flight query failures

Two counters in stats_mysql_global track queries that were in flight when the backend changed state underneath them. backend_lagging_during_query counts queries that may have returned stale data. backend_offline_during_query counts queries that failed. Both are cumulative since the last ProxySQL restart; a nonzero value after steady-state means something disrupted a running query.

backend_offline_during_query sustained increase is a TICKET by the playbook severity model. Each increment means a query was routed to a backend that then disappeared mid-execution: crash, network partition, SHUNNED transition, or operator-triggered OFFLINE_HARD. The diagnostic work is correlating counter increments with the specific backend transitions that caused them.

What this means

Both counters are monotonically increasing cumulative counters that reset only on ProxySQL restart.

• backend_lagging_during_query: ProxySQL detected replication lag exceeding max_replication_lag after the query was already routed. The query may have completed and returned data, but that data is potentially stale. This is a data-quality signal.
• backend_offline_during_query: The backend went offline during execution and the query failed. Depending on mysql-query_retries_on_failure (default 1) and whether multiplexing is active for the session, the client either got a transparent retry or received ERROR 2013 (Lost connection to MySQL server during query) directly. This is an availability signal.

The distinction: a query hitting a lagging backend may still return a result. A query hitting an offline backend does not complete on that backend. Both counters fire because the backend state changed in the window between routing and completion.

ProxySQL writes log entries for these events. When it detects an offline server during a query:

[ERROR] Detected an offline server during query: <host>, <port>

If retry is possible:

[WARNING] Retrying query.

Whether retry succeeds depends on mysql-query_retries_on_failure and whether the session has multiplexing enabled. When multiplexing is disabled for a session (open transactions, SET variables, temporary tables, LOCK TABLES, user-defined variables, or prepared statements in some configurations), ProxySQL cannot transparently retry the query on another backend. The client receives ERROR 2013.

flowchart TD
    A["Query routed to ONLINE backend"] --> B["Query executing"]
    B --> C{"Backend status changes?"}
    C -->|"Replication lag detected"| D["backend_lagging_during_query++"]
    C -->|"Backend offline or shunned"| E["backend_offline_during_query++"]
    C -->|"No change"| F["Normal completion"]
    D --> G["Client gets possibly stale data"]
    E --> H{"Retry possible?"}
    H -->|"Multiplexing enabled"| I["Retried on healthy backend"]
    H -->|"Multiplexing disabled"| J["Client gets ERROR 2013"]

Common causes

Quick checks

# Check both counters (cumulative since restart)
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 ('backend_lagging_during_query','backend_offline_during_query');"

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

# Check monitor check results (leading indicators for status transitions)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global \
      WHERE Variable_Name LIKE 'MySQL_Monitor_%';"

# Check max_replication_lag thresholds (0 means lag checking is disabled)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup_id, hostname, port, status, max_replication_lag \
      FROM runtime_mysql_servers WHERE max_replication_lag > 0;"

# Check retry and multiplexing configuration
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-query_retries_on_failure', \
      'mysql-connect_retries_on_failure','mysql-multiplexing');"

# Grep ProxySQL log for offline/lagging detection events (path may differ by install)
grep -E "Detected an (offline|lagging) server during query" \
  /var/lib/proxysql/proxysql.log | tail -50

# Check multiplexing health (high locked ratio means retries will fail)
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 ('Client_Connections_connected', \
      'Client_Connections_hostgroup_locked');"

How to diagnose it

1. Confirm the counters are actively rising. Take two readings 60 seconds apart. A nonzero value from hours ago is different from a value increasing right now.

2. Identify non-ONLINE backends. Query stats_mysql_connection_pool for status per backend. SHUNNED, OFFLINE_SOFT, or OFFLINE_HARD are candidates.

3. Determine which monitor check is failing. Check MySQL_Monitor_ping_check_ERR, MySQL_Monitor_connect_check_ERR, and MySQL_Monitor_replication_lag_check_ERR in stats_mysql_global. The failing check type tells you what triggered the transition.

4. If backend_lagging_during_query is rising, check max_replication_lag in runtime_mysql_servers against actual replica lag. If set too low relative to normal variation, ProxySQL repeatedly shuns and restores the backend, incrementing the counter each cycle.

5. If backend_offline_during_query is rising, grep the ProxySQL log for “Detected an offline server during query” to find the affected host and timestamp. Correlate those timestamps with backend status transitions in the monitor logs (monitor.mysql_server_ping_log, monitor.mysql_server_connect_log).

6. Check for client-visible errors. Query stats_mysql_errors for error code 2013. If present and increasing, retries are failing, pointing to disabled multiplexing on affected sessions.

7. Check multiplexing health. Compute Client_Connections_hostgroup_locked / Client_Connections_connected. Above 50% means many sessions have multiplexing disabled, so mysql-query_retries_on_failure cannot help them.

8. Look for deployment or maintenance correlation. Spikes aligned with deployments, replica maintenance, or failover events may be expected side effects rather than independent failures.

Metrics and signals to monitor

Fixes

Replication lag causing stale reads

If backend_lagging_during_query is rising because replicas exceed max_replication_lag, determine whether the threshold or the actual lag is the problem.

• If replica lag is genuinely high, address the upstream cause: large transactions, DDL on the primary, insufficient replica I/O capacity.
• If max_replication_lag is too aggressive for your workload, raise it. Tradeoff: clients may read data further behind.
• If max_replication_lag is 0, lag checking is disabled for that backend and the counter should not increment. Verify configuration if you see unexpected behavior.

Backend crash or network failure

If backend_offline_during_query spikes because a backend crashed or became unreachable, ensure the hostgroup has redundant backends to absorb failover. A single-backend hostgroup produces client-visible failures on every status transition.

Verify the network path between ProxySQL and the backend. Intermittent packet loss can trigger enough monitor check failures to reach SHUNNED, disrupting in-flight queries.

Monitor-induced flapping

If backends cycle between ONLINE and SHUNNED rapidly (more than 3 transitions in 10 minutes), monitor thresholds are too aggressive for your network.

• Increase mysql-shun_on_failures (default 5) to require more consecutive failures before shunning.
• Increase mysql-monitor_ping_interval to reduce check frequency.
• Increase mysql-shun_recovery_time_sec (default 10s) if the backend recovers and re-enters rotation before stabilizing.

See backend flapping between ONLINE and SHUNNED for detailed flapping diagnosis.

Planned maintenance using the wrong offline mode

The most preventable cause of backend_offline_during_query spikes is using OFFLINE_HARD when OFFLINE_SOFT would suffice.

OFFLINE_SOFT drains gracefully: existing connections finish their queries, no new connections are sent. OFFLINE_HARD kills existing connections immediately, incrementing the counter for every query in flight.

For planned maintenance, draining, or rolling restarts, use OFFLINE_SOFT:

UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostgroup_id=<hg> AND hostname='<host>';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

See OFFLINE_SOFT vs OFFLINE_HARD vs SHUNNED for the full status reference.

Retry not working due to disabled multiplexing

When backend_offline_during_query is rising and clients receive ERROR 2013, the session has multiplexing disabled. ProxySQL cannot retry on another backend because the backend connection is pinned to the client session.

Common causes of disabled multiplexing:

• Open transactions (Active_Transactions > 0)
• SET session variables ProxySQL tracks as state changes
• Temporary tables, LOCK TABLES, GET_LOCK(), user-defined variables
• Prepared statements (depending on configuration)
• autocommit=0 when mysql-autocommit_false_is_transaction=true

Verify mysql-query_retries_on_failure is greater than 0, then investigate why multiplexing is disabled for affected sessions. Check stats_mysql_processlist for session state details.

Prevention

• Use OFFLINE_SOFT for planned maintenance. Never OFFLINE_HARD unless permanently removing a backend.
• Set max_replication_lag to accommodate normal replica lag variation. Too low causes unnecessary shunning and counter increments.
• Tune monitor sensitivity to match your network. Aggressive check intervals on jittery networks cause flapping, which directly produces these counter increments.
• Ensure hostgroups have at least two backends. Single-backend hostgroups have no failover target.
• Monitor multiplexing health. High hostgroup_locked ratio means retries silently fail.
• Alert on rate, not absolute value. A cumulative counter that never moves is fine. One that spikes during every failover signals clients are being disrupted.

How Netdata helps

• Per-second collection of stats_mysql_global counters pinpoints the exact moment a counter increments, not a polled snapshot minutes later.
• Correlate counter spikes with backend status transitions in stats_mysql_connection_pool to tie each increment to a specific SHUNNED or OFFLINE_HARD event.
• MySQL_Monitor_ping_check_ERR and MySQL_Monitor_replication_lag_check_ERR provide leading indicators that explain why a backend transitioned.
• The Client_Connections_hostgroup_locked ratio tells you whether retry is succeeding or clients are receiving raw errors.

Related guides

• 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 SHUNNED: why a healthy backend gets pulled out of rotation
• ProxySQL ConnERR climbing: backend connection errors and how to localise them
• ProxySQL backend connection pool exhausted: queries queuing for a free connection
• ProxySQL Client_Connections_aborted rising: clients rejected or crashing on connect