📁 Volume II: Laravel Performance Tuning Kit

🔄 Topic 19: Database Proxy (ProxySQL)

Eliminate TCP handshake overhead. Reuse database connections.

"Every Laravel request opens a new connection to MySQL.
TCP handshake. Authentication. Connection setup.
This takes 1-3ms per request.
ProxySQL sits between Laravel and MySQL, pooling connections.
Your app connects to ProxySQL in 0.1ms. ProxySQL reuses existing MySQL connections.
Thousands of requests share dozens of persistent connections."

⚠️ THE CONNECTION OVERHEAD

Laravel opens a new database connection for every request (unless you're using Octane). This means 1-3ms of TCP handshake + authentication per request. For 10,000 requests per minute, that's 10-30 seconds of pure overhead. ProxySQL eliminates this completely.

🔴 The Problem: One Connection Per Request

WITHOUT PROXYSQL (Laravel → MySQL directly) ═══════════════════════════════════════════════════════════════════ Request 1: ┌─────────────────────────────────────────────────────────────────┐ │ Laravel → TCP Handshake (1ms) → Auth (0.5ms) → Query → Close │ └─────────────────────────────────────────────────────────────────┘ Request 2: ┌─────────────────────────────────────────────────────────────────┐ │ Laravel → TCP Handshake (1ms) → Auth (0.5ms) → Query → Close │ └─────────────────────────────────────────────────────────────────┘ Request 3: ┌─────────────────────────────────────────────────────────────────┐ │ Laravel → TCP Handshake (1ms) → Auth (0.5ms) → Query → Close │ └─────────────────────────────────────────────────────────────────┘ Each request: 1.5ms overhead × 10,000 requests = 15 seconds wasted daily WITH PROXYSQL (Laravel → ProxySQL → MySQL) ═══════════════════════════════════════════════════════════════════ ProxySQL starts with 10 persistent connections to MySQL (always open) Request 1: Laravel → ProxySQL (0.1ms) → ProxySQL uses existing connection Request 2: Laravel → ProxySQL (0.1ms) → ProxySQL uses existing connection Request 3: Laravel → ProxySQL (0.1ms) → ProxySQL uses existing connection Overhead per request: 0.1ms (local connection to ProxySQL) Daily saving: ~14 seconds of CPU time

THE BOTTOM LINE

ProxySQL reduces database connection latency by 90-95%. Instead of 1.5ms per request, you pay 0.1ms. For high-traffic Laravel apps, this is a massive win.

🔍 What is ProxySQL?

PROXYSQL ARCHITECTURE ═══════════════════════════════════════════════════════════════════ ┌─────────────────────────────────────────────┐ │ PROXYSQL │ │ ┌───────────────────────────────────────┐ │ │ │ Connection Pool │ │ │ │ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │ │ │ │ │Conn1│ │Conn2│ │Conn3│ │Conn4│ ... │ │ │ │ └──┬──┘ └──┬──┘ └──┬──┘ └──┬──┘ │ │ │ └─────┼───────┼───────┼───────┼───────────┘ │ │ │ │ │ │ │ │ ┌─────┴───────┴───────┴───────┴─────┐ │ │ │ Query Router & Load Balancer │ │ │ └─────────────────┬─────────────────┘ │ └────────────────────┼──────────────────────────┘ │ ┌──────────────────────────┼──────────────────────────┐ │ │ │ ▼ ▼ ▼ ┌───────────┐ ┌───────────┐ ┌───────────┐ │ MySQL │ │ MySQL │ │ MySQL │ │ Master │ │ Replica1 │ │ Replica2 │ └───────────┘ └───────────┘ └───────────┘ FEATURES: ═══════════════════════════════════════════════════════════════════ • Connection Pooling — Reuse connections across requests • Query Routing — Send SELECT to replicas, writes to master • Load Balancing — Distribute reads across multiple replicas • Query Caching — Cache SELECT results in memory • Firewall — Block dangerous queries (DROP, DELETE without WHERE) • Query Rewriting — Transform queries on the fly

⚙️ ProxySQL Configuration

STEP 1: INSTALL PROXYSQL

# Ubuntu/Debian
wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | sudo apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/ubuntu/$(lsb_release -sc) ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt update
sudo apt install proxysql

# Start ProxySQL
sudo systemctl enable proxysql
sudo systemctl start proxysql

STEP 2: CONNECT TO PROXYSQL ADMIN

# Connect to ProxySQL admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# Default credentials: admin:admin
# Change password immediately!

STEP 3: CONFIGURE MYSQL SERVERS

-- Add MySQL servers to ProxySQL
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'master.db.example.com', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'replica1.db.example.com', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'replica2.db.example.com', 3306);

-- Hostgroup 1 = Writes (Master)
-- Hostgroup 2 = Reads (Replicas)

-- Load configuration
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

STEP 4: CONFIGURE QUERY RULES

-- Send SELECT queries to hostgroup 2 (replicas)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 2, 1);

-- Send everything else (INSERT, UPDATE, DELETE) to hostgroup 1 (master)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^INSERT|^UPDATE|^DELETE', 1, 1);

-- Load rules
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

STEP 5: CONFIGURE MYSQL USERS

