ProxySQL multiplexing collapse: when connection pooling silently drops to 1:1

You deployed ProxySQL for connection pooling. The capacity plan assumed a 10:1 multiplexing ratio: 1000 client connections served by 100 backend connections. Instead, the backend pool keeps filling up, ConnUsed tracks the client count almost linearly, and the MySQL backend is hitting max_connections even though traffic has not changed. ProxySQL is running, backends are ONLINE, queries are succeeding, but the proxy is providing zero pooling benefit. It has silently degraded into a 1:1 connection relay with overhead.

Basic checks miss this. “Can I connect to ProxySQL?” passes. “Are backends ONLINE?” passes. Questions rate is fine. The signals that reveal the collapse are the multiplexing ratio and the hostgroup-locked count, and most teams do not track either.

ProxySQL knows exactly why it disabled multiplexing for each session, and the per-session disable reason is queryable. This article covers how to detect the collapse, identify the specific cause, and restore the pooling ratio your capacity plan depends on.

What this means

ProxySQL multiplexes N frontend (client) connections onto M backend (MySQL) connections, where M should be much smaller than N. When a client sends a query, ProxySQL borrows a backend connection from the pool, routes the query, returns the result, then returns the connection to the pool for reuse by another client.

Multiplexing breaks when session state exists on the connection. ProxySQL tracks conditions that make it unsafe to share the backend connection: open transactions, SET variable changes, temporary tables, LOCK TABLES, user-defined variables, prepared statements (text protocol), or GET_LOCK(). When any of these is detected, ProxySQL pins a dedicated backend connection to that client session. The pin is visible as Client_Connections_hostgroup_locked incrementing.

When most or all sessions have multiplexing disabled, the ratio collapses. The multiplexing ratio (Client_Connections_connected / Server_Connections_connected) approaches 1.0. The pinning ratio (Client_Connections_hostgroup_locked / Client_Connections_connected) approaches 1.0. ConnUsed grows linearly with client count instead of sub-linearly. The backend pool fills, and eventually ConnERR starts climbing as the backend’s max_connections is exhausted.

flowchart TD
    A["App issues SET vars or transactions"] --> B["ProxySQL pins backend to session"]
    B --> C["hostgroup_locked climbs"]
    C --> D["Multiplexing ratio near 1:1"]
    D --> E["Backend pool saturates"]
    E --> F["ConnERR as max_connections hit"]
    F --> G["Queries queue or error 1040"]

Common causes

Quick checks

All queries run against the ProxySQL admin interface (default port 6032). These are read-only.

# Check the multiplexing ratio (connected clients / connected backends)
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','Server_Connections_connected');"

# Check the pinning ratio (hostgroup_locked / connected)
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');"

# Check active transactions (pinned by transaction state)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 -e \
  "SELECT Variable_Name, Variable_Value FROM stats_mysql_global
   WHERE Variable_Name = 'Active_Transactions';"

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

# Check pool get failures (direct evidence of pool starvation)
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 ('ConnPool_get_conn_success','ConnPool_get_conn_failure','ConnPool_get_conn_immediate');"

# Check per-session multiplexing disable reasons (ProxySQL 2.x)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 -e \
  "SELECT user, hostgroup, db, command, extended_info
   FROM stats_mysql_processlist
   WHERE extended_info IS NOT NULL AND extended_info != ''
   LIMIT 50;"

# Check relevant multiplexing variables
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-multiplexing','mysql-auto_increment_delay_multiplex',
   'mysql-keep_multiplexing_variables','mysql-autocommit_false_is_transaction');"

How to diagnose it

Step 1: Confirm the collapse.

Compute both ratios from stats_mysql_global. If Client_Connections_hostgroup_locked / Client_Connections_connected exceeds 0.5, multiplexing is seriously degraded. If it approaches 1.0, it has fully collapsed. Compare Client_Connections_connected against Server_Connections_connected: if they are roughly equal, every client has a dedicated backend connection.

Step 2: Identify the disable reason per session.

Query stats_mysql_processlist for sessions with non-empty extended_info. This JSON column (available in ProxySQL 2.x) exposes per-session multiplexing state. Key fields:

