ProxySQL: The Complete Guide to MySQL Load Balancing and Connection Pooling

A fintech startup running MySQL 8.0 on three replica nodes hit a wall at 400,000 queries per second during their quarterly settlement run. Their application servers were opening and closing connections at a rate the MySQL server could not absorb — Too many connections errors cascaded across 40 application pods within 90 seconds. Their on-call engineer spent two hours manually rerouting traffic by editing DNS records while read replicas sat at 12% CPU utilization. The writes were all hammering the primary. The reads were not being distributed at all.

ProxySQL solved this in a single afternoon. It pooled 12,000 application connections down to 400 backend connections, split reads to replicas automatically via query rules, and gave their team a real-time admin interface to adjust routing without touching application code or restarting any MySQL server.

This guide covers ProxySQL from installation through production-grade query routing, connection pooling, and monitoring — with every command you need to replicate the setup.

What is ProxySQL

ProxySQL is an open-source, high-performance proxy for MySQL and MySQL-compatible databases (Percona Server, MariaDB, TiDB, Aurora). It was created by René Cannaò and is maintained by ProxySQL LLC. Unlike HAProxy or nginx, which are general-purpose TCP proxies, ProxySQL speaks the MySQL wire protocol natively. It can inspect individual SQL statements, parse query digests, and make routing decisions based on query content — not just connection metadata.

The key distinction is that ProxySQL is not a simple load balancer. It is a full connection broker. When your application opens a connection to ProxySQL on port 6033, ProxySQL does not immediately open a corresponding connection to MySQL. Instead, it holds the application connection in its frontend pool, picks a backend connection from its pre-established pool when a query arrives, routes that query to the appropriate MySQL server based on your rules, and returns the result. The backend connection is then returned to the pool, ready for the next query from any application connection.

This architecture is why ProxySQL can multiplex 10,000 application connections onto 200 backend MySQL connections without the MySQL server seeing connection overhead.

ProxySQL Architecture

Frontend Connections

The frontend is what your application sees. Applications connect to ProxySQL on port 6033 (or 3306 if you configure it) using the standard MySQL client protocol, with credentials defined in ProxySQL's mysql_users table. From the application's perspective, it is connecting to a MySQL server — there is no driver change, no special library, no application code modification.

ProxySQL supports up to tens of thousands of concurrent frontend connections. The limit is configurable via mysql-max_connections in the global variables table. Each frontend connection is handled by a lightweight thread and does not hold a backend connection open while idle.

Query Processor

Every query that arrives at ProxySQL passes through the query processor, which performs several operations in sequence:

1. Query digest computation — ProxySQL normalizes the query (replacing literals with ? placeholders) to produce a stable fingerprint called the query digest. This is used for matching query rules and for statistics aggregation.
2. Rule matching — ProxySQL evaluates mysql_query_rules in rule_id order. Rules can match on schema name, username, client host, query digest, or a regex against the raw query text. The first rule with apply=1 that matches terminates evaluation.
3. Hostgroup assignment — The matched rule sets the destination_hostgroup. If no rule matches, the connection's default_hostgroup is used.
4. Caching and mirroring (optional) — Rules can also instruct ProxySQL to cache the result for a TTL, or mirror the query to a second hostgroup for comparison testing.

Backend Connection Pool

ProxySQL maintains persistent TCP connections to every MySQL server in mysql_servers. These connections are grouped by hostgroup and reused across many frontend queries. When a frontend query needs a backend connection, ProxySQL picks an available connection from the pool for the target hostgroup using a weighted round-robin algorithm. If all connections are busy, ProxySQL queues the query rather than opening a new connection to MySQL, protecting the database from connection storms.

The pool parameters that matter most in production are max_connections per server (caps backend connections to a single MySQL instance), mysql-max_connections (caps total frontend connections), and mysql-connection_max_age_ms (recycles stale connections).

Installing and Configuring ProxySQL

Step 1: Install ProxySQL

ProxySQL provides packages for all major Linux distributions. The recommended approach is to use the official ProxySQL repository.

# Add the ProxySQL repo (RHEL/CentOS 7+)
cat >/etc/yum.repos.d/proxysql.repo <<'EOF'
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF

yum install proxysql

# Ubuntu / Debian
wget -O /tmp/proxysql.deb \
  https://github.com/sysown/proxysql/releases/download/v2.6.5/proxysql_2.6.5-ubuntu22_amd64.deb
dpkg -i /tmp/proxysql.deb

# Start and enable
systemctl enable proxysql
systemctl start proxysql

ProxySQL stores its working configuration in a SQLite database at /var/lib/proxysql/proxysql.db. When you run configuration commands through the admin interface, they first modify in-memory tables, then you explicitly load them to the runtime and save them to disk. This three-step workflow prevents accidental persistence of bad configuration.

Verify ProxySQL is running and connect to the admin interface:

# Admin interface runs on port 6032, default credentials: admin / admin
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> '

Step 2: Add Backend MySQL Servers

Define your MySQL topology in mysql_servers. Use hostgroup 10 for the primary (writes) and hostgroup 20 for replicas (reads). This hostgroup numbering is a convention, not a requirement — any integers work, but 10/20 is widely adopted.