-- Add MySQL user for application
INSERT INTO mysql_users (username, password, default_hostgroup)
VALUES ('laravel_user', 'strong_password', 1);

-- Load users
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

⚙️ Laravel Configuration (Point to ProxySQL)

STEP 6: UPDATE .env TO USE PROXYSQL

# .env
# Point Laravel to ProxySQL instead of direct MySQL
DB_HOST=127.0.0.1          # ProxySQL runs locally
DB_PORT=3306               # Standard MySQL port (ProxySQL listens here)
DB_DATABASE=laravel
DB_USERNAME=laravel_user
DB_PASSWORD=strong_password

NO APPLICATION CODE CHANGES!

Laravel doesn't know it's talking to ProxySQL. It thinks it's talking directly to MySQL. The only change is the DB_HOST (pointing to ProxySQL instead of your database server).

📊 Connection Pooling: Before vs After

Metric	Without ProxySQL	With ProxySQL	Improvement
Connections per request	1 (new connection)	0.01 (reused connection)	99% fewer connections
TCP handshake per request	1 (1-2ms)	0 (0ms)	100% eliminated
MySQL authentication per request	1 (0.3-0.5ms)	0 (0ms)	100% eliminated
Total connection overhead (10k req)	~15 seconds	~1 second	93% reduction
Max concurrent MySQL connections	= PHP-FPM processes (e.g., 100)	= ProxySQL pool size (e.g., 20)	5x fewer DB connections

MYSQL CONNECTION LIMIT

MySQL has a max_connections limit (default 151). Without ProxySQL, each PHP-FPM process needs a connection. With 100 PHP-FPM children, you need 100 MySQL connections. With ProxySQL, you only need 20-30 persistent connections, regardless of PHP-FPM count.

⚡ Query Caching (Bonus Feature)

PROXYSQL QUERY CACHE ═══════════════════════════════════════════════════════════════════ Without cache: ┌─────────────────────────────────────────────────────────────────┐ │ Request 1: SELECT * FROM users → MySQL (10ms) │ │ Request 2: SELECT * FROM users → MySQL (10ms) │ │ Request 3: SELECT * FROM users → MySQL (10ms) │ └─────────────────────────────────────────────────────────────────┘ With ProxySQL query cache: ┌─────────────────────────────────────────────────────────────────┐ │ Request 1: SELECT * FROM users → MySQL (10ms) → Store in cache│ │ Request 2: SELECT * FROM users → From ProxySQL cache (0.5ms) │ │ Request 3: SELECT * FROM users → From ProxySQL cache (0.5ms) │ └─────────────────────────────────────────────────────────────────┘ ENABLE QUERY CACHING: ═══════════════════════════════════════════════════════════════════ mysql -u admin -padmin -h 127.0.0.1 -P 6032 SET mysql-query_cache_size_MB = 256; SET mysql-query_cache_ttl = 300000; -- 5 minutes LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

PERFORMANCE GAIN

For repetitive queries (e.g., `SELECT * FROM settings`), ProxySQL query cache can serve responses in 0.5ms vs 10ms from MySQL — a 20x improvement.

📊 Monitoring ProxySQL

# Connect to admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# Check connection pool status
SELECT * FROM stats_mysql_connection_pool;

# Check query statistics
SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;

# Check query cache hits
SHOW STATUS LIKE 'QUERY_CACHE%';

# Check connection pool size
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree
FROM stats_mysql_connection_pool;

# Check slow queries
SELECT * FROM stats_mysql_query_digest WHERE avg_query_time > 1000;

PRODUCTION MONITORING

Monitor these metrics in production:

ConnUsed — Should be < ProxySQL pool size
ConnFree — Should be > 0
Query cache hit rate — Aim for > 50%
Slow query count — Investigate queries > 1 second

⚠️ Downsides of ProxySQL

COMPLEXITY

Another moving part in your infrastructure
Need to configure and maintain ProxySQL
Single point of failure (unless you cluster ProxySQL)
Learning curve for advanced features

MEMORY OVERHEAD

ProxySQL uses memory for connection pool and query cache
Expect 1-2GB RAM for medium-sized apps
More for large query caches

WHEN TO USE PROXYSQL

High-traffic Laravel apps (> 1,000 requests/second)
Apps with many PHP-FPM processes (MySQL connection limit reached)
Apps needing query caching without adding Redis
Read/write splitting without Laravel configuration
Not needed for small apps (< 100 requests/second)

📝 Topic 19 Summary: Database Proxy (ProxySQL)

表 Aspect Without ProxySQL With ProxySQL Gain Connection overhead per request 1.5-3ms 0.1-0.3ms 90% reduction MySQL connections (100 PHP-FPM) 100 20-30 70-80% fewer Query cache available? MySQL query cache (deprecated) Yes (built-in) 20x faster for repeated queries Read/write splitting Laravel config ProxySQL rules Flexible

📌 THE RULE: Use ProxySQL when your Laravel app makes 1,000+ database requests per second or when you're hitting MySQL's max_connections limit. For smaller apps, the complexity isn't worth it. But when you need it, ProxySQL is a game-changer.

NEXT TOPIC PREVIEW

Topic 20: APCu vs Redis — Local shared memory vs network-based cache. When to use APCu for tiny, fast-changing data. When Redis is the right choice.