Connection Pooling and Capacity Planning

Four app servers, a pool of twenty connections each, and no trouble. Then capacity planning moves the fleet to twenty servers with the same configuration, and the deploy rolls them all at once. Three hundred of the four hundred connections are refused, requests fail with errors that never appeared in staging, and the database is not even busy.

The server's ceiling here is max_connections = 151. Nothing about the new fleet is wrong in isolation; each server's pool is correctly sized against the work that server does. What nobody counted is that connection limits are per server and pools are per process, so the ceiling has to cover the sum of every pool in the fleet plus the humans and the batch jobs.

A connection is a thread and a set of buffers the server allocates before you send anything

MySQL here runs thread_handling = one-thread-per-connection, so a connection is an operating system thread with a stack. On top of that the server keeps per-connection buffers, and several of them are allocated when the session needs them rather than when it connects. Reading the settings is the only way to know what your server actually reserves:

SHOW VARIABLES WHERE Variable_name IN ('thread_stack','net_buffer_length',
  'read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size',
  'binlog_cache_size','tmp_table_size','max_allowed_packet','thread_cache_size');

thread_stack           1048576
net_buffer_length        16384
read_buffer_size        131072
read_rnd_buffer_size    262144
sort_buffer_size        262144
join_buffer_size        262144
binlog_cache_size        32768
tmp_table_size        16777216
max_allowed_packet    67108864
thread_cache_size            9

The floor is the stack plus the network buffer: 1.02 MiB per connection, and 151 of them is 153 MiB before any query runs. A connection that sorts, joins and scans pays more, because sort_buffer_size, join_buffer_size and read_rnd_buffer_size are allocated per operation and released after it. Count the worst ordinary case as 1.67 MiB and the ceiling at 252 MiB.

Then look at the two settings that are caps rather than reservations. tmp_table_size is 16 MiB and max_allowed_packet is 64 MiB, and the server allocates neither at connect time. That is what makes them dangerous: a hundred connections that each build one in-memory temporary table at the cap is 1.6 GiB that no connect-time arithmetic predicts. Use SELECT ... FROM performance_schema.memory_summary_global_by_event_name when you want the measured figure instead, and remember that it reports the queries that ran, not the workload that could.

A pool in front of MySQL caps the cost, it does not remove it

Pooling exists because opening a connection is expensive for the client: a TCP handshake, an authentication round trip, and TLS negotiation if you use it. Keeping twenty sockets open and reusing them turns that into a one-time payment. What it does not do is move the allocation to the client. Every pooled connection is still a live session on the server with its thread and its buffers, whether or not it is executing anything.

That is the whole trade. A pool converts an unbounded number of short-lived connections into a bounded number of long-lived ones. The bound is the part you have to choose, and the choice has two constraints pulling in opposite directions:

Constraint	What it means in practice
The server's ceiling	Sum of every pool in the fleet, plus replication and admin, must fit under max_connections
The workers' concurrency	A pool smaller than the number of workers that actually run queries at once turns queueing into latency

The first constraint produced the outage above. Four servers with a pool of twenty is 80 connections against 151, which works. Twenty servers with a pool of twenty is 400 against 151, which does not, and the failure arrives all at once because a rolling deploy makes every instance connect at the same moment. Reserve part of max_connections for what is not your application: the on-call engineer, the backup job, one connection per replication channel. A pool that leaves no room for a human during an incident is too large.

When the limit is reached the client gets an error, not a queue

The server does not hold your connection attempt and wait for a slot. It refuses it, and the error tells you which limit you hit. Here is a real one. The account is limited to three concurrent connections, three are held open, and the fourth attempt arrives:

CREATE USER 'pooluser'@'127.0.0.1' IDENTIFIED BY 'poolpass' WITH MAX_USER_CONNECTIONS 3;
GRANT SELECT ON m6lab.* TO 'pooluser'@'127.0.0.1';

ERROR 1226 (42000): User 'pooluser' has exceeded the 'max_user_connections' resource (current value: 3)

The failure is immediate and total: there is no retry loop inside the server, so a client without one fails the request. The counter is the interesting part. Connection_errors_max_connections was still 0 after that refusal, because it counts refusals by the global max_connections ceiling, not by a per-account resource limit:

Aborted_connects                    19
Connection_errors_max_connections    0
Max_used_connections                20
Threads_connected                    6
Threads_created                     29

If your alerting reads Connection_errors_max_connections while your application is capped with MAX_USER_CONNECTIONS, the application can be failing to connect while the counter stays flat. The per-account limit is visible in information_schema.processlist grouped by user, where the three sessions show up and the fourth never arrives. The global ceiling produces ERROR 1040 (HY000): Too many connections, and that one does move the counter. I did not raise max_connections to reproduce it on a shared server; the per-account limit exercises the same refusal path without changing a setting other people are using.

The queue lives in your process, and the timeout that surfaces is yours

Since the server refuses rather than queues, all the queueing happens in the client. A pool of size N hands out N connections and holds the rest of the requests, so a pool that is too small does not produce errors, it produces latency. That is a harder failure to diagnose because the database looks healthy: no errors, low CPU, and requests that take longer than the sum of their queries.

