ProxySQL MySQL MySQL · DBA · Performance

ProxySQL — Connection Pooling & Multiplexing

Deep dive into ProxySQL connection pooling — how multiplexing works, pool variables, stats monitoring and per-server connection limits.

Connection Pooling in ProxySQL

One of ProxySQL's most valuable features is connection pooling and multiplexing. Instead of every application connection creating a dedicated connection to MySQL, ProxySQL maintains a pool of backend connections and reuses them efficiently. This dramatically reduces MySQL connection overhead.

Without ProxySQL:   1000 app connections → 1000 MySQL connections (high overhead)
With ProxySQL:      1000 app connections → ~50-100 MySQL connections (multiplexed)

Connection Pool Variables

SQL — Connection Pool Variables

-- Max connections ProxySQL accepts from applications
UPDATE global_variables SET variable_value='10000'
WHERE variable_name='mysql-max_connections';

-- Max idle backend connections per hostgroup/server
UPDATE global_variables SET variable_value='2048'
WHERE variable_name='mysql-max_connection_pool_size';

-- Free connections beyond this are closed (per server)
UPDATE global_variables SET variable_value='100'
WHERE variable_name='mysql-free_connections_pct';

-- Multiplexing: reuse backend connections (1=enabled)
UPDATE global_variables SET variable_value='1'
WHERE variable_name='mysql-multiplexing';

-- Timeout for waiting for a free backend connection (ms)
UPDATE global_variables SET variable_value='10000'
WHERE variable_name='mysql-connection_delay_multiplex_ms';

-- How long an idle connection is kept before closing (ms)
UPDATE global_variables SET variable_value='28800000'
WHERE variable_name='mysql-connection_max_uses';

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

Key Pool Variables Reference

Variable	Default	Description
mysql-max_connections	2048	Max frontend (app) connections
mysql-max_connection_pool_size	2048	Max backend connections per server
mysql-free_connections_pct	10	% of max connections to keep free in pool
mysql-multiplexing	1	Enable connection multiplexing
mysql-connection_delay_multiplex_ms	0	Delay before multiplexing (ms)
mysql-connection_lifetime_ms	0	Max age of a backend connection (0=unlimited)
mysql-connect_retries_on_failure	10	Retries when backend connection fails
mysql-connect_retries_delay	1	Delay between retries (ms)
mysql-connect_timeout_server	3000	Backend connect timeout (ms)
mysql-connect_timeout_server_max	10000	Max backend connect timeout (ms)
mysql-throttle_connections_per_sec_to_hostgroup	1000000	Rate limit new connections per second

Viewing Pool Statistics

SQL — Pool Stats

-- Connection pool stats per server
SELECT hostgroup, srv_host, srv_port, status,
       ConnUsed, ConnFree, ConnOK, ConnERR,
       MaxConnUsed, Queries,
       Bytes_data_sent, Bytes_data_recv,
       Latency_us
FROM stats.stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;

-- Overall MySQL global stats
SELECT variable_name, variable_value
FROM stats.stats_mysql_global
WHERE variable_name IN (
  'Client_Connections_connected',
  'Client_Connections_created',
  'Client_Connections_aborted',
  'Server_Connections_connected',
  'Server_Connections_created',
  'Server_Connections_aborted',
  'Questions',
  'Slow_queries',
  'ConnPool_get_conn_success',
  'ConnPool_get_conn_failure'
);

Connection Pool History

SQL — Pool History

-- Historical connection pool stats
SELECT hostgroup, srv_host, srv_port,
       SUM(ConnUsed) as total_used,
       SUM(ConnFree) as total_free,
       SUM(Queries) as total_queries
FROM stats_history.stats_mysql_connection_pool
GROUP BY hostgroup, srv_host, srv_port
ORDER BY total_queries DESC;

Multiplexing Behavior

Multiplexing is what allows ProxySQL to serve many app connections with few backend connections. However, multiplexing is automatically disabled for a connection in these situations:

Connection is inside a transaction (BEGIN/START TRANSACTION)
User has transaction_persistent=1 and query is after a write
SET statement was issued (session variable changed)
LOCK TABLES was issued
SQL_CALC_FOUND_ROWS was used
User has fast_forward=1

SQL — Per-Server Connection Limits

-- Check per-server max connections setting
SELECT hostgroup_id, hostname, port, max_connections
FROM mysql_servers
ORDER BY hostgroup_id;

-- Increase max connections for a specific server
UPDATE mysql_servers SET max_connections=2000
WHERE hostname='192.168.1.100';

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

💡 Note: The max_connections in mysql_servers limits connections FROM ProxySQL TO that MySQL server. The mysql-max_connections variable limits connections FROM applications TO ProxySQL.