Files

431 lines
23 KiB
Python

#!/usr/bin/env python3
"""Трек 5.2: план запроса истории и восстановление из backup. Stdlib, Python 3.9+.
Шаги (по умолчанию все): setup → generate → plans → writes → backup.
Работает в ОТДЕЛЬНОЙ БД `research` (рабочая БД messenger не меняется). Команды доступа к
PostgreSQL задаются переменными окружения (по умолчанию — Compose этого репозитория):
SRC_PSQL psql к исходному серверу (docker compose ... exec -T postgres psql -U messenger)
SRC_PG_DUMP pg_dump на исходном сервере (docker compose ... exec -T postgres pg_dump -U messenger)
DST_PSQL psql к НОВОМУ серверу (docker compose ... --profile research exec -T postgres-restore psql -U messenger)
DST_PG_RESTORE pg_restore на новом сервере (... exec -T postgres-restore pg_restore -U messenger)
DUMP_PATH путь файла дампа для pg_dump/pg_restore (/backups/research.dump, bind mount ./backups)
DUMP_HOST_PATH тот же файл с точки зрения хоста (backups/research.dump) — для размера
Результаты: evidence/track2/*.json, *.txt. Секретов и токенов не содержат.
"""
import argparse
import hashlib
import json
import os
import re
import shlex
import statistics
import subprocess
import threading
import time
from datetime import datetime, timezone
from pathlib import Path
ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / "evidence" / "track2"
DC = os.environ.get("COMPOSE", "docker compose --env-file .env")
SRC_PSQL = os.environ.get("SRC_PSQL", DC + " exec -T postgres psql -U messenger")
SRC_PG_DUMP = os.environ.get("SRC_PG_DUMP", DC + " exec -T postgres pg_dump -U messenger")
DST_PSQL = os.environ.get("DST_PSQL", DC + " --profile research exec -T postgres-restore psql -U messenger")
DST_PG_RESTORE = os.environ.get("DST_PG_RESTORE", DC + " --profile research exec -T postgres-restore pg_restore -U messenger")
DUMP_PATH = os.environ.get("DUMP_PATH", "/backups/research.dump")
DUMP_HOST_PATH = Path(os.environ.get("DUMP_HOST_PATH", str(ROOT / "backups" / "research.dump")))
DB = "research"
RESTORED_DB = "research_restored"
VARIANTS = {
# имя: (DDL индекса истории или None, комментарий)
"V0_no_index": (None, "только PK(id): нет индекса по chat_id"),
"V1_chat": ("CREATE INDEX hist_idx ON messages (chat_id)", "btree(chat_id)"),
"V2_chat_created": ("CREATE INDEX hist_idx ON messages (chat_id, created_at)", "btree(chat_id, created_at)"),
"V3_chat_created_id": ("CREATE INDEX hist_idx ON messages (chat_id, created_at, id)",
"btree(chat_id, created_at, id) — выбран в миграции 0001"),
"V4_covering": ("CREATE INDEX hist_idx ON messages (chat_id, created_at, id) INCLUDE (sender_id, text)",
"covering: + INCLUDE(sender_id, text)"),
}
PRODUCTION_INDEX = "messages_chat_created_id_idx"
def run(prefix, args=(), sql=None, check=True, timeout=1800):
proc = subprocess.run(shlex.split(prefix) + list(args), input=sql, capture_output=True, text=True,
cwd=ROOT, timeout=timeout)
if check and proc.returncode != 0:
raise SystemExit("command failed (%s): %s" % (shlex.split(prefix)[0], proc.stderr.strip()[-2000:]))
return proc.stdout
def psql(sql, db=DB, prefix=SRC_PSQL, timeout=1800):
return run(prefix, ["-X", "-q", "-At", "-v", "ON_ERROR_STOP=1", "-d", db], sql=sql, timeout=timeout)
def one(sql, db=DB, prefix=SRC_PSQL):
return psql(sql, db, prefix).strip()
def save(name, data):
OUT.mkdir(parents=True, exist_ok=True)
path = OUT / name
if isinstance(data, str):
path.write_text(data)
else:
path.write_text(json.dumps(data, ensure_ascii=False, indent=2) + "\n")
print(" saved", path.relative_to(ROOT))
# ------------------------------------------------------------------------------------------
def step_setup(args):
print("[setup] recreate database %s and apply migrations" % DB)
psql("DROP DATABASE IF EXISTS %s WITH (FORCE);" % DB, db="postgres")
psql("CREATE DATABASE %s;" % DB, db="postgres")
# Та же схема и тот же учёт версий, что у раннера приложения (src/messenger/db.py).
psql("CREATE TABLE schema_migrations (version text PRIMARY KEY, checksum text NOT NULL,"
" applied_at timestamptz NOT NULL DEFAULT clock_timestamp());")
for path in sorted((ROOT / "migrations").glob("[0-9][0-9][0-9][0-9]_*.sql")):
sql = path.read_text(encoding="utf-8")
digest = hashlib.sha256(sql.encode()).hexdigest()
psql("BEGIN;\n" + sql + "\nINSERT INTO schema_migrations (version, checksum) VALUES ('%s', '%s');\nCOMMIT;"
% (path.stem, digest))
print(" migrations:", [p.stem for p in sorted((ROOT / "migrations").glob("*.sql"))])
def step_generate(args):
print("[generate] users=%d chats=%d messages=%d zipf_s=%.2f" % (args.users, args.chats, args.messages, args.zipf_s))
sql = (ROOT / "research" / "generate.sql").read_text()
t = time.monotonic()
run(SRC_PSQL, ["-X", "-q", "-v", "ON_ERROR_STOP=1", "-d", DB, "-v", "seed=%s" % args.seed,
"-v", "users=%d" % args.users, "-v", "chats=%d" % args.chats,
"-v", "messages=%d" % args.messages, "-v", "zipf_s=%s" % args.zipf_s], sql=sql)
took = time.monotonic() - t
sizes = [int(x) for x in psql("SELECT count(*) FROM messages GROUP BY chat_id ORDER BY 1 DESC").split()]
info = {
"generated_at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
"params": {"seed": args.seed, "users": args.users, "chats": args.chats, "messages": args.messages,
"zipf_s": args.zipf_s},
"generation_seconds": round(took, 2),
"messages_total": sum(sizes),
"chats_with_messages": len(sizes),
"chat_size_top5": sizes[:5],
"chat_size_p50": int(statistics.median(sizes)),
"chat_size_min": sizes[-1],
"top1_share": round(sizes[0] / sum(sizes), 4),
"top10_share": round(sum(sizes[:10]) / sum(sizes), 4),
"messages_table_mb": round(int(one("SELECT pg_table_size('messages')")) / 2**20, 1),
"server_version": one("SHOW server_version"),
"shared_buffers": one("SHOW shared_buffers"),
}
save("dataset.json", info)
print(" ", {k: info[k] for k in ("messages_total", "chat_size_top5", "chat_size_p50", "chat_size_min")})
def _targets():
"""Горячий (крупнейший), медианный и самый маленький чат + курсор/offset на середине истории."""
rows = psql("""
WITH s AS (SELECT chat_id, count(*) n FROM messages GROUP BY chat_id),
r AS (SELECT chat_id, n, row_number() OVER (ORDER BY n DESC, chat_id) rk, count(*) OVER () total FROM s)
SELECT 'hot', chat_id, n FROM r WHERE rk = 1
UNION ALL SELECT 'median', chat_id, n FROM r WHERE rk = (total + 1) / 2
UNION ALL SELECT 'small', chat_id, n FROM r WHERE rk = total;""").splitlines()
targets = []
for line in rows:
label, chat_id, n = line.split("|")
n = int(n)
offset = n // 2
cur = one("SELECT created_at::text || '|' || id FROM messages WHERE chat_id = '%s' "
"ORDER BY created_at, id OFFSET %d LIMIT 1" % (chat_id, max(offset - 1, 0)))
ts, mid = cur.split("|")
targets.append({"label": label, "chat_id": chat_id, "messages": n, "offset": offset, "cursor_ts": ts,
"cursor_id": mid})
return targets
def _queries(t):
c = t["chat_id"]
return {
"keyset_first": "SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '%s' "
"ORDER BY created_at, id LIMIT 51" % c,
"keyset_middle": "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"
% (c, t["cursor_ts"], t["cursor_id"]),
"offset_middle": "SELECT id, chat_id, sender_id, text, created_at FROM messages WHERE chat_id = '%s' "
"ORDER BY created_at, id OFFSET %d LIMIT 51" % (c, t["offset"]),
}
def _plan_summary(plan):
nodes = []
def walk(p):
name = p["Node Type"] + (" using " + p["Index Name"] if "Index Name" in p else "")
nodes.append(name)
for child in p.get("Plans", []):
walk(child)
walk(plan["Plan"])
return nodes
def _apply_variant(name):
ddl = VARIANTS[name][0]
psql("DROP INDEX IF EXISTS hist_idx; DROP INDEX IF EXISTS %s;" % PRODUCTION_INDEX)
if ddl:
psql(ddl + ";")
psql("VACUUM (ANALYZE) messages;")
def step_plans(args):
print("[plans] variants=%s, repeats=%d" % (list(VARIANTS), args.repeats))
targets = _targets()
results = {"targets": targets, "repeats": args.repeats, "variants": {}}
for name, (ddl, note) in VARIANTS.items():
_apply_variant(name)
size = int(one("SELECT coalesce(pg_relation_size(to_regclass('hist_idx')), 0)"))
script, keys = [], []
for t in targets:
for qname, q in _queries(t).items():
for i in range(args.repeats + 1): # первый прогон — прогрев кэша
script.append("\\echo @@@ %s %s %d" % (t["label"], qname, i))
script.append("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + q + ";")
keys.append((t["label"], qname))
out = psql("\n".join(script))
parsed = {}
for block in out.split("@@@ ")[1:]:
header, _, body = block.partition("\n")
label, qname, i = header.split()
if int(i) == 0:
continue
plan = json.loads(body)[0]
parsed.setdefault((label, qname), []).append(plan)
variant = {"note": note, "index_bytes": size, "queries": {}}
for key in keys:
plans = parsed[key]
times = [p["Execution Time"] for p in plans]
last = plans[-1]["Plan"]
variant["queries"]["%s/%s" % key] = {
"exec_ms_median": round(statistics.median(times), 3),
"exec_ms_p90": round(sorted(times)[int(0.9 * (len(times) - 1))], 3),
"shared_hit": last.get("Shared Hit Blocks", 0), "shared_read": last.get("Shared Read Blocks", 0),
"plan": _plan_summary(plans[-1]),
}
results["variants"][name] = variant
print(" %-20s index=%7.1f MiB hot/keyset_middle=%8.3f ms hot/offset_middle=%8.3f ms" % (
name, size / 2**20, variant["queries"]["hot/keyset_middle"]["exec_ms_median"],
variant["queries"]["hot/offset_middle"]["exec_ms_median"]))
if name in ("V1_chat", "V3_chat_created_id"):
hot = targets[0]
text = psql("EXPLAIN (ANALYZE, BUFFERS) " + _queries(hot)["keyset_middle"] + ";")
text2 = psql("EXPLAIN (ANALYZE, BUFFERS) " + _queries(hot)["offset_middle"] + ";")
save("explain_%s_hot.txt" % name, "-- %s; hot chat, %d messages; keyset cursor at 50%%\n%s\n"
"-- same chat, OFFSET %d\n%s" % (note, hot["messages"], text, hot["offset"], text2))
_apply_variant("V3_chat_created_id")
psql("ALTER INDEX hist_idx RENAME TO %s;" % PRODUCTION_INDEX)
save("plans.json", results)
WRITE_FN = r"""
DROP TABLE IF EXISTS bench_messages;
CREATE TABLE bench_messages (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(), chat_id uuid NOT NULL, sender_id uuid NOT NULL,
text text NOT NULL, created_at timestamptz NOT NULL DEFAULT clock_timestamp());
CREATE OR REPLACE FUNCTION bench_write(n int) RETURNS TABLE(ms double precision, wal_bytes numeric) LANGUAGE plpgsql AS $$
DECLARE t0 timestamptz; l0 pg_lsn; pick uuid[]; s uuid;
BEGIN
-- Чаты выбираются с тем же распределением, что и в истории (выборка chat_id из messages).
SELECT array_agg(chat_id) INTO pick FROM (SELECT chat_id FROM messages ORDER BY md5(id::text) LIMIT 5000) x;
SELECT id INTO s FROM users LIMIT 1;
l0 := pg_current_wal_insert_lsn(); t0 := clock_timestamp();
FOR i IN 1..n LOOP
INSERT INTO bench_messages (chat_id, sender_id, text)
VALUES (pick[1 + (i * 7919) % cardinality(pick)], s, 'bench message ' || i || repeat(' lorem ipsum', i % 23));
END LOOP;
ms := extract(epoch FROM clock_timestamp() - t0) * 1000;
wal_bytes := pg_current_wal_insert_lsn() - l0;
RETURN NEXT;
END $$;
"""
def step_writes(args):
print("[writes] %d single-row INSERT per run, %d runs per variant" % (args.write_rows, args.write_runs))
psql(WRITE_FN)
results = {"rows_per_run": args.write_rows, "runs": args.write_runs,
"note": "один INSERT на строку внутри одной транзакции (без fsync на COMMIT); FK исключён, "
"чтобы изолировать стоимость поддержки индекса истории", "variants": {}}
for name, (ddl, note) in VARIANTS.items():
psql("DROP INDEX IF EXISTS bench_hist_idx;")
if ddl:
psql(ddl.replace("hist_idx ON messages", "bench_hist_idx ON bench_messages") + ";")
runs = []
for _ in range(args.write_runs):
psql("TRUNCATE bench_messages; CHECKPOINT;")
ms, wal = one("SELECT ms || '|' || wal_bytes FROM bench_write(%d)" % args.write_rows).split("|")
runs.append((float(ms), float(wal)))
idx = int(one("SELECT coalesce(pg_relation_size(to_regclass('bench_hist_idx')), 0)"))
ms_med = statistics.median(r[0] for r in runs)
wal_med = statistics.median(r[1] for r in runs)
results["variants"][name] = {
"note": note, "ms_median": round(ms_med, 1),
"us_per_insert": round(ms_med * 1000 / args.write_rows, 2),
"wal_bytes_per_insert": round(wal_med / args.write_rows, 1),
"index_bytes_after_run": idx, "runs_ms": [round(r[0], 1) for r in runs]}
print(" %-20s %7.2f us/insert %7.1f WAL B/insert index %6.2f MiB" % (
name, ms_med * 1000 / args.write_rows, wal_med / args.write_rows, idx / 2**20))
psql("DROP TABLE bench_messages; DROP FUNCTION bench_write(int);")
base = results["variants"]["V0_no_index"]
for v in results["variants"].values():
v["overhead_vs_no_index_pct"] = round(100 * (v["us_per_insert"] / base["us_per_insert"] - 1), 1)
save("writes.json", results)
# ------------------------------------------------------------------------------------------
CHECKSUM_SQL = {
"users": "SELECT count(*) || ':' || md5(coalesce(string_agg(id::text || username || display_name || password_hash "
"|| extract(epoch FROM created_at)::text, ',' ORDER BY id), '')) FROM users",
"chats": "SELECT count(*) || ':' || md5(coalesce(string_agg(id::text || title || created_by "
"|| extract(epoch FROM created_at)::text, ',' ORDER BY id), '')) FROM chats",
"chat_members": "SELECT count(*) || ':' || md5(coalesce(string_agg(chat_id::text || user_id || position, ',' "
"ORDER BY chat_id, user_id), '')) FROM chat_members",
"messages_static": "SELECT count(*) || ':' || md5(coalesce(string_agg(id::text || chat_id || sender_id || md5(text) "
"|| extract(epoch FROM created_at)::text, ',' ORDER BY id), '')) FROM messages "
"WHERE text NOT LIKE 'marker-%'",
"schema_migrations": "SELECT count(*) || ':' || md5(string_agg(version || checksum, ',' ORDER BY version)) "
"FROM schema_migrations",
"indexes": "SELECT count(*) || ':' || md5(string_agg(indexdef, ',' ORDER BY indexname)) FROM pg_indexes "
"WHERE schemaname = 'public'",
"constraints": "SELECT count(*) || ':' || md5(string_agg(conname || pg_get_constraintdef(oid), ',' ORDER BY conname)) "
"FROM pg_constraint WHERE connamespace = 'public'::regnamespace",
}
def _writer(stop, counter):
"""Пишет пронумерованные marker-сообщения по одному COMMIT, пока идёт backup."""
member = one("SELECT chat_id || '|' || user_id FROM chat_members ORDER BY chat_id, position LIMIT 1")
chat_id, user_id = member.split("|")
proc = subprocess.Popen(shlex.split(SRC_PSQL) + ["-X", "-q", "-At", "-d", DB], stdin=subprocess.PIPE,
stdout=subprocess.PIPE, stderr=subprocess.DEVNULL, text=True, cwd=ROOT)
n = 0
try:
while not stop.is_set():
n += 1
proc.stdin.write("INSERT INTO messages (chat_id, sender_id, text) VALUES ('%s', '%s', 'marker-%06d'); "
"SELECT %d;\n" % (chat_id, user_id, n, n))
proc.stdin.flush()
line = proc.stdout.readline() # ответ = COMMIT этой вставки подтверждён
counter["acked"] = int(line.strip())
counter.setdefault("t", []).append(time.monotonic())
time.sleep(0.01)
finally:
proc.stdin.close()
proc.wait(timeout=30)
def step_backup(args):
print("[backup] pg_dump -Fc → restore into a NEW server → reconcile")
for t in ("messages",):
psql("DELETE FROM %s WHERE text LIKE 'marker-%%';" % t)
stop, counter = threading.Event(), {"acked": 0}
writer = threading.Thread(target=_writer, args=(stop, counter))
writer.start()
time.sleep(1.0)
before_dump = counter["acked"]
t0 = time.monotonic()
run(SRC_PG_DUMP, ["-Fc", "-Z", "6", "-d", DB, "-f", DUMP_PATH])
dump_s = time.monotonic() - t0
acked_at_dump_end = counter["acked"]
time.sleep(1.0)
stop.set()
writer.join()
total_markers = counter["acked"]
dump_bytes = DUMP_HOST_PATH.stat().st_size if DUMP_HOST_PATH.exists() else None
# Новое хранилище: отдельный сервер/volume; база создаётся пустой, дамп накатывается в 4 потока.
psql("DROP DATABASE IF EXISTS %s WITH (FORCE);" % RESTORED_DB, db="postgres", prefix=DST_PSQL)
psql("CREATE DATABASE %s;" % RESTORED_DB, db="postgres", prefix=DST_PSQL)
t1 = time.monotonic()
run(DST_PG_RESTORE, ["-j", "4", "--no-owner", "--exit-on-error", "-d", RESTORED_DB, DUMP_PATH])
restore_s = time.monotonic() - t1
t2 = time.monotonic()
checks = {}
for name, q in CHECKSUM_SQL.items():
src = one(q)
dst = one(q, db=RESTORED_DB, prefix=DST_PSQL)
checks[name] = {"source": src.split(":")[0], "restored": dst.split(":")[0], "match": src == dst}
# Выборочные сообщения: первое/последнее в 10 крупнейших чатах + 180 псевдослучайных (md5(id)).
sample_sql = """
WITH big AS (SELECT chat_id FROM messages GROUP BY chat_id ORDER BY count(*) DESC, chat_id LIMIT 10),
ends AS (SELECT (SELECT id FROM messages m WHERE m.chat_id = b.chat_id AND text NOT LIKE 'marker-%'
ORDER BY created_at, id LIMIT 1) id FROM big b
UNION SELECT (SELECT id FROM messages m WHERE m.chat_id = b.chat_id AND text NOT LIKE 'marker-%'
ORDER BY created_at DESC, id DESC LIMIT 1) FROM big b),
rnd AS (SELECT id FROM messages WHERE text NOT LIKE 'marker-%' ORDER BY md5(id::text) LIMIT 180)
SELECT string_agg(id::text, ',' ORDER BY id) FROM (SELECT id FROM ends UNION SELECT id FROM rnd) x"""
ids = one(sample_sql).split(",")
fetch = ("SELECT json_agg(row_to_json(m) ORDER BY id)::text FROM (SELECT id, chat_id, sender_id, text, "
"extract(epoch FROM created_at) AS ts FROM messages WHERE id = ANY('{%s}'::uuid[])) m" % ",".join(ids))
sample_src, sample_dst = json.loads(one(fetch)), json.loads(one(fetch, db=RESTORED_DB, prefix=DST_PSQL))
hot = one("SELECT chat_id FROM messages GROUP BY chat_id ORDER BY count(*) DESC, chat_id LIMIT 1")
page_q = ("SELECT md5(string_agg(id::text || md5(text), ',' ORDER BY created_at, id)) FROM (SELECT * FROM messages "
"WHERE chat_id = '%s' AND text NOT LIKE 'marker-%%' ORDER BY created_at, id OFFSET 1000 LIMIT 50) p" % hot)
markers_src = [int(x) for x in psql("SELECT substr(text, 8)::int FROM messages WHERE text LIKE 'marker-%' "
"ORDER BY 1").split()]
markers_dst = [int(x) for x in psql("SELECT substr(text, 8)::int FROM messages WHERE text LIKE 'marker-%' "
"ORDER BY 1", db=RESTORED_DB, prefix=DST_PSQL).split()]
verify_s = time.monotonic() - t2
k = len(markers_dst)
result = {
"measured_at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
"dump": {"format": "custom (-Fc -Z 6)", "seconds": round(dump_s, 3), "bytes": dump_bytes},
"restore": {"parallel_jobs": 4, "seconds": round(restore_s, 3)},
"verify_seconds": round(verify_s, 3),
"rto_dump_plus_restore_plus_verify_seconds": round(dump_s + restore_s + verify_s, 3),
"table_checks": checks,
"sample_messages": {"count": len(ids), "identical": sample_src == sample_dst},
"history_page_hot_chat_offset_1000_identical": one(page_q) == one(page_q, db=RESTORED_DB, prefix=DST_PSQL),
"concurrent_writes": {
"acked_before_dump_started": before_dump,
"acked_when_dump_finished": acked_at_dump_end,
"acked_total": total_markers,
"present_in_source": len(markers_src),
"present_in_restored": k,
"restored_is_contiguous_prefix": markers_dst == list(range(1, k + 1)),
"lost_relative_to_source": len(markers_src) - k,
},
}
result["all_match"] = (all(v["match"] for v in checks.values()) and result["sample_messages"]["identical"]
and result["history_page_hot_chat_offset_1000_identical"]
and result["concurrent_writes"]["restored_is_contiguous_prefix"])
save("backup_restore.json", result)
print(" dump %.2fs (%s bytes), restore %.2fs, verify %.2fs, all_match=%s, markers src=%d restored=%d"
% (dump_s, dump_bytes, restore_s, verify_s, result["all_match"], len(markers_src), k))
if not result["all_match"]:
raise SystemExit("reconciliation FAILED")
def main():
p = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
p.add_argument("steps", nargs="*", default=["setup", "generate", "plans", "writes", "backup"])
p.add_argument("--seed", type=float, default=0.42)
p.add_argument("--users", type=int, default=2000)
p.add_argument("--chats", type=int, default=300)
p.add_argument("--messages", type=int, default=120000)
p.add_argument("--zipf-s", type=float, default=1.1)
p.add_argument("--repeats", type=int, default=15)
p.add_argument("--write-rows", type=int, default=50000)
p.add_argument("--write-runs", type=int, default=5)
args = p.parse_args()
steps = {"setup": step_setup, "generate": step_generate, "plans": step_plans, "writes": step_writes,
"backup": step_backup}
for name in args.steps:
if name not in steps:
raise SystemExit("unknown step " + name)
steps[name](args)
if __name__ == "__main__":
main()