ProxySQL: Reduce PHP-MySQL Connections at Scale

Every PHP application that talks to MySQL opens a connection. In a small app, that's fine. But add 50 PHP-FPM workers, deploy to 3 servers, throw in a traffic spike — and suddenly you have hundreds of simultaneous connections hammering MySQL, each one consuming memory, a thread, and file descriptors.

The solution isn't to buy a bigger server. It's to put a smart proxy in front of MySQL that pools those connections for you. That proxy is ProxySQL.

What Is ProxySQL?

ProxySQL is a high-performance SQL-aware proxy that sits between your application and MySQL. It speaks the MySQL protocol, so your PHP code doesn't know the difference. From PHP's perspective, it's just talking to MySQL as usual.

What ProxySQL actually does behind the scenes:

• Multiplexes hundreds of incoming connections down to a small pool of persistent backend connections
• Routes queries to read replicas vs write primaries based on rules
• Caches query results
• Rewrites queries on the fly
• Monitors backend health and fails over automatically

For this post, we'll focus on the most immediately valuable feature: connection pooling.

The Problem: PHP + MySQL Connection Overhead

PHP-FPM spawns worker processes. Each worker holds a MySQL connection open for the duration of a request (or persistently, with pconnect). The mismatch is:

With 100 workers across 5 app servers, that's 500 connections to MySQL — even if only 10% are actively running queries at any moment. MySQL's max_connections defaults to 151. You hit that wall fast.

With ProxySQL:

500 PHP workers connect to ProxySQL. ProxySQL maintains 20 persistent connections to MySQL and multiplexes all traffic through them. MySQL sees 20 connections instead of 500.

Installing ProxySQL

Ubuntu/Debian:

wget https://github.com/sysown/proxysql/releases/download/v2.6.3/proxysql_2.6.3-ubuntu22_amd64.deb
dpkg -i proxysql_2.6.3-ubuntu22_amd64.deb
systemctl start proxysql
systemctl enable proxysql

Docker (for local dev/testing):

docker run -d \
  --name proxysql \
  -p 6033:6033 \
  -p 6032:6032 \
  proxysql/proxysql

ProxySQL listens on two ports:

• 6033 — MySQL protocol proxy port (your app connects here)
• 6032 — Admin interface (MySQL protocol, but for ProxySQL config)

Configuring ProxySQL

Configuration is done via the admin interface, which is itself a MySQL-compatible interface. Connect to it:

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

Default credentials are admin/admin — change these immediately in production.

Step 1: Add MySQL Backend Server

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (0, '127.0.0.1', 3306, 20);
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

max_connections = 20 means ProxySQL will keep at most 20 connections open to this MySQL server. Tune this based on your MySQL capacity. A good starting point is (max_connections / number_of_proxysql_instances) * 0.8.

Step 2: Create a MySQL User for ProxySQL

On the MySQL side, create the user your app will authenticate as:

CREATE USER 'appuser'@'%' IDENTIFIED BY 'strongpassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

Then tell ProxySQL about this user:

-- In ProxySQL admin (port 6032)
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('appuser', 'strongpassword', 0);
 
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Step 3: Set a Monitoring User

ProxySQL needs a MySQL user to ping backends and check health:

-- On MySQL
CREATE USER 'proxysql_monitor'@'%' IDENTIFIED BY 'monitorpassword';
GRANT USAGE ON *.* TO 'proxysql_monitor'@'%';

-- In ProxySQL admin
SET mysql-monitor_username = 'proxysql_monitor';
SET mysql-monitor_password = 'monitorpassword';
 
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Step 4: Enable Connection Multiplexing

Connection multiplexing is on by default, but verify the key variables:

SELECT variable_name, variable_value
FROM global_variables
WHERE variable_name IN (
  'mysql-connection_max_age_ms',
  'mysql-free_connections_pct',
  'mysql-max_connections'
);

Key variables to understand:

Connecting PHP to ProxySQL

No PHP code changes needed. Just point your connection to ProxySQL's host and port 6033:

// Before: connecting directly to MySQL
$pdo = new PDO('mysql:host=mysql-server;port=3306;dbname=myapp', 'appuser', 'strongpassword');
 
// After: connecting through ProxySQL
$pdo = new PDO('mysql:host=proxysql-server;port=6033;dbname=myapp', 'appuser', 'strongpassword');

That's it. PHP thinks it's talking to MySQL. ProxySQL handles the pooling transparently.

php.ini / PHP-FPM considerations

With ProxySQL pooling connections, you typically don't need pconnect (persistent connections) in PHP anymore — ProxySQL already keeps connections alive on the backend side. Using pconnect with ProxySQL can actually hurt you, because PHP holds a ProxySQL connection open across requests, defeating the multiplexing.

Prefer regular non-persistent connections:

// Good with ProxySQL — let ProxySQL manage persistence
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => false,  // no persistent connections from PHP side
]);