• MultiplexDisabled: whether multiplexing is disabled for this session
• status.user_variable: a user-defined variable (@var) was set
• status.temporary_table: a temporary table exists
• status.get_lock: GET_LOCK() was called
• status.lock_tables: LOCK TABLES was issued
• autocommit: whether autocommit is on or off
• server_status: odd values indicate an open transaction

Look for patterns across sessions. If every session from a specific user or application has the same disable reason, the cause is systemic (usually ORM behavior), not random.

Step 3: Correlate with query patterns.

Cross-reference stats_mysql_query_digest for queries that trigger the disable. Look for SET statements (SET NAMES, SET sql_mode, SET time_zone, SET @var), SELECT @@variable queries (common from Java connectors), and INSERT/UPDATE statements on tables with auto-increment columns. Group by username and schemaname to narrow down which application is responsible.

Step 4: Check the timeline.

If the collapse started after a specific deploy, the cause is likely an application change: a new ORM version, a connection pool library upgrade, or a new SET command added to connection initialization. Correlate the ratio trend against deployment timestamps. A sudden step-change in the pinning ratio is a strong signal of a deploy-triggered behavioral change.

Step 5: Check whether a SET statement locked the hostgroup.

In ProxySQL 2.0.6+, mysql-set_query_lock_on_hostgroup (default enabled) causes ProxySQL to lock a session to its current hostgroup and disable multiplexing when it encounters a SET statement it cannot parse. If you see sessions locked to a hostgroup with no obvious session variable, check for multi-statement commands or unusual SET syntax in the query digest.

Metrics and signals to monitor

Fixes

ORM session variable initialization

The most common cause. An ORM or connection library issues SET NAMES, SET sql_mode, SET time_zone, or similar on every new connection. ProxySQL detects the session variable change and disables multiplexing.

Move session variables to backend defaults. Set character set, SQL mode, and timezone in the MySQL server configuration so the application does not need per-session SET commands. This is the cleanest fix but requires coordination with the application team. Verify that all backends in a hostgroup share the same defaults, or queries may behave differently depending on which backend ProxySQL selects.

Use ProxySQL’s mysql-init_connect. ProxySQL can issue SET commands when establishing a backend connection. Configure mysql-init_connect with the needed statements. ProxySQL applies these once per backend connection, not per client session, preserving multiplexing.

Intercept SET commands via query rules. Create a query rule that matches SET NAMES, SET sql_mode, etc. and returns an OK message without forwarding to the backend. This prevents the session state change. Use with caution: the session’s effective state may differ from what the application expects.

@-symbol queries

Queries containing @@ (like SELECT @@session.auto_increment_increment) or @ (like SET @user_var) permanently disable multiplexing for the session. The disable is never reversed. Java Connector/J connection initialization queries are a frequent trigger because they run on every new connection.

Set multiplex=2 on a query rule matching these queries. The mysql_query_rules.multiplex column accepts three values: 0 disables multiplexing for matching queries, 1 enables it, and 2 prevents @-containing queries from disabling multiplexing. Add a rule early in the chain:

