Connection pools and the database connection limit

In lesson 26 you added servers to carry more load. Each server talks to the database. A question follows: "The traffic grows. More servers start. Now many requests fail, yet the database looks calm. What happened?"

A weak answer blames the database and says "buy a bigger one". A strong answer counts. The database accepts only a fixed number of open links from servers. The answer multiplies the number of servers by the links each server keeps, and compares the result with that fixed number.

At SDE-1 level you should know that the number of links is limited. At SDE-2 level you should do the multiplication and name two fixes. At SDE-3 level you should also choose the size of each server's set of links from numbers, and say what a fix costs.

Think about a library with 200 reading desks. To use a desk, a visitor must register and wait for a helper to set it up. That takes time. So a school registers 20 students at 8 am, and they keep their 20 desks all day. Nobody registers twice.

There is one problem. The library has only 200 desks. If 30 schools each keep 20 desks, they want 600. The library says "full" to the last schools, even if the room is quiet.

A database connection

A connection is an open link between your app and the database. It is a TCP link (lesson 7), and after it opens, the app sends many queries over it. A query is a request to the database (lesson 17). Usually, a connection runs one query at a time.

Opening a connection is slow. The two computers set up the link, the app logs in, and the database prepares memory for it. Postgres starts a new process (a running program) for each connection (see Sources). Assumption: opening a connection takes 20 ms, and one query takes 4 ms.

Suppose the app opens a new connection for each request, and the request sends one query. It then takes 20 + 4 = 24 ms, and the database repeats the setup work thousands of times.

A connection pool

A connection pool is a set of connections that the app opens once and keeps open. A request borrows one connection, sends its queries, and returns it. The next request uses the same connection. Two settings matter:

• Pool size: the most connections the pool may hold.
• Wait limit: how long a request may wait for a free connection when all of them are in use. After that time, the request fails.

With a pool, the same request pays 4 ms and not 24 ms.

The connection limit

Each open connection costs the database memory, even when it is idle, which means not in use. So the database has a connection limit: the most connections it accepts. The Postgres documentation says the default is typically 100, and 3 are kept free for administrators (see Sources). A managed database often sets it from the machine size. Check the provider's page.

When the limit is reached, a new connection fails with an error. The Postgres message is often "sorry, too many clients already". When only the slots for administrators are left, it says that the remaining slots are reserved.

A higher limit does not make the database faster. A database has a fixed number of cores (lesson 15), and 200 queries at once share them. So the limit protects the database.

The budget

Each server keeps its own pool. So the connections that all servers want are:

• connections wanted = number of servers x pool size

If one server runs several copies of the app, each copy has its own pool. Then multiply by the number of copies too. Some pools open all their connections at the start. Others open one only when needed. Under a burst, each pool can still grow to its full size, so plan for that.

The budget is the limit minus the connections kept for administrators and other tools. Connections wanted must stay under the budget.

Here is the trap. An autoscaler is a feature of a cloud (lesson 13) that adds servers when the load rises and removes them when it falls. It multiplies the number of servers. A pool size that was safe with 6 servers can be too big with 20. When the limit is reached, the database is not busy. It simply refuses new connections. So the CPU graph of the database looks fine while many requests fail.

So set the pool size from the largest number of servers that can run. The pool size is at most (limit - reserved) / largest number of servers.

How big should a pool be?

A pool is mostly idle, because a request holds a connection for a few milliseconds. The number of busy connections is:

• busy connections = requests per second x seconds that each request holds a connection

Example: 300 requests per second, each holding a connection for 10 ms (0.010 seconds). Busy = 300 x 0.010 = 3 connections on average.

Use the target of lesson 15: plan for 70% busy. Pool size = busy / 0.7, rounded up, plus some room for short jumps in traffic.

A pool that is too small makes requests wait. A pool that is too big wastes the budget. Another cause of an empty pool is code that holds a connection while it waits for something slow, such as a call to another service. Return the connection before slow work.

A connection pooler

Sometimes the budget is still too small, because there are many servers or many copies of the app. A connection pooler is a small server that sits between your app servers and the database. The app servers connect to the pooler as if it were the database. The pooler keeps a small set of real connections to the database and lends them out. PgBouncer is one example (see Sources).

There are two levels of connection:

1. App side: many connections from the servers to the pooler. They are cheap.
2. Database side: few real connections from the pooler to the database.

A pooler has two main modes:

• Session mode: a real connection goes to a client for as long as the client stays connected. Sharing is small.
• Transaction mode: a real connection goes to a client only during a transaction (lesson 21), and then returns to the pooler. Most of the time a client is not running a query, so a few real connections can serve many clients.

A pooler has costs:

• One more stop on the way for each query. Assumption: 1 ms.
• One more server to run. If it stops, every query fails: a single point of failure (lesson 28). Run two.
• In transaction mode, things that need the same connection for a whole session stop working. The PgBouncer documentation lists them: for example, changing a setting with SET, and locks that last for a whole session.

