Load Balancing
From DNS to SQL Routing
Five levels, in increasing order of precision and running cost. And a paradox the brochures never mention: the balancer becomes the point of failure itself.
The load balancer paradox
You install a balancer so that load spreads and a single server failing is no longer an incident. And in doing so, you place in front of everything else a single component every request has to go through. The service now tolerates losing any of its application servers, and no longer tolerates losing the balancer.
The usual answer is to make it redundant in turn — two balancers, a floating address between them — which amounts to stacking a high-availability architecture underneath the balancing one. It is perfectly doable, it is what we deploy most often, but it means two mechanisms to operate instead of one.
That is why the ladder starts at the bottom
The first two levels — round-robin DNS and anycast — add no central component in the request path. DNS and routing do have their own infrastructure, of course, but it is already there and already redundant: these two levels distribute without creating anything new to operate. They distribute without adding anything to the path, therefore without creating anything to make redundant. They are coarse, but they cost nothing in failure surface. The next three are finer and each introduces a piece you will have to look after. So the right reflex is not to start with HAProxy[1], it is to start as low as the requirement allows.
This subject is the direct continuation of our high-availability architectures: balancing answers “how do we serve more traffic than one machine can take”, high availability answers “how do we stay up when a component dies”. They often overlap and neither ever follows from the other.
The 5 levels we deploy
Sorted by decision precision — from "the resolver and the client decide" to "the proxy reads the SQL statement and decides where to send it". Each step gains accuracy and costs in operation.
Two axes not to be confused. The traffic decision level — what the balancer uses to pick a target — and the depth of the health check. They vary independently: keepalived can check a target over HTTP, HTTPS or by script while distribution stays at layer 4 inside the kernel. So moving to HAProxy is not what buys an application-level check — it is what allows a decision per request, which layer 4 cannot make since it never opens the connection.
| Level | Who decides | Central component? | Health check |
|---|---|---|---|
| 1 · Round-robin DNS + several VIPs | The resolver and the client | None | None on the DNS side |
| 2 · Anycast | The routing table | None | None on the network side |
| 3 · IPVS[2] / LVS (layer 4) | The kernel, on the connection | Yes, must be redundant | Provided by keepalived: TCP, HTTP or script |
| 4 · HAProxy (HTTP mode, layer 7) | The proxy, on the request | Yes, must be redundant | Real application request |
| 5 · SQL routing | The proxy, on the statement and replication state | Yes, must be redundant | Replication lag |
1. Round-robin DNS and several VIPs
Instead of a single floating address, you declare two or three — crossed priorities, each node carrying its own under normal operation — and publish them together in one DNS record. The DNS then hands back several addresses; it is the resolver's behaviour, then the client's, that decides which one is tried — some walk the list, others stop at the first.
What makes it unique: no central component, therefore no new point of failure, and if a node dies its VIP migrates to a survivor. The limits are known — DNS tests nothing, client caching delays corrections, distribution is not weighted. The design is covered at level 2 of high availability.
2. Anycast
One address announced from several places: every client is served by the nearest instance[3] in routing terms, which naturally spreads traffic by zone and cuts latency. No client-side configuration, and again no central component.
The trap: distribution is not weighted and the network tests nothing. An instance announcing without answering becomes a silent black hole — see internal anycast.
3. IPVS / LVS — layer 4
Balancing happens in the kernel, on the connection, without ever opening it. You gain weighting — a large server takes more than a small one. IPVS only distributes: keepalived is what checks the targets and removes the failing ones from the table. Performance is excellent because there is nothing to interpret.
Good news for operations: it is the same keepalived that drives IPVS and carries the floating address. You are not adding a tool, you are switching on the other half of the one you already run.
4. HAProxy — layer 7
The proxy reads the request. It can therefore route by URL or header, handle sticky sessions, terminate encryption, and above all genuinely test application health — not merely check that a port answers.
This is the step where you gain the most precision and pay the most in operation: a configuration that lives, certificates to renew, and one more component to make redundant.
5. Databases: routing, not balancing
This is the level apart, and the classic mistake is treating it like the others. In front of a database the problem is not distributing fairly: it is knowing where each statement is allowed to go. Writes must reach the authoritative node; reads may go elsewhere, but not just anywhere.
But before concluding that you need a specialised proxy, there is one question to ask: who separates reads from writes?
The application already does
Many applications use two distinct connections, one to write and one to read. In that case there is nothing left to interpret: the read stream is already identified, it only needs distributing. HAProxy in TCP mode does that very well — and so does IPVS, with even fewer moving parts.
Nobody does
One connection string, everything through the same channel. You then need a proxy that
speaks the database protocol and decides statement by statement: a generic
balancer sees bytes, not the difference between a
SELECT and an
UPDATE. That is ProxySQL[4]'s territory.
And reading the SQL verb is the easy part. Such a proxy must also pin whatever cannot change node mid-flight:
- • an open transaction, which must stay on one node through to
COMMIT— its reads included; - • a read following a write in the same session, or it may hit a replica that has not received the row yet;
- • session state: user variables, temporary tables, prepared statements, transaction in progress;
- • reads that must reach the primary, to be marked explicitly.
From our own experience: a pure read stream, balanced at layer 4
We have balanced a strictly read-only stream with IPVS, with no SQL proxy[5] at all. Because the separation had already been made upstream, all that was left was a distribution problem — and level 3 solves it with a tool we already run. It is exactly the rule at the top of this page in action: start as low as the requirement allows, and only move up a level when you need what that level uniquely provides.
The real risk is not performance
It is sending a read to a lagging replica. The application gets a fast, perfectly formed and stale answer — a record that was just created and "does not exist yet", a balance that has not moved. Nothing fails, nothing alerts, and the bug surfaces days later through support. That is why a serious proxy watches replication lag and pulls out replicas that fall behind: read distribution is not decided on load, it is decided on freshness.
ProxySQL
A MySQL and MariaDB proxy that routes on rules matching the statements themselves, pools connections and knows how to drop a lagging replica. It is what we deploy when the separation has to be transparent to the application.
MaxScale
MariaDB's own proxy, in the same family of functions. We have not tested it in production and we would rather write that: if your context calls for it, we will qualify it before committing to anything.
The layer that really decides remains replication itself: it determines what is routable and how fresh it is. That subject lives on our dedicated site — Galera cluster for synchronous multi-master replication, and replication-manager for orchestrating failover.
Sticky sessions, and why we try to avoid them
A sticky session steers a given client to the same instance — as long as the cookie set by the balancer stays valid and that instance is still available. It is not a guarantee: if the instance goes down, the session is lost anyway. It rescues applications that keep session state in local memory — and many line-of-business applications do.
What it costs
- • Distribution becomes uneven: one instance can accumulate the long sessions
- • Losing one instance disconnects every user attached to it, even though the service stays up
- • Updating a node means waiting for its sessions to expire, or sacrificing them
What we propose first
Move state out of the application — into a database or a shared cache — so any instance can serve any user. It is application work, sometimes modest, and it removes the problem instead of working around it. When that is not possible we add stickiness, but knowing what we are buying.
How we choose
Two or three nodes, moderate traffic
Level 1. Round-robin DNS across several VIPs is enough, adds no point of failure and maintains itself. There is nothing lower; going higher costs without serving.
Web front end, several applications
Level 4. As soon as you need to route by URL, terminate encryption in one place or genuinely test application health, HAProxy earns its keep — and its own twin along with it.
Read-saturated database
Level 5 only if the application does not already separate its connections — otherwise level 3 or 4 will do. And in every case only after checking replication: routing reads to replicas only makes sense if you know how current they are.
What we operate, and what it costs
A balancer is monitored differently from a server. What matters is not only that it answers, it is that it still has something to send traffic to: a perfectly healthy balancer in front of two dead servers returns errors perfectly. So we watch the number of live targets, the actual distribution between them, and the gap between what the configuration intends and what happens.
Design and deployment
On quote, based on €150 excl. VAT per hour. Scope depends on the level chosen, the application, and what your provider allows in terms of movable addresses.
Operation
From €150 excl. VAT per month per server, reduced from the second node of the same set. See the plans.
Frequently asked questions about load balancing
What is the difference between load balancing and high availability?
High availability answers “how do we stay up when a component dies”. Load balancing answers “how do we serve more traffic than one machine can take”. The two often overlap — spreading traffic across several instances incidentally makes the loss of one bearable — but neither follows from the other. A single balancer in front of two servers improves capacity and does not improve availability: it simply moves the point of failure forward.
Does the load balancer itself have to be made redundant?
Yes, as soon as it is the only path to the service, and that is the central paradox of the subject: by adding a component to improve availability, you create a new one that everything else depends on. The usual answer is two balancers behind a floating address, which brings you back to level 2 of our high-availability architectures. Only designs with no central component — round-robin DNS and anycast — escape that obligation, and that is precisely what makes them interesting.
Layer 4 or layer 7: how do you choose?
The question is whether balancing decisions need to take request content into account. At layer 4 you distribute connections without opening them: very fast, very light, and applicable to any protocol. At layer 7 you read the request, which lets you route by URL, handle sticky sessions and genuinely test application health — at the cost of heavier processing and a restriction to protocols the balancer understands.
Is round-robin DNS enough?
For two or three nodes, internal or moderate traffic, an idempotent service with no sticky sessions, yes — and it has an advantage nothing else offers: no central component, therefore no new point of failure. Its limits are known: DNS tests nothing, client caching delays any correction, distribution is not weighted, and some resolvers do not rotate at all. It is often the right first step, rarely the last.
How do you balance the load of a database?
Not like anything else, because the problem is not distributing but routing. Writes must reach
the authoritative node, reads may go elsewhere — and a generic balancer cannot tell a
SELECT from an
UPDATE. You need a proxy that speaks the database
protocol, such as ProxySQL. And the real risk is not performance: it is sending a read to a
lagging replica and serving stale data with nothing to indicate it.
Sticky sessions: how do they work and should you use them?
A sticky session steers a given client to the same instance, as long as the cookie set by the balancer stays valid and that instance is still available. It is not a guarantee: if the instance goes down, the session is lost anyway. It solves the case of applications keeping session state in local memory. But it is a patch: distribution becomes uneven, and the failure of one instance disconnects every user attached to it. Where possible it is better to move state out of the application — into a database or a shared cache — and do without stickiness.