$ guides / proxysql / proxysql-set-session-variables-disable-multiplexing ▌

Operations Guides

ProxySQL SET statements disabling multiplexing: session state that pins connections

ProxySQL multiplexes N client sessions across M backend connections, where M is smaller than N. When an application issues a SET statement that changes session state, ProxySQL can no longer safely reuse that backend connection for other clients. The connection pins to that client session until the client disconnects.

This is correct behavior. A backend connection carrying a modified session variable (such as sql_mode or time_zone) would produce different query results if handed to another client expecting the default. ProxySQL detects this state and pins the connection to protect correctness.

The operational problem is that most teams never measure whether multiplexing is actually working. ORMs, connection libraries, and GUI tools routinely send SET commands that disable multiplexing silently. The proxy degrades to a near 1:1 client-to-backend ratio with no error, no log entry, and no alert. The capacity model that assumed 10:1 multiplexing becomes fiction.

What it is and why it matters

Multiplexing lets ProxySQL borrow a backend connection from the pool, route a query, return the result, and immediately return the connection for reuse by another client. As long as no session-specific state exists on the backend connection, this reuse is safe.

Session state breaks this model. When ProxySQL detects that a backend connection carries state that would affect query results for a different client, it disables multiplexing for that frontend session. The backend connection stays attached to that client. This is visible in stats_mysql_global as Client_Connections_hostgroup_locked increasing.

The critical behavioral detail: once multiplexing is disabled for a connection, it does not re-enable mid-session for most conditions. It stays pinned until the client disconnects. Only a few states are temporary: active transactions, SQL_LOG_BIN=0, and the auto-increment delay window. Everything else pins permanently.

This matters because backend pool sizing depends on the multiplexing ratio. With 500 connected clients and a 10:1 ratio, you need approximately 50 backend connections. If multiplexing collapses to 1:1, you need 500. A backend MySQL configured with max_connections=200 will reject connections, and queries start failing.

How it works

ProxySQL parses every query through its query processor. For SET statements and other session-modifying commands, it evaluates whether the change creates state that would be unsafe to carry over to a different client. If so, it sets an internal flag on the session that disables multiplexing.

