forked from mtusi2026/mtusi2026lab2
431 lines
23 KiB
Python
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()
|