ProxySQL & MySQL Connection Multiplexing

This guide is part of Pool Architecture & Algorithm Fundamentals. It covers ProxySQL, the protocol-aware proxy that plays the role for MySQL and MariaDB that PgBouncer plays for PostgreSQL: it accepts a large number of client connections, keeps a much smaller set of backend connections per hostgroup, and hands a backend connection to whichever client session has a statement to run. Unlike PgBouncer it parses every query, which lets it route reads and writes to different hostgroups, cache results and — the part this page is about — decide statement by statement whether a backend connection can safely be shared.

MySQL makes that job more important than it is on PostgreSQL. A MySQL server allocates a thread per connection, the stock max_connections is 151, and the failure is abrupt: the 152nd client gets ERROR 1040 (HY000): Too many connections rather than a queue. A fleet of application pods that each keep a local pool of 20 connections reaches that ceiling at eight pods. ProxySQL removes the coupling between pod count and backend connection count, provided nothing in the application quietly turns multiplexing off.

Key operational takeaways:

• Two separate ceilings exist: mysql-max_connections bounds client connections into ProxySQL, while mysql_servers.max_connections bounds backend connections per server per hostgroup. Sizing one does nothing for the other.
• Multiplexing is disabled for a session by open transactions, LOCK TABLES, GET_LOCK(), temporary tables, user variables such as @total, and SQL_CALC_FOUND_ROWS. A handful of these patterns in a hot path turns the proxy into a 1:1 relay.
• Session variables that ProxySQL tracks — autocommit, sql_mode, time_zone, SET NAMES — do not disable multiplexing; ProxySQL replays them when it switches backend connections.
• The signal that the backend pool is full is ConnPool_get_conn_failure climbing in stats_mysql_global together with ConnUsed pinned at max_connections in stats_mysql_connection_pool.
• Keep the application pool small and short-lived behind ProxySQL. The proxy owns physical connection reuse; a large local pool only inflates the client connection count.

Foundational mechanics

ProxySQL runs a small number of worker threads, set by mysql-threads (default 4), each running an event loop that owns a slice of the client sessions. When a client session has a query to execute, the worker consults the query rules to pick a destination hostgroup, then asks the hostgroup manager for a connection from that hostgroup’s free list. The connection pool is keyed by hostgroup, backend server, user, schema and a small set of connection-level options, so a connection opened for app_rw on schema orders is never handed to reporting on schema analytics.

