Configuring MySQL Connection Pooling via MaxScale (MariaDB)

Configuring MaxScale: where the MySQL connection pool breaks

The typical picture looks like this: the application opens a connection for each request and closes it immediately after the response — at peak this is hundreds of short sessions per second.

The backend hits max_connections, the waiting queue at the entrance fills up, some requests are returned as failures, and application handlers keep threads waiting for a freed slot.

Externally this looks like “the database is slow,” although the bottleneck is not the requests but their delivery to the server.

Raising max_connections is a dead end. Each connection reserves memory for the thread stack and sort and join buffers, so the limit hits memory before it hits CPU, and context switching between hundreds of active threads eats up the gain.

The problem is also that most of these connections are idle at any given moment: the application has already opened them, but no request has gone through them yet.

MaxScale sits between the application and MySQL as a proxy layer and reuses backend connections: when idle, client sessions share the same connections to the server instead of occupying their own.

The max_connections parameter in the service section sets how many backend connections the proxy holds simultaneously, multiplex_timeout — how long it waits before returning a freed connection to the common pool.

Configuring MaxScale comes down to making connection queues form at the proxy entrance, where they can be measured, rather than as failures inside the database.

We synchronize timeouts separately: wait_timeout and back_log on the server must be no shorter than connection_timeout and multiplex_timeout in the proxy, otherwise MaxScale hands out a connection that the backend has already closed, and the client gets an error out of nowhere. Let's look at how this is configured.

What a managed connection pool delivers in practice

A connection pool works as a buffer between the application and the database: services hold a small set of channels to the proxy, and the proxy itself holds already established connections to the nodes.

Instead of each process opening its own channel, traffic reuses ready-made ones. On a typical service, the number of simultaneous connections to the database drops severalfold with the same volume of requests.

At peak, the effect is most noticeable. A new request gets a connection instantly, without renegotiation and authentication, so a traffic spike does not turn into a connection queue.

The database sees a steady flow both during quiet hours and at peak — the step of hundreds of simultaneous connections disappears.

A separate point is database node failover. When a replica fails or during planned maintenance, the proxy itself moves new transactions to a live node, and the application continues to work with the same address. No edits to service configurations or restarts are needed.

This removes an entire class of failures: connection limit exceeded errors, latency spikes during mass reconnects, and drops during manual node failover.

The result should be checked by three numbers — how many connections the database sees at peak, how long failover takes, and how many connection errors remain per day. MaxScale covers all these points at the proxy level, without changes to application code.

Balancing options and query routing modes

Routing and balancing solve different tasks: the first determines where a specific query goes, the second distributes load between nodes.

In MaxScale, this is done by modules: readwritesplit parses the query type and routes reads from replicas, readconnroute distributes connections as a whole, and hintrouter lets the application specify the target directly in a query comment.

The choice of mode determines the behavior of transactions, temporary tables, and reads of one's own writes after a write. Separately, it is decided where to keep the connection pool — in the proxy, in the driver, or without one at all.

In practice, modes are combined: writes go to the master, reporting queries go to replicas, and queries sensitive to data freshness are addressed to the master with an explicit instruction.

Before enabling, we run scenarios with schema changes in a session, session variables, and transaction rollback — these are where transparent balancing most often breaks.

MaxScale configuration stages: from load audit to handover

The work is done alongside the production environment, so order matters more than speed here: each step leaves the system in a predictable state. Below is the route from the first measurement to handover to the on-call shift.

1. Database load audit. We capture the real connection profile: daily and weekly peaks, distribution by application, and the share of short and long sessions. The output is a justified pool size instead of a value chosen “by eye.”
2. Routing scheme. In MaxScale, we separate read and write streams, define the replica set and the criterion by which a node is removed from rotation when it falls behind. The scheme is fixed before configuration changes.
3. Limit configuration. We set the limit of simultaneous connections per node, the idle session retention time, and the behavior on overflow — a bounded queue or fast failure.
4. Copy of the production environment. The test bed replicates the topology and node roles, so test results transfer to the production system without adjustments.
5. Load testing. We run the captured profile and separately break the system: shut down a replica, restart a node, drop a connection mid-query. We look at failure duration and traffic recovery speed.
6. Handover to operations. We hand over pool and failure metrics, alert thresholds, the planned failover procedure, and the rollback procedure to the previous scheme.

A stage is closed not after launch, but after the on-call shift has performed a planned failover according to our scheme.

What we hand over: configurations, schemes, procedures

The project is closed not with a report on completed work, but with a set of artifacts your team can use to operate the connection pool without our involvement.

Below is what remains in your hands after MaxScale configuration: working files, schemes, access permissions, and procedures, each with an explanation of why it is needed and what to change in it as load grows.

