forked from mtusi2026/mtusi2026lab2
415 lines
22 KiB
Python
415 lines
22 KiB
Python
#!/usr/bin/env python3
|
||
"""Track 2, часть 1: план запроса истории и стоимость записи.
|
||
|
||
Варианты индексов на messages (кроме PK):
|
||
none — только messages_pkey (id)
|
||
C — (chat_id, created_at), без id
|
||
A — (chat_id, created_at, id), индекс из миграции 001
|
||
A+C — оба (только в бенче записи: цена лишнего индекса)
|
||
|
||
Чтение: запросы приложения (MessageRepo::list_page) на чатах small/medium/
|
||
large/burst: Q1 — первая страница, Q2 — keyset-страница из середины чата,
|
||
Q3 — OFFSET на той же глубине (контраст). EXPLAIN (ANALYZE, BUFFERS),
|
||
1 прогрев + N замеров, медиана Execution Time.
|
||
|
||
Запись: pgbench, однострочные INSERT в отдельных транзакциях (как POST
|
||
/messages), раунды с ротацией порядка вариантов; CHECKPOINT и VACUUM перед
|
||
каждым прогоном; метрики TPS, средняя latency и байты WAL на вставку.
|
||
|
||
В конце индексы возвращаются к состоянию миграций (только A).
|
||
Результат: evidence/track2_indexes.md.
|
||
|
||
python3 scripts/track2_indexes.py
|
||
python3 scripts/track2_indexes.py --write-seconds 20 --write-rounds 5
|
||
"""
|
||
import argparse
|
||
import json
|
||
import re
|
||
|
||
from track2_common import (ENV, MESSAGE_COLS, PERF_USER_ID, Pg, dataset_lines,
|
||
environment_lines, median, pick_chats, write_evidence)
|
||
|
||
db = Pg.compose()
|
||
|
||
INDEXES = {
|
||
"A": ("messages_chat_keyset_idx", "(chat_id, created_at, id)"),
|
||
"C": ("track2_chat_created_idx", "(chat_id, created_at)"),
|
||
}
|
||
READ_VARIANTS = [("none", []), ("C", ["C"]), ("A", ["A"])]
|
||
WRITE_VARIANTS = [("none", []), ("C", ["C"]), ("A", ["A"]), ("A+C", ["A", "C"])]
|
||
CHAT_ORDER = ["small", "medium", "large", "burst"]
|
||
QUERIES = ["Q1", "Q2", "Q3"]
|
||
QUERY_TITLE = {
|
||
"Q1": "первая страница",
|
||
"Q2": "keyset из середины",
|
||
"Q3": "OFFSET из середины",
|
||
}
|
||
PAGE = 51 # limit + 1, как в приложении
|
||
BENCH_SQL = (
|
||
"INSERT INTO messages (id, chat_id, sender_id, text, created_at) "
|
||
"VALUES (gen_random_uuid(), ':chat', ':sender', 'track2-bench', "
|
||
"date_trunc('second', clock_timestamp()));\n"
|
||
)
|
||
|
||
|
||
def set_indexes(keys):
|
||
sql = []
|
||
for key, (name, _) in INDEXES.items():
|
||
if key not in keys:
|
||
sql.append(f"DROP INDEX IF EXISTS {name};")
|
||
for key in keys:
|
||
name, cols = INDEXES[key]
|
||
sql.append(f"CREATE INDEX IF NOT EXISTS {name} ON messages {cols};")
|
||
sql.append("ANALYZE messages;")
|
||
db.psql("\n".join(sql))
|
||
|
||
|
||
def index_size(key):
|
||
return int(db.scalar(f"SELECT pg_relation_size('{INDEXES[key][0]}');"))
|
||
|
||
|
||
# ---------- чтение ----------
|
||
|
||
def build_queries(chat_id, size):
|
||
depth = size // 2
|
||
ts, mid_id = db.rows(f"""
|
||
SELECT created_at::text, id FROM messages WHERE chat_id = '{chat_id}'
|
||
ORDER BY created_at, id OFFSET {depth} LIMIT 1;
|
||
""")[0]
|
||
base = f"SELECT {MESSAGE_COLS} FROM messages WHERE chat_id = '{chat_id}'"
|
||
order = "ORDER BY created_at ASC, id ASC"
|
||
return {
|
||
"Q1": f"{base} {order} LIMIT {PAGE}",
|
||
"Q2": f"{base} AND (created_at, id) > ('{ts}'::timestamptz, '{mid_id}'::uuid) "
|
||
f"{order} LIMIT {PAGE}",
|
||
"Q3": f"{base} {order} OFFSET {depth} LIMIT {PAGE}",
|
||
}
|
||
|
||
|
||
def walk(node):
|
||
yield node
|
||
for child in node.get("Plans", []):
|
||
yield from walk(child)
|
||
|
||
|
||
def summarize(plan_json):
|
||
top = plan_json[0]
|
||
plan = top["Plan"]
|
||
nodes, rows_read = [], 0
|
||
for n in walk(plan):
|
||
label = n["Node Type"]
|
||
if n.get("Index Name"):
|
||
label += f" ({n['Index Name']})"
|
||
nodes.append(label)
|
||
if not n.get("Plans"):
|
||
# Для параллельных узлов EXPLAIN показывает среднее на один loop.
|
||
per_loop = n.get("Actual Rows", 0) + n.get("Rows Removed by Filter", 0)
|
||
rows_read += round(per_loop * n.get("Actual Loops", 1))
|
||
return {
|
||
"ms": top["Execution Time"],
|
||
"buffers": plan.get("Shared Hit Blocks", 0) + plan.get("Shared Read Blocks", 0),
|
||
"rows_read": rows_read,
|
||
"plan": " → ".join(nodes),
|
||
}
|
||
|
||
|
||
def measure_read(sql, reps):
|
||
explain = f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {sql};"
|
||
db.psql(explain) # прогрев
|
||
runs = [summarize(json.loads(db.psql(explain))) for _ in range(reps)]
|
||
result = runs[-1]
|
||
result["ms"] = median([r["ms"] for r in runs])
|
||
return result
|
||
|
||
|
||
def text_plan(sql):
|
||
return db.psql(f"EXPLAIN (ANALYZE, BUFFERS) {sql};", tuples=False).strip()
|
||
|
||
|
||
def read_phase(chats, reps):
|
||
results, plans, sizes = {}, {}, {}
|
||
queries = {label: build_queries(c[0], c[2]) for label, c in chats}
|
||
for variant, keys in READ_VARIANTS:
|
||
print(f"\n[чтение] вариант {variant}")
|
||
set_indexes(keys)
|
||
for key in keys:
|
||
sizes[key] = index_size(key)
|
||
for label in CHAT_ORDER:
|
||
for q in QUERIES:
|
||
r = measure_read(queries[label][q], reps)
|
||
results[(label, q, variant)] = r
|
||
print(f" {label:6} {q}: {r['ms']:8.3f} ms, buffers={r['buffers']:5}, "
|
||
f"rows_read={r['rows_read']:6} {r['plan']}")
|
||
for label, q in (("large", "Q2"), ("burst", "Q2"), ("small", "Q1")):
|
||
plans[(label, q, variant)] = text_plan(queries[label][q])
|
||
return results, plans, sizes
|
||
|
||
|
||
# ---------- запись ----------
|
||
|
||
def install_bench_script():
|
||
db.run(["sh", "-c", "cat > /tmp/track2_insert.sql"], input=BENCH_SQL)
|
||
|
||
|
||
def pgbench_once(chat_id, seconds, clients):
|
||
db.psql(f"""
|
||
DELETE FROM messages WHERE text = 'track2-bench';
|
||
VACUUM messages;
|
||
CHECKPOINT;
|
||
""")
|
||
lsn0 = db.scalar("SELECT pg_current_wal_lsn();")
|
||
out = db.run(["pgbench", "-n", "-U", ENV["POSTGRES_USER"], "-d", ENV["POSTGRES_DB"],
|
||
"-f", "/tmp/track2_insert.sql",
|
||
"-D", f"chat={chat_id}", "-D", f"sender={PERF_USER_ID}",
|
||
"-c", str(clients), "-j", str(clients), "-T", str(seconds)]).stdout
|
||
wal = int(db.scalar(f"SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '{lsn0}');"))
|
||
tx = int(re.search(r"actually processed: (\d+)", out).group(1))
|
||
tps = float(re.search(r"tps = ([\d.]+)", out).group(1))
|
||
lat = float(re.search(r"latency average = ([\d.]+) ms", out).group(1))
|
||
return {"tps": tps, "lat": lat, "wal_per_tx": wal / tx, "tx": tx}
|
||
|
||
|
||
def write_phase(chat_id, seconds, rounds, clients):
|
||
install_bench_script()
|
||
runs = {v: [] for v, _ in WRITE_VARIANTS}
|
||
db.psql("ALTER TABLE messages SET (autovacuum_enabled = false);")
|
||
try:
|
||
for rnd in range(rounds):
|
||
shift = rnd % len(WRITE_VARIANTS)
|
||
order = WRITE_VARIANTS[shift:] + WRITE_VARIANTS[:shift]
|
||
print(f"\n[запись] раунд {rnd + 1}/{rounds}: {' → '.join(v for v, _ in order)}")
|
||
for variant, keys in order:
|
||
set_indexes(keys)
|
||
r = pgbench_once(chat_id, seconds, clients)
|
||
runs[variant].append(r)
|
||
print(f" {variant:5} tps={r['tps']:8.1f} lat={r['lat']:.3f} ms "
|
||
f"WAL={r['wal_per_tx']:.0f} B/insert ({r['tx']} tx)")
|
||
finally:
|
||
db.psql("DELETE FROM messages WHERE text = 'track2-bench';")
|
||
db.psql("ALTER TABLE messages RESET (autovacuum_enabled); VACUUM ANALYZE messages;")
|
||
return runs
|
||
|
||
|
||
# ---------- отчёт ----------
|
||
|
||
def fmt_ms(v):
|
||
return f"{v:.3f}" if v < 10 else f"{v:.1f}"
|
||
|
||
|
||
def conclusions(chats, R, W, sizes):
|
||
size_of = {label: c[2] for label, c in chats}
|
||
out = []
|
||
|
||
# H1: индекс A даёт стоимость страницы, не зависящую от размера и глубины.
|
||
a_cells = [R[(l, q, "A")] for l in ("small", "medium", "large", "burst") for q in ("Q1", "Q2")]
|
||
a_rows = max(c["rows_read"] for c in a_cells)
|
||
a_buf = max(c["buffers"] for c in a_cells)
|
||
a_ms = max(c["ms"] for c in a_cells)
|
||
ok = a_rows <= PAGE
|
||
sorted_cells = [f"{l} {q}" for l in CHAT_ORDER for q in ("Q1", "Q2")
|
||
if "Sort" in R[(l, q, "A")]["plan"]]
|
||
sort_note = (
|
||
"Узла Sort нет ни в одном плане: порядок `(created_at, id)` берётся из индекса."
|
||
if not sorted_cells else
|
||
f"Sort встречается только в {', '.join(sorted_cells)}: для чата из {size_of['small']} "
|
||
f"сообщений планировщику дешевле взять все строки чата Bitmap-сканом по тому же индексу "
|
||
f"и отсортировать их в памяти; строк всё равно не больше размера чата."
|
||
)
|
||
out.append(
|
||
f"**H1 (индекс A, стоимость страницы постоянна) — {'подтверждена' if ok else 'НЕ подтверждена'}.** "
|
||
f"Для Q1 и Q2 на всех чатах (от {size_of['small']} до {size_of['large']} сообщений, "
|
||
f"включая середину чата и чат-всплеск) читается не более {a_rows} строк и {a_buf} буферов, "
|
||
f"время ≤ {fmt_ms(a_ms)} ms. {sort_note}"
|
||
)
|
||
|
||
# Без индекса.
|
||
n_small, n_large = R[("small", "Q1", "none")], R[("large", "Q1", "none")]
|
||
a_small = R[("small", "Q1", "A")]
|
||
out.append(
|
||
f"**Без вторичного индекса** стоимость определяется размером всей таблицы, а не чата: "
|
||
f"первая страница чата из {size_of['small']} сообщений читает {n_small['buffers']} буферов "
|
||
f"({fmt_ms(n_small['ms'])} ms), чата из {size_of['large']} — {n_large['buffers']} буферов "
|
||
f"({fmt_ms(n_large['ms'])} ms). Для маленького чата это в "
|
||
f"{n_small['ms'] / a_small['ms']:.0f}× медленнее индекса A. "
|
||
f"Узел: `{n_small['plan']}`."
|
||
)
|
||
|
||
# H2: C ломается на одинаковых created_at.
|
||
cb, ab = R[("burst", "Q2", "C")], R[("burst", "Q2", "A")]
|
||
cl, al = R[("large", "Q2", "C")], R[("large", "Q2", "A")]
|
||
ok = cb["rows_read"] >= 10 * max(ab["rows_read"], 1)
|
||
out.append(
|
||
f"**H2 (индекс C без id деградирует на одинаковых created_at) — "
|
||
f"{'подтверждена' if ok else 'НЕ подтверждена'}.** "
|
||
f"На обычном большом чате C почти не отличается от A: Q2 {fmt_ms(cl['ms'])} против "
|
||
f"{fmt_ms(al['ms'])} ms, потому что совпадений по секунде мало и Incremental Sort "
|
||
f"сортирует крошечные группы. В чате-всплеске ({size_of['burst']} сообщений в одну секунду) "
|
||
f"условие по id становится Filter, и C читает {cb['rows_read']} строк / {cb['buffers']} "
|
||
f"буферов за {fmt_ms(cb['ms'])} ms, а A — {ab['rows_read']} строк / {ab['buffers']} буферов "
|
||
f"за {fmt_ms(ab['ms'])} ms ({cb['ms'] / ab['ms']:.0f}×). Приложение хранит время с точностью "
|
||
f"до секунды, поэтому такой всплеск реалистичен при нагрузке: `id` в индексе обязателен."
|
||
)
|
||
|
||
# OFFSET.
|
||
q3, q2 = R[("large", "Q3", "A")], R[("large", "Q2", "A")]
|
||
out.append(
|
||
f"**OFFSET против keyset** на середине чата из {size_of['large']} сообщений, индекс A: "
|
||
f"OFFSET читает {q3['rows_read']} строк и {q3['buffers']} буферов ({fmt_ms(q3['ms'])} ms), "
|
||
f"keyset — {q2['rows_read']} строк и {q2['buffers']} буферов ({fmt_ms(q2['ms'])} ms). "
|
||
f"Стоимость OFFSET растёт линейно с глубиной, поэтому приложение использует курсор."
|
||
)
|
||
|
||
# Запись.
|
||
if W:
|
||
def stats(v):
|
||
tps = [r["tps"] for r in W[v]]
|
||
return median(tps), min(tps), max(tps), median([r["wal_per_tx"] for r in W[v]])
|
||
base_tps, base_min, base_max, base_wal = stats("none")
|
||
lines = []
|
||
for v in ("C", "A", "A+C"):
|
||
tps, mn, mx, wal = stats(v)
|
||
overlap = mn <= base_max and mx >= base_min
|
||
lines.append(
|
||
f"{v}: WAL {wal - base_wal:+.0f} B/вставку ({(wal / base_wal - 1) * 100:+.0f}%), "
|
||
f"TPS {(tps / base_tps - 1) * 100:+.1f}%"
|
||
+ (" (в пределах разброса прогонов)" if overlap else "")
|
||
)
|
||
out.append(
|
||
"**H3 (каждый вторичный индекс удорожает запись) — по WAL подтверждена.** "
|
||
f"База без вторичных индексов: {base_wal:.0f} B WAL на вставку, медиана {base_tps:.0f} TPS. "
|
||
+ "; ".join(lines) + ". "
|
||
"Объём WAL детерминирован и растёт с каждым индексом; TPS на локальном диске и "
|
||
"4 клиентах ограничен fsync при COMMIT, поэтому разница по TPS меньше и частично "
|
||
"тонет в шуме — это ограничение замера, а не отсутствие цены."
|
||
)
|
||
|
||
sa, sc = sizes.get("A"), sizes.get("C")
|
||
if sa and sc:
|
||
out.append(
|
||
f"**Размер:** A = {sa / 1024 / 1024:.1f} MiB, C = {sc / 1024 / 1024:.1f} MiB: "
|
||
f"`id` в ключе добавляет {(sa / sc - 1) * 100:.0f}% к размеру индекса."
|
||
)
|
||
|
||
out.append(
|
||
"**Решение:** оставить только A `(chat_id, created_at, id)`. Он один обслуживает и "
|
||
"первую, и любую следующую страницу за константное число буферов независимо от "
|
||
"размера чата и совпадений времени. C экономит немного места, но ломается на всплесках; "
|
||
"A+C платит за второй индекс при каждой записи без выигрыша для чтения."
|
||
)
|
||
return out
|
||
|
||
|
||
def render(chats, R, plans, W, sizes, args, env, dataset):
|
||
md = ["# Track 2. План запроса истории и стоимость записи", "",
|
||
"Сгенерировано `python3 scripts/track2_indexes.py "
|
||
f"--read-reps {args.read_reps} --write-seconds {args.write_seconds} "
|
||
f"--write-rounds {args.write_rounds} --clients {args.clients}`.", "",
|
||
"## Окружение и данные", "", *env, *dataset, "",
|
||
"Выбранные чаты:", "",
|
||
"| Метка | Чат | Сообщений |", "|---|---|---|"]
|
||
for label, (cid, title, n) in chats:
|
||
md.append(f"| {label} | `{title}` | {n} |")
|
||
md += ["",
|
||
"## Гипотезы", "",
|
||
"- **H1.** С индексом A `(chat_id, created_at, id)` страница истории читает ≈limit строк "
|
||
"независимо от размера чата и глубины курсора; без индекса читается вся таблица.",
|
||
"- **H2.** Индекс C `(chat_id, created_at)` без `id` эквивалентен A, пока `created_at` "
|
||
"уникален, но деградирует, когда много сообщений приходится на одну секунду.",
|
||
"- **H3.** Каждый вторичный индекс увеличивает стоимость INSERT (WAL, latency).", "",
|
||
"## Методика", "",
|
||
"- Запросы — те же, что в `MessageRepo::list_page`: `LIMIT 51` (limit+1), "
|
||
"порядок `(created_at, id)`. Курсор Q2 и OFFSET Q3 указывают на середину чата.",
|
||
f"- Для каждого варианта индексы пересоздаются, затем `ANALYZE`. "
|
||
f"`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)`: 1 прогрев + {args.read_reps} замеров, "
|
||
"медиана Execution Time; buffers = shared hit + read последнего замера; "
|
||
"rows_read — строки, прочитанные листовым узлом скана (включая отброшенные Filter).",
|
||
f"- Запись: `pgbench -c {args.clients} -j {args.clients} -T {args.write_seconds}`, "
|
||
"одна вставка на транзакцию в чат large, `created_at` с точностью до секунды как в "
|
||
f"приложении. {args.write_rounds} раунда с циклическим сдвигом порядка вариантов; "
|
||
"перед прогоном `DELETE` тестовых строк, `VACUUM`, `CHECKPOINT` (чтобы full-page writes "
|
||
"были в равных условиях); autovacuum на `messages` выключен на время бенча. "
|
||
"WAL = `pg_wal_lsn_diff` за прогон / число транзакций.", "",
|
||
"## Результаты: чтение", "",
|
||
"Время в ms (медиана) / buffers / rows_read.", "",
|
||
"| Чат | Запрос | none | C | A |", "|---|---|---|---|---|"]
|
||
for label in CHAT_ORDER:
|
||
for q in QUERIES:
|
||
cells = []
|
||
for v, _ in READ_VARIANTS:
|
||
r = R[(label, q, v)]
|
||
cells.append(f"{fmt_ms(r['ms'])} / {r['buffers']} / {r['rows_read']}")
|
||
md.append(f"| {label} | {q} {QUERY_TITLE[q]} | " + " | ".join(cells) + " |")
|
||
md += ["", "Узлы планов:", "", "| Чат | Запрос | Вариант | План |", "|---|---|---|---|"]
|
||
for label in CHAT_ORDER:
|
||
for q in QUERIES:
|
||
for v, _ in READ_VARIANTS:
|
||
md.append(f"| {label} | {q} | {v} | `{R[(label, q, v)]['plan']}` |")
|
||
|
||
if W:
|
||
md += ["", "## Результаты: запись", "",
|
||
"| Вариант | TPS медиана (min–max) | latency avg, ms | WAL, B/вставку | Прогоны TPS |",
|
||
"|---|---|---|---|---|"]
|
||
for v, _ in WRITE_VARIANTS:
|
||
tps = [r["tps"] for r in W[v]]
|
||
md.append(
|
||
f"| {v} | {median(tps):.0f} ({min(tps):.0f}–{max(tps):.0f}) | "
|
||
f"{median([r['lat'] for r in W[v]]):.3f} | "
|
||
f"{median([r['wal_per_tx'] for r in W[v]]):.0f} | "
|
||
f"{', '.join(f'{t:.0f}' for t in tps)} |")
|
||
md += ["", "Размеры индексов:", "", "| Индекс | Ключ | Размер |", "|---|---|---|"]
|
||
pkey = int(db.scalar("SELECT pg_relation_size('messages_pkey');"))
|
||
md.append(f"| messages_pkey | (id) | {pkey / 1048576:.1f} MiB |")
|
||
for key in ("C", "A"):
|
||
if key in sizes:
|
||
md.append(f"| {INDEXES[key][0]} | {INDEXES[key][1]} | {sizes[key] / 1048576:.1f} MiB |")
|
||
|
||
md += ["", "## Выводы", ""]
|
||
for c in conclusions(chats, R, W, sizes):
|
||
md += [c, ""]
|
||
md += ["## Ограничения", "",
|
||
"- Один хост, тёплый кэш (данные помещаются в shared_buffers/page cache): "
|
||
"время отражает CPU и число буферов, а не холодное чтение с диска.",
|
||
"- Литералы в запросах вместо параметров: приложение через libpqxx может получить "
|
||
"generic plan; для этих запросов выбор индекса одинаков, но это не проверялось.",
|
||
"- Данные синтетические, один отправитель, короткие тексты; распределение времени "
|
||
"внутри чата равномерное в окне.",
|
||
"- pgbench мерит БД без HTTP и Argon2; TPS ограничен fsync при COMMIT на локальном "
|
||
"диске, поэтому разница по TPS меньше разницы по WAL.",
|
||
"",
|
||
"## Ключевые планы (EXPLAIN ANALYZE, BUFFERS)", ""]
|
||
for (label, q, v), text in plans.items():
|
||
md += [f"### {label} {q} — {v}", "", "```", text, "```", ""]
|
||
return "\n".join(md)
|
||
|
||
|
||
def main():
|
||
p = argparse.ArgumentParser(description=__doc__.split("\n")[0])
|
||
p.add_argument("--read-reps", type=int, default=7)
|
||
p.add_argument("--write-seconds", type=int, default=15)
|
||
p.add_argument("--write-rounds", type=int, default=3)
|
||
p.add_argument("--clients", type=int, default=4)
|
||
p.add_argument("--skip-write", action="store_true")
|
||
args = p.parse_args()
|
||
|
||
chats = pick_chats(db)
|
||
env, dataset = environment_lines(db), dataset_lines(db)
|
||
print("\n".join(env + dataset))
|
||
for label, (_, title, n) in chats:
|
||
print(f" {label}: {title} ({n})")
|
||
|
||
try:
|
||
R, plans, sizes = read_phase(chats, args.read_reps)
|
||
W = None
|
||
if not args.skip_write:
|
||
large_id = dict(chats)["large"][0]
|
||
W = write_phase(large_id, args.write_seconds, args.write_rounds, args.clients)
|
||
finally:
|
||
set_indexes(["A"])
|
||
print("\nиндексы возвращены к состоянию миграций: messages_pkey + messages_chat_keyset_idx")
|
||
|
||
write_evidence("track2_indexes.md", render(chats, R, plans, W, sizes, args, env, dataset))
|
||
|
||
|
||
if __name__ == "__main__":
|
||
main()
|