Tuning ProxySQL Multiplexing and Backend Connection Limits

This guide is part of ProxySQL & MySQL Connection Multiplexing. It addresses the incident that follows most ProxySQL rollouts within a few months: the proxy was sized for a 10:1 ratio of client to backend connections, a release changed how the application talks to MySQL, and the writer hostgroup now runs at its ceiling. Requests stall for several seconds and then fail with the proxy’s own error rather than MySQL’s:

2026-09-14 09:42:17 [ERROR] SQLSTATE[HY000]: General error: 9001
Max connect timeout reached while reaching hostgroup 10 after 5000ms

The objective is to find out whether the hostgroup needs more backend connections or whether sessions are pinning connections they no longer use, then fix the right one without restarting the proxy.

Rapid incident diagnosis

Connect to the admin interface on port 6032 and read three things in this order. The order matters because each answer rules out a class of cause.

First, confirm the hostgroup is genuinely full rather than failing to connect:

SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnERR, MaxConnUsed
FROM stats.stats_mysql_connection_pool
WHERE hostgroup = 10;

If ConnUsed equals the server’s max_connections and ConnFree is 0, the pool is exhausted. If ConnUsed is well below the limit but ConnERR is climbing, the problem is MySQL refusing connections — authentication, max_user_connections, or the server’s own max_connections — and none of the tuning below applies. That case is covered in Diagnosing MySQL “Too many connections” Errors.

Second, compute the multiplexing ratio:

SELECT
  MAX(CASE WHEN variable_name = 'Client_Connections_connected' THEN variable_value END) AS clients,
  MAX(CASE WHEN variable_name = 'Server_Connections_connected' THEN variable_value END) AS servers,
  MAX(CASE WHEN variable_name = 'Active_Transactions' THEN variable_value END)          AS active_txn
FROM stats.stats_mysql_global;

Compare it with the ratio from before the incident — a Grafana panel of the same two series is the quickest source. A ratio that held at 9:1 for weeks and now reads 2:1 means sessions are pinning. A ratio that is unchanged while traffic doubled means the hostgroup is simply too small.

Third, if the ratio collapsed, find the pinning statements. stats_mysql_query_digest shows what arrived recently; look for the patterns that bind a session:

SELECT hostgroup, count_star, digest_text
FROM stats.stats_mysql_query_digest
WHERE digest_text LIKE '%GET_LOCK%'
   OR digest_text LIKE '%TEMPORARY TABLE%'
   OR digest_text LIKE '%@%'
   OR digest_text LIKE '%SQL_CALC_FOUND_ROWS%'
ORDER BY count_star DESC
LIMIT 20;

The @ filter is broad and also matches @@session system-variable reads, which are the most common pinning source after a driver upgrade.

Triage path for ProxySQL error 9001 Check ConnUsed against the limit, then ConnERR, then the client-to-server ratio. A full pool with a collapsed ratio points to pinning; a full pool with a stable ratio points to an undersized hostgroup; free connections with rising errors point to MySQL refusing connects. Error 9001 hostgroup 10 wait ConnUsed = limit? stats_mysql_connection_pool No: ConnERR rising MySQL refusing connects Ratio vs baseline clients ÷ servers Collapsed sessions pinning Stable hostgroup too small yes Start from the proxy error, not from MySQL status: the server may look idle
Three reads of the admin interface separate the causes. The branch that matters most is the ratio check, because it decides whether the fix is capacity or code.

Mathematical sizing or parameter formula

Backend limits come from the server, not from demand. For each MySQL server, the per-proxy limit is:

per_proxy_max = floor( (server_max_connections − reserved − headroom) / proxy_instances )

reserved covers replication threads, monitoring agents, backup tools, migrations and an operator session. headroom covers one extra proxy instance during a rolling replacement, when old and new pods are both connected. Required backend concurrency comes from Little’s Law applied to backend hold time:

needed_backend = (queries_per_second × mean_backend_hold_seconds) ÷ target_utilisation

Worked example. The writer has max_connections = 600. Reserve 40 and keep headroom equal to one instance’s share. With three ProxySQL instances, solve 3x + x = 560, so x = 140: each instance gets mysql_servers.max_connections = 140, and during a rollout four instances fit inside 560.

Demand at peak is 2,400 writes per second. With multiplexing working, the backend hold time is the query time, 12 ms: 2400 × 0.012 = 28.8 busy connections, which at 70 % target utilisation is 41 across the fleet, or 14 per instance. The limit of 140 is ten times what is needed.

After the release, pinned sessions hold their connection between requests. The backend hold time is now the session lifetime divided by the requests it serves. If the application pool keeps a connection checked out for an average of 180 ms per request because of a GET_LOCK held across an HTTP call, then 2400 × 0.18 = 432 busy connections, or 617 at the 70 % target — half again above the fleet limit of 420. No safe increase in max_connections fixes that; the server budget would have to triple. The formula tells you the release, not the limit, is the fault.