• Working connection pool configurations — service and listener files with connection limits, wait timeouts, and behavior when the pool is exhausted; parameters are commented so values are changed deliberately.
• Routing schemes — which nodes accept writes, which accept reads, how queries are distributed between replicas, and what happens when a replica falls behind or drops out.
• Database access rules — separate accounts for each service, separation of read and write privileges, password rotation, and the procedure for granting access to a new service.
• Catalog of common failures — “pool exhausted,” “node dropped out of the cluster,” “replica fell behind”: symptom, likely cause, on-call action, recovery indicator.
• Instructions for the on-call shift — step-by-step scenarios for checking node health, manual failover to a standby, and escalation procedure if a scenario does not work.
• Operations procedure — how to change pool limits, when to expand the number of connections, which metrics to review daily, and which thresholds count as alarming.
• Metrics dashboard and alerts — pool utilization, number of connection failures, replication lag; alerts are configured to thresholds, not to the fact of a failure.

Configurations are stored in a repository with change history — rolling back to the previous state takes minutes and does not require blind troubleshooting.

Implementation case study: peak load and connection queues

The client is a payment service with uneven traffic: by evening and on promotion days, concurrent users tripled.

The application held connections to the database directly, each instance had its own pool, and at peak up to nine hundred connections were opened to the server against a ceiling of five hundred.

Waiting for a slot reached four seconds, some requests were dropped by timeout, and the on-call engineer learned about the problem from users.

We moved connection management to a separate layer — MariaDB MaxScale in read-write mode: applications connect to the proxy, and it holds a shared pool to the database, separating incoming sessions from real connections.

Connections are reused between requests, and excess ones are closed on idle instead of hanging until the application restarts.

Metrics before and after

Node failure was tested separately: we shut down a replica under load. The proxy removed it from balancing, live sessions were redistributed — the application saw no disruption, and no manual failover or query retries were required.

How does multiplexing differ from a connection pool in code?

A connection pool in the application solves one task — not opening a connection for every request. A connection lives inside the process, returns to the pool, and is reused by the next handler.

The upper bound is set in advance: one copy of the service holds that many connections, and that same copy consumes them.

Connection multiplexing works at a different level — between the application and the database. The proxy, in our case MaxScale, holds its own set of sessions to the server and distributes them between client connections.

While a request is waiting for a response or the application is busy computing, its server session is given to another client — idle time stops consuming database resources.

The difference becomes noticeable with many copies of the service. Each copy with its own pool holds a separate set of connections: scaling up increases their number linearly, the database hits the maximum number of connections and consumes memory for each one.

Multiplexing breaks this dependency — the number of sessions to the database is determined by concurrent load, not by the number of service copies.

Comparing the approaches does not come down to “one instead of the other.” Multiplexing is incompatible with session-level state: temporary tables, user variables, locks, and transactions require a connection to be bound to the client for the duration of the work.

The proxy allocates a separate session to such a client and, after completion, returns it to general circulation. A pool in the application removes the overhead of opening a connection; multiplexing removes the overhead of its idle time, so in a working scheme they go together.

Common technical questions about connection pooling

A connection pool seems simple until a node drops out in the middle of a transaction. Doubts are usually not about the pool itself, but about behavior during failure and when switching writes to a replica.

Some questions are resolved by proxy configuration and driver parameters; the rest require code changes. Below is what most often comes up before work begins.

Do I need to change the application code?

Usually not: the application connects to the proxy address, and the driver and queries remain the same. Changes are needed if the service itself chooses a node for reads or holds a single connection for the entire lifecycle.

How do reads go to replicas?

By a routing rule: writes go to the master, reads go to replicas. We separately configure the behavior when a replica falls behind so that after a write the application does not receive stale data.

How do I diagnose residual connections and queues?

On the proxy you can see the number of sessions to each node, the queue length, and the wait time in it. A queue that does not clear after failover is a sign that the pool size does not match the load.

Can changes be rolled back?

The configuration is stored in a version control system: returning to the previous set of rules is one command, without restarting the application.

Let's discuss your task: we'll select a pool scheme for your load

A connection pool is not a single parameter but a choice of scheme: how traffic is distributed between the master and replicas, what to do with transactions and long queries, and how the application behaves when a node fails.

To discuss the task concretely, it is enough to send the inputs — we will clarify the rest ourselves and come back with an estimate.

What to send for an initial review:

• client applications: how many there are, how they connect, whether there is an external balancer or connections go directly to the database;
• load profile: share of reads and writes, most frequent and longest queries, peak windows;
• topology: number of replicas, replication lag, requirement to read your own data immediately after a write;
• symptoms: connection drops, growth of sleeping sessions, errors during master failover;
• constraints: maintenance window, availability requirements, access and audit rules.

We refine the selection of the routing scheme: read/write separation, connection balancing, session pinning to a node, timeouts, and behavior during master failover.

In response, you receive an estimate and work plan: target topology, pool and routing configuration, zero-downtime implementation stages, and acceptance criteria — the share of queries sent to replicas, failover time, and the number of dropped connections at peak.

After that — database support: monitoring of pool metrics and planned changes for load growth. Describe your task — we will come back with an estimate and a plan.