Once the statement completes, ProxySQL decides whether the backend connection goes back to the free list or stays attached to the session. That decision is the whole of multiplexing. The connection is returned only when the session has no state that lives on the server side: no open transaction, no temporary table, no named lock, no user variable whose value a later statement could read. ProxySQL detects these by inspecting the query it just parsed — a statement that matches CREATE TEMPORARY TABLE, GET_LOCK(, LOCK TABLES, or references @variable flips the session into a non-multiplexed state, and from that point the backend connection stays bound until the session ends or, for some patterns, until the state is cleared.

Session variables are handled differently. ProxySQL tracks a known list of variables — character set, collation, autocommit, sql_mode, time_zone, transaction_isolation, sql_auto_is_null and others — per client session. When the session is next served by a different backend connection whose tracked values differ, ProxySQL issues the corresponding SET statements first. The client never notices, and multiplexing survives. This is why ORMs that run SET NAMES utf8mb4 on every checkout work well behind ProxySQL, while code that stashes intermediate results in @row_count does not.

Two further delays exist for correctness. After an INSERT into a table with an auto-increment column, ProxySQL keeps the connection attached for the next mysql-auto_increment_delay_multiplex statements (default 5) so that a following SELECT LAST_INSERT_ID() reads the value from the same connection. And mysql-connection_delay_multiplex_ms can hold a connection briefly after each statement so that bursts from one session stay on one backend; it defaults to 0 and is rarely worth raising.

Operational Boundary: ProxySQL multiplexes at statement granularity only when the session is stateless between statements. Transaction boundaries are always respected; ProxySQL never moves a session to another backend connection mid-transaction.

Precision sizing & timeout orchestration

Sizing starts at the MySQL server and works outward. Take the server’s max_connections, subtract what must never be consumed by the proxy — replication threads, monitoring, schema migration tools, an operator’s emergency session — and divide the remainder across the ProxySQL instances that point at the server. That quotient is mysql_servers.max_connections for that server in each hostgroup. Only after that do you pick mysql-max_connections, which is purely a client-side admission limit and can be ten to fifty times larger.

The timeout that shapes user-visible latency is mysql-connect_timeout_server_max. When every backend connection in a hostgroup is in use, a new query does not fail immediately; the session waits for a connection to be returned, retrying until this deadline. With the default of 10,000 ms, an exhausted writer hostgroup produces ten-second stalls followed by ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 10 after 10000ms. If your application pool’s acquisition timeout is shorter — HikariCP’s connectionTimeout at 3,000 ms, say — the application gives up first and the proxy keeps working on a request nobody is waiting for. Order the deadlines so the innermost is shortest: proxy wait below application acquisition timeout, application acquisition timeout below the request deadline.

A worked example. A writer with max_connections = 500 serves two ProxySQL instances. Reserve 50 connections for replication, monitoring and operators, and keep 50 as headroom for a third ProxySQL instance during a rolling replacement. That leaves 400, so each instance gets mysql_servers.max_connections = 200. Measured concurrency at peak — ConnUsed summed over the writer — is 130, which gives a comfortable margin. Each instance accepts up to 2,000 client connections, a 10:1 ratio that holds only while most sessions stay multiplexable.

Operational Boundary: These numbers bound connections, not throughput. If Threads_running on the server is already near its core count, adding backend connections moves the queue from ProxySQL into InnoDB, where it is harder to observe. The derivation for that limit lives in Connection Pool Sizing Formulas.

Production configuration examples

ProxySQL is configured through its admin interface, a MySQL-protocol endpoint on port 6032 whose tables are loaded to runtime and saved to disk explicitly. The same values can be bootstrapped from proxysql.cnf, but the admin tables win once the on-disk database exists, which catches out anyone who edits the file and restarts.

Backend servers and their per-server ceilings:

-- Admin interface: mysql -h127.0.0.1 -P6032 -uadmin -p
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, max_replication_lag, comment)
VALUES
  (10, 'mysql-primary.internal', 3306, 1000, 200, 0,  'writer'),
  (20, 'mysql-replica-a.internal', 3306, 1000, 100, 10, 'reader a'),
  (20, 'mysql-replica-b.internal', 3306, 1000, 100, 10, 'reader b');

-- max_replication_lag = 10 shuns a replica more than 10 s behind
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Application users, with the default hostgroup and a per-user client ceiling:

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent, max_connections)
VALUES ('orders_app', '*REDACTED_HASH', 10, 1, 3000);

-- transaction_persistent = 1 keeps every statement of a transaction on the hostgroup
-- where the transaction started, even if a later SELECT matches a read rule
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Global variables that govern pool behaviour and wait time:

