Files
mtusi2026lab2/scripts/track2_indexes.py
vest1ter ac1317d22e ЛР2 на 5: Redis-кэш сессий, отказоустойчивость, трек 2 (индексы и восстановление)
Уровень 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
2026-10-29 15:42:37 +03:00

315 lines
17 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
#!/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()