forked from mtusi2026/mtusi2026lab2
372 lines
14 KiB
Python
372 lines
14 KiB
Python
#!/usr/bin/env python3
|
|
"""Isolated SQL experiment and logical restore; never resets the application DB."""
|
|
|
|
import argparse
|
|
import hashlib
|
|
import json
|
|
import platform
|
|
import statistics
|
|
import subprocess
|
|
import tempfile
|
|
import threading
|
|
import time
|
|
from datetime import UTC, datetime, timedelta
|
|
from pathlib import Path
|
|
from uuid import UUID, uuid4
|
|
|
|
import psycopg
|
|
from argon2 import PasswordHasher
|
|
from psycopg import sql
|
|
from psycopg.conninfo import make_conninfo
|
|
|
|
from messenger.migrate import migrate
|
|
|
|
ROOT = Path(__file__).resolve().parents[1]
|
|
SIZES = [90_000, 5_000, 2_000, 1_000, 500, 500, 250, 250, 250, 250]
|
|
CHAT_IDS = [UUID(int=100 + i) for i in range(10)]
|
|
USER_IDS = [UUID(int=1), UUID(int=2)]
|
|
START = datetime(2026, 10, 17, tzinfo=UTC)
|
|
QUERY = (
|
|
"SELECT id,chat_id,sender_id,text,created_at FROM messages WHERE chat_id=%s "
|
|
"AND (created_at,id) > (%s,%s) ORDER BY created_at,id LIMIT 51"
|
|
)
|
|
|
|
|
|
def docker(*args, **kwargs):
|
|
return subprocess.run(
|
|
["docker", "compose", *args], cwd=ROOT, check=True, capture_output=True, **kwargs
|
|
)
|
|
|
|
|
|
def percentiles(values):
|
|
ordered = sorted(values)
|
|
|
|
def p(fraction):
|
|
index = (len(ordered) - 1) * fraction
|
|
lower = int(index)
|
|
return ordered[lower] + (ordered[min(lower + 1, len(ordered) - 1)] - ordered[lower]) * (
|
|
index - lower
|
|
)
|
|
|
|
return {"p50_ms": p(0.5), "p95_ms": p(0.95), "p99_ms": p(0.99)}
|
|
|
|
|
|
def initialize(conn):
|
|
encoded = PasswordHasher().hash("Synthetic-research-password-only")
|
|
for i, user_id in enumerate(USER_IDS):
|
|
conn.execute(
|
|
"INSERT INTO users (id,username,display_name,password_hash) VALUES (%s,%s,%s,%s)",
|
|
(user_id, "research_" + str(i), "Research", encoded),
|
|
)
|
|
for chat_id in CHAT_IDS:
|
|
conn.execute("INSERT INTO chats (id,title) VALUES (%s,'Synthetic history')", (chat_id,))
|
|
for position, user_id in enumerate(USER_IDS):
|
|
conn.execute(
|
|
"INSERT INTO chat_members (chat_id,user_id,position) VALUES (%s,%s,%s)",
|
|
(chat_id, user_id, position),
|
|
)
|
|
|
|
|
|
def seed(conn, variant):
|
|
conn.execute("TRUNCATE messages")
|
|
conn.execute("DROP INDEX IF EXISTS messages_history")
|
|
conn.execute("DROP INDEX IF EXISTS messages_by_chat")
|
|
if variant == "single":
|
|
conn.execute("CREATE INDEX messages_by_chat ON messages (chat_id)")
|
|
else:
|
|
conn.execute("CREATE INDEX messages_history ON messages (chat_id,created_at,id)")
|
|
with conn.transaction():
|
|
for chat_id, size in zip(CHAT_IDS, SIZES, strict=True):
|
|
conn.execute(
|
|
"INSERT INTO messages (id,chat_id,sender_id,text,created_at) "
|
|
"SELECT md5(%s || ':' || g::text)::uuid,%s,%s,repeat('m',128),"
|
|
"%s::timestamptz + g * interval '1 microsecond' FROM generate_series(1,%s) g",
|
|
(str(chat_id), chat_id, USER_IDS[0], START, size),
|
|
)
|
|
conn.execute("VACUUM (ANALYZE) messages")
|
|
|
|
|
|
def parameters(i):
|
|
chat_index = 8 if i % 4 == 0 else 0
|
|
boundary = int(SIZES[chat_index] * 0.9) if i % 2 == 0 else 0
|
|
return CHAT_IDS[chat_index], START + timedelta(microseconds=boundary), UUID(int=0)
|
|
|
|
|
|
def resource_samples(stop, samples, container):
|
|
while not stop.is_set():
|
|
try:
|
|
result = subprocess.run(
|
|
["docker", "stats", "--no-stream", "--format", "{{json .}}", container],
|
|
capture_output=True,
|
|
text=True,
|
|
timeout=5,
|
|
check=True,
|
|
)
|
|
raw = json.loads(result.stdout)
|
|
samples.append(
|
|
{
|
|
"at": datetime.now(UTC).isoformat(),
|
|
"cpu": raw["CPUPerc"],
|
|
"memory": raw["MemUsage"],
|
|
}
|
|
)
|
|
except OSError, subprocess.SubprocessError, ValueError, KeyError:
|
|
samples.append({"error": "resource_sample_unavailable"})
|
|
stop.wait(0.25)
|
|
|
|
|
|
def measure(conn, variant, repeat, queries, batches, container):
|
|
seed(conn, variant)
|
|
plans = {}
|
|
for name, params in [
|
|
("large_first", parameters(1)),
|
|
("large_deep", parameters(2)),
|
|
("small_deep", parameters(0)),
|
|
]:
|
|
plans[name] = conn.execute(
|
|
"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + QUERY, params
|
|
).fetchone()[0]
|
|
for i in range(20):
|
|
conn.execute(QUERY, parameters(i)).fetchall()
|
|
stop, samples = threading.Event(), []
|
|
sampler = threading.Thread(
|
|
target=resource_samples, args=(stop, samples, container), daemon=True
|
|
)
|
|
sampler.start()
|
|
try:
|
|
latency = []
|
|
started = time.perf_counter()
|
|
for i in range(queries):
|
|
before = time.perf_counter()
|
|
rows = conn.execute(QUERY, parameters(i)).fetchall()
|
|
latency.append((time.perf_counter() - before) * 1000)
|
|
assert len(rows) > 0
|
|
elapsed = time.perf_counter() - started
|
|
reads = {
|
|
"successful_queries": queries,
|
|
"errors": 0,
|
|
"elapsed_seconds": elapsed,
|
|
"queries_per_second": queries / elapsed,
|
|
**percentiles(latency),
|
|
"raw_latency_ms": latency,
|
|
}
|
|
before_wal = conn.execute("SELECT pg_current_wal_lsn()").fetchone()[0]
|
|
latency = []
|
|
started = time.perf_counter()
|
|
for batch in range(batches):
|
|
records = [
|
|
(uuid4(), CHAT_IDS[0], USER_IDS[0], f"write:{batch}:{i}") for i in range(100)
|
|
]
|
|
before = time.perf_counter()
|
|
with conn.transaction(), conn.cursor() as cur:
|
|
cur.executemany(
|
|
"INSERT INTO messages (id,chat_id,sender_id,text) VALUES (%s,%s,%s,%s)", records
|
|
)
|
|
latency.append((time.perf_counter() - before) * 1000)
|
|
elapsed = time.perf_counter() - started
|
|
wal = conn.execute(
|
|
"SELECT pg_wal_lsn_diff(pg_current_wal_lsn(),%s::pg_lsn)", (before_wal,)
|
|
).fetchone()[0]
|
|
writes = {
|
|
"committed_batches": batches,
|
|
"rows_per_batch": 100,
|
|
"errors": 0,
|
|
"elapsed_seconds": elapsed,
|
|
"rows_per_second": batches * 100 / elapsed,
|
|
"wal_bytes_cluster_delta": int(wal),
|
|
**percentiles(latency),
|
|
"raw_batch_latency_ms": latency,
|
|
}
|
|
finally:
|
|
stop.set()
|
|
sampler.join(timeout=6)
|
|
name = "messages_by_chat" if variant == "single" else "messages_history"
|
|
index_bytes = conn.execute("SELECT pg_relation_size(%s::regclass)", (name,)).fetchone()[0]
|
|
return {
|
|
"index": variant,
|
|
"repeat": repeat,
|
|
"initial_messages": sum(SIZES),
|
|
"plans": plans,
|
|
"reads": reads,
|
|
"writes": writes,
|
|
"index_bytes_after_writes": index_bytes,
|
|
"postgres_resource_samples": samples,
|
|
}
|
|
|
|
|
|
def fingerprint(conn):
|
|
counts = {
|
|
table: conn.execute(
|
|
sql.SQL("SELECT count(*) FROM {}").format(sql.Identifier(table))
|
|
).fetchone()[0]
|
|
for table in ("users", "chats", "chat_members", "messages", "sessions", "schema_migrations")
|
|
}
|
|
digest = hashlib.sha256()
|
|
with conn.transaction(), conn.cursor(name="fingerprint") as cur:
|
|
cur.execute("SELECT id,chat_id,sender_id,text,created_at FROM messages ORDER BY id")
|
|
for row in cur:
|
|
digest.update((json.dumps(row, default=str, ensure_ascii=False) + "\n").encode())
|
|
selected = conn.execute(
|
|
"SELECT id,chat_id,sender_id,text,created_at FROM messages ORDER BY id LIMIT 8"
|
|
).fetchall()
|
|
return {
|
|
"counts": counts,
|
|
"all_messages_sha256": digest.hexdigest(),
|
|
"selected_messages": [[str(value) for value in row] for row in selected],
|
|
}
|
|
|
|
|
|
def restore_check(conn, admin, source, restored, config):
|
|
token_hash = hashlib.sha256(uuid4().bytes + uuid4().bytes).digest()
|
|
conn.execute(
|
|
"INSERT INTO sessions (token_hash,user_id,expires_at) "
|
|
"VALUES (%s,%s,clock_timestamp()+interval '1 hour')",
|
|
(token_hash, USER_IDS[0]),
|
|
)
|
|
before = fingerprint(conn)
|
|
with tempfile.TemporaryDirectory(prefix="lab2-backup-") as temporary:
|
|
archive = Path(temporary) / "snapshot.dump"
|
|
started = time.perf_counter()
|
|
dump = docker(
|
|
"exec",
|
|
"-T",
|
|
"postgres",
|
|
"pg_dump",
|
|
"-U",
|
|
"messenger",
|
|
"-d",
|
|
source,
|
|
"--format=custom",
|
|
"--no-owner",
|
|
"--no-acl",
|
|
)
|
|
archive.write_bytes(dump.stdout)
|
|
dump_seconds = time.perf_counter() - started
|
|
# Deliberately committed AFTER the backup; logical restore must not contain it.
|
|
late_id = uuid4()
|
|
conn.execute(
|
|
"INSERT INTO messages (id,chat_id,sender_id,text) VALUES (%s,%s,%s,'after snapshot')",
|
|
(late_id, CHAT_IDS[0], USER_IDS[0]),
|
|
)
|
|
started = time.perf_counter()
|
|
admin.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(restored)))
|
|
docker(
|
|
"exec",
|
|
"-T",
|
|
"postgres",
|
|
"pg_restore",
|
|
"-U",
|
|
"messenger",
|
|
"-d",
|
|
restored,
|
|
"--no-owner",
|
|
"--no-acl",
|
|
input=archive.read_bytes(),
|
|
)
|
|
restore_seconds = time.perf_counter() - started
|
|
with psycopg.connect(make_conninfo(config, dbname=restored), autocommit=True) as target:
|
|
after = fingerprint(target)
|
|
assert before == after, "Restored snapshot differs"
|
|
assert (
|
|
target.execute("SELECT count(*) FROM messages WHERE id=%s", (late_id,)).fetchone()[
|
|
0
|
|
]
|
|
== 0
|
|
)
|
|
return {
|
|
"snapshot": before,
|
|
"restored": after,
|
|
"equal": before == after,
|
|
"dump_seconds": dump_seconds,
|
|
"restore_database_seconds": restore_seconds,
|
|
"archive_bytes": archive.stat().st_size,
|
|
"archive_sha256": hashlib.sha256(archive.read_bytes()).hexdigest(),
|
|
"post_backup_commits_absent_after_restore": 1,
|
|
"archive_retained": False,
|
|
}
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(description=__doc__)
|
|
parser.add_argument("--output", type=Path, default=ROOT / "evidence/query-plan.json")
|
|
parser.add_argument("--queries", type=int, default=200)
|
|
parser.add_argument("--write-batches", type=int, default=20)
|
|
args = parser.parse_args()
|
|
if args.output.exists():
|
|
raise SystemExit("Choose a new output path; existing evidence will not be overwritten")
|
|
password = (ROOT / "secrets/postgres_password").read_text().strip()
|
|
config = make_conninfo(
|
|
host="127.0.0.1",
|
|
port=55432,
|
|
dbname="messenger",
|
|
user="messenger",
|
|
password=password,
|
|
options="-c timezone=UTC",
|
|
)
|
|
source, restored = "research_" + uuid4().hex, "restored_" + uuid4().hex
|
|
container = docker("ps", "-q", "postgres", text=True).stdout.strip()
|
|
result = {
|
|
"started_at": datetime.now(UTC).isoformat(),
|
|
"scope": "SQL, not HTTP latency",
|
|
"initial_chat_sizes": SIZES,
|
|
"concurrency": 1,
|
|
"warmup_queries_per_trial": 20,
|
|
"client_platform": platform.machine(),
|
|
"trials": [],
|
|
}
|
|
with psycopg.connect(config, autocommit=True) as admin:
|
|
admin.execute(sql.SQL("CREATE DATABASE {}").format(sql.Identifier(source)))
|
|
try:
|
|
dsn = make_conninfo(config, dbname=source)
|
|
migrate(dsn, ROOT / "migrations")
|
|
with psycopg.connect(dsn, autocommit=True) as conn:
|
|
initialize(conn)
|
|
result["postgres_version"] = conn.execute("SELECT version()").fetchone()[0]
|
|
result["durability"] = {
|
|
name: conn.execute(sql.SQL("SHOW {}").format(sql.Identifier(name))).fetchone()[
|
|
0
|
|
]
|
|
for name in ("fsync", "synchronous_commit", "full_page_writes")
|
|
}
|
|
for repeat in range(3):
|
|
for variant in (
|
|
("single", "composite") if repeat % 2 == 0 else ("composite", "single")
|
|
):
|
|
trial = measure(
|
|
conn, variant, repeat + 1, args.queries, args.write_batches, container
|
|
)
|
|
result["trials"].append(trial)
|
|
print("Completed " + variant + " repeat " + str(repeat + 1), flush=True)
|
|
result["backup_restore"] = restore_check(conn, admin, source, restored, config)
|
|
result["finished_at"] = datetime.now(UTC).isoformat()
|
|
result["median_by_index"] = {
|
|
variant: {
|
|
"read_queries_per_second": statistics.median(
|
|
t["reads"]["queries_per_second"]
|
|
for t in result["trials"]
|
|
if t["index"] == variant
|
|
),
|
|
"read_p95_ms": statistics.median(
|
|
t["reads"]["p95_ms"] for t in result["trials"] if t["index"] == variant
|
|
),
|
|
"write_rows_per_second": statistics.median(
|
|
t["writes"]["rows_per_second"]
|
|
for t in result["trials"]
|
|
if t["index"] == variant
|
|
),
|
|
}
|
|
for variant in ("single", "composite")
|
|
}
|
|
args.output.parent.mkdir(parents=True, exist_ok=True)
|
|
args.output.write_text(json.dumps(result, ensure_ascii=False, indent=2) + "\n")
|
|
print("PASS: index comparison and backup restore verified")
|
|
finally:
|
|
for name in (restored, source):
|
|
admin.execute(
|
|
sql.SQL("DROP DATABASE IF EXISTS {} WITH (FORCE)").format(sql.Identifier(name))
|
|
)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|