#!/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()