Why Adding App Servers Didn't Raise Our Breach Point

When a service starts throwing errors under load, the usual fix is to add app servers. In our typical-dbsat-v1 study we did exactly that, going from 2 to 3 to 4 m5.large app servers in front of one db.r5.large MySQL 8.0 database. The load where errors began didn't move.

This post covers what we measured, why it happened, where CWM's public engine got it wrong, the first holdout that failed, and the one that passed.

What we measured

Each setup ran an ascending load ladder (300 → 500 → 700 → 900 … RPS total) twice. The first rung with errors in each rep was:

Doubling the app tier did not raise the breach point. The errors also didn't build up gradually. They stayed at 0% for minutes, then jumped to 5–14% within a single minute, while the failed requests still returned quickly. That isn't how CPU saturation looks. It looks like a hard limit being hit. In one of the 4× breaches, database CPU was only about 55%.

Full results: cwm-bench PR #29.

The cause: max_prepared_stmt_count

MySQL has a server-wide cap on how many prepared statements can exist at once: max_prepared_stmt_count. On this setup it was 16,382. A follow-up diagnostic run with 4 app servers showed the count climbing from 14,164 to 16,382 of 16,382. MySQL errno 1461 ("Can't create more than max_prepared_stmt_count statements") started in that same minute. Over the next five minutes, errno 1461 fired 24,672 times (4,476 + 5,722 + 6,297 + 6,090 + 2,087). The app reported these as HTTP 500s.

Because the cap applies to the whole database server, more app servers don't help. More app servers mean more connections, and each connection holds its own prepared statements, so scaling out can actually reach the limit sooner. (One caveat from the study: we confirmed the server-wide cause, but we couldn't identify which queries created the statements, because MySQL error numbers weren't available per endpoint.)

Where CWM's engine was wrong

CWM's public engine didn't previously model the prepared-statement limit, so it couldn't show that the database would run out of prepared-statement slots before the extra app servers helped.

The first fix failed (H0)

Our first model assumed a fixed number of prepared statements per connection: about 59. We preregistered a holdout to test it (PR #47), and it scored H0. In both reps the error onset came after the predicted point, by about 3 minutes in one rep and 18 minutes in the other. The measured count per connection at the predicted crossing was 25.8 and 44.9, not 59.1. The count doesn't track connections alone, so a fixed-ratio model doesn't work.

We published that result as it came out and kept the model internal.

The refit, and a holdout that passed

The model was refit so the prepared-statement count builds up with elapsed time, requests served and open connections. We then ran an independent preregistered holdout at a cap that wasn't used in the fit, 24,630 (PR #49, merged; head commit f0e1c2286f6f9b57f34021a5185f70da49e28d6c).

The holdout passed. In both reps, the first errno 1461 landed in the predicted 700 RPS rung.

Given the measured rung start times, CWM predicted the load step in which MySQL first hits its prepared-statement limit, for this topology, cap and schedule. The cap and schedule were chosen using earlier development runs.

Scope: 2× m5.large + db.r5.large, MySQL 8.0, max_connections 500, cap 24,630.

What it doesn't show: exact timing. The limit was hit 383 s and 286 s earlier than forecast, and timing wasn't part of the pass criteria. The holdout also doesn't validate error volume or any other topology. At onset, the database was not limited by connections (about 470 of 500) or by CPU (about 81–87%), which matches the cap as the cause.

This prepared-statement limit model is now in CWM. It's included by default as an extra result in MySQL simulations, so you can see the modeled load step where the cap is hit before deploying or changing infrastructure.

See /accuracy for how this fits with CWM's other validated claims.

Practical takeaway: check the database before you scale out

If adding app servers doesn't move your breach point, check these before you add more:

1. Compare Prepared_stmt_count with max_prepared_stmt_count. Run SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count'; and SHOW VARIABLES LIKE 'max_prepared_stmt_count';. If the count is close to the cap, adding app servers can make things worse if they open more connections or create more prepared statements.
2. Look for errno 1461 in the database errors. If your app reports them as generic 500s, start classifying MySQL errors separately.
3. Review statement caching in your driver or ORM. Check whether statements are closed (Com_stmt_close), how large the per-connection cache is, and whether variable-length IN (...) lists are creating a new statement for each shape.
4. Count connections, not just servers. Total prepared statements grow with pool size × app servers.
5. Simulate the change first. Model the extra servers and higher traffic before deploying or changing infrastructure.

FAQ

Why didn't adding app servers increase the RPS my service can handle? In our study, the bottleneck was a server-wide MySQL limit, max_prepared_stmt_count, not app CPU. Every extra app server added connections that held prepared statements, so the breach stayed between 500 and 900 RPS with 2, 3 or 4 servers.

What is MySQL errno 1461? It means MySQL can't create more prepared statements because the max_prepared_stmt_count limit has been reached. Apps often report it as a generic HTTP 500.

What does the error onset look like? It's a sudden step. In our runs, error rates went from 0% to 5–14% within one minute, while latency stayed low.

Does CWM model the prepared-statement limit? Yes. It's now in CWM as a default extra result for MySQL simulations. It's validated by one preregistered holdout on 2× m5.large + db.r5.large, MySQL 8.0, max_connections 500, cap 24,630, where it predicted the right load step in both reps.

How accurate is the timing? We don't claim exact timing. In the passing holdout, the limit was hit 383 s and 286 s earlier than forecast. Only the load step was scored.

Did the model ever fail? Yes. An earlier fixed-per-connection version scored H0 in PR #47, because the statements per connection (about 26–45) were well below the assumed 59.

Should I just raise max_prepared_stmt_count? It can buy headroom, but fix statement churn first by closing statements, sizing caches correctly, and normalizing variable query shapes. Then simulate the new setup before deploying.