How to test connection pool exhaustion

Send more concurrent requests than the pool has connections and read the responses. Against a pool of two with a 300 ms acquire timeout, four requests gave two 200s after 1.0 s and two 503s reading pool acquire timeout after 300ms. The database answered a direct query during the same second.

Why check this

A pool is the part of a service that fails first under load, and it fails in a way that points at the wrong component. Run this before a release that adds a slow downstream call inside a transaction, after any change to pool size, and when an incident shows request timeouts with a database that looks idle.

The failure it catches is the outage nobody can locate. Every request times out, the database reports no slow queries and no locks, and the cause is one handler that returns on an error path without giving its connection back. After a few of those the pool is empty and stays empty until a restart.

Prerequisites

• Node 22, which bundles node:sqlite over SQLite 3.51.3.
• curl 8.1.2 and a sqlite3 shell, for the client side and for reading the database directly.
• Port 9573 free. Check with netstat -ano | grep 9573 before starting.
• A table to read. Save as schema.sql and create the database with sqlite3 shop.db < schema.sql.

CREATE TABLE orders (id INTEGER PRIMARY KEY, ref TEXT NOT NULL UNIQUE, total INTEGER NOT NULL);
INSERT INTO orders (ref, total) VALUES ('A-1000', 500);

• The service, with a pool of two and an acquire timeout. Save as pool-server.mjs. The /leak route is the bug this page hunts: it answers the request and never releases.

import { createServer } from 'node:http';
import { DatabaseSync } from 'node:sqlite';

const SIZE = 2;              // pool size
const ACQUIRE_TIMEOUT = 300; // ms a request waits for a free connection

const idle = Array.from({ length: SIZE }, () => new DatabaseSync('shop.db'));
const waiters = [];
let inUse = 0;

const acquire = () => new Promise((resolve, reject) => {
  if (idle.length) { inUse += 1; return resolve(idle.pop()); }
  const w = { resolve };
  w.timer = setTimeout(() => {
    waiters.splice(waiters.indexOf(w), 1);
    reject(new Error(`pool acquire timeout after ${ACQUIRE_TIMEOUT}ms`));
  }, ACQUIRE_TIMEOUT);
  waiters.push(w);
});

const release = (db) => {
  const w = waiters.shift();
  if (w) { clearTimeout(w.timer); w.resolve(db); return; }
  inUse -= 1; idle.push(db);
};

createServer(async (req, res) => {
  const url = new URL(req.url, 'http://localhost');
  if (url.pathname === '/stats') {
    res.end(JSON.stringify({ size: SIZE, inUse, idle: idle.length, waiting: waiters.length }) + '\n');
    return;
  }
  let db;
  try { db = await acquire(); }
  catch (err) { res.statusCode = 503; res.end(err.message + '\n'); return; }
  const n = db.prepare('SELECT count(*) AS n FROM orders').get().n;
  if (url.pathname === '/leak') { res.end(`orders=${n} (connection not released)\n`); return; }
  setTimeout(() => { release(db); res.end(`orders=${n}\n`); },
    Number(url.searchParams.get('hold') ?? 0));
}).listen(9573, () => console.log('pool server on 9573, size %d, acquire timeout %d ms', SIZE, ACQUIRE_TIMEOUT));

• The load. Save as burst.sh and make it executable.

#!/bin/sh
# Four concurrent requests at a pool of two, each holding its connection 1000 ms.
for i in 1 2 3 4; do
  curl -s -o body.$i -w "req$i  HTTP %{http_code}  %{time_total}s  " \
    "http://127.0.0.1:9573/orders?hold=1000" > line.$i &
done
sleep 0.15
echo "pool stats mid burst:  $(curl -s http://127.0.0.1:9573/stats)"
echo "read straight from db: $(sqlite3 shop.db 'SELECT count(*) FROM orders;') row"
wait
for i in 1 2 3 4; do cat line.$i body.$i; done

• The hold here is a timer, which stands in for a slow query or a downstream call inside the transaction. A driver that blocks on network I/O behaves the same way from the pool's side, and the numbers below are one machine with no network in them.

Steps