The shape is easy to reproduce without a driver. Six jobs, each of which holds a connection for one second, run behind a pool of three:

seq 1 6 | xargs -P 3 -I{} bash -c 'run_one {}'

job 1: started +0.00s, finished +1.19s
job 2: started +0.00s, finished +1.19s
job 3: started +0.00s, finished +1.19s
job 4: started +1.20s, finished +2.37s
job 5: started +1.20s, finished +2.37s
job 6: started +1.20s, finished +2.37s

Three jobs run and three wait 1.2 seconds for a slot, and the batch takes 2.37 seconds for work that takes 1.19 seconds at full width. Nothing failed. Each waiting request looked exactly like a slow query in a trace, and the fix is a pool size change rather than a query rewrite. Every pool library has the same two knobs here: a maximum size and a timeout on waiting for a slot. SQLAlchemy's QueuePool takes pool_size, max_overflow and pool_timeout, and when the wait exceeds pool_timeout the checkout fails instead of blocking. No MySQL client library is installed here, so that measurement is the queue rather than a driver's timer, and the timer exists so a saturated pool fails at the pool instead of becoming a request that never completes.

Two more pool settings matter on MySQL. wait_timeout is 28,800 seconds here, and the server closes an idle connection when it expires, so a pool must either ping on checkout or recycle connections older than that; SQLAlchemy's pool_recycle does the second. And a pool that resets connections on return is what stops a leaked transaction from poisoning the next borrower, because the reset issues a rollback that removes the locks and the snapshot with the uncommitted data.

Idle in transaction is a connection that keeps costing you after the request ends

The failure that pooling makes easier is a connection that is checked out of the pool, idle, and inside a transaction. It holds row locks and an open snapshot, the server sees it as a sleeping session, and the pool sees it as in use. Here is one, with the holder on the left and a writer waiting on the right:

waiting_pid  waiting_lock_mode  blocking_pid  blocking_lock_mode  waiting_age_s  waiting_query
       6617  X,REC_NOT_GAP              6482  X,REC_NOT_GAP                 2  UPDATE orders SET amount_cents=1 WHERE id=1

id    user  command  idle_seconds  trx_id  trx_age_s  trx_rows_locked
6482  root  Sleep               4  282676          4                1

Connection 6482 has been idle for four seconds with a four-second-old transaction and one row locked, and connection 6617 has been waiting on it for two of those seconds. The blocked writer gave up when its own timeout expired:

ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction

That is a different error from a pool timeout, and telling them apart is most of the diagnosis. ERROR 1205 is the server saying it waited the configured innodb_lock_wait_timeout for a row lock. A pool timeout is your client saying it waited for a slot. The first points at the transaction in sys.innodb_lock_waits; the second points at the pool size. Both look like "the database was slow" in the request log, and only one is fixed by touching the database.

Size the pool from the workers, not from the ceiling the server gives you

The number of connections a process can use is bounded by the number of queries it runs at the same time, and that is bounded by its worker count. Two arithmetic steps, both from numbers you already have:

pool_size = workers that run queries concurrently
fleet     = sum(pool_size * instances) + replication + admin headroom
fleet < max_connections

Twenty servers with eight workers each is 160 connections against a 151 ceiling, which fails the second line before you write any code. Twelve instances with a pool of eight is 96, which leaves 55 connections of headroom on this server and is the kind of number that survives a burst. If your workers each run two queries concurrently, multiply before you sum; if they run none concurrently, the pool can be smaller than the worker count and the extras will simply queue.

A pool larger than the concurrency it serves does not make anything faster. The server has one thread per connection, so connections waiting on a lock are not work in progress; they are committed memory and scheduler pressure. The measured cost of one idle pooled connection is small, and the measured cost of 400 active ones is an outage. Pick the smallest pool that keeps your workers busy, then watch its queue wait, because that number says whether the choice was right.

Show the solution and the reasoning

The third connection fails with ERROR 1226 (42000): User '...' has exceeded the 'max_user_connections' resource (current value: 2). Threads_connected rose to include the two sessions, and Connection_errors_max_connections stayed where it was, because it counts refusals by the global ceiling and a per-account limit is enforced against the account. That is the practical trap: an outage with a flat graph. Watch Threads_connected as a fraction of the limit and group processlist by user to see which account is at its own cap.

For the per-connection estimate, separate reservation from cap. thread_stack plus net_buffer_length is the floor, about 1 MiB per connection and 100 MiB for 100 connections. Add sort_buffer_size, join_buffer_size, read_rnd_buffer_size and binlog_cache_size for a connection actively sorting and joining, and the estimate roughly doubles. tmp_table_size and max_allowed_packet are not allocated at connect at all; they cap what one query can consume, which is why a hundred concurrent queries each building a 16 MiB temporary table is a 1.6 GiB event no connect-time arithmetic predicts. performance_schema memory instrumentation gives the measured figure instead, which is a record of the past rather than a guarantee about the future.

The second session receives ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, and while it is blocked, sys.innodb_lock_waits names the connection it is waiting behind and the lock mode involved. That is the discriminator. A pool timeout has no blocking transaction, because nothing on the server is holding anything; the request never got a connection. Logging the error code with the request separates them, and logging the blocking connection id points at the code path that opened a transaction and went to sleep holding it.