Why connections are pooled
Before an app can send a query, it needs a connection to the database: a network link that has been opened, secured and logged in. Setting one up can take longer than the query itself, and each open connection costs the database memory.
So apps do not open a connection per request. They open a small number at start-up and keep them. This set is the connection pool.
A request needs the database
It borrows a free connection from the pool.
It runs its query
While it does, nobody else can use that connection.
It gives the connection back
The next request can use it straight away, with no setup.
If every connection is in use, the request waits
It stands in a line inside the app, before the database has heard about it.
Step 4 is where the trouble lives.
One slow query, and everything waits
Here is a pool of 5. Two requests arrive each tick and a normal query takes 2 ticks, so about 4 connections are busy at any time. Comfortable.
From tick 8, one request in five is a slow report that takes 30 ticks.
The pool: 5 connections
Requests waiting for a connection, tick by tick
- quick query
- slow query
- requests waiting
Normal traffic. 2 requests arrive per tick and each query takes 2 ticks. Five connections are plenty. From tick 8, one request in 5 will be a slow report.
- Answered
- 0
- Waiting for a connection
- 0
- Cancelled
- 0
- Database in use
- 0 / 100
Each slow query takes a connection and keeps it. Another arrives before the first finishes, and takes a second. Within a few ticks all five are held by slow queries, and the quick requests, the vast majority, have nowhere to go. They wait in line, and the line grows without limit.
Now read the last number in the animation: the database is using 5 of the 100 connections it could handle. It is close to idle. Its CPU is low. Every dashboard on the database side says "healthy".
The fix: a time limit on queries
A connection that is released on time can never be hoarded. Give every query a timeout. If it runs too long, it is cancelled and its connection goes back to the pool.
The pool: 5 connections
Requests waiting for a connection, tick by tick
- quick query
- slow query
- requests waiting
Normal traffic. 2 requests arrive per tick and each query takes 2 ticks. Five connections are plenty. From tick 8, one request in 5 will be a slow report.
- Answered
- 0
- Waiting for a connection
- 0
- Cancelled
- 0
- Database in use
- 0 / 100
The slow reports now fail after 3 ticks. That is a real cost: those requests get an error. But they are one request in five, and without the limit they were taking down the other four as well.
pool = create_pool(
max_connections=5,
acquire_timeout=2.0, # give up waiting for a connection after 2 s
)
with pool.connection() as connection:
connection.execute("SET statement_timeout = '3s'") # cancel long queries
connection.execute(query)The pool: 5 connections
Requests waiting for a connection, tick by tick
- quick query
- slow query
- requests waiting
Normal traffic. 2 requests arrive per tick and each query takes 2 ticks. Five connections are plenty. From tick 8, one request in 5 will be a slow report.
- Answered
- 0
- Waiting for a connection
- 0
- Cancelled
- 0
- Database in use
- 0 / 100
Other things that help
- Fix the slow query. Usually it is missing an index. See Indexes and B-Trees.
- Give slow work its own pool. Reports and exports use a separate small pool, so they can only starve each other.
- Always return the connection. Code that borrows a connection and forgets to give it back on an error path is a slow leak that ends the same way.
- Do not hold a connection while doing something else, such as calling another service. The connection sits idle and unavailable.
Key takeaways
- A connection pool is a fixed set of open database connections that requests borrow and return.
- When all are in use, new requests wait in the app, before reaching the database.
- A few slow queries can hold every connection and starve everything else.
- The database looks idle while this happens: the queue is in front of the pool.
- Set a query timeout, keep slow work in its own pool, and watch how many requests are waiting for a connection.