Count DB pool timeouts and connection failures instead of inferring them¶
Follow-up to #2566 and EPFL-ENAC/openshift-app-config#30.
Problem¶
The two DB failure modes are both alerted on, but only through gauge thresholds standing in for events that are actually countable:
| failure mode | signal before this change | gap |
|---|---|---|
| QueuePool timeout (pod-local, recoverable) | DbPoolSaturationHigh > 80% for 10m |
a pod can hit 100%, time out real requests and recover inside the window |
FATAL: remaining connection slots (global) |
DbServerConnectionsHigh > 80 for 2m |
dev went 60 → 100 in minutes on 2026-08-31; the burst outran the window |
HighErrorRate is the backstop — both modes end in 500s — but it cannot say
which, and on 2026-08-31 that distinction was the whole diagnosis: pod-local
saturation sat at ~20% while the server itself was full.
Design¶
db.pool.timeouts — a subclassed pool, not handle_error¶
handle_error is the obvious single choke point and it does not work. A
QueuePool checkout timeout never reaches the DBAPI, so no listener on that
event ever sees one. Verified against a live engine (pool_size=1,
max_overflow=0, pool_timeout=1, two concurrent checkouts): sqlalchemy.exc.TimeoutError
propagated and the handle_error listener recorded nothing.
InstrumentedQueuePool subclasses AsyncAdaptedQueuePool and counts in
connect(). That is the single call every checkout makes, so it is exactly one
increment per timed-out request — unlike _do_get, which re-enters itself on
the overflow path and would double-count.
It reaches the engine through _pool_kwargs, which already returns {} for
sqlite. That is what makes the counter a no-op under NullPool/StaticPool,
for free and in the same place as the existing guard.
db.connect.failures — handle_error, narrowed twice¶
handle_error does fire for connect failures, and its ExceptionContext
discriminates. Verified on a live engine:
- connect failure (closed port) →
context.connection is None - query error (
select 1/0) →context.connectionis theConnection
connection is None alone is not enough, and this is the trap in this
change. The real engine sets pool_pre_ping=True, and since #2566 the DB reaps
idle backends after 30 min — so a stale pooled socket is routine. Verified with
pool_pre_ping=True and the pooled backend terminated server-side:
handle_error: connection=None is_pre_ping=True orig=AdminShutdown sqlstate='57P01'
second checkout succeeded, select 1 -> 1
That is the pool healing itself, and the request that follows succeeds.
Counting it would tick the counter all day on a healthy pod and make
increase(...) > 0 useless — the exact alert this change exists to enable.
Skipping is_pre_ping costs nothing during a real outage: when the reconnect
also fails, that attempt fires its own event with is_pre_ping false.
Verified by killing the pooled backend and setting connection limit 0 on the
role in the same breath — the #2566 shape:
connection=None is_pre_ping=True orig=AdminShutdown sqlstate='57P01' <- skipped
connection=None is_pre_ping=False orig=OperationalError ...too many connections for role...
Driven through the shipped listener, that scenario counts
[(1, {'sqlstate': '53300'})], and the healthy reconnect counts nothing.
The listener is registered only when the engine is not sqlite, mirroring
_connect_args / _pool_kwargs.
The SQLSTATE label is derived from message text, and that is a ceiling¶
psycopg exposes no SQLSTATE on connection-establishment errors. Verified
against a real 53300, forced locally with create role ... connection limit 1
and a second login: err.sqlstate and err.diag.sqlstate are both None, and
the pgconn handed to the exception is a FinishedPGconn carrying only
error_message text. libpq has no result object at that point, so there is
nothing else to read. (Query-time errors are unaffected — those carry a real
sqlstate, e.g. 22012.)
So connect_failure_sqlstate() prefers a real sqlstate when one exists, and
otherwise recognises 53300 from the four messages Postgres uses for it:
too many clients alreadyremaining connection slots are reservedtoo many connections for roletoo many connections for database
Anything unrecognised is still counted, labelled unknown — the total is what
says "connections are failing", the label only says which kind.
The ceiling: this is message matching, so a server running with a
non-English lc_messages would label a real 53300 as unknown. All three of
our instances are English. If that ever changes, the alert should move to the
unlabelled total.
DB_POOL_TIMEOUT 30 → 5¶
A pool timeout today means a user watches a spinner for 30 seconds and then
gets an error. Five carries the same information at a sixth of the harm, and
surfaces the counter sooner. Under normal load it changes nothing: measured
peak is ~4 checked_out per pod against a ceiling of 13 (#2566), so nothing
ever waits.
Steps¶
-
InstrumentedQueuePool+db.pool.timeoutscounter (backend/app/db.py), wired through_pool_kwargsso sqlite skips it. -
count_connect_failure+db.connect.failurescounter, registered as ahandle_errorlistener onengine.sync_enginefor non-sqlite only, and narrowed on bothcontext.connectionandcontext.is_pre_ping. -
connect_failure_sqlstatewith the four 53300 messages and theunknownbucket. -
DB_POOL_TIMEOUTdefault 30 → 5 (backend/app/core/config.py), withbackend/.env.exampleand the pinned assertion inbackend/tests/unit/test_db_pool_settings.pyupdated to match. - Tests in
test_db_pool_settings.py: a real exhausted-pool checkout timeout driven throughgreenlet_spawn(the async engine's own wrapper around every pool call), a successful checkout that must not count, the four 53300 messages, theunknownfallback, a realsqlstatewinning over the message, andhandle_errorneither on an open connection nor on a failed pre-ping counting. All three verified failing with the code they cover removed. - Prometheus rules. Deployed as
DbPoolCheckoutTimeoutandDbServerConnectionSlotsExhausted(names below are the plan's drafts):DbPoolTimeoutsandDbConnectionsRefusedonincrease(...) > 0live in EPFL-ENAC/openshift-app-config (overlays/*/monitoring/specific-namespace-alerts.yaml) and are a separate PR now that the metrics exist.
Open¶
- No auth/network split. With psycopg dropping SQLSTATE at connect time,
every non-53300 failure lands in
unknown— an unreachable host and a bad password are indistinguishable on the label. Add markers for28P01/3D000if that distinction is ever wanted; the total already counts them. - Not exercised against a real 53300 in a deployed environment. The label
logic is pinned to the verbatim psycopg message text captured from a forced
local 53300, but the end-to-end path (server full → counter → Prometheus)
will only be confirmed by the next incident, or by deliberately squeezing
dev's
max_connections. - Neither counter counts a failure that is retried and succeeds. For
connections that is deliberate —
pool_pre_pingrecovery is excluded by design. For timeouts there is no retry today, but if one is ever added the counter stops measuring failed requests. DB_POOL_TIMEOUTmay be pinned in the deploy repo. #2566 setsDB_POOL_SIZEexplicitly in dev'sbackend.envandworker.env; ifDB_POOL_TIMEOUTis set in either, this default change is a no-op there. Check both maps when the alert-rules PR lands.