The Scaling Wall: When Your Master Database Hits 100% CPU
I recall the exact moment a platform I managed jumped from 100 to 10,000 concurrent users. The dashboard stayed green for an hour, then total chaos broke loose. Latency spiked from 50ms to over 2 seconds. We had a standard Master-Slave setup, but the application was hammering the Master with every single query—even basic SELECTs. Our Slave sat at 2% utilization while the Master’s CPU pinned at 98%.
The gut reaction is usually a hardware upgrade. You think about adding more RAM or moving to faster NVMe drives. But that’s just a temporary fix. The real bottleneck is often architectural: your application lacks the intelligence to distribute workloads across the cluster.
The Hidden Cost of Application-Level Routing
Back when I was still manually managing connection pools, I tried to handle Read/Write splitting directly in the PHP code. I defined db_master for writes and db_slave for reads. It worked—until we added a second slave. Then the master failed, a slave was promoted, and I had to manually update config files across 24 microservices. One typo in an IP address took us offline for twenty minutes.
Hardcoding database topology creates three major headaches:
- Configuration Debt: Every node change requires an application redeploy.
- Connection Exhaustion: If 100 app instances each maintain 20 connections to 3 nodes, your DB wastes 6,000 connections just sitting idle.
- Health Blindness: Applications rarely check if a slave is lagging by 30 seconds before sending it a critical query.
Why ProxySQL Beats the Alternatives
Before landing on ProxySQL, I evaluated the usual suspects. HAProxy is a fantastic TCP balancer, but it’s “SQL-blind.” It can’t distinguish a DELETE from a SELECT, making it useless for query-type splitting. MaxScale is powerful, but its licensing shifts made it a difficult sell for our budget-conscious stakeholders.
ProxySQL is different because it’s a high-performance, protocol-aware proxy. It understands the MySQL language. It can cache repetitive queries, rewrite messy SQL on the fly, and route traffic based on granular rules. The application never needs to know the database topology has changed.
A Better Approach: Implementing ProxySQL
In my production environments, I place ProxySQL between the app and the database. To the application, ProxySQL looks like a single, indestructible MySQL server. To the database, ProxySQL looks like a very efficient, well-behaved client.
1. Grouping Your Servers
ProxySQL uses Hostgroups to organize nodes. I typically use Hostgroup 0 for the Master (Writes) and Hostgroup 1 for the Slaves (Reads).
-- Access the ProxySQL admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032
-- Define the cluster nodes
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0, '10.0.0.10', 3306); -- Master
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '10.0.0.11', 3306); -- Slave 1
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '10.0.0.12', 3306); -- Slave 2
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;2. Managing Users Securely
ProxySQL acts as a gatekeeper, so it needs to mirror your backend users. When I’m migrating large user lists or converting CSV data into SQL inserts for these configurations, I use toolcraft.app/en/tools/data/csv-to-json. Since it processes everything in the browser, I don’t have to worry about database credentials leaking to a third-party server.
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('app_user', 'strong_pass', 0);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;3. Routing Traffic with Precision
This is where the logic takes hold. We instruct ProxySQL to send SELECTs to the slaves, with one critical exception: SELECT ... FOR UPDATE. Those must stay on the master to ensure row locking works correctly.
-- Keep locking reads on the Master
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 0, 1);
-- Route all other SELECTs to the Slave pool
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT', 1, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Hard-Won Lessons from Production
Setting up the rules is the easy part. Keeping the system stable under load requires a few extra tweaks.
Solving the “New Record” Race Condition
Users hate creating a profile, hitting save, and then seeing a “404 Not Found” because the Slave they were redirected to is 500ms behind the Master. ProxySQL can monitor Seconds_Behind_Master. I set a strict threshold—usually 10 seconds—to automatically pull lagging slaves out of the read rotation.
UPDATE mysql_servers SET max_replication_lag = 10 WHERE hostgroup_id = 1;
LOAD MYSQL SERVERS TO RUNTIME;The Connection Pooling Win
One of the most dramatic improvements I’ve seen is in connection multiplexing. In one project, we had 2,000 application threads fighting for connections. By putting ProxySQL in front, we condensed those into just 150 persistent backend connections. This change alone dropped Master CPU usage by 18% without rewriting a single line of application code.
Real-Time Query Auditing
The stats database is a goldmine for troubleshooting. You don’t need to enable expensive slow query logs on your database nodes. Instead, query the proxy directly to find your heaviest hitters:
SELECT count_star, sum_time, hostgroup, digest_text
FROM stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 5;Final Thoughts
Moving your load balancing logic out of the application and into ProxySQL is a game-changer for scalability. It provides a clean layer of abstraction. You can perform maintenance, scale your read capacity, or handle a failover at 3 AM without the application ever dropping a single packet. Start with basic routing, and once you see the performance gains, you can explore advanced features like query mirroring and caching.