flowchart TD
    A["Client sends query"] --> B{"SET or session state?"}
    B -- "No" --> C["Borrow backend conn
Route query
Return to pool"]
    B -- "Yes" --> D{"Recognized by
SET parser?"}
    D -- "Yes" --> E["Disable multiplexing
Pin backend conn"]
    D -- "No, unparseable" --> F["mysql-set_query_lock_on_hostgroup
Default: true"]
    F -- "true" --> E
    F -- "false" --> G["Disable multiplexing only
Routing still active"]
    E --> H["Conn stays pinned
until client disconnects"]
    G --> H
    C --> I["Multiplexing preserved"]

Statements and conditions that disable multiplexing

These SET statements are explicitly recognized by ProxySQL’s parser and disable multiplexing when executed:

SET SQL_SAFE_UPDATES
SET FOREIGN_KEY_CHECKS
SET UNIQUE_CHECKS
SET AUTO_INCREMENT_INCREMENT
SET AUTO_INCREMENT_OFFSET
SET GROUP_CONCAT_MAX_LEN
SET SQL_LOG_BIN=0
Any query containing user-defined variables (using @ syntax)

Beyond SET statements, multiplexing is also disabled by:

LOCK TABLES
GET_LOCK()
SQL_CALC_FOUND_ROWS
CREATE TEMPORARY TABLE
PREPARE via text protocol
Active transactions

Two hardcoded exceptions exist: SELECT @@tx_isolation and SELECT @@version do NOT disable multiplexing, even though they reference session variables. ProxySQL recognizes these as read-only introspection queries.

Temporary versus permanent pinning

ProxySQL distinguishes between permanent and temporary states:

Permanent (disabled until client disconnects):

User-defined variables (@var)
GET_LOCK()
SQL_CALC_FOUND_ROWS
Temporary tables
PREPARE (text protocol)

Temporary (disabled for a bounded window):

Active transactions (re-enabled after COMMIT or ROLLBACK)
SQL_LOG_BIN=0 (re-enabled after SET SQL_LOG_BIN=1)
Auto-increment delay window

The auto-increment delay

mysql-auto_increment_delay_multiplex defaults to 5. After any INSERT or UPDATE that touches an auto-increment column, ProxySQL disables multiplexing for the next 5 queries on that connection. This is a global variable affecting all hostgroups. The rationale is that the application may call LAST_INSERT_ID() immediately after the insert, and ProxySQL must ensure that call reaches the same backend.

This default is a silent multiplexing killer for write-heavy workloads. An application doing one insert followed by four reads will have multiplexing disabled for every cycle, regardless of whether it ever calls LAST_INSERT_ID(). Many teams discover this only after investigating why their backend connection count is unexpectedly high.

mysql-connection_delay_multiplex_ms (default 0) provides a time-based variant. When set to a non-zero value, multiplexing is disabled for that many milliseconds after any query completes.

Unparseable SET statements

ProxySQL’s SET parser handles a specific set of statements. When it encounters a SET it cannot parse (for example, SET lc_messages = 'en_US' sent by phpMyAdmin, or SET PROFILING = 1 sent by Navicat), behavior depends on mysql-set_query_lock_on_hostgroup.

mysql-set_query_lock_on_hostgroup defaults to true (1) since ProxySQL 2.0.6. When enabled, an unparseable SET statement causes both multiplexing AND query routing to be disabled. The client is locked to a single backend connection for the rest of the session.

Setting mysql-set_query_lock_on_hostgroup=0 prevents the hostgroup lock but does NOT re-enable multiplexing. The connection stays pinned to a backend but can be routed to different hostgroups. This can cause problems if the application uses temporary tables, since the temp table lives on one backend but subsequent queries might route to another.

Where it shows up in production

ORM connection initialization

Most ORMs (Hibernate, Django ORM, SQLAlchemy, ActiveRecord) send one or more SET statements when establishing a connection. Common patterns include SET NAMES, SET time_zone, SET sql_mode, and SET SESSION transaction_isolation. Whether each one pins depends on ProxySQL version and exact syntax. The result can be a backend connection pinned from the first query.

The multiplexing collapse cascade: a deployment or ORM upgrade adds session variable initialization to every connection. ProxySQL detects the state change and disables multiplexing for each affected session. Backend connections pin 1:1. With 500 clients, ProxySQL needs 500 backend connections instead of 50. Backend MySQL’s max_connections is hit. New queries fail with error 1040. ProxySQL shuns the backend. Remaining backends absorb the load, also hit their limits.

Multi-statement SET TRANSACTION regression

In ProxySQL 2.6.0, the multi-statement pattern SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN causes ProxySQL to set lock_hostgroup=true and disable multiplexing permanently for the session. The connection is not returned to the pool even after COMMIT. This is tracked as GitHub issue #4896.

GUI and admin tools

phpMyAdmin sends SET lc_messages = 'en_US' and SET collation_connection = '...' on connection. Navicat sends SET PROFILING = 1. Neither is recognized by ProxySQL’s parser. With the default mysql-set_query_lock_on_hostgroup=true, these tools pin connections for their entire session.

Session tracking in ProxySQL 3.0.8

ProxySQL 3.0.8 introduced mysql-session_track_variables (default 0, disabled). When set to 1 (optional) or 2 (enforced), ProxySQL uses MySQL’s native session-state tracking protocol to detect variable changes that the SET parser cannot see. This catches SET commands issued inside stored procedures or with dynamic right-hand-side values. This complements the static SET parser rather than replacing it.

Detecting multiplexing disablement

The pinning ratio

The most direct signal is the pinning ratio: Client_Connections_hostgroup_locked / Client_Connections_connected. A healthy proxy should have most connections multiplexed. A pinning ratio above 50% sustained is serious degradation. A ratio approaching 1.0 means every client has a dedicated backend connection and multiplexing has fully collapsed.

-- Check current pinning ratio
SELECT
  (SELECT Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'Client_Connections_hostgroup_locked') AS locked,
  (SELECT Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'Client_Connections_connected') AS connected;

Per-session multiplexing state

stats_mysql_processlist includes an extended_info field (JSON) that shows why multiplexing was disabled for each session. Look for MultiplexDisabled: true and a status object with boolean flags for specific causes.

-- Find sessions with multiplexing disabled
SELECT user, db, hostgroup, time_ms, extended_info
FROM stats_mysql_processlist
WHERE extended_info != ''
ORDER BY time_ms DESC;

The multiplexing ratio

The broader multiplexing ratio is Client_Connections_connected / Server_Connections_connected. This tells you how effectively the proxy is pooling backend connections overall. Track this over time and correlate with application deployments.

Operational mitigations

Move session variables to server-side configuration

The most effective fix is to stop sending per-session SET commands for values that should be global. Character set, timezone, and sql_mode are typically the same for all sessions. Configure them on the MySQL server directly, or use ProxySQL’s mysql-init_connect to set them once when the backend connection is established, rather than having the application set them per session.

This moves the SET from the application layer (which triggers per-session pinning) to the proxy layer (which sets it on the backend connection at creation time, before any client uses it).

Query rules with multiplex override

mysql_query_rules has a multiplex column that accepts:

0 - disable multiplexing for matching queries
1 - enable multiplexing for matching queries
2 - do not disable multiplexing for this specific query, even if it contains user variables

A query rule with multiplex=2 and a match pattern for a query containing @ variables tells ProxySQL to keep multiplexing despite the variable reference. Use this only when you are certain the variable does not affect correctness for other clients sharing the same backend connection.

Tune mysql-auto_increment_delay_multiplex

If your write workload does not rely on LAST_INSERT_ID(), you can reduce mysql-auto_increment_delay_multiplex from the default of 5 to 1 or 0. This restores multiplexing faster after writes.

-- Check current value
SELECT variable_name, variable_value FROM global_variables
WHERE variable_name = 'mysql-auto_increment_delay_multiplex';

-- Reduce delay (verify application does not use LAST_INSERT_ID first)
SET mysql-auto_increment_delay_multiplex = 1;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Warning: reducing this value is safe only if your application never relies on LAST_INSERT_ID() returning the auto-increment value from the immediately preceding INSERT on the same connection. If LAST_INSERT_ID() is used, reducing the delay can route that call to a different backend and return a stale or zero value.

Enable session tracking (3.0.8+)

On ProxySQL 3.0.8 and later, enabling mysql-session_track_variables provides more complete detection of session state changes. This reduces false negatives where ProxySQL keeps multiplexing enabled for sessions that secretly changed state, and helps the parser catch SET statements inside stored procedures that it would otherwise miss.

Tradeoffs

The tension between multiplexing efficiency and session-state isolation is fundamental. ProxySQL must pin a connection when state exists because sharing it would produce incorrect results. The safe mitigations are narrow:

Eliminate unnecessary session state by moving global values to server defaults or mysql-init_connect.
Accept pinning for sessions that genuinely need state (transactions, temporary tables, user variables). Size your backend pool accordingly.
Track the pinning ratio. If it is 80% and your workload does not justify it, you have a configuration problem, not a capacity problem.

Signals to watch in production

Signal	Why it matters	Warning sign
Pinning ratio (`hostgroup_locked / connected`)	Direct measure of multiplexing degradation	Sustained above 0.5
Multiplexing ratio (`connected / server_connected`)	Overall pooling effectiveness	Trending toward 1:1
`ConnPool_get_conn_failure`	Queries unable to get backend connections	Any sustained increase
`Active_Transactions`	Connections pinned by open transactions	High relative to client count
`stats_mysql_processlist` extended_info	Per-session reason for pinning	Sessions with `MultiplexDisabled: true`
Backend `ConnUsed` vs `max_connections`	Pool saturation from pinned connections	Approaching limit

How Netdata helps

Correlates Client_Connections_hostgroup_locked with Client_Connections_connected to surface the pinning ratio as a trend, making gradual multiplexing collapse visible before backend pool exhaustion.
Tracks ConnPool_get_conn_failure as a leading indicator that pinned connections are consuming pool capacity.
Provides per-second granularity on backend connection metrics (ConnUsed, ConnFree, ConnOK, ConnERR), letting you pinpoint exactly when a deployment or ORM change started pinning connections.
Correlates multiplexing metrics with query digest changes and application deployment timing.