Backend connections needed before and after pinning At 2,400 writes per second, a 12 ms hold time needs 41 backend connections at 70 percent utilisation, well under the fleet limit of 420. A 180 ms pinned hold time needs 617, far above the limit. Backend connections needed at 2,400 writes/s (70 % target) fleet limit: 3 × 140 = 420 12 ms hold 41 connections 180 ms hold (pinned) 617 connections same query time, same traffic, 15× the hold time needed = qps × hold time ÷ 0.7
Pinning does not change query time; it changes how long each backend connection is held. That multiplier, not traffic, is what exhausted the hostgroup.

Exact remediation & configuration

Apply the fixes in two stages: a runtime change that stops the errors now, and a code or rule change that restores multiplexing.

Stage one — buy time within the budget. Raise the per-server limit only as far as the server budget allows, and shorten the proxy wait so failures are fast rather than slow. Admin changes take effect on LOAD ... TO RUNTIME without dropping client sessions:

-- Temporary: use the rollout headroom while no rollout is in progress (560 / 3 = 186)
UPDATE mysql_servers SET max_connections = 180 WHERE hostgroup_id = 10;
LOAD MYSQL SERVERS TO RUNTIME;

-- Fail in 3 s instead of 10 s so application retries and circuit breakers engage
UPDATE global_variables SET variable_value = '3000'
WHERE variable_name = 'mysql-connect_timeout_server_max';
LOAD MYSQL VARIABLES TO RUNTIME;

Do not SAVE ... TO DISK the stage-one limit. Leaving it only in runtime means a proxy restart reverts to the value that respects rollout headroom.

Stage two — restore multiplexing. Identify which pattern pins. For driver housekeeping reads of session variables, which are harmless to share, tell ProxySQL explicitly that the statement does not bind the session:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, multiplex, apply)
VALUES
  (40, 1, '^SELECT @@session\.transaction_read_only', 1, 0),
  (41, 1, '^SELECT @@session\.auto_increment_increment', 1, 0);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

For genuine state — a named lock held across a network call — change the code. A row lock inside a short transaction gives the same mutual exclusion and releases the connection at COMMIT:

# Before: pins the ProxySQL session for as long as the lock is held
cur.execute("SELECT GET_LOCK(%s, 10)", (f"invoice:{invoice_id}",))
charge = payment_gateway.charge(invoice_id)          # 150 ms network call
cur.execute("UPDATE invoices SET status='paid' WHERE id=%s", (invoice_id,))
cur.execute("SELECT RELEASE_LOCK(%s)", (f"invoice:{invoice_id}",))

# After: no session state, and no connection held during the network call
charge = payment_gateway.charge(invoice_id, idempotency_key=f"invoice:{invoice_id}")
with conn.cursor() as cur:
    cur.execute("START TRANSACTION")
    cur.execute("SELECT status FROM invoices WHERE id=%s FOR UPDATE", (invoice_id,))
    cur.execute("UPDATE invoices SET status='paid' WHERE id=%s AND status<>'paid'", (invoice_id,))
    cur.execute("COMMIT")

Once the ratio recovers, revert the stage-one limit to the budgeted value of 140 at runtime and keep the shorter connect timeout, making sure the application’s acquisition timeout is longer than the proxy wait so the proxy’s error reaches the logs.

Runtime and disk configuration layers in ProxySQL Stage one raises max_connections to 180 in the runtime layer only. Stage two fixes pinning and reverts runtime to 140, which matches the value saved on disk. MEMORY table edited by UPDATE max_connections 180 RUNTIME LOAD ... TO RUNTIME serves live traffic DISK SAVE ... TO DISK max_connections 140 load Stage one never reaches disk; a restart restores the budget Stage two: fix pinning, then load 140 back into runtime so memory, runtime and disk agree again
The runtime limit lives only in memory and reverts on restart. The saved value stays at the budget that leaves room for a rolling replacement.

Validation & verification

Verify from both sides of the proxy, then under load.

On ProxySQL, the ratio should return to its pre-release level within one traffic cycle and ConnPool_get_conn_failure should stop increasing:

SELECT variable_name, variable_value FROM stats.stats_mysql_global
WHERE variable_name IN ('Client_Connections_connected','Server_Connections_connected',
                        'ConnPool_get_conn_failure','ConnPool_get_conn_immediate');

Run it twice a minute apart. ConnPool_get_conn_failure must be identical in both samples, and ConnPool_get_conn_immediate should account for nearly all new successes.

On MySQL, confirm the thread count dropped and nothing is sitting idle inside a transaction:

SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Threads_running','Max_used_connections');

