The method
Not a stopwatch and not a vibe. This repository learned the lesson twice
(ADR: The search index; ADR: The SQL Server backend, the addendum on the CI
failure that could not explain itself), and the method is the one in
SearchIndexBenchmarkTests: paired rounds, both sides inside each round, the
order alternated, and the median of the per-round differences reported beside
each side's own medians, because sequential timings over a shared network
measure the network and pairing measures the difference.
Every round is what a visitor does, in order: register, sign in, load a listing
page, open a vehicle, read the filter values, place a bid, raise it, and reset.
Twenty rounds, each on a fresh account, against each container's own Azure
address rather than the domain, so the edge is not in either column; the edge's
own share is measured once, separately, at the end.
The request charges are not measured from the client, because the client
cannot see them. They are read from the Cosmos DB container's own Admin
metrics after the rounds, which is the container reporting what the store
charged it for each route, as the median over the requests it saw
(ADR: What the store is actually doing).
The cold starts are the ones each container actually had on the roll that
put this version on it, read from each container's own startup timings, which
count from the process's own start time (ADR: Backends, side by side). Nothing
was restarted to make a number.
"""Measure both stores in one session, the way the search index was measured.
Since 19 October both sites are one web app (ADR: One web app for every .NET process): pass the two public
domains, or the one origin twice, and the origin answers each store by the domain's name; at the bare origin a
side gets the store its X-Yard-Store header names, which this script does not send, so it would read SQL twice.
Paired rounds, both containers inside each round, alternating which goes
first, and the median of the per-round differences alongside each side's own
medians. Sequential timings on a shared network measure the network; pairing
measures the difference (api/TheYard.Tests/SearchIndexBenchmarkTests.cs, and
ADR: Measuring both stores).
Every round is what a visitor does: register, sign in, load a listing page,
open a vehicle, read the filter values, place a bid, raise it, and reset. Each
request is timed from this machine; the Cosmos DB side's request charge per
route is read from that container's own Admin metrics afterwards, because the
container is the only thing that knows what the store charged it.
python scripts/measure_stores.py --sql http://... --cosmos http://... --rounds 20
"""
from __future__ import annotations
import argparse
import http.cookiejar
import json
import statistics
import sys
import time
import urllib.error
import urllib.request
import uuid
from datetime import datetime, timezone
ROUTES = [
"register",
"sign in",
"listing page",
"vehicle page",
"filter values",
"bid write",
"bid raise",
"reset",
]
class Side:
"""One container: its origin, a cookie jar per round, and the timings it produced."""
def __init__(self, name: str, origin: str) -> None:
self.name = name
self.origin = origin.rstrip("/")
self.timings: dict[str, list[float]] = {route: [] for route in ROUTES}
self.opener = None
def fresh_browser(self) -> None:
jar = http.cookiejar.CookieJar()
self.opener = urllib.request.build_opener(urllib.request.HTTPCookieProcessor(jar))
def call(self, method: str, path: str, body: dict | None = None) -> tuple[int, dict | list | None, float]:
data = None if body is None else json.dumps(body).encode()
request = urllib.request.Request(self.origin + path, data=data, method=method)
if data is not None:
request.add_header("Content-Type", "application/json")
started = time.perf_counter()
try:
with self.opener.open(request, timeout=60) as response:
elapsed = (time.perf_counter() - started) * 1000
raw = response.read()
status = response.status
except urllib.error.HTTPError as error:
elapsed = (time.perf_counter() - started) * 1000
raw = error.read()
status = error.code
parsed = None
if raw:
try:
parsed = json.loads(raw)
except json.JSONDecodeError:
parsed = None
return status, parsed, elapsed
def timed(self, route: str, method: str, path: str, body: dict | None = None):
status, parsed, elapsed = self.call(method, path, body)
self.timings[route].append(elapsed)
return status, parsed
def one_round(side: Side, round_number: int) -> None:
side.fresh_browser()
email = f"measure-{round_number}-{uuid.uuid4().hex}@example.com"
status, _ = side.timed("register", "POST", "/api/auth/register", {"email": email, "password": "correct horse"})
assert status == 200, f"{side.name}: register answered {status}"
status, _ = side.timed("sign in", "POST", "/api/auth/login", {"email": email, "password": "correct horse"})
assert status == 200, f"{side.name}: sign in answered {status}"
status, listing = side.timed("listing page", "GET", "/api/vehicles?limit=100&status=live")
assert status == 200 and listing, f"{side.name}: listing answered {status}"
now_ms = int(time.time() * 1000)
vehicle = next(
v for v in listing["vehicles"]
if v["auction_status"] == "live" and v["auction_ends_at"] - now_ms > 5 * 60 * 1000
)
vehicle_id = vehicle["id"]
status, opened = side.timed("vehicle page", "GET", f"/api/vehicles/{vehicle_id}")
assert status == 200, f"{side.name}: vehicle answered {status}"
status, _ = side.timed("filter values", "GET", "/api/facets")
assert status == 200, f"{side.name}: facets answered {status}"
amount = opened["min_next_bid"]
for attempt in range(3):
status, answer = side.timed("bid write", "POST", f"/api/vehicles/{vehicle_id}/bids", {"amount": amount})
if status == 200:
break
side.timings["bid write"].pop()
_, opened, _ = side.call("GET", f"/api/vehicles/{vehicle_id}")
amount = opened["min_next_bid"]
assert status == 200, f"{side.name}: bid answered {status}: {answer}"
raised = answer["vehicle"]["min_next_bid"]
status, answer = side.timed("bid raise", "POST", f"/api/vehicles/{vehicle_id}/bids", {"amount": raised})
assert status == 200, f"{side.name}: raise answered {status}: {answer}"
status, _ = side.timed("reset", "DELETE", "/api/bids")
assert status == 204, f"{side.name}: reset answered {status}"
def percentile(values: list[float], p: int) -> float:
ordered = sorted(values)
rank = max(0, min(len(ordered) - 1, int(-(-p * len(ordered) // 100)) - 1))
return ordered[rank]
def metrics(side: Side) -> dict:
side.fresh_browser()
status, parsed, _ = side.call("GET", "/api/admin/metrics")
return parsed if status == 200 and isinstance(parsed, dict) else {}
def main() -> int:
parser = argparse.ArgumentParser()
parser.add_argument("--sql", required=True, help="origin of the container on Azure SQL Database")
parser.add_argument("--cosmos", required=True, help="origin of the container on Azure Cosmos DB")
parser.add_argument("--rounds", type=int, default=20)
parser.add_argument("--out", default="measure-stores.json")
args = parser.parse_args()
sql = Side("Azure SQL Database", args.sql)
cosmos = Side("Azure Cosmos DB", args.cosmos)
before = {"sql": metrics(sql), "cosmos": metrics(cosmos)}
for round_number in range(args.rounds):
order = [sql, cosmos] if round_number % 2 == 0 else [cosmos, sql]
for side in order:
one_round(side, round_number)
print(f"round {round_number + 1} of {args.rounds} done", file=sys.stderr)
after = {"sql": metrics(sql), "cosmos": metrics(cosmos)}
rows = []
for route in ROUTES:
a, b = sql.timings[route], cosmos.timings[route]
diffs = [y - x for x, y in zip(a, b)]
rows.append(
{
"route": route,
"sql_p50_ms": round(statistics.median(a), 1),
"sql_p95_ms": round(percentile(a, 95), 1),
"cosmos_p50_ms": round(statistics.median(b), 1),
"cosmos_p95_ms": round(percentile(b, 95), 1),
"median_difference_ms": round(statistics.median(diffs), 1),
}
)
charges = {entry["route"]: entry for entry in after["cosmos"].get("store_by_route", [])}
result = {
"measured_at": datetime.now(timezone.utc).isoformat(),
"rounds": args.rounds,
"sql": {"origin": sql.origin, "startup": after["sql"].get("startup")},
"cosmos": {"origin": cosmos.origin, "startup": after["cosmos"].get("startup"), "store": after["cosmos"].get("store"), "charges_by_route": charges},
"rows": rows,
"before": {"sql_window": before["sql"].get("requests", {}).get("window"), "cosmos_window": before["cosmos"].get("requests", {}).get("window")},
}
with open(args.out, "w", encoding="utf-8") as handle:
json.dump(result, handle, indent=2)
print(f"\n{args.rounds} paired rounds, milliseconds, from {sys.platform}\n")
print("| what a visitor does | SQL p50 | SQL p95 | Cosmos p50 | Cosmos p95 | median difference (Cosmos minus SQL) |")
print("| --- | --- | --- | --- | --- | --- |")
for row in rows:
print(f"| {row['route']} | {row['sql_p50_ms']} | {row['sql_p95_ms']} | {row['cosmos_p50_ms']} | {row['cosmos_p95_ms']} | {row['median_difference_ms']:+} |")
print("\nCosmos DB request charge by route (this container's own store log, median per request):")
for route, entry in sorted(charges.items()):
print(f" {route}: {entry['ru_p50']} RU over {entry['operations_per_request']} operations, max {entry['ru_max']} RU, {entry['cross_partition']} cross-partition")
for name, side in (("sql", sql), ("cosmos", cosmos)):
startup = after[name].get("startup") or {}
print(f"\n{side.name} startup: ready {startup.get('ready_ms')} ms, store check {startup.get('schema_ms')} ms, seed {startup.get('seed_ms')} ms / {startup.get('seed_ru')} RU, catalogue {startup.get('catalogue_ms')} ms, bids {startup.get('bids_ms')} ms")
return 0
if __name__ == "__main__":
sys.exit(main())
Live from scripts/measure_stores.py, region *, read from this build at 8003063.
Reading them
The paths that never touch a store are equal, to within noise. The listing
page, the vehicle page and the filter values differ by seven, five and less
than one millisecond over twenty rounds, on requests of 130 to 170 ms. That is
parity by construction, which is what the design record promised: those
requests are served from the in-memory index on both sides and the store is not
on their path.
Every path that touches a store is faster on Cosmos DB, by a margin that is
the geography and the driver rather than the engine. A bid write is 135 ms
against 219, a registration 216 against 319. The Azure SQL Database is in West
US 3 because West US 2 refused to create one (ADR: The SQL Server backend), so
every SQL statement pays a hop between regions that the Cosmos DB account, in
the container's own region, does not. A bid is two operations on both sides (a
read and a write), so the difference is the hop and the driver rather than the
shape of the work. The honest reading is that the second container's store is
next door and the first's is one region away, and the numbers say what that
costs: about 80 ms on a write, about 35 on a sign-in.
The cold starts are a wash and both are dominated by the same thing. Six
seconds against four and a half, and in both cases most of it is the store
check (the connection being established and the identity token acquired) and
the catalogue being expanded to 100,000 in memory, which is the same code on
both sides. The Cosmos DB container's check had been 48,793 ms on 1.0.0.90,
before the identity library was pinned (ADR: A second store on Cosmos DB, and
what it costs, addendum), and that number is the reason cold start is measured
rather than assumed.
The whole session cost the document store 1,096 request units. Twenty
registrations, twenty sign-ins, forty bids and twenty resets, on the free
tier, for $0.00, and it would have been a twenty-seventh of a cent on
serverless.