1. 1

   Step 1.

   Start the service and read the pool before any load.

   node --disable-warning=ExperimentalWarning pool-server.mjs & sleep 1.5; curl -s http://127.0.0.1:9573/stats
   

   pool server on 9573, size 2, acquire timeout 300 ms
   {"size":2,"inUse":0,"idle":2,"waiting":0}

   Two connections, both idle, nobody queued. Every number below is read against this baseline.

2. 2

   Step 2.

   Send twice as many concurrent requests as the pool can serve.

   ./burst.sh
   

   pool stats mid burst:  {"size":2,"inUse":2,"idle":0,"waiting":2}
   read straight from db: 1 row
   req1  HTTP 200  1.005757s  orders=1
   req2  HTTP 200  1.009227s  orders=1
   req3  HTTP 503  0.302717s  pool acquire timeout after 300ms
   req4  HTTP 503  0.313112s  pool acquire timeout after 300ms

   Line 1 is the shape of the fault: both connections in use, two requests queued behind them. Line 2 is the part that misleads an incident call, a direct read answering during the same second the service was returning 503. Requests 3 and 4 gave up at the acquire timeout, 300 ms, without ever reaching the database.

3. 3

   Step 3.

   Take two connections on a path that never gives them back.

   for i in 1 2; do curl -s http://127.0.0.1:9573/leak; done && curl -s http://127.0.0.1:9573/stats
   

   orders=1 (connection not released)
   orders=1 (connection not released)
   {"size":2,"inUse":2,"idle":0,"waiting":0}

   Both requests answered with 200 and the pool is empty. Nothing is queued, because nothing is asking yet.

4. 4

   Step 4.

   Send one ordinary request, with no other traffic at all.

   curl -s -w "\nHTTP %{http_code}  %{time_total}s\n" http://127.0.0.1:9573/orders
   

   pool acquire timeout after 300ms
   
   HTTP 503  0.308548s

   One request against an idle machine and an idle database, and it fails. This is the state that never recovers. The burst in step 2 drained for a second; a leak drains until the process restarts.

5. 5

   Step 5.

   Stop the service and confirm the port is free.

   netstat -ano | grep "9573.*LISTENING"
   

     TCP    0.0.0.0:9573           0.0.0.0:0              LISTENING       44072
   TCP    [::]:9573              [::]:0                 LISTENING       44072

   Take the process id from the last column and stop that one process with powershell -Command "Stop-Process -Id 44072 -Force", then run the same command again and expect no output.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | Responses at exactly the acquire timeout | Requests are queuing for a connection, not running a query | Read the pool gauges. The database is not involved yet. | | inUse at the pool size with waiting above zero | Live saturation under load | Shorten the work done while holding a connection before raising the size. | | inUse at the pool size with waiting at zero and no traffic | Connections leaked and were never returned | Find the handler that returns before its release call. | | Failures at the timeout while a direct query answers instantly | The fault is in the pool, not the engine | Take the database off the incident list and look at handler duration. | | Responses slower than the hold but under the timeout | The queue is draining | The pool is small for this load and still working. |

Thresholds

Common mistakes

What to check next

• How to check database locks: the most common reason a pooled connection is held and cannot finish.
• How to check transaction rollback: the error path that returns early and leaks the connection.
• How to check transaction isolation level: what a long open transaction on a pooled connection does to everyone else.
• How to find slow queries: the hold time that decides how large the pool has to be.
• How to test database migration: migrations take a connection too, and run while the service still wants one.

FAQ

How do I test a connection pool without a load tool?

Four concurrent curl calls are enough when the pool is small, as in step 2. Size the test to the pool, not to production traffic: one more concurrent request than the pool has connections is all it takes to make the queue visible.

What should a pool expose for this to be testable?

Size, in use, idle and waiting, on an endpoint the test can poll. Without those four numbers the only symptom is a 503 at the client, and steps 3 and 4 cannot be told apart from ordinary saturation.

Does this result apply to PostgreSQL or MySQL?

The pool behaviour does, since the queue is in the application. The database side does not: this ran against SQLite, which has one writer, and no server-side connection limit was involved. Rerun against your engine before quoting a pool size.

Should the acquire timeout be longer?

A longer timeout converts rejections into slow responses, which is worse when the caller has its own deadline. Keep it below the caller's timeout and treat the rejections as the signal to shorten how long a request holds a connection.

Verified