SELECT COUNT(*) AS idle_in_txn
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
WHERE p.command = 'Sleep';

A non-zero idle_in_txn that persists across samples means some code still holds a transaction open between statements, which pins in ProxySQL and holds locks in InnoDB.

Under load, replay the traffic that caused the incident at 1.5× and assert on the proxy counter rather than on application latency alone:

sysbench oltp_write_only --mysql-host=proxysql.internal --mysql-port=6033 \
  --mysql-user=orders_app --mysql-password="$PW" --tables=8 --threads=256 --time=300 run

mysql -h127.0.0.1 -P6032 -uadmin -p"$ADMIN_PW" -Nse \
  "SELECT MaxConnUsed FROM stats.stats_mysql_connection_pool WHERE hostgroup=10" \
  | awk '{ if ($1 > 112) { print "FAIL: MaxConnUsed", $1, "> 80% of 140"; exit 1 } else print "OK", $1 }'

The assertion keeps peak use under 80 % of the budgeted limit, which is the margin that absorbs the next unplanned pinning pattern.

Keeping the ratio healthy after the incident

Pinning regressions return with new code, so make the multiplexing ratio something the team sees before the pool is exhausted, not after.

Track the ratio as a first-class metric. With ProxySQL’s built-in Prometheus endpoint enabled, record the ratio per instance and alert when it drops sharply relative to its own recent history rather than against a fixed value, because the healthy ratio depends on traffic mix:

groups:
  - name: proxysql-multiplexing
    rules:
      - record: proxysql:multiplex_ratio
        expr: proxysql_client_connections_connected / clamp_min(proxysql_server_connections_connected, 1)
      - alert: ProxySQLMultiplexingCollapsed
        expr: proxysql:multiplex_ratio < 0.5 * avg_over_time(proxysql:multiplex_ratio[7d])
        for: 15m
        labels: { severity: warning }
        annotations:
          summary: "ProxySQL {{ $labels.instance }} multiplexing ratio halved versus the 7-day average"

Review digests in CI or at release time. stats_mysql_query_digest retains every normalised statement with its hostgroup and count. After a deploy to a staging proxy, export the digest table and diff it against the previous release, flagging any new digest that contains a pinning pattern:

SELECT digest, digest_text, count_star
FROM stats.stats_mysql_query_digest
WHERE (digest_text LIKE '%GET_LOCK(%' OR digest_text LIKE '%TEMPORARY%' OR digest_text REGEXP '@[a-z_]')
ORDER BY count_star DESC;

Reset digest statistics deliberately. Reading from stats_mysql_query_digest_reset returns the table and clears it, which makes before-and-after comparisons clean: reset, run the release’s traffic for an hour, read.

Keep a budget document next to the configuration. The per-server limits in mysql_servers are the output of arithmetic — server max_connections, reserved slots, rollout headroom, instance count. Store the inputs alongside the proxysql.cnf or the Terraform that sets them, so the next change to any input prompts recalculating the limit instead of editing it by feel.

Check Frequency Healthy result
Multiplex ratio versus 7-day average Continuous alert Above 50 % of average
New pinning digests after release Every release None, or each one reviewed
MaxConnUsed versus max_connections per server Weekly Below 80 %
Sum of per-instance limits versus server budget On any topology change Within budget with rollout headroom

Frequently Asked Questions

Should mysql_servers.max_connections be the same on every server in a hostgroup?
Only if the servers are identical. A reader with half the memory of its peers should get a lower limit and a lower weight, otherwise ProxySQL spreads load evenly and the smaller instance exhausts first. The limit applies per server, so a two-reader hostgroup with 100 each can open 200 in total.
Does raising mysql-free_connections_pct help during exhaustion?
No. It controls how many idle connections are kept when demand falls, not how many can be opened. It only helps with churn: if ConnOK rises steadily while the hostgroup is not full, connections are being closed and reopened, and a higher percentage keeps more of them warm.
Is multiplex = 2 on a query rule ever useful?
Value 2 tells ProxySQL not to disable multiplexing for the statement even though it references a user variable, while still treating other signals normally. It suits read-only uses of @@ variables and is narrower than 1. Test it against the digest in a staging proxy before using it in production.
Why did the error appear only on the writer hostgroup?
Pinning statements usually live in write paths — locks, auto-increment reads and temporary staging tables — and transaction_persistent = 1 keeps whole transactions on the hostgroup where they began. Reader hostgroups mostly see stateless SELECT traffic, so their ratio stays high.
Can ProxySQL tell me which application code issued a pinning statement?
Not directly; it sees SQL, users and client addresses. Add a query comment with the calling module — many ORMs support this — and ProxySQL keeps comments out of the digest by default but logs them in the events log when query logging is enabled for the matching rule, which ties the digest back to code.