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
315 lines
17 KiB
Python
315 lines
17 KiB
Python
#!/usr/bin/env python3
|
||
"""Трек 2 (оценка «5»), часть 1: план запроса истории и стоимость записи для разных индексов.
|
||
|
||
Гипотезы
|
||
H1. С индексом (chat_id, created_at, id) стоимость страницы истории не зависит ни от размера чата,
|
||
ни от глубины страницы; с индексом только по chat_id она растёт с размером чата (сортировка
|
||
всего чата), без индекса — с размером всей таблицы.
|
||
H2. Каждый индекс удорожает запись; покрывающий индекс с текстом — заметно сильнее остальных
|
||
(размер индекса, WAL), а выигрыш на чтении у него мал.
|
||
H3. OFFSET-пагинация дорожает с глубиной страницы, keyset — нет.
|
||
|
||
Методика (повторяемая, фиксированный seed)
|
||
1. Отдельная база messenger_bench, схема — миграциями Alembic (как в рабочей базе).
|
||
2. ≥100 000 сообщений в чатах неравномерного размера: 1×60 000, 4×5 000, 50×300, 500×20;
|
||
случайные отправители из участников, случайное время за 30 дней (куча не упорядочена по времени),
|
||
текст 32–500 символов. VACUUM ANALYZE.
|
||
3. Для каждого варианта индекса (единственный вторичный индекс на messages):
|
||
none | chat_id | chat_created_id (рабочий) | covering (chat_id, created_at, id) INCLUDE (sender_id, text)
|
||
— чтение: ТОТ ЖЕ запрос, что выполняет API (history_query), по чату каждого класса, первая и
|
||
«глубокая» (90%) страница; 3 прогона прогрева + N замеров EXPLAIN (ANALYZE, TIMING OFF, BUFFERS),
|
||
медиана и p95 Execution Time, буферы, узел плана;
|
||
— запись: bulk INSERT 20 000 строк в одной транзакции и 1 000 одиночных INSERT с COMMIT
|
||
(как делает API), время и объём WAL; затем вставленное удаляется и VACUUM.
|
||
4. Для рабочего индекса дополнительно OFFSET-пагинация на той же глубине.
|
||
|
||
Запуск из venv при поднятом postgres (настройки из .env):
|
||
.venv/Scripts/python.exe scripts/track2_indexes.py
|
||
Результаты: evidence/track2-indexes.json, evidence/track2-indexes.md, evidence/track2-plans.txt
|
||
"""
|
||
import argparse
|
||
import json
|
||
import os
|
||
import statistics
|
||
import subprocess
|
||
import sys
|
||
import time
|
||
from datetime import datetime, timezone
|
||
from pathlib import Path
|
||
|
||
ROOT = Path(__file__).resolve().parents[1]
|
||
APP = ROOT / 'app'
|
||
sys.path.insert(0, str(APP))
|
||
|
||
import psycopg # noqa: E402
|
||
from sqlalchemy.dialects import postgresql # noqa: E402
|
||
|
||
from core.config import settings # noqa: E402
|
||
from database_functions.messages_functions import history_query # noqa: E402
|
||
|
||
BENCH_DB = 'messenger_bench'
|
||
PAGE = 50
|
||
CLASSES = [('huge', 1, 60000), ('medium', 4, 5000), ('small', 50, 300), ('tiny', 500, 20)]
|
||
VARIANTS = {
|
||
'none': None,
|
||
'chat_id': 'CREATE INDEX bench_chat_id_idx ON messages (chat_id)',
|
||
'chat_created_id': 'CREATE INDEX messages_chat_history_idx ON messages (chat_id, created_at, id)',
|
||
'covering': 'CREATE INDEX bench_history_covering_idx ON messages (chat_id, created_at, id) INCLUDE (sender_id, text)',
|
||
}
|
||
TEXT_EXPR = "left(md5(g::text) || repeat(' слово', (random() * 80)::int), 500)"
|
||
|
||
|
||
def connect(dbname, autocommit=True):
|
||
cfg = settings.db
|
||
return psycopg.connect(host=cfg.host, port=cfg.port, dbname=dbname, user=cfg.user,
|
||
password=cfg.read_password(), autocommit=autocommit)
|
||
|
||
|
||
def prepare_database(rebuild):
|
||
with connect('postgres') as admin:
|
||
exists = admin.execute("SELECT 1 FROM pg_database WHERE datname = %s", (BENCH_DB,)).fetchone()
|
||
if exists and rebuild:
|
||
admin.execute(f'DROP DATABASE "{BENCH_DB}" WITH (FORCE)')
|
||
exists = None
|
||
if not exists:
|
||
admin.execute(f'CREATE DATABASE "{BENCH_DB}"')
|
||
# Схема — теми же миграциями, что и рабочая база.
|
||
env = dict(os.environ, APP_CONFIG__DB__NAME=BENCH_DB)
|
||
subprocess.run([sys.executable, '-m', 'alembic', 'upgrade', 'head'], cwd=APP, env=env, check=True,
|
||
capture_output=True)
|
||
|
||
|
||
def load_data(conn):
|
||
if conn.execute("SELECT count(*) FROM messages").fetchone()[0] > 0:
|
||
return False
|
||
conn.execute("SELECT setseed(0.42)")
|
||
conn.execute("""INSERT INTO users (username, display_name, password_hash)
|
||
SELECT 'bench_u' || g, 'Bench ' || g, 'not-a-login' FROM generate_series(1, 300) g""")
|
||
for cls, count, size in CLASSES:
|
||
for i in range(count):
|
||
chat = conn.execute("INSERT INTO chats (title) VALUES (%s) RETURNING id", (f'{cls} {i}',)).fetchone()[0]
|
||
members = 2 + i % 5
|
||
conn.execute("""INSERT INTO chat_members (chat_id, user_id)
|
||
SELECT %s, id FROM users ORDER BY random() LIMIT %s""", (chat, members))
|
||
conn.execute(f"""
|
||
INSERT INTO messages (chat_id, sender_id, created_at, text)
|
||
SELECT %(c)s, m.user_id,
|
||
timestamptz '2026-09-01 00:00:00+00' + random() * interval '30 days', {TEXT_EXPR}
|
||
FROM generate_series(1, %(n)s) AS g
|
||
CROSS JOIN LATERAL (SELECT user_id FROM chat_members
|
||
WHERE chat_id = %(c)s ORDER BY random() + g * 0 LIMIT 1) AS m
|
||
""", {'c': chat, 'n': size})
|
||
conn.execute("VACUUM ANALYZE")
|
||
return True
|
||
|
||
|
||
def set_variant(conn, name):
|
||
for (index,) in conn.execute("""SELECT indexname FROM pg_indexes
|
||
WHERE tablename = 'messages' AND indexname <> 'messages_pkey'""").fetchall():
|
||
conn.execute(f'DROP INDEX "{index}"')
|
||
if VARIANTS[name]:
|
||
conn.execute(VARIANTS[name])
|
||
conn.execute("VACUUM ANALYZE messages") # visibility map нужна для Index Only Scan
|
||
row = conn.execute("""SELECT coalesce(sum(pg_relation_size(indexrelid)), 0) FROM pg_index
|
||
WHERE indrelid = 'messages'::regclass AND NOT indisprimary""").fetchone()
|
||
return int(row[0])
|
||
|
||
|
||
def representative_chats(conn):
|
||
"""Самый большой чат каждого класса: (класс, chat_id, число сообщений)."""
|
||
result = []
|
||
for cls, _, _ in CLASSES:
|
||
row = conn.execute("""SELECT c.id, count(*) FROM chats c JOIN messages m ON m.chat_id = c.id
|
||
WHERE c.title LIKE %s GROUP BY c.id ORDER BY count(*) DESC LIMIT 1""",
|
||
(cls + ' %',)).fetchone()
|
||
result.append((cls, row[0], row[1]))
|
||
return result
|
||
|
||
|
||
def cursor_at(conn, chat_id, index):
|
||
return tuple(conn.execute("""SELECT created_at, id FROM messages WHERE chat_id = %s
|
||
ORDER BY created_at, id OFFSET %s LIMIT 1""", (chat_id, index)).fetchone())
|
||
|
||
|
||
def app_sql(chat_id, after):
|
||
compiled = history_query(chat_id, PAGE, after).compile(dialect=postgresql.psycopg.dialect())
|
||
return str(compiled), compiled.params
|
||
|
||
|
||
def measure(conn, sql, params, runs):
|
||
"""Медиана/p95 Execution Time (без потаймерного оверхеда узлов), буферы и узел плана."""
|
||
times, plan = [], None
|
||
for i in range(3 + runs):
|
||
doc = conn.execute("EXPLAIN (ANALYZE, TIMING OFF, BUFFERS, FORMAT JSON) " + sql, params).fetchone()[0][0]
|
||
if i >= 3:
|
||
times.append(doc['Execution Time'])
|
||
plan = doc
|
||
top = plan['Plan']
|
||
node, cur = [], top
|
||
while cur:
|
||
node.append(cur['Node Type'] + (f" ({cur['Index Name']})" if 'Index Name' in cur else ''))
|
||
cur = (cur.get('Plans') or [None])[0]
|
||
times.sort()
|
||
return {
|
||
'median_ms': round(statistics.median(times), 3),
|
||
'p95_ms': round(times[max(0, int(0.95 * len(times)) - 1)], 3),
|
||
'buffers': top.get('Shared Hit Blocks', 0) + top.get('Shared Read Blocks', 0),
|
||
'plan': ' -> '.join(node),
|
||
}
|
||
|
||
|
||
def text_plan(conn, sql, params):
|
||
return "\n".join(r[0] for r in conn.execute("EXPLAIN (ANALYZE, BUFFERS) " + sql, params).fetchall())
|
||
|
||
|
||
def wal_lsn(conn):
|
||
return conn.execute("SELECT pg_current_wal_insert_lsn()").fetchone()[0]
|
||
|
||
|
||
def wal_bytes(conn, start):
|
||
return int(conn.execute("SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), %s)", (start,)).fetchone()[0])
|
||
|
||
|
||
def cleanup_writes(conn):
|
||
conn.execute("DELETE FROM messages WHERE text LIKE 'bench_w %'")
|
||
conn.execute("VACUUM ANALYZE messages")
|
||
|
||
|
||
def write_cost(conn, chat_id, sender_id, bulk_rows, single_rows, bulk_repeats=3):
|
||
# Bulk: одна транзакция, как импорт; несколько повторов, берём медиану.
|
||
bulk_times, bulk_wals = [], []
|
||
for _ in range(bulk_repeats):
|
||
lsn = wal_lsn(conn)
|
||
t = time.perf_counter()
|
||
with conn.transaction():
|
||
conn.execute(f"""INSERT INTO messages (chat_id, sender_id, created_at, text)
|
||
SELECT %s, %s, clock_timestamp(), 'bench_w ' || {TEXT_EXPR}
|
||
FROM generate_series(1, %s) AS g""", (chat_id, sender_id, bulk_rows))
|
||
bulk_times.append(time.perf_counter() - t)
|
||
bulk_wals.append(wal_bytes(conn, lsn))
|
||
cleanup_writes(conn)
|
||
bulk_s = statistics.median(bulk_times)
|
||
bulk_wal = statistics.median(bulk_wals)
|
||
# Одиночные: INSERT + COMMIT на каждое сообщение, как в API (synchronous_commit по умолчанию).
|
||
lsn = wal_lsn(conn)
|
||
latencies = []
|
||
for g in range(single_rows):
|
||
t = time.perf_counter()
|
||
conn.execute("""INSERT INTO messages (chat_id, sender_id, text)
|
||
VALUES (%s, %s, 'bench_w одиночное ' || %s)""", (chat_id, sender_id, g))
|
||
latencies.append(time.perf_counter() - t)
|
||
single_wal = wal_bytes(conn, lsn)
|
||
cleanup_writes(conn)
|
||
latencies.sort()
|
||
return {
|
||
'bulk_rows': bulk_rows, 'bulk_repeats': bulk_repeats,
|
||
'bulk_seconds_each': [round(x, 3) for x in bulk_times], 'bulk_seconds': round(bulk_s, 3),
|
||
'bulk_rows_per_second': round(bulk_rows / bulk_s), 'bulk_wal_bytes_per_row': round(bulk_wal / bulk_rows),
|
||
'single_rows': single_rows,
|
||
'single_latency_ms_median': round(1000 * statistics.median(latencies), 3),
|
||
'single_latency_ms_p95': round(1000 * latencies[int(0.95 * len(latencies)) - 1], 3),
|
||
'single_wal_bytes_per_row': round(single_wal / single_rows),
|
||
}
|
||
|
||
|
||
def main():
|
||
p = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
|
||
p.add_argument('--runs', type=int, default=15, help='замеров на каждый запрос')
|
||
p.add_argument('--bulk', type=int, default=20000)
|
||
p.add_argument('--single', type=int, default=1000)
|
||
p.add_argument('--rebuild', action='store_true', help='пересоздать messenger_bench с нуля')
|
||
p.add_argument('--output', default=str(ROOT / 'evidence'))
|
||
args = p.parse_args()
|
||
|
||
started = time.perf_counter()
|
||
prepare_database(args.rebuild)
|
||
with connect(BENCH_DB) as conn:
|
||
load_seconds = time.perf_counter()
|
||
loaded = load_data(conn)
|
||
load_seconds = round(time.perf_counter() - load_seconds, 1)
|
||
env = {k: conn.execute(f"SHOW {k}").fetchone()[0]
|
||
for k in ('server_version', 'shared_buffers', 'work_mem', 'random_page_cost', 'synchronous_commit')}
|
||
total = conn.execute("SELECT count(*) FROM messages").fetchone()[0]
|
||
chats = representative_chats(conn)
|
||
sender = conn.execute("SELECT user_id FROM chat_members WHERE chat_id = %s LIMIT 1",
|
||
(chats[0][1],)).fetchone()[0]
|
||
cursors = {cls: cursor_at(conn, chat, int(n * 0.9)) for cls, chat, n in chats}
|
||
|
||
results, plans = {}, []
|
||
for variant in VARIANTS:
|
||
index_bytes = set_variant(conn, variant)
|
||
table_bytes = conn.execute("SELECT pg_table_size('messages')").fetchone()[0]
|
||
reads = {}
|
||
for cls, chat, n in chats:
|
||
for page, after in (('first', None), ('deep_90pct', cursors[cls])):
|
||
sql, params = app_sql(chat, after)
|
||
reads[f'{cls}/{page}'] = {'chat_messages': n, **measure(conn, sql, params, args.runs)}
|
||
if variant == 'chat_created_id':
|
||
cls, chat, n = chats[0]
|
||
offset_sql = ("SELECT id, chat_id, sender_id, created_at, text FROM messages WHERE chat_id = %(c)s "
|
||
"ORDER BY created_at, id OFFSET %(o)s LIMIT %(l)s")
|
||
for page, depth in (('first', 0), ('deep_90pct', int(n * 0.9))):
|
||
reads[f'{cls}/{page}/OFFSET'] = {
|
||
'chat_messages': n, **measure(conn, offset_sql, {'c': chat, 'o': depth, 'l': PAGE + 1}, args.runs)}
|
||
plans.append(f"## chat_created_id: huge chat, OFFSET {int(n * 0.9)} (NOT used by the API)\n"
|
||
+ text_plan(conn, offset_sql, {'c': chat, 'o': int(n * 0.9), 'l': PAGE + 1}))
|
||
sql, params = app_sql(chats[0][1], cursors['huge'])
|
||
plans.append(f"## {variant}: huge chat ({chats[0][2]} msgs), deep page (90%), API query\n"
|
||
+ text_plan(conn, sql, params))
|
||
writes = write_cost(conn, chats[0][1], sender, args.bulk, args.single)
|
||
results[variant] = {'index_bytes': index_bytes, 'table_bytes': table_bytes, 'reads': reads, 'writes': writes}
|
||
print(f"{variant}: index {index_bytes / 2**20:.1f} MiB, huge deep {reads['huge/deep_90pct']['median_ms']} ms, "
|
||
f"bulk {writes['bulk_rows_per_second']} rows/s, single p50 {writes['single_latency_ms_median']} ms")
|
||
set_variant(conn, 'chat_created_id') # оставить базу с рабочим индексом
|
||
|
||
report = {
|
||
'generated_at': datetime.now(timezone.utc).strftime('%Y-%m-%dT%H:%M:%SZ'),
|
||
'database': BENCH_DB, 'postgres_settings': env, 'messages_total': total,
|
||
'chat_classes': [{'class': c, 'chats': k, 'messages_each': s} for c, k, s in CLASSES],
|
||
'representative_chats': [{'class': c, 'messages': n} for c, _, n in chats],
|
||
'data_loaded_this_run': loaded, 'load_seconds': load_seconds if loaded else None,
|
||
'runs_per_query': args.runs, 'page_limit': PAGE + 1,
|
||
'variants': results, 'total_seconds': round(time.perf_counter() - started, 1),
|
||
}
|
||
out = Path(args.output)
|
||
out.mkdir(parents=True, exist_ok=True)
|
||
(out / 'track2-indexes.json').write_text(json.dumps(report, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
|
||
(out / 'track2-plans.txt').write_text("\n\n".join(plans) + "\n", encoding='utf-8')
|
||
(out / 'track2-indexes.md').write_text(markdown(report), encoding='utf-8')
|
||
print(markdown(report))
|
||
|
||
|
||
def markdown(r):
|
||
v = r['variants']
|
||
lines = [f"# Трек 2: индексы истории ({r['generated_at']})", "",
|
||
f"PostgreSQL {r['postgres_settings']['server_version']}, shared_buffers {r['postgres_settings']['shared_buffers']}, "
|
||
f"work_mem {r['postgres_settings']['work_mem']}; {r['messages_total']} сообщений; "
|
||
f"медиана из {r['runs_per_query']} замеров, тёплый кэш.", "",
|
||
"## Чтение страницы истории (запрос API), медиана мс / буферы", "",
|
||
"| чат (сообщений) / страница | " + " | ".join(v) + " |",
|
||
"| --- |" + " --- |" * len(v)]
|
||
keys = [k for k in v['chat_created_id']['reads']]
|
||
for key in keys:
|
||
cells = []
|
||
for name in v:
|
||
m = v[name]['reads'].get(key)
|
||
cells.append(f"{m['median_ms']} / {m['buffers']}" if m else "—")
|
||
n = v['chat_created_id']['reads'][key]['chat_messages']
|
||
lines.append(f"| {key} ({n}) | " + " | ".join(cells) + " |")
|
||
lines += ["", "## Узел плана (глубокая страница самого большого чата)", ""]
|
||
for name in v:
|
||
lines.append(f"- **{name}**: {v[name]['reads']['huge/deep_90pct']['plan']}")
|
||
lines += ["", "## Размер и стоимость записи", "",
|
||
"| вариант | индекс, MiB | bulk, строк/с | bulk WAL, байт/строку | одиночный INSERT+COMMIT p50 / p95, мс | одиночный WAL, байт/строку |",
|
||
"| --- | --- | --- | --- | --- | --- |"]
|
||
for name in v:
|
||
w = v[name]['writes']
|
||
lines.append(f"| {name} | {v[name]['index_bytes'] / 2**20:.1f} | {w['bulk_rows_per_second']} | "
|
||
f"{w['bulk_wal_bytes_per_row']} | {w['single_latency_ms_median']} / {w['single_latency_ms_p95']} | "
|
||
f"{w['single_wal_bytes_per_row']} |")
|
||
return "\n".join(lines) + "\n"
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|