-- Connect to ProxySQL admin on port 6032 first
-- Add the primary MySQL server (hostgroup 10 = writers)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, comment)
VALUES
  (10, '10.0.1.10', 3306, 1000, 2000, 'primary-writer'),
  (20, '10.0.1.11', 3306, 1000, 2000, 'replica-1'),
  (20, '10.0.1.12', 3306, 1000, 2000, 'replica-2');

-- Verify the configuration
SELECT hostgroup_id, hostname, port, weight, max_connections, status
FROM mysql_servers
ORDER BY hostgroup_id, hostname;

-- Activate the changes
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

The weight column controls the proportion of traffic distributed to each server within a hostgroup. A server with weight=2000 receives twice the traffic of a server with weight=1000. Set lower weights for servers with less capacity. The max_connections column caps how many backend connections ProxySQL will open to that specific MySQL instance — this is your primary protection against overwhelming a MySQL server during traffic spikes.

Step 3: Configure Users

ProxySQL maintains its own user table. Users defined here map to MySQL credentials on the backend, and you specify the default hostgroup per user. All queries from this user will land in the default hostgroup unless a query rule overrides it.

-- Add application users
INSERT INTO mysql_users (username, password, default_hostgroup, max_connections, transaction_persistent)
VALUES
  ('appuser',    'AppPassword123!', 10, 1000, 1),
  ('readonly',   'ReadPassword456!', 20,  500, 0);

-- transaction_persistent=1 means all queries within a transaction
-- stay on the same hostgroup (prevents splitting transactions across servers)

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- Add a monitoring user (ProxySQL uses this to health-check MySQL servers)
UPDATE global_variables
SET variable_value = 'proxysql_monitor'
WHERE variable_name = 'mysql-monitor_username';

UPDATE global_variables
SET variable_value = 'MonitorPass789!'
WHERE variable_name = 'mysql-monitor_password';

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

The monitor user must exist on all MySQL servers with REPLICATION CLIENT privilege so ProxySQL can check replication lag:

-- Run this on the MySQL PRIMARY (it replicates to replicas)
CREATE USER 'proxysql_monitor'@'%' IDENTIFIED BY 'MonitorPass789!';
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'proxysql_monitor'@'%';
FLUSH PRIVILEGES;

Step 4: Set Up Query Rules

Query rules are the mechanism that enables read/write splitting. Without rules, all traffic routes to the user's default_hostgroup. With rules, ProxySQL inspects each query and routes reads to replicas and writes to the primary.

-- Route SELECT queries to reader hostgroup (20)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (100, 1, '^SELECT', 20, 1);

-- Route SELECT ... FOR UPDATE to writer (needs primary, it takes row locks)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (50, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- Route all writes explicitly to writer (belt and suspenders)
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (200, 1, '^(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE)', 10, 1);

-- Lower rule_id = evaluated first; rule 50 catches SELECT...FOR UPDATE
-- before rule 100 catches generic SELECTs

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

Connection Pooling and Multiplexing

Connection pooling is the primary reason teams deploy ProxySQL. Modern application frameworks open a connection pool of 10–50 connections per pod. Multiply by 100 pods and MySQL sees 1,000–5,000 connections, most of them idle. MySQL's connection overhead is not trivial — each connection allocates memory for thread stacks, sort buffers, and join buffers. At 5,000 connections, this overhead consumes gigabytes of RAM and degrades query performance.

ProxySQL collapses this with multiplexing. One backend connection serves many frontend connections in sequence. The key configuration parameters are:

-- Set global MySQL-facing configuration
UPDATE global_variables SET variable_value = '2000'
  WHERE variable_name = 'mysql-max_connections';

UPDATE global_variables SET variable_value = '36000000'
  WHERE variable_name = 'mysql-default_query_timeout';

-- Enable connection multiplexing
UPDATE global_variables SET variable_value = '1'
  WHERE variable_name = 'mysql-multiplexing';

-- How long to keep idle backend connections open (ms)
UPDATE global_variables SET variable_value = '600000'
  WHERE variable_name = 'mysql-connection_max_age_ms';

-- Ping backend connections every 10 seconds to keep them alive
UPDATE global_variables SET variable_value = '10000'
  WHERE variable_name = 'mysql-ping_interval_server_msec';

-- Reconnect attempts on backend connection failure
UPDATE global_variables SET variable_value = '3'
  WHERE variable_name = 'mysql-connect_retries_on_failure';

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

With multiplexing=1 (the default), ProxySQL reuses backend connections across frontend queries. Multiplexing is automatically disabled for a specific frontend connection when session state makes it unsafe: active transactions, SET commands that change session variables, LOCK TABLES, temporary tables, or SQL_CALC_FOUND_ROWS. ProxySQL tracks this per-connection and re-enables multiplexing once the session state is clean.

The default_query_timeout value of 36000000 milliseconds (10 hours) is intentionally permissive. Set it to match your longest legitimate query. Queries exceeding this timeout are killed and the frontend connection receives an error. For OLTP workloads, a value of 60000 (60 seconds) is more appropriate.

Read/Write Splitting with Hostgroups

Hostgroups are ProxySQL's unit of server grouping. A hostgroup is simply an integer ID; all servers with the same hostgroup ID form a pool. ProxySQL load-balances queries across all ONLINE servers in the target hostgroup using weighted round-robin.

You can verify routing is working correctly by querying ProxySQL's statistics and comparing server-level query counts:

-- Check which servers are receiving queries
SELECT hostgroup, srv_host, srv_port,
       ConnUsed, ConnFree, ConnOK, ConnERR,
       Queries, Bytes_data_sent, Bytes_data_recv
FROM stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;

-- Example output showing read/write split working:
-- hostgroup=10, 10.0.1.10:3306, Queries=42183   (primary, writes only)
-- hostgroup=20, 10.0.1.11:3306, Queries=318450  (replica 1, reads)
-- hostgroup=20, 10.0.1.12:3306, Queries=321089  (replica 2, reads)

For a more nuanced topology — for example, routing analytical queries to a dedicated replica with more RAM and no replication lag sensitivity — add a third hostgroup and a query rule targeting it:

-- Add a high-memory analytics replica as hostgroup 30
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, comment)
VALUES (30, '10.0.1.13', 3306, 1000, 500, 'analytics-replica');