A food-delivery app has a sale at 8 pm. We look at its connections.

Assumptions (made up to teach with, not real traffic):

• The database allows 200 connections. We keep 10 for administrators, so the budget is 200 - 10 = 190.
• Each server keeps one pool of 20 connections.
• Normally 6 servers run. At the sale, the autoscaler can grow to 20 servers.
• A request runs 2 queries of 4 ms each. It holds a connection for 10 ms in total.
• At the peak, each server gets 300 requests per second.
• The database has 16 cores, and each query uses 1 ms of CPU.
• Opening a connection takes 20 ms.

Step 1: why a pool. A request runs 2 queries of 4 ms each, so it needs 8 ms. Without a pool, it also opens a connection first: 20 + 8 = 28 ms. That is 28 / 8 = 3.5 times more than the 8 ms with a pool.

Step 2: the budget.

• 6 servers: 6 x 20 = 120 connections. That is under 190. It uses 120 / 190 = 63% of the budget.
• 9 servers: 9 x 20 = 180. Still under 190.
• 10 servers: 10 x 20 = 200. This is over 190.
• 20 servers: 20 x 20 = 400. That is 400 / 190 = 2.1 times the budget.

So the database supports 190 / 20 = 9.5, which is 9 servers. This answers the predict box. At 20 servers, 9 servers can hold a full pool (180 connections), and the other 11 servers share the last 10. Requests on them fail.

Step 3: the database looks calm. Only the 9 working servers send queries: 9 x 300 x 2 = 5,400 queries per second. Each uses 1 ms: 5,400 ms of CPU per second = 5.4 cores. That is 5.4 / 16 = 34% of the database. The CPU is low, yet more than half of the servers fail.

Step 4: size the pool.

• Busy connections per server = 300 x 0.010 = 3.
• At 70% busy: 3 / 0.7 = 4.3, so 5. We choose 8 to leave room for short jumps in traffic. This is our choice, not a rule.
• A pool of 8 allows 8 / 0.010 = 800 requests per second. The peak is 300, so the pool is 300 / 800 = 37.5% busy.
• 20 servers x 8 = 160 connections. That is under 190. It fits.

The pool of 20 was 20 / 3 = 6.7 times bigger than the need.

Step 5: a pooler. Suppose we want more than 20 servers, or many copies of the app on each server. We add a pooler in transaction mode. The servers keep their pools of 8. That is 160 connections to the pooler, and the database does not see them.

• The real connections are busy only while a query runs. Queries per second = 20 x 300 x 2 = 12,000. Each lasts 4 ms: 12,000 x 4 ms = 48,000 ms = 48 busy connections.
• At 70% busy: 48 / 0.7 = 68.6, so 69. We give the pooler a pool of 80 real connections. That is under 190.

The cost: 1 ms more for each query, so 2 ms more for each request. That is 2 / 10 = 20% of the 10 ms hold time. Also, we now run a pooler.

• A pool solves the slow setup of a connection: 8 ms instead of 28 ms for a request. It costs database memory and a budget to keep. I would use a pool in every app that talks to a database.
• A small pool solves the limit: 160 connections instead of 400. It costs waiting if it is too small. I would change the size when requests wait, or when the number of servers changes.
• A pooler solves the limit for many servers: 80 real connections serve 160 or more. It costs one more stop for each query (1 ms), one more server to run, and some database features. I would add one when servers times pool does not fit the budget even with small pools.

"A bigger pool makes the app faster." In the example, the pool of 20 was 6.7 times bigger than the 3 connections in use. The extra connections were idle and used the budget. The cores of the database (16 here) limit the speed, not the connections.

"The database CPU is only 34%, so the database is fine." The limit is on connections, not on CPU. In the example, 11 of 20 servers could not get a full pool while the CPU was low. Compare the connections with the limit first.

"A pooler removes the limit." It moves the limit. The busy real connections follow the load: 48 in the example. If we plan for 70% busy, the database can have 190 x 0.7 = 133 busy connections. Each request needs 2 x 4 ms = 8 ms, which is 0.008 connections. So 133 / 0.008 = 16,625 requests per second (rounded down). Beyond that, you need less work for each request or a bigger database.

1. What is a connection pool, and why does the database limit connections? In transaction mode, when does a client hold a real connection?
2. A database has a limit of 100 connections, and 3 are kept for administrators. The autoscaler can grow to 12 servers. (a) What is the largest pool size per server? (b) A server gets 200 requests per second, and each request holds a connection for 15 ms. How many connections are busy on average? (c) How many requests per second can a pool of 8 allow?
3. After the autoscaler grows from 8 to 24 servers, the API shows "too many clients" errors, and the database CPU is 20%. Each server has a pool of 10. The limit is 150, with 10 kept for administrators. What do you check, what do you change first, and what is the lasting fix?