forked from mtusi2026/mtusi2026lab2
187 lines
10 KiB
Python
187 lines
10 KiB
Python
#!/usr/bin/env python3
|
|
"""Трек 2 (часть A): план запроса истории на ≥100 000 сообщений с НЕРАВНОМЕРНЫМИ чатами.
|
|
|
|
Гипотеза H1: составной индекс (chat_id, created_at, id) даёт страницу истории за O(размер страницы)
|
|
для любого чата и любой глубины, а индекс только по chat_id или «глобальный» (created_at, id) — нет;
|
|
покрывающий вариант (INCLUDE) читает меньше страниц, но платит за это размером индекса и стоимостью записи.
|
|
Гипотеза H2: каждый дополнительный индекс заметно удорожает вставку, и цена растёт с шириной индекса.
|
|
|
|
Запуск (ОТДЕЛЬНАЯ БД эксперимента, не рабочая!):
|
|
DATABASE_URL_FILE=/path/to/url python scripts/track2_indexes.py --out evidence/track2-indexes.json
|
|
"""
|
|
import argparse
|
|
import json
|
|
import os
|
|
import platform
|
|
import statistics
|
|
import sys
|
|
import time
|
|
import uuid
|
|
from pathlib import Path
|
|
|
|
sys.path.insert(0, str(Path(__file__).resolve().parents[1]))
|
|
import psycopg # noqa: E402
|
|
|
|
from app import config, migrate # noqa: E402
|
|
|
|
# Неравномерные размеры: 1 гигант, 2 больших, 10 средних, 50 малых, 500 «почти пустых» = ровно 100 000.
|
|
SIZES = [50000, 20000, 10000] + [1000] * 10 + [100] * 50 + [10] * 500
|
|
CONFIGS = [
|
|
("C0_pk_only", None),
|
|
("C1_chat_id", "CREATE INDEX hist_idx ON messages (chat_id)"),
|
|
("C2_chat_created_id", "CREATE INDEX hist_idx ON messages (chat_id, created_at, id)"),
|
|
("C3_covering_include", "CREATE INDEX hist_idx ON messages (chat_id, created_at, id) INCLUDE (sender_id, text)"),
|
|
("C4_global_created_id", "CREATE INDEX hist_idx ON messages (created_at, id)"),
|
|
]
|
|
COLS = "id, chat_id, sender_id, text, created_at"
|
|
PAGE = 50
|
|
READ_RUNS, WARMUP = 300, 20
|
|
SINGLE_INSERTS = 2000
|
|
BULK_REPEATS = 3
|
|
|
|
|
|
def pct(values, p):
|
|
values = sorted(values)
|
|
return values[max(0, min(len(values) - 1, int(round(p * len(values) + 0.5)) - 1))]
|
|
|
|
|
|
def seed_source(conn):
|
|
"""Один раз генерируем данные в messages_src: тексты разной длины (10–400 символов), время по возрастанию в чате."""
|
|
conn.execute("TRUNCATE messages, chat_members, chats, sessions, users CASCADE")
|
|
conn.execute("DROP TABLE IF EXISTS messages_src")
|
|
user = uuid.uuid4()
|
|
conn.execute("INSERT INTO users (id, username, display_name, password_hash) VALUES (%s,'track2_user','x','x')", (user,))
|
|
chats = [uuid.uuid4() for _ in SIZES]
|
|
for cid in chats:
|
|
conn.execute("INSERT INTO chats (id, title, created_by) VALUES (%s,'t',%s)", (cid, user))
|
|
conn.execute("INSERT INTO chat_members (chat_id, user_id) VALUES (%s,%s)", (cid, user))
|
|
conn.execute("""CREATE TABLE messages_src AS
|
|
SELECT gen_random_uuid() AS id, c.chat_id, %s::uuid AS sender_id,
|
|
left(repeat('сообщение ', 60), 10 + (random() * 390)::int) AS text,
|
|
timestamptz '2026-01-01 00:00:00+00' + (g.n * 7 + c.idx) * interval '1 second'
|
|
+ (random() * 1000) * interval '1 microsecond' AS created_at
|
|
FROM (SELECT unnest(%s::uuid[]) AS chat_id, unnest(%s::int[]) AS size, generate_series(1, %s) AS idx) c
|
|
CROSS JOIN LATERAL generate_series(1, c.size) AS g(n)""", (user, chats, SIZES, len(SIZES)))
|
|
conn.execute("ANALYZE messages_src")
|
|
return user, chats
|
|
|
|
|
|
def reset_history_index(conn, ddl):
|
|
conn.execute("DROP INDEX IF EXISTS messages_chat_created_id_idx")
|
|
conn.execute("DROP INDEX IF EXISTS hist_idx")
|
|
if ddl:
|
|
conn.execute(ddl)
|
|
|
|
|
|
def measure_writes(conn, ddl, chats):
|
|
"""Стоимость записи: (1) пакетная загрузка 100 000 строк, (2) 2000 одиночных INSERT с коммитом (как работает API)."""
|
|
bulk = []
|
|
for _ in range(BULK_REPEATS):
|
|
conn.execute("TRUNCATE messages")
|
|
reset_history_index(conn, ddl)
|
|
t0 = time.perf_counter()
|
|
conn.execute("INSERT INTO messages (id, chat_id, sender_id, text, created_at) SELECT * FROM messages_src")
|
|
bulk.append(time.perf_counter() - t0)
|
|
conn.execute("VACUUM (ANALYZE) messages")
|
|
# Одиночные вставки: чаты выбираются пропорционально размеру (больше сообщений — чаще пишут), autocommit = COMMIT на каждую.
|
|
import random
|
|
rnd = random.Random(42)
|
|
sender = conn.execute("SELECT sender_id FROM messages LIMIT 1").fetchone()[0]
|
|
singles = []
|
|
for _ in range(SINGLE_INSERTS):
|
|
cid = rnd.choices(chats, weights=SIZES)[0]
|
|
t0 = time.perf_counter()
|
|
conn.execute("INSERT INTO messages (id, chat_id, sender_id, text) VALUES (%s,%s,%s,%s)",
|
|
(uuid.uuid4(), cid, sender, "одиночная вставка " + "x" * rnd.randint(10, 300)))
|
|
singles.append(time.perf_counter() - t0)
|
|
conn.execute("VACUUM (ANALYZE) messages")
|
|
size = lambda rel: conn.execute("SELECT pg_relation_size(%s)", (rel,)).fetchone()[0]
|
|
return {
|
|
"bulk_insert_100k_seconds": {"runs": [round(x, 3) for x in bulk], "median": round(statistics.median(bulk), 3)},
|
|
"single_insert_ms": {"n": len(singles), "mean": round(1000 * statistics.mean(singles), 3),
|
|
"p50": round(1000 * pct(singles, .50), 3), "p95": round(1000 * pct(singles, .95), 3),
|
|
"p99": round(1000 * pct(singles, .99), 3)},
|
|
"history_index_bytes": size("hist_idx") if ddl else 0,
|
|
"table_bytes": size("messages"),
|
|
"pk_index_bytes": size("messages_pkey"),
|
|
}
|
|
|
|
|
|
def plan_summary(conn, sql, params):
|
|
plan = conn.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql, params).fetchone()[0][0]
|
|
nodes, stack = [], [plan["Plan"]]
|
|
while stack:
|
|
node = stack.pop()
|
|
nodes.append(node["Node Type"])
|
|
stack.extend(node.get("Plans", []))
|
|
root = plan["Plan"]
|
|
return {"nodes": nodes[::-1], "execution_ms": round(plan["Execution Time"], 3),
|
|
"shared_buffers": root.get("Shared Hit Blocks", 0) + root.get("Shared Read Blocks", 0),
|
|
"rows": root["Actual Rows"]}
|
|
|
|
|
|
def measure_reads(conn, queries):
|
|
out = {}
|
|
for name, (sql, params) in queries.items():
|
|
for _ in range(WARMUP):
|
|
conn.execute(sql, params).fetchall()
|
|
times = []
|
|
for _ in range(READ_RUNS):
|
|
t0 = time.perf_counter()
|
|
conn.execute(sql, params).fetchall()
|
|
times.append(time.perf_counter() - t0)
|
|
out[name] = {"p50_ms": round(1000 * pct(times, .50), 3), "p95_ms": round(1000 * pct(times, .95), 3),
|
|
"plan": plan_summary(conn, sql, params)}
|
|
return out
|
|
|
|
|
|
def main():
|
|
ap = argparse.ArgumentParser()
|
|
ap.add_argument("--out", default="evidence/track2-indexes.json")
|
|
ap.add_argument("--reverse", action="store_true", help="обратный порядок конфигураций: проверка, что результат не зависит от порядка/прогрева")
|
|
args = ap.parse_args()
|
|
migrate.apply_all(config.DATABASE_URL)
|
|
with psycopg.connect(config.DATABASE_URL, autocommit=True) as conn:
|
|
user, chats = seed_source(conn)
|
|
total = conn.execute("SELECT count(*) FROM messages_src").fetchone()[0]
|
|
big, small, mid = chats[0], chats[-1], chats[3] # 50 000 / 10 / 1 000 сообщений
|
|
deep_ts, deep_id = conn.execute("SELECT created_at, id FROM messages_src WHERE chat_id=%s ORDER BY created_at, id OFFSET 40000 LIMIT 1", (big,)).fetchone()
|
|
mid_ts, mid_id = conn.execute("SELECT created_at, id FROM messages_src WHERE chat_id=%s ORDER BY created_at, id OFFSET 500 LIMIT 1", (mid,)).fetchone()
|
|
first = "SELECT %s FROM messages WHERE chat_id=%%s ORDER BY created_at, id LIMIT %%s" % COLS
|
|
keyset = "SELECT %s FROM messages WHERE chat_id=%%s AND (created_at, id) > (%%s, %%s) ORDER BY created_at, id LIMIT %%s" % COLS
|
|
offset = "SELECT %s FROM messages WHERE chat_id=%%s ORDER BY created_at, id OFFSET 40000 LIMIT %%s" % COLS
|
|
queries = {
|
|
"Q1_first_page_giant_50k": (first, (big, PAGE + 1)),
|
|
"Q2_deep_keyset_giant_at_40k": (keyset, (big, deep_ts, deep_id, PAGE + 1)),
|
|
"Q3_first_page_tiny_10": (first, (small, PAGE + 1)),
|
|
"Q4_keyset_mid_1k_at_500": (keyset, (mid, mid_ts, mid_id, PAGE + 1)),
|
|
"Q5_OFFSET_40000_giant": (offset, (big, PAGE + 1)),
|
|
}
|
|
env = {
|
|
"postgres": conn.execute("SHOW server_version").fetchone()[0],
|
|
"shared_buffers": conn.execute("SHOW shared_buffers").fetchone()[0],
|
|
"work_mem": conn.execute("SHOW work_mem").fetchone()[0],
|
|
"fsync": conn.execute("SHOW fsync").fetchone()[0],
|
|
"synchronous_commit": conn.execute("SHOW synchronous_commit").fetchone()[0],
|
|
"cpus": os.cpu_count(), "python": platform.python_version(), "platform": platform.platform(),
|
|
}
|
|
results = {"environment": env, "dataset": {"messages": total, "chats": len(SIZES), "chat_size_distribution":
|
|
{"50000": 1, "20000": 1, "10000": 1, "1000": 10, "100": 50, "10": 500}, "text_length_chars": "10..400 (равномерно)",
|
|
"page_size": PAGE, "read_runs": READ_RUNS, "warmup": WARMUP, "single_inserts": SINGLE_INSERTS,
|
|
"bulk_repeats": BULK_REPEATS, "order": "reverse" if args.reverse else "forward"}, "configs": {}}
|
|
for name, ddl in (reversed(CONFIGS) if args.reverse else CONFIGS):
|
|
print("== %s ==" % name, flush=True)
|
|
w = measure_writes(conn, ddl, chats)
|
|
r = measure_reads(conn, queries)
|
|
results["configs"][name] = {"ddl": ddl, "writes": w, "reads": r}
|
|
print(" bulk %.2fs, single p50 %.3f ms, index %.1f MiB; Q1 p50 %.3f ms, Q2 p50 %.3f ms, Q3 p50 %.3f ms" % (
|
|
w["bulk_insert_100k_seconds"]["median"], w["single_insert_ms"]["p50"], w["history_index_bytes"] / 2**20,
|
|
r["Q1_first_page_giant_50k"]["p50_ms"], r["Q2_deep_keyset_giant_at_40k"]["p50_ms"], r["Q3_first_page_tiny_10"]["p50_ms"]), flush=True)
|
|
Path(args.out).parent.mkdir(parents=True, exist_ok=True)
|
|
Path(args.out).write_text(json.dumps(results, ensure_ascii=False, indent=2) + "\n")
|
|
print("saved", args.out)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|