Connection Overload: When ProxySQL Is Essential
Applications rarely skimp on connections: a typical scenario is to open a connection for one or two queries and close it. While requests are in the dozens, this goes unnoticed; at peak they are in the hundreds, and the database hits the connection limit.
Then a chain starts: some attempts are rejected, some queue on the server side, the application retries failed requests and exhausts the limit completely.
Response time in such a situation grows not linearly but in jumps.
While a free handler is available, latency is steady; beyond the threshold the queue grows faster than traffic arrives, and the 95th percentile shoots up severalfold with the median almost unchanged—on a graph this looks like "the database suddenly became slow." The bottleneck here is not CPU or disk, but constant connection recreation: each one spends on handshake, privilege check, and session allocation.
A local pool inside the application does not remove the problem: with horizontal scaling, service copies multiply their own pool, and the limit is near again. This is where ProxySQL is needed—a layer between application and database.
It holds long-lived connections to the server, gives clients ready ones, separates reads and writes, limits retries, and instead of rejecting, queues the query with a predictable wait limit.
The setup itself is not just installation: routing rules, timeouts, pool sizes for the load profile, and metrics showing that the limit has been pushed back are needed. We break down how this is covered.
What a Managed Connection Pool Gives the Application
Each application worker process holds its own connection to the database. While there are dozens, this is unnoticeable.
As soon as the application is scaled for peak load, the number of processes hits the connection ceiling: queries wait for a channel to open, and the latency tail grows faster than traffic.
A managed connection pool removes this coupling. An intermediate layer accepts thousands of client connections and multiplexes them into a small number of persistent connections to database servers: 2,000 incoming map to 40 outgoing.
The application no longer depends on the process count limit—under peak, throughput grows, while stable response time remains within the same bounds as under calm load.
We deploy ProxySQL and configure routing rules: reads go to replicas, writes to the primary, connections are reused between queries. Node availability is checked constantly, so failover takes seconds and requires no changes to application code.
Client connections do not break during a brief node failure—the pool reconnects, not the application, and transient errors do not reach the user.
We confirm the effect with metrics: share of queries served without waiting for a connection, percentile response time before and after peak, queue depth, and number of failovers.
Comparison of Query Routing Modes
Read/write routing is handled at one of four levels—from an intermediate layer in front of the database to explicit connection selection in code. The level determines where traffic separation logic lives and how much code must be changed when topology changes. Below are the applicability conditions and limitations of each option.
An intermediate layer such as ProxySQL sits between the application and the database: the application sees a single address, while the layer holds the pool, monitors node health, and separates reads and writes itself.
This is the path with the fewest code changes when there are multiple services and different languages.
| Option | When it fits | Limitations |
|---|---|---|
| Intermediate layer in front of the database | Multiple applications and languages, need a shared pool and read/write separation without code changes | Another point of failure and an extra network segment; requires its own monitoring, updates, and connection headroom |
| Built-in pool in the application | One service with predictable load, pool lives within the process | During scaling the number of connections grows proportionally, database limits are calculated manually, node failure is handled by code |
| Driver-side pool | The application already works with the database directly, needs connection reuse and availability checks | The driver does not perform read/write routing; failover remains with the application |
| Manual distribution | One-off tasks: migrations, reports, exports to a specific node | Does not scale, logic spreads across code, a wrong node choice is visible only under real load |
In practice, options are combined: the intermediate layer holds the shared pool and traffic separation, while the built-in pool smooths bursts within the process. Manual distribution is left for one-off jobs—as an exception, not a permanent scheme.
How We Implement a Connection Pool: Stages of Work
ProxySQL in front of the database is not "set and forget." The order of work determines whether the database survives peak without a rise in errors and without changes to application code.
An error in the sequence almost always shows up in production: the pool exists, but connections still hit the primary node's limit. We break down how we build the work.
-
Collecting metrics and load profile. From production servers we gather statistics: how many connections are opened and how long they live, which queries dominate, where wait time grows. This is the baseline for before/after comparison—without it, tuning turns into guesswork.
-
Designing the routing scheme. We determine what goes to the primary, what goes to replicas, and how the application behaves if a replica lags. Failure points and failover order are recorded on the diagram in advance.
-
Configuring the pool and query rewrite rules. We set pool boundaries by user and database, connection lifetime, and the number of server connections per client connection. Rewrite rules bring differing queries to a single form—cache hit rate grows, load on servers drops.
-
Testing under load. We repeat the profile from the first stage on the staging environment and separately break a node: we watch how quickly routing rebuilds and whether application errors increase. We adjust rules based on numbers, not feelings.
-
Going live. We enable gradually—first a share of traffic, then all of it, monitoring latency and connection counts. An exit path remains: traffic can be returned directly if metrics go beyond bounds.
-
Handover and training. We provide configurations, the scheme, rules, and statistics analysis: how to read pool state and what to change as load grows. The team should be able to add a rule or expand the pool itself.
Access Policies, Configurations, and Procedures
Before putting the connection pool into operation, the customer receives a package of artifacts that allows the system to be maintained, handed over between shifts, and restored after failure. Below is the mandatory minimum: without it, ProxySQL lives on manual edits during an incident.
- Configuration files. The main config and per-backend overrides, arranged by environment and under version control, with descriptions: which parameter is responsible for what and what changes when it is edited.
- Traffic flow diagrams. The query path from application through proxy to replicas: read/write separation rules, query parsing, behavior on node failure.
- Accounts and permissions. Separate users for the application, monitoring, and administration, with least-privilege permissions and binding to routing rules; secrets in a vault, not in a file on disk.
- Connection limits. Boundaries per pool, user, host, and backend with justification for each: under which scenario it is reached and how the system degrades further.
- Access policies. Who and from which networks reaches the administrative interface, what applies to client queries, and how the external perimeter is closed.
- Maintenance and on-call procedures. The procedure for editing rules and limits, reloading configuration without dropping live connections, maintenance windows, rollback points, and an on-call instruction: what to look at and what to do first.
We keep documents updated together with the configuration—on the next topology change, the logic is not restored from memory.
Case Study: How Database Throughput Grew
A service with uneven flow: during morning and evening peak hours, the number of simultaneous database requests grew severalfold; between them, load dropped almost to zero.
Each worker process held its own connection to MySQL, and the application hit the ceiling on their number: adding processes was impossible—the database began to degrade for all queries at once.
Before: Response Time Grew with Load
During peak hours, 95th percentile response time grew threefold versus calm hours, and some queries timed out. The main failure class was connections running out, new queries queuing and failing with a connection establishment error.
Throughput grew with the number of processes only up to a certain point; beyond that, only response time grew.
After: A Flat Line at Peak
ProxySQL was placed between the application and the database—a proxy layer with its own connection pool that reuses connections between worker processes.
Routing rules separated reads and writes, analytical queries went to a replica, and health-check removed a problematic node from rotation before errors accumulated on it.
Response time during peak hours stopped diverging with load: the 95th percentile line became flat and close to calm hours.
Throughput grew severalfold on the same hardware, the process count ceiling was removed, and failures of the "connections ran out" class no longer recurred in observations.
How ProxySQL Differs from a Pool Inside the Application
A built-in pool lives inside the application process: connections open on the first query, return to the local set, and are reused by the next call. While there is one service, this is enough—connection establishment overhead is not repeated on every query.
The problem begins with horizontal scaling: each application copy holds its own set, and the number of connections to the database grows with the number of copies, not with real load.
A separate layer in front of the database changes the scheme. The application no longer holds long-lived connections—it gives short sessions, while shared connections to the database server are maintained by an intermediate node with a fixed set size.
Ten service copies and one node produce a predictable number of connections that does not depend on how many instances you started at that moment.
It also removes the pain with restarts: when the application is updated, connections to the database do not break or get recreated in an avalanche.
The second difference is language independence. A built-in pool is configured with the tools of a specific data access library, and each stack has its own parameters, timeouts, and behavior on disconnection.
An intermediate layer sits at the network level, so services in different languages go through the same entry point with the same rules.
The third is a single point of rules. Routing reads to replicas, terminating long queries, prioritizing background jobs, rate-limiting requests—all are described in one configuration file instead of being duplicated in each service's code. This noticeably simplifies incident analysis: you can see whose query it was and where it went.
Practical conclusion: a built-in pool is reasonable as a local connection cache within an instance, while a separate layer is needed when there is more than one service and database interaction rules must be shared. ProxySQL in this role covers both scenarios—connection reuse and centralized rules.
Technical Doubts: Questions About Pool Configuration
Doubts before enabling ProxySQL are usually the same: what happens on node failure, are code changes needed, will the pool withstand a burst. Below are brief answers; during task analysis we check each point against your replication scheme.
What happens when a database node stops responding?
The pool checks node availability and removes the failed one from the output, and the query goes to a live node.
An unfinished connection is closed and does not return to the pool; the client receives an error instead of a hanging response; the maximum wait time and number of retries are set explicitly.
Do application code changes need to be made?
The application connects to the pool as to a regular server—only the address in the connection settings changes. Changes are needed where specific node addresses or custom replica selection logic are hardcoded in the code: we move that to the pool level.
Will the pool withstand a load burst?
Connections are reused, so growth in the number of clients does not produce an avalanche of connections to the database. During a burst, an ingress queue and a limit on simultaneous connections per node are enabled: excess queries wait instead of overwhelming the server.
How are reads and writes separated?
SELECTs go to replicas, writes and everything inside transactions go to the primary. Locking SELECTs are recognized as modifying and also go to the primary, so the application does not receive stale data.
Can access be restricted?
Accounts and permissions are issued at the pool level, and direct access to database ports is closed on the firewall. The log shows who sent which query—incident analysis becomes noticeably easier.
Let's Discuss the Task and Come Back with an Estimate and Plan
For a substantive analysis, what is already at hand is enough: the environment diagram, configs, and statistics for a representative period. The closer the submitted data is to real traffic, the more accurate the estimate and plan—without guesses or eyeballing.
On your side, it is useful to send:
- a diagram of the current environment: applications, backends, database entry points, and traffic paths;
- current proxy configs and routing rules, including privilege distribution and separation of read and write nodes;
- a statistics export for a period with noticeable load: connection peaks, failure share, queue wait time;
- a list of known growth points—what has already broken, where limits were raised manually;
- constraints: maintenance windows, rollback requirements, change approval procedure.
Based on the analysis, you receive an estimate and plan: target pool scheme, stage-by-stage work order, acceptance criteria, and a set of checks that show the result before rolling out to production.
We plan the switchover without service downtime—first we configure ProxySQL on a copy of the environment, run load, then move it to production.
Describe the task and send what you have—we will come back with an estimate and plan.