ProxySQL hostgroup_locked connections: reading the multiplexing-health ratio

ProxySQL exists to multiplex: N client connections served by M backend connections, where M is ideally much less than N. When that ratio degrades, the proxy adds latency without providing pooling benefit. The single metric that captures this degradation is Client_Connections_hostgroup_locked divided by Client_Connections_connected.

ORMs and connection libraries can silently disable multiplexing by emitting SET commands or opening transactions on every connection. The proxy runs at near 1:1 for months, and the capacity plan that assumed 10:1 multiplexing is fiction.

This article covers how to read the ratio, what causes hostgroup locking, and how to distinguish transient pinning from a systemic pattern that needs intervention.

What the ratio measures

Divide client connections locked to a hostgroup by total connected clients. Both values come from stats_mysql_global:

pinning ratio = Client_Connections_hostgroup_locked / Client_Connections_connected

A connection becomes hostgroup-locked when ProxySQL detects session state that makes it unsafe to share the backend connection with other clients. Once locked, that client holds a dedicated backend connection until the state clears or the client disconnects.

The ratio tells you what fraction of your client connections are consuming dedicated backend connections. At 0.0, every client is fully multiplexed. At 1.0, every client has its own backend connection. The intermediate values are where the operational story lives:

The 50% threshold is not a hard line. A workload dominated by long transactions will naturally sit higher. The signal that matters is change from your established baseline, especially after a deploy.

How hostgroup locking works

ProxySQL’s multiplexing engine borrows a backend connection from the pool for each query, routes it, returns the result, then returns the connection to the pool for reuse by another client. This works when the client session carries no state that would leak across connections.

When session state appears, ProxySQL pins the backend connection to that client. The connection is removed from the reusable pool and dedicated to that session. This is hostgroup locking.

