test_db_connection_budget.py
python
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