forked from mtusi2026/mtusi2026lab2
676 lines
27 KiB
Python
676 lines
27 KiB
Python
#!/usr/bin/env python3
|
|
import argparse
|
|
import json
|
|
import re
|
|
import statistics
|
|
import subprocess
|
|
import time
|
|
from datetime import datetime, timezone
|
|
from pathlib import Path
|
|
|
|
ROOT = Path(__file__).resolve().parents[1]
|
|
BACKUPS = ROOT / "backups"
|
|
CUSTOM = "SET plan_cache_mode = force_custom_plan;\n"
|
|
DATABASE = "track"
|
|
LIMIT = 51
|
|
WARM_RUNS = 5
|
|
COLD_RUNS = 3
|
|
CHATS = {"largest": 1, "chat_50": 50, "smallest": 1000}
|
|
COLUMNS = "SELECT id, chat_id, sender_id, text, created_at FROM messages"
|
|
QUERIES = {
|
|
"first": (
|
|
"(uuid, int)",
|
|
COLUMNS + " WHERE chat_id = $1 ORDER BY created_at, id LIMIT $2",
|
|
"track_chat({k}), {limit}",
|
|
),
|
|
"keyset_deep": (
|
|
"(uuid, timestamptz, uuid, int)",
|
|
COLUMNS
|
|
+ " WHERE chat_id = $1 AND (created_at, id) > ($2, $3) ORDER BY created_at, id LIMIT $4",
|
|
"track_chat({k}), '{at}', '{id}', {limit}",
|
|
),
|
|
"offset_deep": (
|
|
"(uuid, int, int)",
|
|
COLUMNS + " WHERE chat_id = $1 ORDER BY created_at, id LIMIT $2 OFFSET $3",
|
|
"track_chat({k}), {limit}, {position}",
|
|
),
|
|
}
|
|
VARIANTS = {
|
|
"none": [],
|
|
"chat_id": ["CREATE INDEX track_chat_idx ON messages (chat_id)"],
|
|
"created_at_id": ["CREATE INDEX track_time_idx ON messages (created_at, id)"],
|
|
"chat_id_created_at_id": [
|
|
"CREATE INDEX messages_history_idx ON messages (chat_id, created_at, id)"
|
|
],
|
|
}
|
|
EVICT = (
|
|
"SELECT count(pg_buffercache_evict(bufferid)) FROM pg_buffercache "
|
|
"WHERE reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database());"
|
|
)
|
|
INSERT_SCRIPT = """\\set k random(1, 1000)
|
|
BEGIN;
|
|
SELECT last_message_at FROM chats WHERE id = track_chat(:k) FOR UPDATE;
|
|
WITH message AS (
|
|
INSERT INTO messages (id, chat_id, sender_id, text, created_at)
|
|
VALUES (track_uuid7(), track_chat(:k), track_user(2 * :k - 1), 'pgbench', clock_timestamp())
|
|
RETURNING chat_id, created_at
|
|
)
|
|
UPDATE chats SET last_message_at = message.created_at FROM message WHERE chats.id = message.chat_id;
|
|
COMMIT;
|
|
"""
|
|
VERIFY = """SELECT json_build_object(
|
|
'users', (SELECT count(*) FROM users),
|
|
'chats', (SELECT count(*) FROM chats),
|
|
'chat_members', (SELECT count(*) FROM chat_members),
|
|
'messages', (SELECT count(*) FROM messages WHERE text NOT LIKE 'rpo-marker-%'),
|
|
'migrations', (SELECT string_agg(version || ':' || left(checksum, 12), ',' ORDER BY version) FROM schema_migrations),
|
|
'per_chat_counts_md5', (SELECT md5(string_agg(chat_id || ':' || n, ',' ORDER BY chat_id))
|
|
FROM (SELECT chat_id, count(*) AS n FROM messages WHERE text NOT LIKE 'rpo-marker-%' GROUP BY chat_id) AS c),
|
|
'history_md5_largest', (SELECT md5(string_agg(id || '|' || sender_id || '|' || text || '|' || created_at, E'\\n' ORDER BY created_at, id))
|
|
FROM messages WHERE chat_id = track_chat(1) AND text NOT LIKE 'rpo-marker-%'),
|
|
'history_md5_chat_50', (SELECT md5(string_agg(id || '|' || sender_id || '|' || text || '|' || created_at, E'\\n' ORDER BY created_at, id))
|
|
FROM messages WHERE chat_id = track_chat(50)),
|
|
'history_md5_smallest', (SELECT md5(string_agg(id || '|' || sender_id || '|' || text || '|' || created_at, E'\\n' ORDER BY created_at, id))
|
|
FROM messages WHERE chat_id = track_chat(1000)),
|
|
'sample_rows', (SELECT count(*) FROM messages
|
|
WHERE abs(hashtext(id::text)) % 1000 = 7 AND text NOT LIKE 'rpo-marker-%'),
|
|
'sample_md5', (SELECT md5(string_agg(m::text, E'\\n' ORDER BY id)) FROM messages AS m
|
|
WHERE abs(hashtext(id::text)) % 1000 = 7 AND text NOT LIKE 'rpo-marker-%'),
|
|
'indexes', (SELECT string_agg(indexname, ',' ORDER BY indexname) FROM pg_indexes WHERE schemaname = 'public'),
|
|
'constraints', (SELECT count(*) FROM pg_constraint WHERE connamespace = 'public'::regnamespace),
|
|
'markers', (SELECT count(*) FROM messages WHERE text LIKE 'rpo-marker-%'),
|
|
'last_marker_at', (SELECT max(created_at) FROM messages WHERE text LIKE 'rpo-marker-%')
|
|
)"""
|
|
|
|
|
|
def compose(*args, stdin=None, check=True):
|
|
done = subprocess.run(
|
|
["docker", "compose", "--profile", "track", *args],
|
|
cwd=ROOT,
|
|
input=stdin,
|
|
capture_output=True,
|
|
text=True,
|
|
check=False,
|
|
)
|
|
if check and done.returncode != 0:
|
|
raise SystemExit("docker compose " + " ".join(args[:3]) + ": " + done.stderr.strip()[-400:])
|
|
return done.stdout
|
|
|
|
|
|
def psql(sql, service="postgres", database=DATABASE, variables=None):
|
|
assigned = []
|
|
for name, value in (variables or {}).items():
|
|
assigned += ["-v", name + "=" + str(value)]
|
|
command = ["exec", "-T", service, "psql", "-U", "messenger", "-d", database, "-XAtq"]
|
|
return compose(*command, "-v", "ON_ERROR_STOP=1", *assigned, "-f", "-", stdin=sql)
|
|
|
|
|
|
def scalar(sql, **kwargs):
|
|
return psql(sql, **kwargs).strip()
|
|
|
|
|
|
def now():
|
|
return datetime.now(timezone.utc).isoformat(timespec="milliseconds").replace("+00:00", "Z")
|
|
|
|
|
|
def header():
|
|
commit = subprocess.run(
|
|
["git", "rev-parse", "--short", "HEAD"],
|
|
cwd=ROOT,
|
|
capture_output=True,
|
|
text=True,
|
|
check=True,
|
|
)
|
|
return {
|
|
"date": now(),
|
|
"commit": commit.stdout.strip(),
|
|
"postgres": scalar("SHOW server_version", database="postgres"),
|
|
}
|
|
|
|
|
|
def prepare_database(messages):
|
|
exists = scalar("SELECT 1 FROM pg_database WHERE datname = 'track'", database="postgres")
|
|
if not exists:
|
|
psql("CREATE DATABASE track", database="postgres")
|
|
compose("run", "--rm", "-T", "migrate", "python", "-m", "messenger.migrate", DATABASE)
|
|
psql((ROOT / "scripts/sql/track-setup.sql").read_text())
|
|
started = time.perf_counter()
|
|
output = psql(
|
|
(ROOT / "scripts/sql/track-seed.sql").read_text(), variables={"messages": messages}
|
|
)
|
|
dataset = json.loads(output.strip().splitlines()[-1])
|
|
dataset["seed_seconds"] = round(time.perf_counter() - started, 1)
|
|
return dataset
|
|
|
|
|
|
def drop_history_indexes():
|
|
names = scalar(
|
|
"SELECT string_agg(indexname, ',') FROM pg_indexes "
|
|
"WHERE tablename = 'messages' AND indexname <> 'messages_pkey'"
|
|
)
|
|
for name in filter(None, names.split(",")):
|
|
psql("DROP INDEX " + name)
|
|
|
|
|
|
def build(variant):
|
|
drop_history_indexes()
|
|
started = time.perf_counter()
|
|
for statement in VARIANTS[variant]:
|
|
psql(statement)
|
|
seconds = time.perf_counter() - started
|
|
psql("VACUUM ANALYZE messages")
|
|
sizes = json.loads(
|
|
scalar(
|
|
"SELECT json_build_object('table', pg_table_size('messages'), 'indexes', "
|
|
"coalesce(json_object_agg(indexrelname, pg_relation_size(indexrelid)), '{}'::json)) "
|
|
"FROM pg_stat_user_indexes WHERE relname = 'messages'"
|
|
)
|
|
)
|
|
sizes["build_seconds"] = round(seconds, 2)
|
|
return sizes
|
|
|
|
|
|
def walk(plan, found):
|
|
label = plan["Node Type"]
|
|
if "Index Name" in plan:
|
|
label += "(" + plan["Index Name"] + ")"
|
|
found.append(label)
|
|
for child in plan.get("Plans", []):
|
|
walk(child, found)
|
|
return found
|
|
|
|
|
|
def scanned(plan):
|
|
rows = 0
|
|
if "Scan" in plan["Node Type"]:
|
|
read = plan["Actual Rows"] + plan.get("Rows Removed by Filter", 0)
|
|
rows += (read + plan.get("Rows Removed by Index Recheck", 0)) * plan["Actual Loops"]
|
|
return rows + sum(scanned(child) for child in plan.get("Plans", []))
|
|
|
|
|
|
def explain_many(sql_prefix, statement, runs, cold, text=False):
|
|
parts = [sql_prefix]
|
|
for _ in range(runs):
|
|
if cold:
|
|
parts.append(EVICT)
|
|
parts.append("\\echo ===PLAN===")
|
|
parts.append("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + statement + ";")
|
|
if text:
|
|
parts.append("\\echo ===TEXT===")
|
|
parts.append("EXPLAIN (ANALYZE, BUFFERS) " + statement + ";")
|
|
output = psql("\n".join(parts))
|
|
body, _, plan_text = output.partition("===TEXT===")
|
|
decoder = json.JSONDecoder()
|
|
plans = [decoder.raw_decode(chunk.strip())[0][0] for chunk in body.split("===PLAN===")[1:]]
|
|
return plans, plan_text.strip()
|
|
|
|
|
|
def summarize(plans):
|
|
times = [plan["Execution Time"] for plan in plans]
|
|
top = sorted(plans, key=lambda plan: plan["Execution Time"])[len(plans) // 2]["Plan"]
|
|
return {
|
|
"median_ms": round(statistics.median(times), 3),
|
|
"min_ms": round(min(times), 3),
|
|
"max_ms": round(max(times), 3),
|
|
"shared_hit": statistics.median(plan["Plan"].get("Shared Hit Blocks", 0) for plan in plans),
|
|
"shared_read": statistics.median(
|
|
plan["Plan"].get("Shared Read Blocks", 0) for plan in plans
|
|
),
|
|
"rows_scanned": scanned(top),
|
|
"plan": " > ".join(walk(top, [])),
|
|
}
|
|
|
|
|
|
def targets():
|
|
found = {}
|
|
for label, k in CHATS.items():
|
|
size = int(scalar(f"SELECT count(*) FROM messages WHERE chat_id = track_chat({k})"))
|
|
position = int(size * 0.9)
|
|
row = scalar(
|
|
f"SELECT created_at || ',' || id FROM messages WHERE chat_id = track_chat({k}) "
|
|
f"ORDER BY created_at, id OFFSET {max(position - 1, 0)} LIMIT 1"
|
|
)
|
|
at, identifier = row.split(",")
|
|
found[label] = {"k": k, "size": size, "position": position, "at": at, "id": identifier}
|
|
return found
|
|
|
|
|
|
def statement_for(name, target):
|
|
signature, sql, arguments = QUERIES[name]
|
|
prepare = f"PREPARE q{signature} AS {sql};"
|
|
return prepare, "EXECUTE q(" + arguments.format(limit=LIMIT, **target) + ")"
|
|
|
|
|
|
def read_matrix(variant, chats, plans_file):
|
|
results = []
|
|
for label, target in chats.items():
|
|
for name in QUERIES:
|
|
prepare, execute = statement_for(name, target)
|
|
custom = CUSTOM + prepare
|
|
warm, plan_text = explain_many(
|
|
custom + "\n" + execute + ";", execute, WARM_RUNS, False, True
|
|
)
|
|
cold, _ = explain_many(custom, execute, COLD_RUNS, True)
|
|
plans_file.write(
|
|
"=== {} | {} ({} сообщений) | {} | тёплый кэш\n{}\n\n".format(
|
|
variant, label, target["size"], name, plan_text
|
|
)
|
|
)
|
|
for cache, plans in (("warm", warm), ("cold", cold)):
|
|
record = {
|
|
"variant": variant,
|
|
"chat": label,
|
|
"chat_size": target["size"],
|
|
"query": name,
|
|
"cache": cache,
|
|
}
|
|
record.update(summarize(plans))
|
|
results.append(record)
|
|
return results
|
|
|
|
|
|
def plan_modes(variant, chats):
|
|
results = []
|
|
for label in ("largest", "smallest"):
|
|
prepare, execute = statement_for("first", chats[label])
|
|
for mode in ("force_custom_plan", "force_generic_plan"):
|
|
plans, _ = explain_many(
|
|
CUSTOM.replace("force_custom_plan", mode) + prepare + "\n" + execute + ";",
|
|
execute,
|
|
WARM_RUNS,
|
|
False,
|
|
)
|
|
record = {"variant": variant, "chat": label, "mode": mode}
|
|
record.update(summarize(plans))
|
|
results.append(record)
|
|
return results
|
|
|
|
|
|
COMMIT_MODES = {"sync": "", "async": "PGOPTIONS='-c synchronous_commit=off' "}
|
|
WAL_STATS = "SELECT json_build_object('records', wal_records, 'fpi', wal_fpi) FROM pg_stat_wal"
|
|
|
|
|
|
def mebibytes(usage):
|
|
number, unit = re.match(r"([\d.]+)\s*(\w+)", usage).groups()
|
|
return round(float(number) * {"KiB": 1 / 1024, "GiB": 1024}.get(unit, 1), 1)
|
|
|
|
|
|
def pgbench(seconds, mode):
|
|
compose(
|
|
"exec", "-T", "postgres", "sh", "-c", "cat > /tmp/track-insert.sql", stdin=INSERT_SCRIPT
|
|
)
|
|
psql("CHECKPOINT", database="postgres")
|
|
before = scalar("SELECT pg_current_wal_lsn()", database="postgres")
|
|
counted = json.loads(scalar(WAL_STATS, database="postgres"))
|
|
command = f"{COMMIT_MODES[mode]}pgbench -U messenger -d track -n -c 4 -j 4 -T {seconds} -f /tmp/track-insert.sql"
|
|
running = subprocess.Popen(
|
|
["docker", "compose", "exec", "-T", "postgres", "sh", "-c", command],
|
|
cwd=ROOT,
|
|
stdout=subprocess.PIPE,
|
|
stderr=subprocess.PIPE,
|
|
text=True,
|
|
)
|
|
time.sleep(seconds / 2)
|
|
container = compose("ps", "-q", "postgres").strip()
|
|
cpu, memory = subprocess.run(
|
|
["docker", "stats", "--no-stream", "--format", "{{.CPUPerc}}|{{.MemUsage}}", container],
|
|
capture_output=True,
|
|
text=True,
|
|
check=True,
|
|
).stdout.split("|")
|
|
output, errors = running.communicate()
|
|
if running.returncode != 0:
|
|
raise SystemExit("pgbench: " + errors.strip()[-400:])
|
|
after = scalar("SELECT pg_current_wal_lsn()", database="postgres")
|
|
wal = int(scalar(f"SELECT pg_wal_lsn_diff('{after}', '{before}')", database="postgres"))
|
|
stats = json.loads(scalar(WAL_STATS, database="postgres"))
|
|
transactions = int(
|
|
re.search(r"number of transactions actually processed: (\d+)", output).group(1)
|
|
)
|
|
psql("DELETE FROM messages WHERE text = 'pgbench'; VACUUM ANALYZE messages, chats;")
|
|
return {
|
|
"transactions": transactions,
|
|
"tps": round(float(re.search(r"tps = ([\d.]+)", output).group(1)), 1),
|
|
"latency_avg_ms": float(re.search(r"latency average = ([\d.]+) ms", output).group(1)),
|
|
"wal_bytes_per_insert": round(wal / transactions),
|
|
"wal_records_per_insert": round((stats["records"] - counted["records"]) / transactions, 2),
|
|
"wal_fpi_per_insert": round((stats["fpi"] - counted["fpi"]) / transactions, 3),
|
|
"postgres_cpu_percent": float(cpu.strip().rstrip("%")),
|
|
"postgres_memory_mib": mebibytes(memory),
|
|
}
|
|
|
|
|
|
def write_cost(variant, seconds, repeats, mode):
|
|
runs = [pgbench(seconds, mode) for _ in range(repeats)]
|
|
result = {"variant": variant, "commit": mode, "seconds": seconds, "clients": 4, "runs": runs}
|
|
for key in (
|
|
"tps",
|
|
"latency_avg_ms",
|
|
"wal_bytes_per_insert",
|
|
"wal_records_per_insert",
|
|
"wal_fpi_per_insert",
|
|
"postgres_cpu_percent",
|
|
"postgres_memory_mib",
|
|
):
|
|
values = [run[key] for run in runs]
|
|
result[key] = statistics.median(values)
|
|
result[key + "_range"] = [min(values), max(values)]
|
|
return result
|
|
|
|
|
|
def bench(arguments):
|
|
out = Path(arguments.out)
|
|
out.mkdir(parents=True, exist_ok=True)
|
|
report = {"meta": header(), "dataset": prepare_database(arguments.messages)}
|
|
chats = targets()
|
|
report["targets"] = chats
|
|
report["method"] = {
|
|
"limit": LIMIT,
|
|
"warm_runs": WARM_RUNS,
|
|
"cold_runs": COLD_RUNS,
|
|
"reads": "plan_cache_mode = force_custom_plan: время, план и строки матрицы — custom-план одного и того же запуска (медианного); generic меряется отдельно в plan_cache",
|
|
"cold": "pg_buffercache_evict для всех буферов базы перед каждым запуском; кэш ОС VM не сбрасывается",
|
|
"checkpoint": "CHECKPOINT перед каждым прогоном pgbench: все наборы начинают с одинаковой долей full page writes",
|
|
"control": "после составного набора запись повторяется без индексов истории (порядок A-B-C-D-A): дрейф VM и рост PK отделяются от цены индекса",
|
|
"wal_split": "pg_stat_wal: записи WAL и full page images на вставку, отдельно от байтов",
|
|
"write": f"pgbench -c 4 -j 4 -T {arguments.write_seconds} x{arguments.write_repeats}: SELECT FOR UPDATE + INSERT messages + UPDATE chats; sync — как в приложении, async — synchronous_commit=off, чтобы отделить обслуживание индекса от fsync коммита; docker stats контейнера postgres (сервер и клиент pgbench) в середине прогона",
|
|
}
|
|
report["variants"], report["reads"], report["plan_cache"], report["writes"] = {}, [], [], []
|
|
with (out / "plans.txt").open("w") as plans_file:
|
|
plans_file.write(
|
|
"date: {date} commit: {commit} {postgres}\n".format(**report["meta"])
|
|
+ "EXPLAIN (ANALYZE, BUFFERS) при plan_cache_mode = force_custom_plan\n\n"
|
|
)
|
|
for order, variant in enumerate([*VARIANTS, "none"], 1):
|
|
print("набор индексов:", variant, flush=True)
|
|
sizes = build(variant)
|
|
report["variants"].setdefault(variant, sizes)
|
|
if order <= len(VARIANTS):
|
|
report["reads"] += read_matrix(variant, chats, plans_file)
|
|
if VARIANTS[variant]:
|
|
report["plan_cache"] += plan_modes(variant, chats)
|
|
for mode in COMMIT_MODES:
|
|
write = write_cost(variant, arguments.write_seconds, arguments.write_repeats, mode)
|
|
report["writes"].append(dict(write, order=order, sizes=sizes))
|
|
(out / "bench.json").write_text(json.dumps(report, ensure_ascii=False, indent=1))
|
|
(out / "summary.txt").write_text(summary(report))
|
|
print(summary(report))
|
|
|
|
|
|
def summary(report):
|
|
lines = [
|
|
"date: {date} commit: {commit} {postgres}".format(**report["meta"]),
|
|
"dataset: " + json.dumps(report["dataset"], ensure_ascii=False),
|
|
"",
|
|
]
|
|
lines.append(
|
|
"variant | chat | query | warm median ms | cold median ms | hit/read (cold) | rows scanned | plan"
|
|
)
|
|
keyed = {(r["variant"], r["chat"], r["query"], r["cache"]): r for r in report["reads"]}
|
|
for variant in VARIANTS:
|
|
for chat in CHATS:
|
|
for query in QUERIES:
|
|
warm, cold = (
|
|
keyed[(variant, chat, query, "warm")],
|
|
keyed[(variant, chat, query, "cold")],
|
|
)
|
|
lines.append(
|
|
"{} | {} | {} | {} | {} | {}/{} | {} | {}".format(
|
|
variant,
|
|
chat,
|
|
query,
|
|
warm["median_ms"],
|
|
cold["median_ms"],
|
|
cold["shared_hit"],
|
|
cold["shared_read"],
|
|
warm["rows_scanned"],
|
|
warm["plan"],
|
|
)
|
|
)
|
|
lines += [
|
|
"",
|
|
"order | variant | commit | build s | table MB | indexes MB | tps median [min..max] | latency ms | WAL bytes/insert | WAL records/insert | FPI/insert | postgres CPU % | postgres MiB",
|
|
]
|
|
for write in report["writes"]:
|
|
sizes = write["sizes"]
|
|
indexes = sum(sizes["indexes"].values()) / 1048576
|
|
lines.append(
|
|
"{} | {} | {} | {} | {:.1f} | {:.1f} | {} | {} | {} | {} | {} | {} | {}".format(
|
|
write["order"],
|
|
write["variant"],
|
|
write["commit"],
|
|
sizes["build_seconds"],
|
|
sizes["table"] / 1048576,
|
|
indexes,
|
|
"{} [{}..{}]".format(write["tps"], *write["tps_range"]),
|
|
write["latency_avg_ms"],
|
|
write["wal_bytes_per_insert"],
|
|
write["wal_records_per_insert"],
|
|
write["wal_fpi_per_insert"],
|
|
write["postgres_cpu_percent"],
|
|
write["postgres_memory_mib"],
|
|
)
|
|
)
|
|
lines += ["", "variant | chat | plan_cache_mode | median ms | plan"]
|
|
for row in report["plan_cache"]:
|
|
lines.append(
|
|
"{} | {} | {} | {} | {}".format(
|
|
row["variant"], row["chat"], row["mode"], row["median_ms"], row["plan"]
|
|
)
|
|
)
|
|
return "\n".join(lines) + "\n"
|
|
|
|
|
|
def project():
|
|
return json.loads(compose("config", "--format", "json"))["name"]
|
|
|
|
|
|
def dump_with_markers(messages):
|
|
BACKUPS.mkdir(exist_ok=True)
|
|
target = BACKUPS / f"track-{messages}.dump"
|
|
markers = subprocess.Popen(
|
|
[
|
|
"docker",
|
|
"compose",
|
|
"exec",
|
|
"-T",
|
|
"postgres",
|
|
"psql",
|
|
"-U",
|
|
"messenger",
|
|
"-d",
|
|
DATABASE,
|
|
"-XAtq",
|
|
"-c",
|
|
"DO $$ BEGIN FOR i IN 1..600 LOOP "
|
|
"INSERT INTO messages (id, chat_id, sender_id, text, created_at) "
|
|
"VALUES (track_uuid7(), track_chat(1), track_user(1), 'rpo-marker-' || i, clock_timestamp()); "
|
|
"COMMIT; PERFORM pg_sleep(0.02); END LOOP; END $$",
|
|
],
|
|
cwd=ROOT,
|
|
stdout=subprocess.DEVNULL,
|
|
stderr=subprocess.DEVNULL,
|
|
)
|
|
time.sleep(1.0)
|
|
clock = "psql -U messenger -d track -XAtc 'SELECT extract(epoch FROM clock_timestamp())' >&2"
|
|
with target.open("wb") as sink:
|
|
done = subprocess.run(
|
|
[
|
|
"docker",
|
|
"compose",
|
|
"exec",
|
|
"-T",
|
|
"postgres",
|
|
"sh",
|
|
"-c",
|
|
f"{clock} && pg_dump -U messenger -Fc -d {DATABASE} && {clock}",
|
|
],
|
|
cwd=ROOT,
|
|
stdout=sink,
|
|
stderr=subprocess.PIPE,
|
|
check=True,
|
|
)
|
|
started, finished = (float(value) for value in done.stderr.split())
|
|
seconds = finished - started
|
|
dump_started, dump_finished = (
|
|
datetime.fromtimestamp(value, timezone.utc).isoformat(timespec="microseconds")
|
|
for value in (started, finished)
|
|
)
|
|
time.sleep(0.5)
|
|
psql(
|
|
"SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE query LIKE 'DO $$ BEGIN FOR i IN 1..600%' AND pid <> pg_backend_pid()"
|
|
)
|
|
markers.wait()
|
|
return target, {
|
|
"file": str(target.relative_to(ROOT)),
|
|
"bytes": target.stat().st_size,
|
|
"dump_seconds": round(seconds, 2),
|
|
"dump_started": dump_started,
|
|
"dump_finished": dump_finished,
|
|
}
|
|
|
|
|
|
def fresh_restore_instance():
|
|
volume = project() + "_pgrestore"
|
|
compose("rm", "-sf", "postgres-restore")
|
|
subprocess.run(["docker", "volume", "rm", "-f", volume], capture_output=True, check=False)
|
|
started = time.perf_counter()
|
|
compose("up", "-d", "--wait", "postgres-restore")
|
|
seconds = round(time.perf_counter() - started, 2)
|
|
created = subprocess.run(
|
|
["docker", "volume", "inspect", "-f", "{{.CreatedAt}}", volume],
|
|
capture_output=True,
|
|
text=True,
|
|
check=True,
|
|
)
|
|
return seconds, {"volume": volume, "volume_created": created.stdout.strip()}
|
|
|
|
|
|
def restore(path):
|
|
timings = {}
|
|
for section, jobs in (("pre-data", 1), ("data", 4), ("post-data", 4)):
|
|
started = time.perf_counter()
|
|
compose(
|
|
"exec",
|
|
"-T",
|
|
"postgres-restore",
|
|
"pg_restore",
|
|
"-U",
|
|
"messenger",
|
|
"-d",
|
|
DATABASE,
|
|
"--section=" + section,
|
|
"-j",
|
|
str(jobs),
|
|
"/backups/" + path.name,
|
|
)
|
|
timings[section + "_seconds"] = round(time.perf_counter() - started, 2)
|
|
return timings
|
|
|
|
|
|
def recovery(arguments):
|
|
out = Path(arguments.out)
|
|
out.mkdir(parents=True, exist_ok=True)
|
|
report = {
|
|
"meta": header(),
|
|
"method": "время начала и конца pg_dump — clock_timestamp() PostgreSQL источника, теми же часами, что created_at маркеров",
|
|
"runs": [],
|
|
"summary": [],
|
|
}
|
|
for messages in arguments.sizes:
|
|
dataset = prepare_database(messages)
|
|
for attempt in range(1, arguments.repeats + 1):
|
|
print("восстановление набора:", messages, "попытка", attempt, flush=True)
|
|
report["runs"].append(recover_once(dataset, attempt))
|
|
runs = [run for run in report["runs"] if run["messages"] == dataset["messages"]]
|
|
rto = [run["restore"]["rto_seconds"] for run in runs]
|
|
report["summary"].append(
|
|
{
|
|
"messages": dataset["messages"],
|
|
"repeats": len(runs),
|
|
"rto_median_seconds": statistics.median(rto),
|
|
"rto_range_seconds": [min(rto), max(rto)],
|
|
"new_instance_median_seconds": statistics.median(
|
|
run["restore"]["new_instance_seconds"] for run in runs
|
|
),
|
|
"all_checks_passed": all(run["all_checks_passed"] for run in runs),
|
|
"markers_lost_while_dump_was_running": [
|
|
run["rpo"]["markers_lost_while_dump_was_running"] for run in runs
|
|
],
|
|
}
|
|
)
|
|
(out / "recovery.json").write_text(json.dumps(report, ensure_ascii=False, indent=1))
|
|
for row in report["summary"]:
|
|
print(json.dumps(row, ensure_ascii=False))
|
|
|
|
|
|
def recover_once(dataset, attempt):
|
|
path, dump = dump_with_markers(dataset["messages"])
|
|
source = json.loads(scalar(VERIFY))
|
|
instance_seconds, instance = fresh_restore_instance()
|
|
timings = restore(path)
|
|
started = time.perf_counter()
|
|
restored = json.loads(scalar(VERIFY, service="postgres-restore"))
|
|
verify_seconds = round(time.perf_counter() - started, 2)
|
|
compared = [key for key in source if key not in ("markers", "last_marker_at")]
|
|
checks = {key: source[key] == restored[key] for key in compared}
|
|
after_snapshot = (
|
|
"SELECT count(*) FROM messages WHERE text LIKE 'rpo-marker-%' AND created_at > '{}'".format(
|
|
restored["last_marker_at"] or "-infinity"
|
|
)
|
|
)
|
|
lost = int(scalar(after_snapshot))
|
|
during = int(scalar(after_snapshot + f" AND created_at <= '{dump['dump_finished']}'"))
|
|
psql("DELETE FROM messages WHERE text LIKE 'rpo-marker-%'")
|
|
return {
|
|
"messages": dataset["messages"],
|
|
"attempt": attempt,
|
|
"dataset": dataset,
|
|
"dump": dump,
|
|
"instance": instance,
|
|
"restore": dict(
|
|
timings,
|
|
new_instance_seconds=instance_seconds,
|
|
verify_seconds=verify_seconds,
|
|
rto_seconds=round(instance_seconds + sum(timings.values()) + verify_seconds, 2),
|
|
),
|
|
"checks": checks,
|
|
"all_checks_passed": all(checks.values()),
|
|
"source": source,
|
|
"restored": restored,
|
|
"rpo": {
|
|
"markers_in_source": source["markers"],
|
|
"markers_in_backup": restored["markers"],
|
|
"markers_lost": lost,
|
|
"markers_lost_while_dump_was_running": during,
|
|
"last_marker_in_backup": restored["last_marker_at"],
|
|
"dump_started": dump["dump_started"],
|
|
"dump_finished": dump["dump_finished"],
|
|
},
|
|
}
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(
|
|
description="Трек 2: планы истории, стоимость записи, backup/restore"
|
|
)
|
|
commands = parser.add_subparsers(dest="command", required=True)
|
|
bench_parser = commands.add_parser("bench")
|
|
bench_parser.add_argument("--messages", type=int, default=1000000)
|
|
bench_parser.add_argument("--write-seconds", type=int, default=10)
|
|
bench_parser.add_argument("--write-repeats", type=int, default=5)
|
|
recovery_parser = commands.add_parser("recovery")
|
|
recovery_parser.add_argument("--sizes", type=int, nargs="+", default=[100000, 1000000])
|
|
recovery_parser.add_argument("--repeats", type=int, default=3)
|
|
for subparser in (bench_parser, recovery_parser):
|
|
subparser.add_argument("--out", default=str(ROOT / "evidence" / "track"))
|
|
arguments = parser.parse_args()
|
|
if arguments.command == "bench":
|
|
bench(arguments)
|
|
else:
|
|
recovery(arguments)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|