forked from mtusi2026/mtusi2026lab2
Уровень 4: - Redis как кэш сессий (cache-aside, источник истины — PostgreSQL): TTL <= остатка сессии, метка revoked при logout + SET NX против гонки, при отказе Redis — работа через базу и фоновая проверка доступности - тесты: гонка регистрации, откат посреди транзакции, недоступность БД и Redis (44 теста) - EXPLAIN (ANALYZE, BUFFERS) на 22 000 сообщений, history_query общий для API и скрипта - эксперименты отказа Redis/PostgreSQL под нагрузкой; dns_opt у API, чтобы отказ БД давал быстрый 503, а не зависание на поиске имени остановленного контейнера Уровень 5, трек 2: - 105 000 сообщений в неравномерных чатах, 4 варианта индекса + OFFSET против keyset, стоимость записи (время, WAL), два независимых прогона - pg_dump -> pg_restore в новый контейнер -> сверка md5 всех таблиц и выборки сообщений, RTO 14 с, демонстрация RPO
162 lines
8.3 KiB
Python
162 lines
8.3 KiB
Python
#!/usr/bin/env python3
|
|
"""EXPLAIN (ANALYZE, BUFFERS) запроса истории сообщений на синтетических данных (уровень 4).
|
|
|
|
Создаёт пользователей explain_*, один большой чат (по умолчанию 12 000 сообщений) и несколько
|
|
маленьких, затем анализирует ТОТ ЖЕ запрос, что выполняет API (database_functions.history_query):
|
|
первая, средняя и последняя страница большого чата, первая страница маленького и, для сравнения,
|
|
OFFSET-пагинация до той же глубины. Повторный запуск сначала удаляет данные прошлого запуска.
|
|
|
|
Запуск из venv при поднятом PostgreSQL (настройки из .env):
|
|
.venv/Scripts/python.exe scripts/explain_history.py --output evidence/explain-history.txt
|
|
"""
|
|
import argparse
|
|
import re
|
|
import sys
|
|
import uuid
|
|
from datetime import datetime, timezone
|
|
from pathlib import Path
|
|
|
|
ROOT = Path(__file__).resolve().parents[1]
|
|
sys.path.insert(0, str(ROOT / 'app'))
|
|
|
|
from sqlalchemy import text # noqa: E402
|
|
from sqlalchemy.dialects import postgresql # noqa: E402
|
|
|
|
from core.db import engine # noqa: E402
|
|
from database_functions.messages_functions import history_query # noqa: E402
|
|
|
|
PAGE = 50 # limit по умолчанию в API
|
|
|
|
|
|
def cleanup(conn):
|
|
conn.execute(text("""
|
|
DELETE FROM messages WHERE sender_id IN (SELECT id FROM users WHERE username LIKE 'explain\\_%');
|
|
DELETE FROM chat_members WHERE user_id IN (SELECT id FROM users WHERE username LIKE 'explain\\_%');
|
|
DELETE FROM chats c WHERE NOT EXISTS (SELECT 1 FROM chat_members m WHERE m.chat_id = c.id)
|
|
AND c.title LIKE 'explain %';
|
|
DELETE FROM users WHERE username LIKE 'explain\\_%';
|
|
"""))
|
|
|
|
|
|
def create_chat(conn, title, members):
|
|
chat_id = conn.scalar(text("INSERT INTO chats (title) VALUES (:t) RETURNING id"), {"t": title})
|
|
for user_id in members:
|
|
conn.execute(text("INSERT INTO chat_members (chat_id, user_id) VALUES (:c, :u)"), {"c": chat_id, "u": user_id})
|
|
return chat_id
|
|
|
|
|
|
def fill(conn, chat_id, a, b, n):
|
|
# Разные created_at (шаг 1 мс), отправители чередуются; id — gen_random_uuid() по умолчанию.
|
|
conn.execute(text("""
|
|
INSERT INTO messages (chat_id, sender_id, created_at, text)
|
|
SELECT :chat, CASE WHEN g % 2 = 0 THEN CAST(:a AS uuid) ELSE CAST(:b AS uuid) END,
|
|
clock_timestamp() - (:n - g) * interval '1 millisecond', 'сообщение ' || g
|
|
FROM generate_series(1, :n) AS g
|
|
"""), {"chat": chat_id, "a": a, "b": b, "n": n})
|
|
|
|
|
|
def position(conn, chat_id, index):
|
|
"""(created_at, id) сообщения с порядковым номером index (0-based) — то, что лежит в курсоре."""
|
|
row = conn.execute(text("""
|
|
SELECT created_at, id FROM messages WHERE chat_id = :c
|
|
ORDER BY created_at, id OFFSET :i LIMIT 1
|
|
"""), {"c": chat_id, "i": index}).one()
|
|
return row.created_at, row.id
|
|
|
|
|
|
def explain(conn, sql, params):
|
|
rows = conn.exec_driver_sql("EXPLAIN (ANALYZE, BUFFERS) " + sql, params).all()
|
|
return "\n".join(r[0] for r in rows)
|
|
|
|
|
|
def app_query(chat_id, after):
|
|
compiled = history_query(chat_id, PAGE, after).compile(dialect=postgresql.psycopg.dialect())
|
|
return str(compiled), compiled.params
|
|
|
|
|
|
def summarize(plan):
|
|
node = re.search(r"(Index Scan using \S+|Index Only Scan using \S+|Seq Scan on \S+|Bitmap Heap Scan on \S+)", plan)
|
|
rows_removed = sum(int(x) for x in re.findall(r"Rows Removed by Filter: (\d+)", plan))
|
|
# Первая строка Buffers относится к верхнему узлу плана и включает всё, что под ним.
|
|
top = re.search(r"Buffers: ([^\n]*)", plan)
|
|
top = top.group(1) if top else ""
|
|
hits = sum(int(x) for x in re.findall(r"hit=(\d+)", top))
|
|
reads = sum(int(x) for x in re.findall(r"read=(\d+)", top))
|
|
exec_ms = re.search(r"Execution Time: ([\d.]+) ms", plan)
|
|
return {"node": node.group(1) if node else "?", "rows_removed_by_filter": rows_removed,
|
|
"top_buffers_hit": hits, "top_buffers_read": reads,
|
|
"execution_ms": float(exec_ms.group(1)) if exec_ms else None}
|
|
|
|
|
|
def main():
|
|
p = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
|
|
p.add_argument('--big', type=int, default=12000, help='сообщений в большом чате')
|
|
p.add_argument('--small-chats', type=int, default=20)
|
|
p.add_argument('--small', type=int, default=500, help='сообщений в каждом маленьком чате')
|
|
p.add_argument('--output', default=str(ROOT / 'evidence' / 'explain-history.txt'))
|
|
args = p.parse_args()
|
|
|
|
tag = uuid.uuid4().hex[:8]
|
|
with engine.begin() as conn:
|
|
cleanup(conn)
|
|
a = conn.scalar(text("INSERT INTO users (username, display_name, password_hash) "
|
|
"VALUES (:u, 'Explain A', 'not-a-login') RETURNING id"), {"u": f"explain_a_{tag}"})
|
|
b = conn.scalar(text("INSERT INTO users (username, display_name, password_hash) "
|
|
"VALUES (:u, 'Explain B', 'not-a-login') RETURNING id"), {"u": f"explain_b_{tag}"})
|
|
big = create_chat(conn, "explain big", [a, b])
|
|
fill(conn, big, a, b, args.big)
|
|
small = None
|
|
for i in range(args.small_chats):
|
|
small = create_chat(conn, f"explain small {i}", [a, b])
|
|
fill(conn, small, a, b, args.small)
|
|
with engine.begin() as conn:
|
|
conn.execute(text("ANALYZE messages"))
|
|
|
|
out = []
|
|
with engine.connect() as conn:
|
|
total = conn.scalar(text("SELECT count(*) FROM messages"))
|
|
size = conn.scalar(text("SELECT pg_size_pretty(pg_total_relation_size('messages'))"))
|
|
indexes = conn.execute(text("SELECT indexdef FROM pg_indexes WHERE tablename = 'messages' ORDER BY indexname")).all()
|
|
version = conn.scalar(text("SHOW server_version"))
|
|
out.append(f"# {datetime.now(timezone.utc):%Y-%m-%dT%H:%M:%SZ} PostgreSQL {version}")
|
|
out.append(f"# messages: {total} rows total, {size} with indexes; big chat {args.big}, "
|
|
f"{args.small_chats} small chats x {args.small}; ANALYZE done")
|
|
out += ["# indexes on messages:"] + ["# " + r[0] for r in indexes]
|
|
|
|
cases = [
|
|
("1. big chat, first page (no cursor)", big, None),
|
|
("2. big chat, middle page (cursor after #%d)" % (args.big // 2), big, position(conn, big, args.big // 2)),
|
|
("3. big chat, last page (cursor after #%d)" % (args.big - PAGE - 1), big,
|
|
position(conn, big, args.big - PAGE - 1)),
|
|
("4. small chat, first page", small, None),
|
|
]
|
|
summary = []
|
|
for title, chat_id, after in cases:
|
|
sql, params = app_query(chat_id, after)
|
|
plan = explain(conn, sql, params)
|
|
summary.append((title, summarize(plan)))
|
|
out += ["", "## " + title, "-- SQL generated by the API (history_query):", sql, "", plan]
|
|
|
|
# Для сравнения: та же глубина через OFFSET — так API НЕ делает.
|
|
offset_sql = ("SELECT * FROM messages WHERE chat_id = %(c)s ORDER BY created_at, id "
|
|
"OFFSET %(o)s LIMIT %(l)s")
|
|
plan = explain(conn, offset_sql, {"c": big, "o": args.big - PAGE - 1, "l": PAGE + 1})
|
|
title = "5. for comparison: OFFSET pagination to the same depth as case 3 (NOT used by the API)"
|
|
summary.append((title, summarize(plan)))
|
|
out += ["", "## " + title, offset_sql, "", plan]
|
|
|
|
head = ["", "# SUMMARY (top node, rows removed by filter, buffers of the top node, execution time)"]
|
|
for title, s in summary:
|
|
head.append(f"# {title}: {s['node']}; removed={s['rows_removed_by_filter']}; "
|
|
f"buffers hit={s['top_buffers_hit']} read={s['top_buffers_read']}; {s['execution_ms']} ms")
|
|
text_out = "\n".join(out[:5] + head + out[5:]) + "\n"
|
|
dest = Path(args.output)
|
|
dest.parent.mkdir(parents=True, exist_ok=True)
|
|
dest.write_text(text_out, encoding="utf-8")
|
|
print("\n".join(head))
|
|
print("written", dest)
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|