-- Route queries against the reporting schema to hostgroup 30
INSERT INTO mysql_query_rules (rule_id, active, match_digest, schemaname, destination_hostgroup, apply)
VALUES (75, 1, '^SELECT', 'reporting_db', 30, 1);

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

ProxySQL Query Rules in Practice

Query rules are more powerful than simple regex routing. They support caching, mirroring, query blocking, and response rewriting. The match_digest column matches against the normalized query fingerprint (literals replaced with ?), while match_pattern matches against the raw query text.

Cache frequently repeated SELECT queries with a TTL:

-- Cache the user session lookup for 5 seconds (5000 ms)
INSERT INTO mysql_query_rules
  (rule_id, active, match_digest, cache_ttl, destination_hostgroup, apply)
VALUES
  (300, 1, 'SELECT \* FROM sessions WHERE token = \?', 5000, 20, 1);

-- Block dangerous queries (full-table scans without WHERE on large tables)
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, error_msg, apply)
VALUES
  (999, 1, '^SELECT \* FROM orders$', 'Full table scan blocked by DBA policy', 1);

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

Mirror traffic to a shadow hostgroup without impacting production latency. Mirroring sends a copy of matched queries to a second hostgroup asynchronously — the client receives the response from the primary hostgroup immediately, and the mirror result is discarded:

-- Mirror 10% of SELECT traffic to a canary MySQL 9.0 test instance
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, comment)
VALUES (50, '10.0.1.20', 3306, 1000, 100, 'mysql-9-canary');

INSERT INTO mysql_query_rules
  (rule_id, active, match_digest, mirror_hostgroup, destination_hostgroup, apply)
VALUES
  (400, 1, '^SELECT', 50, 20, 1);

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

Monitoring ProxySQL

ProxySQL exposes an extensive statistics schema through the admin interface. The most important views for day-to-day operations are in the stats schema.

Monitor connection pool health per hostgroup and server:

-- Real-time connection pool status
SELECT hostgroup AS hg,
       srv_host,
       ConnUsed,
       ConnFree,
       ConnOK,
       ConnERR,
       MaxConnUsed,
       Queries,
       Latency_us / 1000.0 AS latency_ms
FROM stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;

-- Identify slow queries (digest-level aggregation)
SELECT digest_text,
       count_star AS executions,
       sum_time / count_star / 1000 AS avg_ms,
       min_time / 1000 AS min_ms,
       max_time / 1000 AS max_ms,
       sum_rows_sent / count_star AS avg_rows
FROM stats_mysql_query_digest
WHERE sum_time / count_star > 100000  -- queries averaging > 100ms
ORDER BY sum_time DESC
LIMIT 20;

-- Check which query rules are being hit
SELECT rule_id, match_digest, destination_hostgroup, hits
FROM stats_mysql_query_rules
ORDER BY hits DESC;

For replication lag awareness, ProxySQL can automatically remove replicas that fall behind a lag threshold. Configure this in mysql_replication_hostgroups:

-- Link writer and reader hostgroups for replication-aware routing
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
VALUES (10, 20, 'primary-replica pair');

-- Set max acceptable replication lag (seconds) before server is excluded
UPDATE global_variables
SET variable_value = '5'
WHERE variable_name = 'mysql-monitor_replication_lag_interval';

UPDATE global_variables
SET variable_value = '10'
WHERE variable_name = 'mysql-monitor_slave_lag_when_null';

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

Common Mistakes and How to Avoid Them

Working with JusDB on ProxySQL

JusDB deploys and manages ProxySQL for engineering teams running MySQL at scale. Our DBAs handle installation, hostgroup design, query rule tuning, and 24/7 failover monitoring — so your connection layer never becomes a bottleneck.

Explore JusDB MySQL Services →  |  Talk to a DBA

Related reading:

• MySQL Indexes: The Complete Guide
• MySQL Group Replication
• MySQL Architecture Deep Dive