How to Set Up Database Connection Pooling with ProxySQL

By Admin · Mar 2, 2026 · Updated Apr 23, 2026 · 29 views · 4 min read

How to Set Up Database Connection Pooling with ProxySQL

ProxySQL is a high-performance MySQL protocol-aware proxy that provides connection pooling, query routing, query caching, and load balancing. It sits between your application and MySQL servers, making it a powerful tool for scaling database workloads across your Breeze instances.

Installing ProxySQL

On your Breeze instance running Ubuntu or Debian:

wget -qO - https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key | \
  sudo apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/$(lsb_release -sc)/ ./" | \
  sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt update
sudo apt install -y proxysql mysql-client

Start ProxySQL:

sudo systemctl enable proxysql
sudo systemctl start proxysql

ProxySQL Architecture

ProxySQL uses three layers of configuration:

RUNTIME — the active configuration currently in use.
MEMORY — staging area for configuration changes.
DISK — persistent storage in SQLite for surviving restarts.

Changes are made in MEMORY, then loaded to RUNTIME and saved to DISK.

Connecting to the Admin Interface

ProxySQL has a MySQL-compatible admin interface on port 6032:

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> '

Adding Backend MySQL Servers

-- Writer hostgroup (10) and reader hostgroup (20)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, weight)
VALUES
  (10, 'primary-breeze-ip', 3306, 100, 1000),
  (20, 'replica1-breeze-ip', 3306, 100, 500),
  (20, 'replica2-breeze-ip', 3306, 100, 500);

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

Configuring MySQL Users

Add application users that ProxySQL will use to authenticate clients and connect to backends:

INSERT INTO mysql_users (username, password, default_hostgroup, max_connections)
VALUES ('appuser', 'AppPassword123!', 10, 200);

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

The default_hostgroup determines which server group handles queries by default.

Query Routing Rules

Route read queries to replicas and write queries to the primary:

-- Send SELECT queries to the reader hostgroup
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (100, 1, '^SELECT.*FOR UPDATE$', 10, 1);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (200, 1, '^SELECT', 20, 1);

-- All other queries go to the writer hostgroup (default)

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

This sends SELECT ... FOR UPDATE to the primary (since it takes locks) and all other SELECT queries to the replicas, distributing read load across your Breeze fleet.

Connection Pooling Configuration

Tune connection pooling parameters:

UPDATE global_variables SET variable_value = 2000
  WHERE variable_name = 'mysql-max_connections';

UPDATE global_variables SET variable_value = 10000
  WHERE variable_name = 'mysql-connection_max_age_ms';

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

UPDATE global_variables SET variable_value = 5000
  WHERE variable_name = 'mysql-connect_timeout_server';

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

Connection multiplexing allows ProxySQL to reuse backend connections across different client sessions, dramatically reducing the number of connections to your MySQL servers.

Query Caching

Enable query caching for frequently executed read queries:

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, apply)
VALUES (300, 1, '^SELECT.*FROM products WHERE', 60000, 1);

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

The cache_ttl is in milliseconds. Cached queries are served directly from ProxySQL memory without hitting the backend database.

Monitoring ProxySQL

Monitor connection pool and query performance from the admin interface:

-- Connection pool status
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR
FROM stats_mysql_connection_pool;

-- Top queries by execution time
SELECT digest_text, count_star, sum_time, min_time, max_time
FROM stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 10;

-- Global statistics
SELECT * FROM stats_mysql_global;

Your application connects to ProxySQL on port 6033 instead of directly to MySQL. ProxySQL handles all connection management, query routing, and caching transparently, making it easy to scale your MySQL infrastructure across multiple Breeze instances without changing application code.

How to Set Up Database Connection Pooling with ProxySQL