The read database is falling behind, so you scale the thing that reads from it. More app instances, more workers per instance, and because each of those wants to talk to the database you bump max_connections up so nobody gets turned away. It feels right, more capacity on both ends, and for a while the graphs even nod along. Then the p99 starts climbing, and it keeps climbing, and the throughput you added all those connections to buy never really shows up. You have more connections open to MySQL than you have ever had, and reads are slower than when you had a tenth of them.
I didn’t believe it either until I watched the number go the wrong way, so I built a small harness and made it happen on purpose.
The problem
A connection to MySQL is not free, and it is not just a socket. Every connection is a server-side thread, and every thread that is actually running a query wants a CPU, a slice of the buffer pool, and its turn on the same internal locks everyone else is holding. You have a fixed number of cores. Once the number of queries running at the same time passes that, the extra ones are not doing work, they are waiting, and worse, they are making the ones that are working slower by fighting them for the same resources.
So “let more callers in” quietly becomes “let more callers contend.” The naive shape, one database connection per concurrent caller, means the concurrency you push at MySQL is unbounded by anything except how many clients you happen to have. The database has no say in it. And the moment offered concurrency crosses the core count, you stop buying throughput and start buying latency.
Every client gets a connection
The setup is a single MySQL 8.0.46, pinned to 4 CPUs, with a 200,000-row table: an indexed primary key id, a non-indexed val, and a payload column to give the rows some weight. The read is a range scan that has to do a little real work rather than a trivial point-lookup, otherwise the server never breaks a sweat and there’s nothing to see:
SELECT id, val FROM reads_test
WHERE id BETWEEN ? AND ? AND val >= ?
ORDER BY val DESC LIMIT 50
A single one of those, run serially with nobody else around, takes about 2.6ms. That’s the floor. Now the experiment: spin up C worker threads, give each its own dedicated MySQL connection, and have all of them run that query as fast as they can for 8 seconds. Then do it again for the next C. One connection per busy caller, exactly the shape you get when every app worker holds its own.
Here’s throughput as the connection count climbs from 1 to 512:
Throughput vs. connection count (4 cores)
The peak sits at 4 connections, which is not a coincidence, it’s the core count. After that every connection you add makes the number go down. It’s a gentle decline, 1137 down to 720, so if you only ever look at throughput you might shrug this off as a rounding error and keep bumping max_connections. That would be a mistake, because throughput is not where this hurts.
The tail is where it hurts
Throughput barely moved. The p99 moved like a rocket. Same runs, but plotting the 99th-percentile latency at each connection count:
p99 latency vs. connection count
8 milliseconds to 1.7 seconds. That’s a 211x blow-up on p99 while throughput dropped a mere 1.58x. This is the whole point and it’s why the throughput graph lies to you: the database is doing very nearly the same amount of work per second at 512 connections as at 4, it’s just that any given query now waits behind hundreds of others for its turn on four cores. Nobody is dropped, everybody is late. The average hides it because most of the mass is still moving, but the reads at the tail, the ones your slowest users actually feel, went from imperceptible to a page that visibly hangs.
Why more connections is slower
Four cores can run four queries at once. That’s the ceiling on real work, and I hit it at 4 connections, which is exactly where throughput peaked. Everything past that is a query that’s ready to run but has nowhere to run, so it sits in a runnable queue and the OS shuffles all of them on and off the cores in turns. Each turn costs a context switch, each switch cools the caches the previous query warmed, and every one of them is still holding buffer-pool latches and contending on the same internal mutexes while it waits. You’ve turned four busy cores into four cores that spend a growing slice of their time managing a crowd instead of answering queries.
One honest note on the shape of this, because the harness earns its keep by being honest. My first version of the query was a pure aggregate with no sort, and on that workload throughput stayed almost dead flat as I added connections and only the tail blew up. That’s a real result, but it undersells the story, because a pure CPU-bound aggregate keeps MySQL pegged near saturation no matter what and there’s no headroom to lose. Adding the ORDER BY val DESC gives each query a real filesort to do, which is closer to what actual reads look like, and that’s the version where you can watch throughput itself bend back down past the core count. The tell was always the tail. The throughput dip is the same disease showing up on the graph people actually watch.
A small pool, in front
Here’s the part that feels backwards. Keep all 512 callers. They still exist, they still want to read, none of them go away. But instead of 512 connections to MySQL, put a fixed pool of P connections in the middle and make every caller borrow one, run its query, and hand it back. The callers queue in the application, cheaply, on a semaphore, instead of queueing inside the database on latches and cores. The database only ever sees P queries at once, and if you set P near the core count, it sees exactly as much concurrency as it can actually turn into work.
Same 512 offered clients, but now behind a bounded pool:
p99 at 512 clients: direct vs. a bounded pool
The pool of 8 answers the same 512 callers at a 42.1ms p99 and 957 QPS. Direct, those callers got 1714.6ms and 720 QPS. Fewer connections, 41 times lower tail latency, and more throughput, which recovers 84% of the absolute peak the whole system ever reached. And notice the gradient: the tighter the pool, the better it does, right down to 8. The pool of 64 is barely better than going direct, because a pool of 64 lets 64 queries contend, which is the same crowd on the same four cores. The number that matters was never how many clients you have. It’s how many of them you let touch the database at once.
The takeaway
Reads don’t get faster because you opened more connections. Past the core count, another connection doesn’t add capacity, it just adds one more query fighting the same four cores, and that fight shows up as tail latency long before it shows up as throughput. The fix isn’t a bigger max_connections, it’s a smaller number of connections shared behind a pool, sized somewhere near the cores the database actually has, with everybody else waiting cheaply in the app instead of piling into the engine.
Two things to remember. Watch the p99, not the average, because this failure is nearly invisible in the mean and brutal at the tail. And when a read tier is falling behind, the instinct to let more callers through is usually the exact thing making it slower. Size the pool to the cores the database has, not to the number of callers waiting in front of it.
The harness, the queries, and every number here are on GitHub if you want to run it. These are laptop numbers on a database pinned to four cores, so read the ratios and the shapes, not the absolute QPS, your hardware will land somewhere else.
Comments