gabriel / musehub public
test_db_connection_budget.py python
399 lines 17.1 KB
Raw
sha256:05f91a60a3d1f6d2235c9cd488bc86a6ec4788c5cf1a6e7d2ff633b15f47fc75 fix(db): review of b727ec9d — coord watch uses a session fa… Opus 5.5 minor ⚠ breaking 2 days ago
1 """Database connection budget (musehub#240).
2
3 Every uvicorn worker and every job worker opens its own SQLAlchemy pool, and a blue/green deploy
4 briefly runs two app slots. With 20 + 40 per process that allowed hundreds of connections against a
5 database that accepts about 80, and a burst of concurrent page requests exhausted it: Postgres refused
6 new connections and pages returned 5xx.
7
8 The budget tests pin the invariant: the deployment's worst case fits ``settings.db_connection_budget``.
9 The database-backed tests check the behaviour that the invariant relies on, against the test Postgres:
10 a process never holds more than its pool, every session gives its connection back (after success, an
11 exception or a cancellation), and excess concurrency waits and then fails in the application with a
12 pool timeout instead of opening more server connections.
13 """
14 from __future__ import annotations
15
16 import asyncio
17 import contextlib
18 import re
19 import time
20 from collections.abc import AsyncGenerator
21 from pathlib import Path
22
23 import pytest
24 import sqlalchemy.exc
25 from sqlalchemy import text
26 from sqlalchemy.ext.asyncio import AsyncEngine
27
28 import musehub.db.database as database
29 from musehub.config import settings
30
31 _ENTRYPOINT = Path(__file__).resolve().parent.parent / "entrypoint.sh"
32
33
34 def _uvicorn_workers_default() -> int:
35 m = re.search(r"UVICORN_WORKERS:-(\d+)", _ENTRYPOINT.read_text())
36 assert m, "entrypoint.sh must give UVICORN_WORKERS a default"
37 return int(m.group(1))
38
39
40 def _web_pool() -> int:
41 return settings.db_pool_size + settings.db_max_overflow
42
43
44 def _wire_pool() -> int:
45 return settings.db_wire_pool_size + settings.db_wire_max_overflow
46
47
48 def _job_pool() -> int:
49 return settings.db_worker_pool_size + settings.db_worker_max_overflow
50
51
52 def _job_children() -> int:
53 """The worker's analysis and prebuild subprocesses, each with its own engine, alive beside the worker's pool."""
54 import musehub.intel_child as intel_child
55 import musehub.prebuild_child as prebuild_child
56
57 return sum(m._POOL_SIZE + m._MAX_OVERFLOW for m in (intel_child, prebuild_child))
58
59
60 #: The migration connection ``alembic upgrade head`` holds while the new slot starts.
61 _MIGRATION_CONNECTIONS = 1
62
63
64 # ── the invariant ───────────────────────────────────────────────────────────────
65
66
67 def test_app_containers_start_no_embedded_job_worker() -> None:
68 """Jobs run only in the dedicated worker container: a job loop inside the web container let a cold code-intelligence index compete with
69 page requests (production, 2026-11-14). The budget below counts no job worker per app slot; recount it if that changes."""
70 assert not re.findall(r"python3? -m musehub\.worker", _ENTRYPOINT.read_text())
71
72
73 def test_worst_case_fits_budget_during_blue_green() -> None:
74 """Two app slots (uvicorn workers each, a web pool and a reserved wire pool per worker), the worker container (its pool and its
75 analysis/prebuild children) and the deploy's migration connection."""
76 slot = _uvicorn_workers_default() * (_web_pool() + _wire_pool())
77 worst = 2 * slot + _job_pool() + _job_children() + _MIGRATION_CONNECTIONS
78 assert worst <= settings.db_connection_budget, (
79 f"blue/green worst case {worst} connections exceeds db_connection_budget "
80 f"{settings.db_connection_budget}"
81 )
82
83
84 def test_pools_are_bounded_and_fail_fast() -> None:
85 assert settings.db_pool_size >= 1 and settings.db_max_overflow >= 0
86 assert settings.db_worker_pool_size >= 1 and settings.db_worker_max_overflow >= 0
87 assert 0 < settings.db_pool_timeout <= 10, "overload must fail fast, not tie requests up"
88 assert settings.db_wire_pool_size >= 1 and settings.db_wire_max_overflow >= 0
89 assert 0 < settings.db_wire_pool_timeout <= 60, "a push may wait for its reserved connection, but not forever"
90
91
92 def test_every_wire_route_uses_the_reserved_pool() -> None:
93 """Pool selection is by route class: every refs/push/fetch route, as mounted on the app, is a WirePoolRoute (and no page route is)."""
94 from fastapi.routing import APIRoute
95
96 from musehub.api.routes.wire import router as wire_router
97 from musehub.main import app
98
99 import inspect
100
101 import musehub.main as main_module
102
103 assert "init_db(wire_pool=True)" in inspect.getsource(main_module), "the web app must create the reserved wire pool"
104 wire_paths = {r.path for r in wire_router.routes}
105 assert {"/{owner}/{slug}/refs", "/{owner}/{slug}/push/mpack-presign", "/{owner}/{slug}/fetch"} <= wire_paths
106 def api_routes(routes: list[object]) -> list[APIRoute]: # FastAPI keeps included routers lazy (original_router), so walk them
107 found: list[APIRoute] = []
108 for r in routes:
109 if isinstance(r, APIRoute):
110 found.append(r)
111 elif (inner := getattr(r, "original_router", None)) is not None:
112 found.extend(api_routes(list(inner.routes)))
113 return found
114
115 mounted = api_routes(list(app.routes))
116 assert len(mounted) > 200, f"only {len(mounted)} routes found: the walk missed the included routers"
117 assert sum(isinstance(r, database.WirePoolRoute) for r in mounted) == len(wire_router.routes)
118 for route in mounted:
119 endpoint_module = getattr(route.endpoint, "__module__", "")
120 assert isinstance(route, database.WirePoolRoute) == (endpoint_module == "musehub.api.routes.wire"), (route.path, endpoint_module)
121
122
123 # ── wiring ──────────────────────────────────────────────────────────────────────
124
125
126 @pytest.mark.asyncio
127 async def test_init_db_uses_web_pool_unless_overridden(monkeypatch: pytest.MonkeyPatch) -> None:
128 seen: list[dict[str, object]] = []
129 real = database.create_async_engine
130
131 def capture(url: str, **kwargs: object) -> AsyncEngine:
132 seen.append(kwargs)
133 return real(url, **kwargs)
134
135 async def no_schema_check(*_: object) -> None:
136 return None
137
138 monkeypatch.setattr(database, "create_async_engine", capture)
139 monkeypatch.setattr("musehub.db.schema_check.assert_schema_matches_orm", no_schema_check)
140 monkeypatch.setattr(settings, "database_url", "postgresql+asyncpg://u:[email protected]:1/never-connected")
141 try:
142 await database.init_db(wire_pool=True)
143 await database.close_db()
144 await database.init_db(pool_size=1, max_overflow=1)
145 assert database._wire_engine is None, "a job process gets no wire pool"
146 finally:
147 await database.close_db()
148 assert len(seen) == 3, "web process: web + wire engines; job process: one engine"
149 assert (seen[0]["pool_size"], seen[0]["max_overflow"]) == (settings.db_pool_size, settings.db_max_overflow)
150 assert seen[0]["pool_timeout"] == settings.db_pool_timeout
151 assert (seen[1]["pool_size"], seen[1]["max_overflow"]) == (settings.db_wire_pool_size, settings.db_wire_max_overflow)
152 assert seen[1]["pool_timeout"] == settings.db_wire_pool_timeout
153 assert (seen[2]["pool_size"], seen[2]["max_overflow"]) == (1, 1)
154
155
156 @pytest.mark.asyncio
157 async def test_job_worker_initialises_its_own_allocation(monkeypatch: pytest.MonkeyPatch) -> None:
158 import musehub.worker as worker
159
160 calls: list[dict[str, object]] = []
161
162 class _Stop(Exception):
163 pass
164
165 async def fake_init_db(**kwargs: object) -> None:
166 calls.append(kwargs)
167 raise _Stop
168
169 monkeypatch.setattr(database, "init_db", fake_init_db)
170 with pytest.raises(_Stop):
171 await worker.run()
172 assert calls == [{"pool_size": settings.db_worker_pool_size, "max_overflow": settings.db_worker_max_overflow}]
173
174
175 # ── behaviour against Postgres ──────────────────────────────────────────────────
176
177
178 @pytest.fixture
179 async def bounded_db(monkeypatch: pytest.MonkeyPatch) -> AsyncGenerator[AsyncEngine, None]:
180 """The production engine (``init_db``), pointed at the test database, with a short pool timeout."""
181 from tests.conftest import _TEST_DATABASE_URL
182
183 monkeypatch.setattr(settings, "database_url", _TEST_DATABASE_URL)
184 monkeypatch.setattr(settings, "db_pool_timeout", 2)
185 monkeypatch.setattr(settings, "db_wire_pool_timeout", 2)
186 await database.init_db(wire_pool=True)
187 try:
188 yield database.get_engine()
189 finally:
190 await database.close_db()
191
192
193 async def _server_backends(states: tuple[str, ...] = ()) -> int:
194 """Client connections to the test database, other than this query's own."""
195 from tests.conftest import _TEST_ENGINE
196
197 sql = ("SELECT count(*) FROM pg_stat_activity WHERE datname = current_database() "
198 "AND backend_type = 'client backend' AND pid <> pg_backend_pid()")
199 if states:
200 sql += " AND state = ANY(:states)"
201 async with _TEST_ENGINE.connect() as conn:
202 return int((await conn.execute(text(sql), {"states": list(states)} if states else {})).scalar_one())
203
204
205 def _request_session(route: object = None) -> contextlib.AbstractAsyncContextManager[database.AsyncSession]:
206 """``get_db`` exactly as FastAPI drives a dependency with ``yield``, for a request matched to ``route`` (None: a page route)."""
207 from starlette.requests import HTTPConnection
208
209 return contextlib.asynccontextmanager(database.get_db)(HTTPConnection({"type": "http", "route": route}))
210
211
212 async def _settled(engine: AsyncEngine) -> None:
213 """Wait (bounded) for returned connections to finish resetting."""
214 for _ in range(50):
215 if engine.pool.checkedout() == 0 and await _server_backends(("active", "idle in transaction")) == 0:
216 return
217 await asyncio.sleep(0.1)
218
219
220 @pytest.mark.asyncio
221 async def test_concurrent_requests_never_exceed_the_pool(bounded_db: AsyncEngine) -> None:
222 limit = _web_pool()
223 before = await _server_backends()
224 peak_checked_out = peak_server = 0
225 done = asyncio.Event()
226
227 async def request() -> None:
228 async with _request_session() as session:
229 await session.execute(text("SELECT pg_sleep(0.3)"))
230
231 async def sample() -> None:
232 nonlocal peak_checked_out, peak_server
233 while not done.is_set():
234 peak_checked_out = max(peak_checked_out, bounded_db.pool.checkedout())
235 peak_server = max(peak_server, await _server_backends() - before)
236 await asyncio.sleep(0.05)
237
238 sampler = asyncio.create_task(sample())
239 await asyncio.gather(*(request() for _ in range(3 * limit)))
240 done.set()
241 await sampler
242 assert peak_checked_out == limit, f"the pool should fill to {limit}, peaked at {peak_checked_out}"
243 assert peak_server <= limit, f"{peak_server} server connections for a pool of {limit}"
244
245
246 @pytest.mark.asyncio
247 async def test_excess_concurrency_times_out_in_the_app_not_the_server(bounded_db: AsyncEngine) -> None:
248 limit = _web_pool()
249 before = await _server_backends()
250 hold = asyncio.Event()
251 held = 0
252
253 async def occupy() -> None:
254 nonlocal held
255 async with _request_session() as session:
256 await session.execute(text("SELECT 1"))
257 held += 1
258 await hold.wait()
259
260 holders = [asyncio.create_task(occupy()) for _ in range(limit)]
261 while held < limit:
262 await asyncio.sleep(0.02)
263 t0 = time.monotonic()
264 with pytest.raises(sqlalchemy.exc.TimeoutError):
265 async with _request_session() as session:
266 await session.execute(text("SELECT 1"))
267 waited = time.monotonic() - t0
268 assert await _server_backends() - before <= limit
269 hold.set()
270 await asyncio.gather(*holders)
271 assert 1.5 <= waited < 5, f"pool timeout should be ~settings.db_pool_timeout (2 s), took {waited:.1f}s"
272
273
274 @pytest.mark.asyncio
275 async def test_connection_returned_after_a_normal_request(bounded_db: AsyncEngine) -> None:
276 async with _request_session() as session:
277 await session.execute(text("SELECT 1"))
278 await _settled(bounded_db)
279 assert bounded_db.pool.checkedout() == 0
280 assert await _server_backends(("idle in transaction",)) == 0
281
282
283 @pytest.mark.asyncio
284 async def test_connection_returned_after_an_exception(bounded_db: AsyncEngine) -> None:
285 with pytest.raises(ZeroDivisionError):
286 async with _request_session() as session:
287 await session.execute(text("SELECT 1"))
288 1 / 0
289 await _settled(bounded_db)
290 assert bounded_db.pool.checkedout() == 0
291 assert await _server_backends(("idle in transaction",)) == 0
292
293
294 @pytest.mark.asyncio
295 async def test_connection_returned_after_a_cancelled_request(bounded_db: AsyncEngine) -> None:
296 started = asyncio.Event()
297
298 async def slow_request() -> None:
299 async with _request_session() as session:
300 await session.execute(text("SELECT 1"))
301 started.set()
302 await session.execute(text("SELECT pg_sleep(30)"))
303
304 task = asyncio.create_task(slow_request())
305 await started.wait()
306 await asyncio.sleep(0.2)
307 task.cancel()
308 with pytest.raises(asyncio.CancelledError):
309 await task
310 await _settled(bounded_db)
311 assert bounded_db.pool.checkedout() == 0, "a cancelled request must give its connection back"
312 assert await _server_backends(("active", "idle in transaction")) == 0, "the server-side query must not keep running"
313
314
315 async def _hold_web_pool(limit: int, route: object = None) -> tuple[list[asyncio.Task[None]], asyncio.Event]:
316 """Hold ``limit`` connections of the pool ``route`` draws from (None: the web pool) until the returned event is set."""
317 hold = asyncio.Event()
318 held = 0
319
320 async def occupy() -> None:
321 nonlocal held
322 async with _request_session(route) as session:
323 await session.execute(text("SELECT 1"))
324 held += 1
325 await hold.wait()
326
327 holders = [asyncio.create_task(occupy()) for _ in range(limit)]
328 while held < limit:
329 await asyncio.sleep(0.02)
330 return holders, hold
331
332
333 @pytest.mark.asyncio
334 async def test_wire_sessions_come_from_the_reserved_pool(bounded_db: AsyncEngine) -> None:
335 """With every web-pool connection held, a wire-route session is still served at once, from the wire engine."""
336 from musehub.api.routes.wire import router as wire_router
337
338 holders, hold = await _hold_web_pool(_web_pool())
339 try:
340 t0 = time.monotonic()
341 async with _request_session(wire_router.routes[0]) as session:
342 await session.execute(text("SELECT 1"))
343 assert session.bind is database._wire_engine
344 assert database._wire_engine is not None and database._wire_engine.pool.checkedout() == 1
345 assert time.monotonic() - t0 < 1.0
346 assert bounded_db.pool.checkedout() == _web_pool(), "the wire session must not have used the web pool"
347 finally:
348 hold.set()
349 await asyncio.gather(*holders)
350
351
352 @pytest.mark.asyncio
353 async def test_wire_request_succeeds_while_page_traffic_holds_the_web_pool(bounded_db: AsyncEngine) -> None:
354 """End to end through the app: GET /refs (auth dependency + endpoint, one shared session) answers while the web pool is exhausted.
355 Before the reserved pool this waited for the web pool and failed with a pool timeout (HTTP 500), the staging push failure of 2026-11-14."""
356 from httpx import ASGITransport, AsyncClient
357
358 from musehub.main import app
359
360 saved = dict(app.dependency_overrides)
361 app.dependency_overrides.clear()
362 holders, hold = await _hold_web_pool(_web_pool())
363 try:
364 async with AsyncClient(transport=ASGITransport(app=app), base_url="https://localhost:1337") as client:
365 t0 = time.monotonic()
366 resp = await client.get("/nobody-wire-pool/no-such-repo/refs")
367 waited = time.monotonic() - t0
368 assert resp.status_code == 404, resp.text
369 assert waited < settings.db_pool_timeout, f"the wire request waited {waited:.1f}s: it queued behind the web pool"
370 assert bounded_db.pool.checkedout() == _web_pool()
371 finally:
372 hold.set()
373 await asyncio.gather(*holders)
374 app.dependency_overrides.update(saved)
375
376
377 @pytest.mark.asyncio
378 async def test_pool_timeout_answers_503_with_retry_after_not_500(bounded_db: AsyncEngine) -> None:
379 """When even the reserved pool is saturated, the client is told to come back (503 + Retry-After), the signal ``muse push`` retries on;
380 a bare 500 would read as a server bug and fail the push outright."""
381 from httpx import ASGITransport, AsyncClient
382
383 from musehub.api.routes.wire import router as wire_router
384 from musehub.main import POOL_BUSY_RETRY_AFTER_SECONDS, app
385
386 saved = dict(app.dependency_overrides)
387 app.dependency_overrides.clear()
388 web_holders, web_hold = await _hold_web_pool(_web_pool())
389 wire_holders, wire_hold = await _hold_web_pool(_wire_pool(), wire_router.routes[0])
390 try:
391 async with AsyncClient(transport=ASGITransport(app=app), base_url="https://localhost:1337") as client:
392 resp = await client.get("/nobody-wire-pool/no-such-repo/refs")
393 assert resp.status_code == 503, resp.text
394 assert resp.headers["Retry-After"] == str(POOL_BUSY_RETRY_AFTER_SECONDS)
395 finally:
396 web_hold.set()
397 wire_hold.set()
398 await asyncio.gather(*web_holders, *wire_holders)
399 app.dependency_overrides.update(saved)
File History 5 commits
sha256:05f91a60a3d1f6d2235c9cd488bc86a6ec4788c5cf1a6e7d2ff633b15f47fc75 fix(db): review of b727ec9d — coord watch uses a session fa… Opus 5.5 minor ⚠ 2 days ago
sha256:6bfed5e4f138a4efcd06b05b721a3a725865bbf3987488c7c687c00ebbaa07a7 Answer a DB pool timeout with 503 + Retry-After, not 500 Opus 5.5 patch 2 days ago
sha256:b727ec9dbf506d6228433c29ef6eac7b3a233cd974fda36b8f3b06398bc69fd9 Reserve a DB pool for the wire routes so page bursts cannot… Opus 5.5 minor ⚠ 2 days ago
sha256:0e417fd1f8f54348dd85a8773fec7ca16c21f464b4478fd995fd0ea191a80090 fix(deploy): web containers run no background job worker; j… Opus 5.5 2 days ago
sha256:efd76c389b3e14781ef11c102e3a52f41e9f076a0c5bcc4a1dc7118cc0352a9b fix(db): bound per-process connection pools to the database… Opus 5.5 minor ⚠ 11 days ago