flowchart TD
    A["Client query arrives"] --> B{"Session state
present?"}
    B -->|No| C["Borrow backend conn
from pool"]
    C --> D["Execute and
return to pool"]
    D --> A
    B -->|Yes| E["Pin backend conn
to this session"]
    E --> F["hostgroup_locked
count rises"]
    F --> G{"Reversible?"}
    G -->|"Transaction / SET"| H["Unpin after
COMMIT or reset"]
    H --> A
    G -->|"Temp table / GET_LOCK
user variable"| I["Stays pinned until
client disconnects"]

The critical distinction is between transient and permanent pinning. Transactions and some SET commands produce state that clears when the transaction commits or the variable is reset. When that happens, ProxySQL can return the connection to the pool. Other state types disable multiplexing for the entire session lifetime.

Permanent pin (until client disconnects):

• Temporary tables (CREATE TEMPORARY TABLE)
• GET_LOCK() (named locks)
• LOCK TABLES
• User-defined variables (in some configurations)

Transient pin (cleared by COMMIT or state reset):

• Open transactions (BEGIN, START TRANSACTION)
• Some session variable changes

There is also a configuration-induced category. The mysql-set_query_lock_on_hostgroup variable (default 1 since ProxySQL v2.0.6) causes any SET statement that ProxySQL cannot parse to disable both multiplexing and query routing, locking the client to a single backend connection. This is safer than the legacy behavior (value 0, which did not disable routing on unparseable SET) but causes more hostgroup locking. Teams that upgraded from pre-2.0.6 may see a sudden increase in locked connections.

What triggers hostgroup locking

The conditions that disable multiplexing fall into categories based on whether the session can recover:

The mysql-keep_multiplexing_variables global variable (default: tx_isolation,version) lists session variables that are safe for multiplexing. When ProxySQL encounters a SET command for a variable in this list, it tracks the value per-session without disabling multiplexing. Variables not on the list trigger a lock.

ProxySQL v3.0.1 introduced improved parameter and variable tracking (PR #4799) that reduces unnecessary multiplex disabling by better tracking which session variables are safe to multiplex. The improvement changes when locking occurs; Client_Connections_hostgroup_locked remains the count of sessions locked to a hostgroup.

Reading the ratio in production

Query both counters from the admin interface:

-- Check the current pinning ratio
SELECT
  Variable_Name,
  Variable_Value
FROM stats_mysql_global
WHERE Variable_Name IN (
  'Client_Connections_hostgroup_locked',
  'Client_Connections_connected'
);

Compute the ratio manually: hostgroup_locked / connected. The ratio is a snapshot, not a rate. Track it as a time series. A single reading tells you the current state; a trend tells you whether things are getting worse.

Correlate with the multiplexing ratio (frontend to backend connection count) for the full picture:

-- Multiplexing ratio: how many clients share each backend connection
SELECT
  (SELECT CAST(Variable_Value AS UNSIGNED)
   FROM stats_mysql_global
   WHERE Variable_Name = 'Client_Connections_connected') AS clients,
  (SELECT CAST(Variable_Value AS UNSIGNED)
   FROM stats_mysql_global
   WHERE Variable_Name = 'Server_Connections_connected') AS backend_conns;

A healthy proxy has clients far exceeding backend connections. If they are nearly equal, multiplexing has collapsed.

To find which sessions are locked and why, inspect the process list. Column names vary by ProxySQL version; verify the schema first with SHOW COLUMNS FROM stats_mysql_processlist:

-- Find locked sessions and their pin reasons
SELECT ThreadID, UserName, HostGroup, status, Time_ms,
       extended_info
FROM stats_mysql_processlist
WHERE extended_info IS NOT NULL
  AND extended_info != ''
ORDER BY Time_ms DESC;

The extended_info column is present in the stats_mysql_processlist schema in ProxySQL 2.0.6 and the later 2.x releases checked here. For an older or downstream build, verify it with SHOW CREATE TABLE stats.stats_mysql_processlist.

The extended_info column returns JSON with boolean fields indicating the disable reason. The documented fields include found_rows, get_lock, lock_tables, no_multiplex, temporary_table, and user_variable. A server_status value with bit 0 set (any odd value) indicates SERVER_STATUS_IN_TRANS, meaning the session is inside an open transaction. A value of 33 also sets bit 5 (SERVER_QUERY_NO_INDEX_USED), so it is not solely a transaction indicator.

Transient pinning vs systemic collapse

Not all hostgroup locking is a problem. A database with active transactions will always have some locked connections. The question is whether the pinning is proportional to actual transactional work or whether it indicates a pattern that breaks multiplexing for sessions that should be multiplexable.

Transient pinning is normal when:

• Active_Transactions in stats_mysql_global is proportional to hostgroup_locked
• The ratio rises during peak write hours and falls during read-heavy periods
• Individual sessions show lock reasons of user_variable or transaction status that clear after the operation completes

Systemic collapse looks like:

• The ratio stays above 50% regardless of workload phase
• Active_Transactions is low but hostgroup_locked is high, meaning sessions are locked but not in transactions
• The ratio jumped after a deploy and never recovered
• Most locked sessions show the same disable reason in extended_info
• ConnUsed in stats_mysql_connection_pool tracks Client_Connections_connected almost linearly

The most common systemic pattern is an ORM or connection library that emits SET commands on every new connection. Common offenders include SET NAMES, SET time_zone, SET sql_mode, and SET SESSION TRANSACTION ISOLATION LEVEL. Each of these can disable multiplexing if the variable is not in mysql-keep_multiplexing_variables or if the statement cannot be parsed.

A known regression in ProxySQL v2.6.0 (GitHub issue #4896) causes multi-statement commands like SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN to set lock_hostgroup to true and disable multiplexing permanently for the session, even after COMMIT. The connection is only released to the pool when the client disconnects. If your application sends transaction isolation and BEGIN as a single multi-statement command, this may be the cause.

Another known issue (GitHub #5005, v2.7.3) involves functions like CONVERT_TZ() triggering STATUS_MYSQL_CONNECTION_USER_VARIABLE and disabling multiplexing even when relevant variables are listed in mysql-keep_multiplexing_variables.

When you see the ratio spike after a deploy, compare the extended_info output before and after. The new disable reason tells you what changed in the application’s connection behavior.

Signals to watch

The pinning ratio alone does not tell the full story. A ratio of 0.6 with 200 active transactions on 300 connections may be healthy for a write-heavy workload. The same ratio with 5 active transactions on 300 connections means 175 connections are pinned for reasons unrelated to transactions, and something is wrong.

How Netdata helps

Netdata’s ProxySQL collector collects both counters per second, letting you track the pinning ratio as a live trend rather than point-in-time snapshots. The useful correlations:

• Per-second ratio tracking catches the exact moment multiplexing degrades, often coinciding with a deploy or configuration change.
• Active_Transactions correlation distinguishes transaction-induced pinning (expected) from configuration-induced pinning (action needed).
• Backend pool metrics (ConnUsed, ConnFree, ConnPool_get_conn_failure) show whether pinning has progressed to backend saturation.
• Anomaly scoring on the pinning ratio flags deviations from baseline without fixed thresholds, useful when different ProxySQL instances have different normal ratios.
• Multi-instance views help when several ProxySQL instances serve different traffic classes with different multiplexing profiles.

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
• ProxySQL ConnERR climbing: backend connection errors and how to localise them
• ProxySQL ConnPool_get_conn_failure rising: the most direct pool-starvation signal
• 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