Verifying It Works

Check connection counts on the MySQL side:

-- Run on MySQL directly
SHOW STATUS LIKE 'Threads_connected';

Before ProxySQL: you'd see a count close to your PHP worker count.
After ProxySQL: you should see a low, stable number matching your max_connections pool config.

Check ProxySQL's connection pool stats:

-- Run in ProxySQL admin (port 6032)
SELECT hostgroup, srv_host, srv_port, status, connused, connfree, connok, connERR
FROM stats_mysql_connection_pool;

Check query routing stats:

SELECT hostgroup, schemaname, username, digest_text, count_star, sum_time
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;

This shows you exactly which queries are running, how many times, and how long they take in aggregate — a free slow query log, basically.

Read/Write Splitting (Bonus)

Once you have ProxySQL set up, adding read/write splitting is straightforward. Define query rules to route SELECTs to a read replica (hostgroup 1) and everything else to the writer (hostgroup 0):

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES 
  (1, 1, '^SELECT.*FOR UPDATE', 0, 1),  -- SELECT FOR UPDATE goes to writer
  (2, 1, '^SELECT',            1, 1);   -- All other SELECTs go to readers
 
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Add your replica to hostgroup 1:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections)
VALUES (1, 'mysql-replica', 3306, 20);
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Your PHP app sends all queries to ProxySQL on port 6033 — ProxySQL handles the routing. Zero application changes.

Docker Compose Setup for Development

A complete local stack for testing:

services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: myapp
      MYSQL_USER: appuser
      MYSQL_PASSWORD: strongpassword
    ports:
      - "3307:3306"  # exposed on 3307 so 3306 stays free
 
  proxysql:
    image: proxysql/proxysql
    volumes:
      - ./proxysql.cnf:/etc/proxysql.cnf
    ports:
      - "6033:6033"  # app port
      - "6032:6032"  # admin port
    depends_on:
      - mysql
 
  php:
    image: php:8.3-fpm
    environment:
      DB_HOST: proxysql
      DB_PORT: 6033

proxysql.cnf for the dev environment:

datadir="/var/lib/proxysql"
 
admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
}
 
mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    connect_timeout_server=3000
    monitor_username="root"
    monitor_password="rootpass"
    ping_timeout_server=500
    sessions_sort=true
    monitor_connect_interval=60000
    monitor_ping_interval=10000
}
 
mysql_servers=
(
    { address="mysql", port=3306, hostgroup=0, max_connections=20 }
)
 
mysql_users=
(
    { username="appuser", password="strongpassword", default_hostgroup=0 }
)

Common Gotchas

Session state is not preserved across multiplexed connections. If your PHP code sets SET @variable or USE database and then expects it to persist into the next query on a "different" backend connection — it won't. ProxySQL tracks session variables and replays them when it assigns a connection, but complex session state can cause issues. If you rely heavily on session variables, test carefully.

Transactions are pinned. Once a transaction starts (BEGIN / START TRANSACTION), ProxySQL pins that session to one backend connection for the duration of the transaction. This is correct behavior — you don't want ProxySQL to route your UPDATE to a different connection than your BEGIN. But it means transactions hold a backend connection exclusively, so keep transactions short.

Password hashing. ProxySQL needs to store MySQL user passwords. If your MySQL uses caching_sha2_password (default in MySQL 8.0), you may need to either use mysql_native_password or configure ProxySQL properly. The simplest approach for new setups:

-- On MySQL — use native password for ProxySQL compatibility
ALTER USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'strongpassword';

Admin password. Change the default admin/admin credentials immediately:

UPDATE global_variables 
SET variable_value = 'newadmin:newstrongpassword' 
WHERE variable_name = 'admin-admin_credentials';
 
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

When ProxySQL Is (and Isn't) Worth It

Good fit:

• PHP-FPM with many workers hitting one MySQL
• Multiple app servers all connecting to the same database
• You want read/write splitting without application changes
• You need query routing, mirroring, or rewriting

Not needed:

• Single server, low traffic, few connections
• Already using a connection pool at the app layer (e.g., a long-running Go/Node service with its own pool)
• PgBouncer is a better fit if you're on PostgreSQL

Summary

ProxySQL is a battle-tested solution for the "too many MySQL connections" problem that every PHP application eventually hits. The key steps:

✅ Install ProxySQL and configure backend servers
✅ Add MySQL users to ProxySQL's user table
✅ Set max_connections per backend to limit MySQL load
✅ Point PHP at ProxySQL's port 6033 instead of MySQL directly
✅ Disable persistent connections on the PHP side
✅ Monitor via stats_mysql_connection_pool and stats_mysql_query_digest

The payoff: MySQL sees a small, stable connection pool regardless of how many PHP workers or app servers you add. You can scale your application tier horizontally without touching the database configuration.