UPDATE global_variables SET variable_value = '4096'    WHERE variable_name = 'mysql-max_connections';
UPDATE global_variables SET variable_value = '20'      WHERE variable_name = 'mysql-free_connections_pct';
UPDATE global_variables SET variable_value = '2000'    WHERE variable_name = 'mysql-connect_timeout_server';
UPDATE global_variables SET variable_value = '5000'    WHERE variable_name = 'mysql-connect_timeout_server_max';
UPDATE global_variables SET variable_value = '3600000' WHERE variable_name = 'mysql-connection_max_age_ms';
UPDATE global_variables SET variable_value = '8'       WHERE variable_name = 'mysql-threads';  -- needs restart

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Query rules that send plain reads to the replicas while keeping locking reads on the writer:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
  (100, 1, '^SELECT .* FOR UPDATE', 10, 1),
  (200, 1, '^SELECT ',              20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

The application pool in front of ProxySQL should be sized like a pool in front of any multiplexer: enough connections for one process’s concurrency, no more. A Spring Boot service pointed at port 6033:

spring:
  datasource:
    url: jdbc:mysql://proxysql.internal:6033/orders?useServerPrepStmts=false&cachePrepStmts=true
    hikari:
      maximum-pool-size: 16        # one process's worker concurrency
      minimum-idle: 4
      connection-timeout: 6000     # longer than mysql-connect_timeout_server_max (5000)
      max-lifetime: 1500000        # 25 min, below mysql-wait_timeout on the proxy
      keepalive-time: 300000

useServerPrepStmts=false keeps Connector/J on client-side prepared statements, which avoids per-backend re-preparation behind the proxy. The connection-timeout is deliberately longer than the proxy’s own wait so that the proxy error, which names the hostgroup, reaches the logs instead of a generic pool timeout.

Diagnostics & telemetry

All runtime statistics live in the stats schema of the admin interface. Two tables answer most pool questions.

stats_mysql_connection_pool has one row per backend server per hostgroup. ConnUsed is the number of backend connections currently lent to sessions, ConnFree the idle ones in the free list, ConnOK and ConnERR the cumulative successful and failed connection attempts, MaxConnUsed the high-water mark, and Latency_us the ping latency the monitor module measured.

SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR, MaxConnUsed, Queries, Latency_us
FROM stats.stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;

stats_mysql_global carries the proxy-wide counters. The ones that matter for the pool are Client_Connections_connected, Server_Connections_connected, ConnPool_get_conn_immediate (a connection was available at once), ConnPool_get_conn_success and ConnPool_get_conn_failure, plus MyHGM_myconnpoll_reused against MyHGM_myconnpoll_get, which together give the reuse ratio.

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

The ratio Client_Connections_connected / Server_Connections_connected is the effective multiplexing ratio. Track it over time: a ratio that falls from 10:1 to 2:1 after a deploy means the new code introduced state that pins sessions, even if nothing else changed.

Exhaustion and starvation look different here. Exhaustion — the hostgroup genuinely needs more backend connections — shows ConnUsed equal to max_connections for every server in the hostgroup, ConnPool_get_conn_failure rising, and a short, stable query latency in stats_mysql_query_digest. Starvation — connections are held by sessions that are not running queries — shows the same ConnUsed ceiling but with Active_Transactions high and many sessions in stats_mysql_processlist sitting in a transaction with no current command. The first is fixed by budget; the second by finding the code path that holds a transaction open across network calls, the diagnosis for which is in Detecting Connection Pool Saturation.

For Prometheus, ProxySQL 2.1 and later exposes its own metrics endpoint when admin-restapi_enabled is set, publishing proxysql_connection_pool_conn_used, proxysql_connection_pool_conn_free and the global counters on port 6070. Scraping it removes the need for a separate exporter; the dashboard conventions in Building a Grafana Dashboard for Connection Pool Metrics apply unchanged.

Integration & proxy compatibility

ProxySQL sits in the same position as PgBouncer and inherits the same class of problem: anything the application assumes about session continuity must either be tracked by the proxy or kept inside a transaction. The difference is that ProxySQL errs toward correctness — it pins the session rather than breaking it — so incompatibilities show up as a collapsing multiplexing ratio rather than as wrong results.

A multiplexing query rule can override the default for statements that ProxySQL misclassifies. Setting multiplex = 0 on a rule forces the session to stay bound after a matching statement; multiplex = 1 tells ProxySQL that a statement it would otherwise treat as stateful is safe. The second is useful for driver housekeeping such as Connector/J’s SELECT @@session.transaction_read_only, which reads a session variable and would otherwise pin every connection:

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

In front of Aurora MySQL, ProxySQL’s mysql_aws_aurora_hostgroups table discovers the writer and readers from information_schema.replica_host_status, which removes the DNS delay on failover; the connection-limit arithmetic in AWS Aurora Connection Scaling still applies to each instance. Running ProxySQL next to RDS Proxy is rarely useful — both multiplex, and stacking them doubles the pinning surface. Under Kubernetes the common pattern is a ProxySQL deployment per database behind a Service, not a sidecar per pod: a sidecar per pod multiplies backend connections by the pod count, which is the exact coupling the proxy was meant to remove.

Common failure patterns & remediation

The step-by-step versions of the two most common incidents are in Tuning ProxySQL Multiplexing and Backend Connection Limits and Diagnosing MySQL “Too many connections” Errors.

Failover, backend health and connection draining

ProxySQL’s monitor module is what turns a pool of backend connections into a pool that survives server failures. It runs its own lightweight checks, independent of client traffic, and records results in the monitor schema: connect checks every mysql-monitor_connect_interval, pings every mysql-monitor_ping_interval, read_only checks every mysql-monitor_read_only_interval, and replication lag checks for servers with max_replication_lag set. The results drive server status in runtime_mysql_servers.

OFFLINE_SOFT is the tool for planned maintenance. Setting a server to it lets every session finish its current transaction while the hostgroup stops assigning that server to new work, which drains its connection count to zero without a single client error:

UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'mysql-replica-a.internal';
LOAD MYSQL SERVERS TO RUNTIME;

-- Wait until the server carries no borrowed connections, then do the maintenance
SELECT srv_host, status, ConnUsed, ConnFree
FROM stats.stats_mysql_connection_pool WHERE srv_host = 'mysql-replica-a.internal';

For unplanned writer failover, ProxySQL reads read_only from every server listed in mysql_replication_hostgroups. When the old primary becomes read-only and a replica becomes writable — whether through an orchestrator, Group Replication or a managed service — ProxySQL moves the servers between the writer and reader hostgroups on its next check, typically within a second or two. Connections to the demoted server that are idle are closed; connections in the middle of a write fail and the client must retry. The application pool does not need to reconnect, because its connections are to ProxySQL, not to the database, which is the main operational advantage over pointing application pools directly at a DNS name that moves.

Two settings keep failover from becoming a connection storm. mysql-connect_retries_on_failure (default 10) bounds how many backend connect attempts a single session makes, and mysql-query_retries_on_failure (default 1) lets ProxySQL transparently retry a query that failed because its connection died, when the query is safe to retry — it does not retry inside a transaction. Leave the query retry at 1: higher values hide real failures and multiply load on a recovering server.

Operational Boundary: ProxySQL detects role changes; it does not perform them. Promotion, fencing and replication topology belong to the replication tooling or the managed service.

Frequently Asked Questions

Step-by-Step Guides

Each guide below applies this topic to one concrete incident or configuration task.

• Diagnosing MySQL “Too many connections” Errors — find which accounts and hosts consumed MySQL max_connections, keep an admin path open during the incident, and set per-user caps so it cannot recur.
• Routing Reads and Writes with ProxySQL Query Rules — write ProxySQL query rules that send reads to replicas and writes to the writer, keep read-your-writes correct, and verify routing from the digest statistics.
• Tuning MySQL wait_timeout and Pool Idle Settings — stop ‘MySQL server has gone away’ and communications link failures by ordering wait_timeout, proxy idle timeouts and pool idle and lifetime settings correctly.
• Tuning ProxySQL Multiplexing and Backend Connection Limits — restore a collapsed ProxySQL multiplexing ratio and size mysql_servers.max_connections per hostgroup from the server budget, with verification queries.

Related

• Pool Architecture & Algorithm Fundamentals — the parent overview of pool topology, algorithms and lifecycle.
• Tuning ProxySQL Multiplexing and Backend Connection Limits — restoring the multiplexing ratio and sizing each hostgroup.
• Diagnosing MySQL “Too many connections” Errors — finding which clients consumed the server’s thread budget.
• PgBouncer vs RDS Proxy vs pgpool-II — the equivalent proxy decision for PostgreSQL.
• Connection Pool Sizing Formulas — the arithmetic behind the per-server budget used above.