-- Allow multiplexing for @@variable queries (verify regex matches your workload)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex, apply)
VALUES (10, 1, '^SELECT @@', 2, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Also check mysql-keep_multiplexing_variables (default: tx_isolation,version). Variables listed here will not disable multiplexing when queried via SELECT @@var. Add variables your connector queries, but verify the application does not depend on per-session values that differ from backend defaults.

mysql-auto_increment_delay_multiplex on write-heavy workloads

After any INSERT or UPDATE that triggers an auto-increment, ProxySQL disables multiplexing for the next N queries on that connection (default: mysql-auto_increment_delay_multiplex = 5). This protects LAST_INSERT_ID() from returning a value from a different session. On a write-heavy workload with a 3:2 read/write ratio, two out of every five queries trigger the delay, making effective multiplexing nearly impossible.

Reduce the delay. If the application does not rely on LAST_INSERT_ID() after subsequent queries, lower the value:

SET mysql-auto_increment_delay_multiplex = 1;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

In ProxySQL 2.4.0+, mysql-auto_increment_delay_multiplex_timeout_ms (default 10000) auto-invalidates the delay after a timeout, providing a safety net if the query-count delay is long. The variable and default first appear in the 2.4.0 source tree.

Transaction-heavy workloads

Long-running transactions pin backend connections for their entire duration. If the application holds transactions open across multiple round trips (for example, HTTP request-scoped transactions), each concurrent in-flight request consumes a dedicated backend connection. This is a workload characteristic, not a misconfiguration. The capacity plan must account for it rather than assuming multiplexing will reduce the connection count. Options include shortening transaction scope in the application, increasing backend max_connections, or adding backend servers to distribute pinned connections.

Emergency: backend pool exhausted

If the backend pool is already exhausted and queries are failing with error 1040, error 1040 comes from the MySQL backend rejecting connections, not from ProxySQL. You may need to raise limits on both sides:

-- On the MySQL backend: increase the server's own max_connections
-- WARNING: each connection consumes memory. Verify the backend has headroom.
SET GLOBAL max_connections = <higher_value>;

-- On ProxySQL: increase the per-backend cap if it is the bottleneck
UPDATE mysql_servers SET max_connections = <higher_value>
  WHERE hostname = '<backend_host>' AND port = <port>;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Increasing ProxySQL’s per-server limit only helps if ProxySQL is the bottleneck. If the backend MySQL’s own max_connections is exhausted, raise it on the MySQL side. SET GLOBAL max_connections does not persist across MySQL restarts; update the configuration file as well. Neither fix addresses the root cause of the multiplexing collapse.

Prevention

• Monitor the pinning ratio continuously. Track Client_Connections_hostgroup_locked / Client_Connections_connected as a first-class metric. Alert when it exceeds 0.5 sustained. This is the only signal that directly measures multiplexing health.
• Test multiplexing impact after application deploys. ORM upgrades, connection pool library changes, and new SET commands are the most common triggers. After any deploy that touches database connection handling, verify the pinning ratio has not changed.
• Review query rules for multiplex settings. The mysql_query_rules.multiplex column gives per-rule control. Use it to explicitly enable multiplexing for queries where ProxySQL conservatively disables it.
• Baseline the multiplexing ratio per workload. Different applications have different natural ratios. A transaction-heavy service may legitimately run at 3:1, while a read-heavy API should achieve 20:1 or better. Alert on change from baseline, not absolute thresholds.
• Remember that stats reset on restart. stats_mysql_global counters reset when ProxySQL restarts. External collection must persist data to detect slow degradation trends over time.

How Netdata helps

• Per-second collection of connection metrics. Netdata collects Client_Connections_connected, Server_Connections_connected, Client_Connections_hostgroup_locked, and Active_Transactions every second, giving immediate visibility into ratio changes.
• Correlation across the failure cascade. When the pinning ratio rises, Netdata lets you correlate it simultaneously with ConnUsed, ConnFree, ConnPool_get_conn_failure, and Server_Connections_delayed to confirm downstream impact on the backend pool.
• Anomaly detection on ratio trends. A slow degradation from 10:1 to 3:1 over weeks is easy to miss with static thresholds. Anomaly detection flags the trend shift before the pool saturates.
• Deploy-time correlation. If the collapse follows an application deploy, per-second granularity lets you pinpoint the exact moment the ratio changed and correlate it with the deployment window.

Related guides

• ProxySQL backend connection pool exhausted: queries queuing for a free connection
• 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
• How ProxySQL actually works in production: a mental model for operators
• ProxySQL error 9001 Max connect timeout reached while reaching hostgroup
• ProxySQL monitor check failures: connect, ping, read-only, and replication-lag probes failing
• ProxySQL MySQL_Monitor_Workers is zero: health checks stopped and status is stale
• ProxySQL monitoring checklist: the signals every production proxy needs
• ProxySQL monitoring maturity model: from survival to expert
• ProxySQL zero ONLINE backends in a hostgroup: total outage for that traffic class
• ProxySQL error 1040 Too many connections: the backend MySQL rejecting the pool