Slow at Scale, Worse When Scaled: It Is Usually a Query-Count Problem

Mostafa Dhouib·8 min read

Slow at Scale, Worse When Scaled: It Is Usually a Query-Count Problem

The short answer. Capacity fixes a queue. It does not fix work. If each request does far too much, every server you add pays for that work again, so the bill scales and the latency does not. Profile one request end to end before provisioning anything, count the queries and the redundant outbound calls, and remove them. On a flight-search product that took the median search from 12.8 seconds to 2.8, the ninety-fifth percentile from 29.6 to 7.5, the database queries per search from 149 to 38, and the infrastructure cost per search from 9.5 cents to 2.9, on the same hardware.

There is a particular kind of slow that misleads everybody, and it has a tell.

The product works. Nothing errors. It is just slow, and when you add capacity it does not get meaningfully faster, and the bill goes up in a way that feels disproportionate. Sometimes it even gets worse.

That combination is close to diagnostic. Slow plus unresponsive to capacity plus expensive means the problem is inside one request, not across many of them.

What capacity actually buys

It is worth being exact about this, because the reflex to scale is not stupid, it is just aimed at a different problem.

Adding servers increases how many requests you can handle at once. If requests are arriving faster than you can serve them, they queue, and queueing time shows up as latency. More capacity drains the queue, the queueing time falls, and latency improves. That is a real mechanism and it works.

But it only addresses queueing time. The time a request spends doing its own work is unchanged by the number of machines available to do it. If one search takes eleven seconds of actual work, it takes eleven seconds of actual work on a cluster of fifty machines, and you are now paying fifty times over for the privilege.

So the question to ask before provisioning is simply: how much of this latency is waiting, and how much is working? If the system is slow even when it is not busy, it is working, and capacity is the wrong instrument.

Count the queries

The single most useful number in this kind of diagnosis is the query count for one request. Not the average query time, not the slow query log, the count.

Slow query logs are good at finding the one query that takes four seconds. They are structurally bad at finding a hundred and forty-nine queries that each take eight milliseconds, because none of them is slow. Each one is individually fine. Together they are the whole problem.

On the flight-search product the count was 149 per search. That is not a number anybody designed. It is a number that accumulates: a loop that looks up a related record per iteration, a helper that fetches a configuration row every time it is called, a serialisation step that resolves a reference per field. Each of those is reasonable in isolation and invisible in code review, because the code that causes it does not look like a database call.

Batching and caching took it to 38.

That pattern has a name in most ecosystems, usually some variant of the N+1 problem, and the name is less useful than the measurement. You do not find it by knowing about it. You find it by counting.

Three things a profile tends to find

Repeated reads of the same data. The same configuration, the same lookup table, the same user record, fetched many times within one request because the code paths that need it do not know about each other. The fix is request-scoped caching, and it is usually the largest single win.

Lookups inside loops. One query per item, where one query for all items would do. The fix is batching, and the hard part is noticing, because the loop body usually calls something that calls something that queries.

Serial calls that could be concurrent. Where a request makes several independent outbound calls one after another, total latency is the sum. Run them concurrently, with a bound, and it approaches the slowest one instead. On the flight-search product the provider fan-out was serialised, and parallelising it with a concurrency ceiling was a large part of the tail improvement, from 29.6 seconds at the ninety-fifth percentile to 7.5.

Keep the bound. Unbounded concurrency replaces a latency problem with a rate-limit problem or a connection-pool problem, and those are harder to see.

Measure cost per request alongside latency

This is the part most performance work skips, and it is what makes the result defensible.

Track infrastructure cost per request as a first-class number next to latency. On this engagement it went from 9.5 cents per search to 2.9. That number does two things.

It proves the work was not a trade. A great deal of performance work buys latency with money, by adding caching tiers, bigger instances or more replicas. That is sometimes right, but it should be a stated decision rather than a side effect nobody priced. When latency and cost per request both improve, the work removed waste rather than relocating it.

It also makes the argument for not scaling. "We can make it four times faster for a third of the cost" is a different conversation from "we can make it faster", and it is the conversation that stops someone provisioning a larger cluster next quarter.

Why this work expires if you let it

Removing work is not a permanent change in the way raising a limit is. The queries came back one at a time, through ordinary feature work, and they will do it again. Every new feature that adds a lookup inside a loop puts a few back.

So the count needs a guard. A test that asserts the query count for the main request paths is the cheapest form of this, and it fails loudly the first time a change reintroduces the pattern, at the point where it is one line to fix rather than a quarter of archaeology later.

Without that, the honest statement about this kind of work is that it holds exactly as long as the work stays removed.

Profile the real mix

One caution on generalising from a profile. The numbers above were measured on the actual query mix the product served. A substantially different request shape, in that case far more candidate hubs or much longer date ranges, has its own profile and its own hot spots.

This is not a reason to skip profiling. It is a reason to profile the thing you actually serve rather than a synthetic benchmark that exercises a shape nobody requests.

The order of operations

Profile one request end to end. Count the queries and the outbound calls. Remove the repeated reads, batch the loops, parallelise what is independent with a bound. Measure latency and cost per request together. Put a guard on the count so it stays removed.

Then, if it is still slow when it is busy, scale what is left. By that point you are scaling something worth scaling.

FAQ

Why did adding servers make my slow application worse instead of better? Because capacity only reduces queueing time, and your latency was work time. Every additional server pays the per-request cost again, so the bill scales with the waste while the latency barely moves. The tell is a system that is slow even when it is not busy, which means requests are not waiting for a machine, they are working.

How do I tell whether I have a capacity problem or a per-request work problem? Measure latency when the system is idle. If a single request on an unloaded system is already slow, no amount of capacity will help, because there is nothing queueing. If it is fast when idle and slow under load, you have genuine queueing and capacity is a reasonable instrument.

Why doesn't the slow query log find this? Because the problem is usually not one slow query, it is a large number of fast ones. A hundred and forty-nine queries at eight milliseconds each will not appear in any slow query log, and each one looks reasonable on its own. Count the queries per request instead, which is the measurement that makes the pattern visible.

What causes a request to make a hundred-plus database queries? Three things, usually together: the same data read repeatedly within one request by code paths that do not know about each other, a lookup executed once per item inside a loop, and reference resolution during serialisation. None of them look like database calls at the call site, which is why they survive code review and only show up in a profile.

Should I measure infrastructure cost per request as well as latency? Yes, because it distinguishes removing waste from buying latency with money. If latency improves and cost per request also falls, the work removed work. If latency improves and cost rises, you relocated the problem, which may still be the right call but should be a decision rather than a surprise.

How do I stop the query count creeping back up? Assert it. A test that pins the query count for the main request paths fails the first time ordinary feature work reintroduces a lookup inside a loop, when it costs one line to fix. Performance won by removing work is not durable the way a raised limit is. It holds only as long as the work stays removed.